Auto filling multiple combo/list boxes

[slenish]

I have a new problem that I am playing with involving the combo boxes. Alright now im going to try to explain this as best I can and hope (NeoPa) doesnt notice if I mess up :D

This is going to be a tie in to what I was doing last time with the combo boxes and trying to make the auto-fill and update.

What im trying to do is make it so I can type information in to one Combo Box such as an ID number and it will look up on the table and pull the rest of the information associated with that ID number and auto fill in other combo boxes with the right information.

So for example (on my form), I have a box labeled Company ID, Then I also have another set of boxes with company location broken in to 3 boxes that have city, state, and zip code. What I want is if I type in the Company ID it will reference that with an ID number on my table and then fill in the other boxes with the matching City, State, and Zip.

Im hoping this is clear enough.

[yarbrough40]

have you tried using a DLookup in the OnChange() event of the box your typing into? Then inserting the returned value into your ComboBoxes?

[slenish]

I wasnt sure if I should go with the DLookUp function or a code for doing a cascading combo/list box??

Also I read over the DLookUp function more. Would I put that function in each box or only the ID number box and it will know to fill in the other boxes?

[yarbrough40]

you will need to put it in the OnChange() event of the box where your users are inputting the ID number. The code you write in that event will handle adding the items into the combo boxes. (I'm assuming you are familiar with writing VBA code in event procedures)

[slenish]

Im not real familar with VBA code yet. Im trying my best. I have this so far for the DLookUp.

=DLookUp("[Facility City]", "Facility Info Test 2", _
      "[Facility ID] = Form![Facility ID]")

Except im getting an error saying, "Expected line number, or statement, or end of statement. Im not sure what I need to declare at the beginning of this??

[yarbrough40]

ok so looks like you're getting there... forgive me but I am not in a place where I have an instance of Access to refer to but I believe you are missing "&" in your third parameter to properly concatenate your string... like so:

 
 =DLookUp("[Facility City]", "Facility Info Test 2", _
       "[Facility ID] = " & Form![Facility ID]")

what you will want to do is (in the OnUpdate() event of the box being typed into only):

• declare a string variable

• assign the value of that variable to the result of the DLookup()

• insert the variable's value into your combo boxes

[slenish]

Ok here is where I am now.

Dim FacilityID As String

Me!txtFacilityID = DLookup("[Facility City]", "tblFacilityInfo","[Facility ID] = & "[B]Form[/B]![Facility ID])

Now you said insert the variables value into the other boxes. Do I use an if statement? Or just something like txtFacilityID = Combo20. Im not sure the right way to declare this?

also im getting an error message still saying I need a list seperator at the part I highlighted. Any idea what im missing?

Really appercaite the help. Also who is the person in your picture?? I feel like i know that face but cant place it.

[TheSmileyCoder]

If the secondary boxes are just for displaying the info, I would do a left join in my recordsource, and simply pull the data in via the query. Once you fill in the combobox the textboxes will be automatically filled.

[yarbrough40]

I agree - that is an easier approach

[slenish]

Well the other boxes are not just for displaying information that is part of the problem.

See right now I have a list of about 100 ID's that go with different locations. So I wont be using this same 100 over and over, I will also be updating the 100 as well. So I want to make it so if I type in an ID that is new I can type in the city, state, and zip to go along with it and it will save it on the back end for later use.

[TheSmileyCoder]

In that case I would still use the same approach, and then when you type in something thats not in the combo's list, you can use the NotInList event to open a seperate form for editing this information.

[slenish]

How would I use left join?

Like This?

SELECT tblA.id, IsNull([tblB].[id]) AS flgB, IsNull([tblC].[Id]) AS flgC
FROM (tblA LEFT JOIN tblB ON tblA.id = tblB.id) LEFT JOIN tblC ON tblA.id = tblC.id;

Also if this is the right approcach that means I have to make 3 seperate tables and make an ID number to link each thing together right? Could I use this same approach with all combo boxs or does it have to be mixed with some text boxs?