Thread: Linking Info in Forms
06-02-04, 12:43 #1Registered User
- Join Date
- Jun 2004
Unanswered: Linking Info in Forms
I want to drop down a combo box...choose an agency...then have two different text boxes fill in with that agency's address and phone number.
and i am stumped...any ideas out there? i would greatly appreciate it!
06-02-04, 13:01 #2Cavalier King Charles
- Join Date
- Dec 2002
- Préverenges, Switzerland
as a first pass, let the wizard take the strain.
switch the controls wizard on, make a new combo, answer the questions.
izycurrently using SS 2008R2
06-03-04, 11:25 #3Registered User
Originally Posted by captainmcdoogs2
- Join Date
- Jun 2004
instead of making 2 text boxes, use one list box. the reason for this is with list boxes and combo boxes, you can use a row source query to get all of the information you need. Here's what you do.....
create the list box(you can use the wizard if you want, but it's not necessary--since you will be getting the data from the row source query. Now, go into the properties of the listbox and click on rowsource(it's under "data" and "all". three little dots should come up to the right of where you clicked. click on the dots and it will bring up the query builder. Now, choose the table(s) that the address and phone number are in. double click on the address and the phone number so that they show up on the query builder.
Now, this is where it gets fun... The value that's in the combo box(the agency), you need that. Whatever the value is that goes into the table--whether it's the agency name or a agency number for like a primary key or something. if you don't know what the value is that the combobox stores, check the rowsource for the combobox, and the first field in the query is the field that is stored in the table. That's what you need.
anyway, if you're not in the query builder for the listbox, go back in. now, find the table that the agency value is in, and double click on the field that stores in your combobox. what we want to say now, is when the value in the combobox equals the value in the table, show me what i want to see
example: when cboAgency = yourtable.agency then show me address and phone number <<<---------not actual code
so in order to do this, we need to click on criteria for that agency field and say [forms]![formname]![comboboxname]
that tells it to query only fields that equal the combo box
just a few more things you need to make sure you do or it won't work:
go into your combo box and go into the row source. right click on the query builder and select "totals". the fields in the combobox have to say "group by", which is default when you select totals.
you only need to do one more thing...in the properties for the combobox, go to "on enter"(in "all" or "event"), click the three little dots, choose code builder, and put in this code exactly(except change the combobox name)
That tells it to requery everytime you change the value in the combobox. it's also a good idea to put the same piece of code under the form properties(change the combobox on the properties window to "form". under "event" go to "on load" and put in the same code. this requeries when the form first opens, in case your combobox automatically opens with a record
i hope i understood your problem and that this helps you out some