By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,286 Members | 1,673 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,286 IT Pros & Developers. It's quick & easy.

Auto filling multiple combo/list boxes

100+
P: 283
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.
Mar 17 '10 #1
Share this Question
Share on Google+
11 Replies


yarbrough40
100+
P: 320
have you tried using a DLookup in the OnChange() event of the box your typing into? Then inserting the returned value into your ComboBoxes?
Mar 17 '10 #2

100+
P: 283
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?
Mar 17 '10 #3

yarbrough40
100+
P: 320
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)
Mar 17 '10 #4

100+
P: 283
Im not real familar with VBA code yet. Im trying my best. I have this so far for the DLookUp.
Expand|Select|Wrap|Line Numbers
  1. =DLookUp("[Facility City]", "Facility Info Test 2", _
  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??
Mar 17 '10 #5

yarbrough40
100+
P: 320
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:
Expand|Select|Wrap|Line Numbers
  1.  
  2.  =DLookUp("[Facility City]", "Facility Info Test 2", _
  3.        "[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
Mar 17 '10 #6

100+
P: 283
Ok here is where I am now.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim FacilityID As String
  3.  
  4. Me!txtFacilityID = DLookup("[Facility City]", "tblFacilityInfo","[Facility ID] = & "Form![Facility ID])
  5.  
  6.  
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.
Mar 17 '10 #7

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 17 '10 #8

yarbrough40
100+
P: 320
I agree - that is an easier approach
Mar 17 '10 #9

100+
P: 283
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.
Mar 17 '10 #10

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Mar 17 '10 #11

100+
P: 283
How would I use left join?

Like This?
Expand|Select|Wrap|Line Numbers
  1. SELECT tblA.id, IsNull([tblB].[id]) AS flgB, IsNull([tblC].[Id]) AS flgC
  2. 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?
Mar 17 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.