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 |
#1
|
|||
|
|||
Linking 2 fields in a form
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. |
#2
|
|||
|
|||
Linking 2 fields in a form
The combo box should be an unbound control and the text box should be the
bound control. It has nothing to do with the tables. You use the After Update event of the combo box to populate the text box: Private Sub MyCombo_AfterUpdate() Me.MyTextBox = Me.MyCombo End Sub -- Dave Hargis, Microsoft Access MVP " 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. |
#3
|
|||
|
|||
Linking 2 fields in a form
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. |
#4
|
|||
|
|||
Linking 2 fields in a form
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. |
#5
|
|||
|
|||
Linking 2 fields in a form
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 - |
#6
|
|||
|
|||
Linking 2 fields in a form
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 - |
#7
|
|||
|
|||
Linking 2 fields in a form
Correct. Me is a shortcut designator that refers to the current form or
report. Be aware also that the Me reference can only be used in the form's code module. If you call a sub or function that is in a standard or class module, you have to use a fully qualified reference to the form. -- Dave Hargis, Microsoft Access MVP " 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 - |
#8
|
|||
|
|||
Linking 2 fields in a form
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 - |
#9
|
|||
|
|||
Linking 2 fields in a form
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 - |
#10
|
|||
|
|||
Linking 2 fields in a form
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 - |
|
Thread Tools | |
Display Modes | |
|
|