Updating a field based on value in another field 2

[sms28]

I have a form with 2 combo boxes, Box A and Box B. Box A contains the list of Agency IDs and Box B contains the Agency Names. When the user select a value from Box A, I want to replace the value in Box B with the name that corresponds to the Agency ID just selected in Box A. I need to the reverse as well, i.e. when a name is selected from Box B, I need to update the value in Box A with matching Agency ID. The Agency ID and Agency Name are in table Agency_list with Agency ID as the key.

 

[WildHare]

It sounds a bit like you're working at cross purposes. First off, if there's a one-to-one relationship between the ID and the Name, why do you need two combos? Are you using this combo to ASSIGN a value to a field, or to use as a selection or search critieria?

You use the term "update", but I'm not sure exactly what you are trying to do. If you can clear up some confusion, I'm sure there's a simple answer for you.

Jim
Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[sms28]

To simplify. Let's say the form contains 1 combo box [Agency_ID] and a text box [Agency_Name]. When the user selects a value in the combo box, I want its name to appear in the text box. The two fields are found in a table called Agency_List with Agency_ID as the key.

 

[WildHare]

Ok - that makes a little more sense. Your first post did say TWO combo boxes..

Make the source for the first combo box a query that grabs the ID and the NAME. Set the combo box to have TWO columns, and make the ID the bound column. Set the column widths to an appropriate width greater than 0 inches for the ID, and 0 (zero) for the name (the name will not be visible in the drop-down).

In the AFTER UPDATE event of the combo box, set the TEXT box to contain the 2nd column of the combo:

Me!TextBox = me!ComboBox.Columns(1)

I guess my question now is, why are you using the ID in the first place? Is that the value that the user will know/have? Wouldn't it be easier to just build the combo box with the NAME itself? A drop down of alphabetical names is generally much easier to use than one of numbers, unless your users have ONLY the numbers, and need to get the names.

In any case, the additional columns of a combo box can be referenced using the COLUMNS property, with the number being ZERO based - the first column is Columns(0), the second column Columns(1) and so on. Setting the column width to zero is an easy way of including multiple values on a 'line' of a combo box, but keeping them unseen by the user. You can then reference them later on.

Jim




Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[sms28]

Jim,

Thanks for your response. That solution worked. I agree presenting the list of names is preferable, but on this particular form the user requested the numbers. We do have several dialogs that present this list of names.

Thanks again.

 

[WildHare]

I'm just checking up on my threads there - and wondering why some one appears to have marked your questions with "stars", but not my answers.. LOL



JMH


(PS - I'll really start worrying about 'stars' when they start payin' us for 'em.)

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[attrofy]

Here Jim, I'll give you a star. You have answered quite a few of my questions - albeit vicariously through other posters threads. This sparked some ideas on a como box I am working on.

Russell