If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Linking 2 fields in a form
I understand what you are saying, but I can't get a clear explanation on how
the combo is set up or what table fields are bound to what controls. For example, what is the Column Count property of the combo? What is the Bound Column property of the combo? Is the Comb bound or unbound. What controls are bound to what fields in the table. I can't help if I don't know this info. -- Dave Hargis, Microsoft Access MVP " wrote: Thanks for the lengthy reply. I understand the explanation. However, I don't think I have explained very well... I have a table (MAIN) which contains all info, updated from the Form. Two of the fields in this table are EventItemName (Text) and EventItemID (Number). A separate table (EventInfo) contains only 2 Fields - EventItemName (Text) and EventItemID (Number). This table is used for the Form Combo Box (EventItemName). On the Form, I want to save the user time as each Event Item Name has a unique ID. So when they select the Name in the combo, the Text Box automatically updates with the associated ID. However, I then want both the Name and ID to be updated in the MAIN Table. As it stands, the AfterUpdate code is making both Fields in this table the same (EventItemID). Hope this makes it a little clearer! On Apr 22, 5:44 pm, Klatuu wrote: The control source property of a control will tell you whether it is a bound or unbound control. A bound control will have the name of a field in the form's record source in the property. An unbound control will have either nothing or an expression beginning with an =. An expression beginning with an = is use to display a value to a user, but does not update the form's recordset and cannot be edited. But, this is not your problem. A combo box also has a Bound Column property in addition to the Control Source property. Don't confuse the two. The Bound Column property of a combo box identifies which column in the combo's row source the combo will return. So if you have a combo with a Bound Column of 1 and a Control Source of SomeField, then the value of the first column in the combo's row source will be the value that is save in the form's record source. The problem is how you are populating the text box. If you have a two column combo box with EventID being the first column and Event being the second column and the Bound Column is 1, then if you say =Me.MyCombo It will return the EventID value You can do one of two things. 1. You can change the bound column to 2 which will then return the Event column 2. You can use the combo's Columns collection. Here is can get confusing because the columns collection index begins with 0. So to return the value of the first column: =Me.MyCombo.Column(0) 'The first column =Me.MyCombo.Column(1) 'This returns the second column -- Dave Hargis, Microsoft Access MVP " wrote: Hi again...on checking, the table which is the control source now contains the same data for both Event and Event ID fields. I have a separate table for the Row Source which contains the Event and the associated Event ID. How do I fix this....how do I ensure that the combo is the unbound control and the textbox the bound control? On Apr 22, 11:59 am, wrote: Never mind - got it...stays as "Me" - works great - thanks! On Apr 22, 11:49 am, wrote: Thanks Dave. Forgive my ignorance, but what do I type in place of the "ME" On Apr 21, 4:45 pm, Klatuu wrote: Cascading combos is not relavent to this post as there is only one combo involved. -- Dave Hargis, Microsoft Access MVP "TC" wrote: Search for Cascading Combo boxes there are many posts on how to do this. TC " wrote: Hi I have 2 fields in a Form – one for Event (Combo Box) and the other Event ID (Text Box). How can I have the Event ID field populate automatically when an Event is selected? How would I need to structure the tables to accomplish this? Many thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#12
|
|||
|
|||
Linking 2 fields in a form
What is the Column Count property of the combo? 1
What is the Bound Column property of the combo? 2 Is the Comb bound or unbound? Bound (Surely if it’s unbound, it doesn’t write the selection back to the MAIN Table?) What controls are bound to what fields in the table? Combo bound to EventItem_Name and Text box bound to EventItemID On Apr 23, 3:31*pm, Klatuu wrote: I understand what you are saying, but I can't get a clear explanation on how the combo is set up or what table fields are bound to what controls. *For example, what is the Column Count property of the combo? What is the Bound Column property of the combo? *Is the Comb bound or unbound. *What controls are bound to what fields in the table. I can't help if I don't know this info. -- Dave Hargis, Microsoft Access MVP " wrote: Thanks for the lengthy reply. *I understand the explanation. *However, I don't think I have explained very well... I have a table (MAIN) which contains all info, updated from the Form. Two of the fields in this table are EventItemName (Text) and EventItemID (Number). *A separate table (EventInfo) contains only 2 Fields - EventItemName (Text) and EventItemID (Number). *This table is used for the Form Combo Box (EventItemName). On the Form, I want to save the user time as each Event Item Name has a unique ID. *So when they select the Name in the combo, the Text Box automatically updates with the associated ID. *However, I then want both the Name and ID to be updated in the MAIN Table. *As it stands, the AfterUpdate code is making both Fields in this table the same (EventItemID). Hope this makes it a little clearer! On Apr 22, 5:44 pm, Klatuu wrote: The control source property of a control will tell *you whether it is a bound or unbound control. *A bound control will have the name of a field in the form's record source in the property. *An unbound control will have either nothing or an expression beginning with an =. *An expression beginning with an = is use to display a value to a user, but does not update the form's recordset and cannot be edited. But, this is not your problem. A combo box also has a Bound Column property in addition to the Control Source property. *Don't confuse the two. *The Bound Column property of a combo box identifies which column in the combo's row source the combo will return. *So if you have a combo with a Bound Column of 1 and a Control Source of SomeField, then the value of the first column in the combo's row source will be the value that is save in the form's record source. The problem is how you are populating the text box. *If you have a two column combo box with EventID being the first column and Event being the second column and the Bound Column is 1, then if you say =Me.MyCombo It will return the EventID value You can do one of two things. 1. You can change the bound column to 2 which will then return the Event column 2. You can use the combo's Columns collection. *Here is can get confusing because the columns collection index begins with 0. *So to return the value of the first column: =Me.MyCombo.Column(0) *'The first column =Me.MyCombo.Column(1) *'This returns the second column -- Dave Hargis, Microsoft Access MVP " wrote: Hi again...on checking, the table which is the control source now contains the same data for both Event and Event ID fields. *I have a separate table for the Row Source which contains the Event and the associated Event ID. How do I fix this....how do I ensure that the combo is the unbound control and the textbox the bound control? On Apr 22, 11:59 am, wrote: Never mind - got it...stays as "Me" - works great - thanks! On Apr 22, 11:49 am, wrote: Thanks Dave. *Forgive my ignorance, but what do I type in place of the "ME" On Apr 21, 4:45 pm, Klatuu wrote: Cascading combos is not relavent to this post as there is only one combo involved. -- Dave Hargis, Microsoft Access MVP "TC" wrote: Search for Cascading Combo boxes there are many posts on how to do this. TC " wrote: Hi I have 2 fields in a Form – one for Event (Combo Box) and the other Event ID (Text Box). *How can I have the Event ID field populate automatically when an Event is selected? *How would I need to structure the tables to accomplish this? Many thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#13
|
|||
|
|||
Linking 2 fields in a form
No, an unbound control will not update anything in a table, but it is common
to use an unbound combo for search when you don't want the value of the current record changed. You know, I did forget to ask one thing, and that is what the row source of the combo is. If it is EventID, EventName, then that is the problem. The combo having the bound column of 1, but bound to the EventName, will put the EventID in the EventName field. And if the After Update event of the combo is Me.TheTextBox = Me.TheComboBox, it will also put the EventID in the EventID field. If this is correct, all you need to do is bind the combo to the EventID field and the text box to the EventName field -- Dave Hargis, Microsoft Access MVP " wrote: What is the Column Count property of the combo? 1 What is the Bound Column property of the combo? 2 Is the Comb bound or unbound? Bound (Surely if it’s unbound, it doesn’t write the selection back to the MAIN Table?) What controls are bound to what fields in the table? Combo bound to EventItem_Name and Text box bound to EventItemID On Apr 23, 3:31 pm, Klatuu wrote: I understand what you are saying, but I can't get a clear explanation on how the combo is set up or what table fields are bound to what controls. For example, what is the Column Count property of the combo? What is the Bound Column property of the combo? Is the Comb bound or unbound. What controls are bound to what fields in the table. I can't help if I don't know this info. -- Dave Hargis, Microsoft Access MVP " wrote: Thanks for the lengthy reply. I understand the explanation. However, I don't think I have explained very well... I have a table (MAIN) which contains all info, updated from the Form. Two of the fields in this table are EventItemName (Text) and EventItemID (Number). A separate table (EventInfo) contains only 2 Fields - EventItemName (Text) and EventItemID (Number). This table is used for the Form Combo Box (EventItemName). On the Form, I want to save the user time as each Event Item Name has a unique ID. So when they select the Name in the combo, the Text Box automatically updates with the associated ID. However, I then want both the Name and ID to be updated in the MAIN Table. As it stands, the AfterUpdate code is making both Fields in this table the same (EventItemID). Hope this makes it a little clearer! On Apr 22, 5:44 pm, Klatuu wrote: The control source property of a control will tell you whether it is a bound or unbound control. A bound control will have the name of a field in the form's record source in the property. An unbound control will have either nothing or an expression beginning with an =. An expression beginning with an = is use to display a value to a user, but does not update the form's recordset and cannot be edited. But, this is not your problem. A combo box also has a Bound Column property in addition to the Control Source property. Don't confuse the two. The Bound Column property of a combo box identifies which column in the combo's row source the combo will return. So if you have a combo with a Bound Column of 1 and a Control Source of SomeField, then the value of the first column in the combo's row source will be the value that is save in the form's record source. The problem is how you are populating the text box. If you have a two column combo box with EventID being the first column and Event being the second column and the Bound Column is 1, then if you say =Me.MyCombo It will return the EventID value You can do one of two things. 1. You can change the bound column to 2 which will then return the Event column 2. You can use the combo's Columns collection. Here is can get confusing because the columns collection index begins with 0. So to return the value of the first column: =Me.MyCombo.Column(0) 'The first column =Me.MyCombo.Column(1) 'This returns the second column -- Dave Hargis, Microsoft Access MVP " wrote: Hi again...on checking, the table which is the control source now contains the same data for both Event and Event ID fields. I have a separate table for the Row Source which contains the Event and the associated Event ID. How do I fix this....how do I ensure that the combo is the unbound control and the textbox the bound control? On Apr 22, 11:59 am, wrote: Never mind - got it...stays as "Me" - works great - thanks! On Apr 22, 11:49 am, wrote: Thanks Dave. Forgive my ignorance, but what do I type in place of the "ME" On Apr 21, 4:45 pm, Klatuu wrote: Cascading combos is not relavent to this post as there is only one combo involved. -- Dave Hargis, Microsoft Access MVP "TC" wrote: Search for Cascading Combo boxes there are many posts on how to do this. TC " wrote: Hi I have 2 fields in a Form – one for Event (Combo Box) and the other Event ID (Text Box). How can I have the Event ID field populate automatically when an Event is selected? How would I need to structure the tables to accomplish this? Many thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#14
|
|||
|
|||
Linking 2 fields in a form
Thanks...found a slight addition to your AfterUpdate code and it
works! Me!TextBoxName = Me!ComboName.Column(1) On Apr 23, 4:30*pm, Klatuu wrote: No, an unbound control will not update anything in a table, but it is common to use an unbound combo for search when you don't want the value of the current record changed. You know, I did forget to ask one thing, and that is what the row source of the combo is. *If it is EventID, EventName, then that is the problem. *The combo having the bound column of 1, but bound to the EventName, will put the EventID in the EventName field. *And if the After Update *event of the combo is * * Me.TheTextBox = Me.TheComboBox, it will also put the EventID in the EventID field. If this is correct, all you need to do is bind the combo to the EventID field and the text box to the EventName field -- Dave Hargis, Microsoft Access MVP " wrote: What is the Column Count property of the combo? 1 What is the Bound Column property of the combo? 2 Is the Comb bound or unbound? Bound (Surely if it’s unbound, it doesn’t write the selection back to the MAIN Table?) What controls are bound to what fields in the table? Combo bound to EventItem_Name and Text box bound to EventItemID On Apr 23, 3:31 pm, Klatuu wrote: I understand what you are saying, but I can't get a clear explanation on how the combo is set up or what table fields are bound to what controls. *For example, what is the Column Count property of the combo? What is the Bound Column property of the combo? *Is the Comb bound or unbound. *What controls are bound to what fields in the table. I can't help if I don't know this info. -- Dave Hargis, Microsoft Access MVP " wrote: Thanks for the lengthy reply. *I understand the explanation. *However, I don't think I have explained very well... I have a table (MAIN) which contains all info, updated from the Form.. Two of the fields in this table are EventItemName (Text) and EventItemID (Number). *A separate table (EventInfo) contains only 2 Fields - EventItemName (Text) and EventItemID (Number). *This table is used for the Form Combo Box (EventItemName). On the Form, I want to save the user time as each Event Item Name has a unique ID. *So when they select the Name in the combo, the Text Box automatically updates with the associated ID. *However, I then want both the Name and ID to be updated in the MAIN Table. *As it stands, the AfterUpdate code is making both Fields in this table the same (EventItemID). Hope this makes it a little clearer! On Apr 22, 5:44 pm, Klatuu wrote: The control source property of a control will tell *you whether it is a bound or unbound control. *A bound control will have the name of a field in the form's record source in the property. *An unbound control will have either nothing or an expression beginning with an =. *An expression beginning with an = is use to display a value to a user, but does not update the form's recordset and cannot be edited. But, this is not your problem. A combo box also has a Bound Column property in addition to the Control Source property. *Don't confuse the two. *The Bound Column property of a combo box identifies which column in the combo's row source the combo will return. *So if you have a combo with a Bound Column of 1 and a Control Source of SomeField, then the value of the first column in the combo's row source will be the value that is save in the form's record source. The problem is how you are populating the text box. *If you have a two column combo box with EventID being the first column and Event being the second column and the Bound Column is 1, then if you say =Me.MyCombo It will return the EventID value You can do one of two things. 1. You can change the bound column to 2 which will then return the Event column 2. You can use the combo's Columns collection. *Here is can get confusing because the columns collection index begins with 0. *So to return the value of the first column: =Me.MyCombo.Column(0) *'The first column =Me.MyCombo.Column(1) *'This returns the second column -- Dave Hargis, Microsoft Access MVP " wrote: Hi again...on checking, the table which is the control source now contains the same data for both Event and Event ID fields. *I have a separate table for the Row Source which contains the Event and the associated Event ID. How do I fix this....how do I ensure that the combo is the unbound control and the textbox the bound control? On Apr 22, 11:59 am, wrote: Never mind - got it...stays as "Me" - works great - thanks! On Apr 22, 11:49 am, wrote: Thanks Dave. *Forgive my ignorance, but what do I type in place of the "ME" On Apr 21, 4:45 pm, Klatuu wrote: Cascading combos is not relavent to this post as there is only one combo involved. -- Dave Hargis, Microsoft Access MVP "TC" wrote: Search for Cascading Combo boxes there are many posts on how to do this. TC " wrote: Hi I have 2 fields in a Form – one for Event (Combo Box) and the other Event ID (Text Box). *How can I have the Event ID field populate automatically when an Event is selected? *How would I need to structure the tables to accomplish this? Many thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
#15
|
|||
|
|||
Linking 2 fields in a form
That will work
-- Dave Hargis, Microsoft Access MVP " wrote: Thanks...found a slight addition to your AfterUpdate code and it works! Me!TextBoxName = Me!ComboName.Column(1) On Apr 23, 4:30 pm, Klatuu wrote: No, an unbound control will not update anything in a table, but it is common to use an unbound combo for search when you don't want the value of the current record changed. You know, I did forget to ask one thing, and that is what the row source of the combo is. If it is EventID, EventName, then that is the problem. The combo having the bound column of 1, but bound to the EventName, will put the EventID in the EventName field. And if the After Update event of the combo is Me.TheTextBox = Me.TheComboBox, it will also put the EventID in the EventID field. If this is correct, all you need to do is bind the combo to the EventID field and the text box to the EventName field -- Dave Hargis, Microsoft Access MVP " wrote: What is the Column Count property of the combo? 1 What is the Bound Column property of the combo? 2 Is the Comb bound or unbound? Bound (Surely if it’s unbound, it doesn’t write the selection back to the MAIN Table?) What controls are bound to what fields in the table? Combo bound to EventItem_Name and Text box bound to EventItemID On Apr 23, 3:31 pm, Klatuu wrote: I understand what you are saying, but I can't get a clear explanation on how the combo is set up or what table fields are bound to what controls. For example, what is the Column Count property of the combo? What is the Bound Column property of the combo? Is the Comb bound or unbound. What controls are bound to what fields in the table. I can't help if I don't know this info. -- Dave Hargis, Microsoft Access MVP " wrote: Thanks for the lengthy reply. I understand the explanation. However, I don't think I have explained very well... I have a table (MAIN) which contains all info, updated from the Form.. Two of the fields in this table are EventItemName (Text) and EventItemID (Number). A separate table (EventInfo) contains only 2 Fields - EventItemName (Text) and EventItemID (Number). This table is used for the Form Combo Box (EventItemName). On the Form, I want to save the user time as each Event Item Name has a unique ID. So when they select the Name in the combo, the Text Box automatically updates with the associated ID. However, I then want both the Name and ID to be updated in the MAIN Table. As it stands, the AfterUpdate code is making both Fields in this table the same (EventItemID). Hope this makes it a little clearer! On Apr 22, 5:44 pm, Klatuu wrote: The control source property of a control will tell you whether it is a bound or unbound control. A bound control will have the name of a field in the form's record source in the property. An unbound control will have either nothing or an expression beginning with an =. An expression beginning with an = is use to display a value to a user, but does not update the form's recordset and cannot be edited. But, this is not your problem. A combo box also has a Bound Column property in addition to the Control Source property. Don't confuse the two. The Bound Column property of a combo box identifies which column in the combo's row source the combo will return. So if you have a combo with a Bound Column of 1 and a Control Source of SomeField, then the value of the first column in the combo's row source will be the value that is save in the form's record source. The problem is how you are populating the text box. If you have a two column combo box with EventID being the first column and Event being the second column and the Bound Column is 1, then if you say =Me.MyCombo It will return the EventID value You can do one of two things. 1. You can change the bound column to 2 which will then return the Event column 2. You can use the combo's Columns collection. Here is can get confusing because the columns collection index begins with 0. So to return the value of the first column: =Me.MyCombo.Column(0) 'The first column =Me.MyCombo.Column(1) 'This returns the second column -- Dave Hargis, Microsoft Access MVP " wrote: Hi again...on checking, the table which is the control source now contains the same data for both Event and Event ID fields. I have a separate table for the Row Source which contains the Event and the associated Event ID. How do I fix this....how do I ensure that the combo is the unbound control and the textbox the bound control? On Apr 22, 11:59 am, wrote: Never mind - got it...stays as "Me" - works great - thanks! On Apr 22, 11:49 am, wrote: Thanks Dave. Forgive my ignorance, but what do I type in place of the "ME" On Apr 21, 4:45 pm, Klatuu wrote: Cascading combos is not relavent to this post as there is only one combo involved. -- Dave Hargis, Microsoft Access MVP "TC" wrote: Search for Cascading Combo boxes there are many posts on how to do this. TC " wrote: Hi I have 2 fields in a Form – one for Event (Combo Box) and the other Event ID (Text Box). How can I have the Event ID field populate automatically when an Event is selected? How would I need to structure the tables to accomplish this? Many thanks in advance.- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
|
Thread Tools | |
Display Modes | |
|
|