Populate a (different) text box based on selection made in a list box - same form.

[marydoyle9]

I've looked at many of the posts that already address this issue... but am still struggling.

I created a form (AddrChg) with New address information (stored in AddrChg table). On the form, the user will be required to select a county (County table) from a list box (County table - has three columns: county number, county name, fax number). I created another field (text box?) on the form that I would like to have auto-populate with the Counties' fax number (...as mentioned, stored on the County table). So, if a user selected Polk county, the corresponding fax number for that row will autmoatically display in the "Fax Number" test box. The data entered in this form will be written to the AddrChg table.

I've tried a few of the things that were on other posts but I am obvioulsy too new at doing this to get it right cuz I have yet to be successful. I don't believe what I need is a cascading combo box. I fthese two foelds need to be bound, how? Do I need to add an event? If so, to which box (field) and what's the event? If I need to add code PLEASE be very specific - I am NOT a VB programmer (so I would need an explanatin on what it is I am adding and to where and how, etc etc etc). If there is something else I need to do, please keep explanation as simple as possible...

Thanks!!!!!

 

[misscrf]

the bad part of my advice is that you have to hard code the choices into the backend. This means that if the county list updates, you will have to update the code.

Here is why. The code that I would think would do this, looks at the list box (onchange/afterupdate) to see what choice was made. It doesn't look at the text, it looks at the numeric value of the list choice.

That would make this:

If Me.listbox = 1 then
me.textbox = [countytable].[faxfield] where [countynumber = 1]
elseif Me.listbox = 2 then
me.textbox = [countytable].[faxfield] where [countynumber = 2]


This is a horrible excuse for the code you need, but you will probably be able to hammer it out.

 

[marydoyle9]

So does this mean I will have to check each county number? (There are 99 counties.)

 

[Mile-O]

the bad part of my advice is that you have to hard code the choices into the backend.

That is bad advice.

Mary, have a look at my Combobox Examples database.

Also, there should be no need to copy data entered in one table into another.

 

[Mile-O]

If Me.listbox = 1 then
me.textbox = [countytable].[faxfield] where [countynumber = 1]
elseif Me.listbox = 2 then
me.textbox = [countytable].[faxfield] where [countynumber = 2]

misscrf, have you never heard of the SELECT CASE Structure?

i.e.

Select Case Me.Listbox
    Case Is = 1

    Case Is = 2

    Case Else

End Select

Much neater than all those IF THEN ELSEs

 

[marydoyle9]

Woo-hoo!!!! Got it to work!!!

SJ McAbney.........

Thanks so much... and thank you for the examples which made it REAL easy to change.