Requery a Text Box based on another Combo Box 1

[ShellyL]

I've seen similar threads, but nothing seems to fit exactly what I need. Hoping someone can help!

I'm dealing with two tables: An EVENT table, where we list all events, and an ISSUANCE table, where we list one or more surveys related to an event. They are linked so Event.EEvent_ID = Issuance.IEvent_Num.

On the Event table, we name the primary Trip Director ([EEvent_Trip_Director]). On the ISSUANCE Table, we also want to track the specific survey's Trip Director ([IEvent_Trip_Director); this person may or many not be the same as the primary Event director.

Here's what I want to do: I've created a Form with the ISSUANCE table as its record source to Add new "Issuances" (Form Name: [Issuance_Add]).
The first combo box (Cbo_IEvent_Num) has you select the EVENT. After they update this, I'd like to populate the next text box, [Txt_IEvent_Trip_Director], with whatever value is on the EVENT table under [EEvent_Trip_Director]for that EventID. That way, if it's the same Trip Director, a user doesn't have to change anything. However, the SHOULD be able to change the name to someone else if needed.

So how can I get the Trip Director text box on my Issuance form to default to the Trip Director named on the Event table after the Issuance Form Event ID is updated? (While still allowing them to change the value of the field)

PS: I'd like to do this with a few other fields as well, so let me know if I need to do anything different with text vs. numeric data.

THANK YOU!

 

[Pampers]

Are the two tables connected in a way?


Pampers.

You're never too young to learn

 

[PHV]

Have a look at the Column property of the ComboBox object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ShellyL]

Hi Pampers..
Yes, the two tables are related (one-to-many, Event-to-Issuance at Event.EEvent_ID = Issuance.IEvent_Num).

PHV: Not sure what I could look for??

~ShellyL

 

[BSman]

After you select the event in the combo box, then you need to do the following in your VBA code, assuming you also have a table that lists all of the possible event directors. I'm going to assume that the event combo box is named cboEvent with a data source that selects the Event AND the event's director, and the issuance director combo box (cboIDirector) has a data source that lists all directors from the director table list. Although you should use some kind of ID, I'll just assume that you use the full names.

me.cboIDirector = me.cboEvent.column(1)

The confusing part of VBA is that this is referring to the second column, since for this syntax the first column is numbered 0; HOWEVER, in the properties of the combo box when you select the default column number, column #1 means the first column, which would be column(0) in the above expression. The consistency of Microsoft.

 

[ShellyL]

BEAUTIFUL! Once I changed the row source for my cboEvent box to the Event Table, I could easily make several fields for the Issaunce table default to the values in the Event table in the After Update event code! (And yes, I am using an ID for the employee names rather than names.

THANK YOU MUCH FOR THIS SIMPLE SOLUTION!

 

[PHV]

PHV: Not sure what I could look for
So, now you know ;-)