Linking 2 fields in a form

[robingSA]

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.

 

[Klatuu]

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

 

[TC]

Search for Cascading Combo boxes there are many posts on how to do this.
TC

 

[Klatuu]

Cascading combos is not relavent to this post as there is only one combo
involved.

 

[robingSA]

Thanks Dave. Forgive my ignorance, but what do I type in place of the
"ME"

 

[robingSA]

Never mind - got it...stays as "Me" - works great - thanks!

 

[Klatuu]

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.

 

[robingSA]

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?

 

[Klatuu]

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

 

[robingSA]

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!

 

[Klatuu]

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.

 

[robingSA]

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

 

[Klatuu]

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

 

[robingSA]

Thanks...found a slight addition to your AfterUpdate code and it
works!

Me!TextBoxName = Me!ComboName.Column(1)

 

[Klatuu]

That will work