i have two tables.
form 1 is based on the fields from table 1.
table 2 contains fields for names and phone numbers.
on form 1 there is a drop down box which allows me to choose a name from table 2.
form 1 also has a ‘phone number’ field.
i would like the ‘phone number’ field on form 1 to autofill with the phone number from table 2 that corresponds with the name i choose from the drop down box.
i cannot figure out how to do this, yet i’m sure there must be a way (other than to use a subform).
I have limited experience with Access but here are a couple of things you can try:
It’s possible to put “triggers” in data fields so that writing to the field causes something else to happen. In this case, copying the field to table 1.
You can, IIRC, have an underlying Excel spreadsheet supporting your Access database. Keeping data fields in sync is straightforward in Excel.
There is probably a simpler way to do this, but one of us is going to have to read the Access documentation to find it out. Guess which one of us it’s going to be?
Assuming there is one field with identical information in both tables, set up a query. Use the information from Table 1 and 2 as needed. If you want the phone number from Table 2 to fit in with Table 1 data, for instance, use the phone number field from Table 2 and all the rest from Table 1.
IIRC, go to the query tab, click on “new.” Choose “Design view” and click on the two tables you want to include in the query. Close the “show table” window. Make sure you only have two tables. Move the cursor to the field in table 1 and drag it to the identical field in table 2. Then add the fields to the query and test it.
Once the query works (and you may have to play with it), you can make a form out of it.
i actually got to this point before i posted, but i was hoping there was just a way to reference the query from the original form and not have to make a whole new form. but i guess making a new form based on the 2 table query is the best way to do it. thanks for the info.
ZW, look under the help files, what your attempting to do is quite simple and can be done on one form. IIRC, you reference the query every time you enter your first field. So, your rowsource for your first combo box is table1.
The rowsource for the second combo box would be a query based on what’s selected in the first combo. I wish I could continue, but…, back to work!!
I don’t understand why you would not want to use a subform; it would be perfect in this case. Keep in mind that it does not have to ‘look’ like a subform.
ok, i’m listening - how do i make a subform not look like a subform (i.e., no record numbers, arrows, etc.)? aesthetics is why i didn’t want to use a subform.
and bf, that’s the idea i was getting at but the access help files put me through a maze of circular logic. it’s that part where you left off and went back to work that stumps me.
By changing the various properties of the subform (Right click on the left-hand subform ruler, Form Properties, Default View - Single Form), you can force it to displayed as a single form of your own design. If this is the same design as the main form, then it will appear to be one big form. You can get rid of everything commonly associated with a subform such as rulers of scroll bars by playing with these properties or by carefully sizing the subform. The only exception is that you cannot get rid of the record selector as far as I know. This is because you cannot be sure that there will only be one to one much with the main and subforms. Anyway, with this minor limitation, this is still much easier than rid VB triggers all over the place and the result is almost the same.
The trick in using sub-forms is to actually create the form before you use the wizard. Go ahead and make a new form based on the second table, including all the fields. Make is look as close to the first Form as you can. (i.e. same backgrounds, colors, stuff like that) Then when you create the sub-form from whatever the first one was, choose the name field as your master and child. You may have to tweak the sub-form a bit to make it blend in, but that’s no big deal, just takes time.
Also, if you don’t want to use the sub-form, you can use a function called a DLOOKUP but it’s a pain to get working sometimes. Feel free to e-mail me if you need any other suggestions.
thank you atrael. i used to use lookup functions a lot in excel so i was immediately drawn to this suggestion. in the future i will use a multi-table query for this kind of thing, but since the form was already created using subforms and various custom functions, the dlookup function was a good suggestion. you were right about it being a pain to get working - the quotation mark placement was the key. here’s the final syntax (vendor and Ticket being the two tables):
Yeah, if I would have known you’d go that way, I would have warned you about the stupid quotation marks. They screwed me the first time I tried to get it working. It can be a pain to use that function, and there are some instances where it won’t work, but it should do fine for what you’re doing. Have fun, and feel free to drop me a line if you ever need anything else.