Auto fills in forms or Databases. (please help - frustrated) 1

[ursino1]

I am trying to make an auto-fill form or table (mainly working with the table) It has a refrence number and the refrence number has associated fields.
This refrence number and its fields are in Table 1.
I am updating information in Table 2. ( the one I am trying to create). What I want to happen is: when I enter the refrence number in table 2, it will auto fill the associated fields, using table 1.

Example:

Table 1
1234 - Mary - Blue

Table 2

1234 - autofill - autofill - other_table2_fields
----
The number 1234 is what I am entering into that field, and what I want to happen is for the other information (mary, blue ) to auto fill into the 2nd and 3rd field.

I have tried all kinds of relationships and queries and I still can't get what I want. Please help me or at least point me in the right direction. Thanks so much.

 

[Maquis]

Why are the fields you want to auto fill even in table 2? If they are always going to be the same as the fields in table 1, then you don't need them in table 2 at all.

If the fields can be different, but you just want to default them to the values in table 1 then you need to use a form to enter data into table 2 and put some VBA code in the Change or lost focus event of the reference number field. Something like this:

Dim Criteria as string
Criteria = "[reference number] = " & me!reference number
me!autofill1 = dlookup(value1,Table 1,Criteria)
me!autofill2 = dlookup(value2,Table 1,Criteria)

Hope this helps. B-)

 

[ursino1]

That code helps a little bit, It gives an option. I can work with it - thanx for the tip and code.

However, I do need the fields, to be the same. Sort of like entering a Zip code in a database and having the city and state auto populate, which I found a code for, during my search. The reason I need this, is because, even though it duplicates data, I need that same information on this table. I know I query and add them together as an end result, but someone is asking me to put them on the table, sorry.

---Posted by Erika Yoxall---
Add this simple code in the OnExit() event-handler for the field containing the Zipcode.Sub Zip_OnExit(Cancel As Integer)Dim varState, varCity As Variant varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] &quot varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] &quot If (Not IsNull(varState)) Then Me![State] = varState If (Not IsNull(varCity)) Then Me![City] = varCityEnd Sub