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.

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.

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.

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.

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 -

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 -

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 -

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 -

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 -

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 -