Auto Fill Fields

[Quan9r]

Novice user:
I would like to fill a field within a form based on another field. I have a seperate table with both fields "matched" in them.
Do I need to create a macro or query to auto fill the "empty" field?
The table does have one field in it now....I just want to auto fill the other...
Please remember I'm a novice and am unfailiar with "code"

Thank you!

 

[huzza]

Hi,
If I understand this correclty you have tables set out as follows (for example): Table1 stores Surname and Firstname Table2 stores Surname and Address so the two are "matched" on surname.
Now you want your users to enter Firstname on the form and the other field to bring up address, is that correct?
If so then you can use the following:

Private Sub Text1_AfterUpdate()
  Dim dbs As Database, rst As Recordset
  Dim strSQL As String, LastName as String

  Set dbs = CurrentDb
  strSQL = "SELECT Surname FROM Table1 WHERE [Firstname]= '" Me.Text1.value "'"
  Set rst = dbs.OpenRecordset(strSQL)
  With rst
    LastName = !Surname
    .Close
  End With

  strSQL = "SELECT Address FROM Table2 WHERE [Surname]= '" LastName "'"
  Set rst = dbs.OpenRecordset(strSQL)
  With rst
    Me.Text2.value = !Address
    .Close
  End With

  Set dbs = Nothing
End Sub

This code works out what the "matched" value is and uses this to get the appropriate entry for the other field on the form. To make use of this code you will need to right click on the field you want the user too fill in and choose properties, then click on the event tab and for the afterupdate event choose [event procedure] then click on the button with ... on at the end of the box. This will take you to the code module with the first line and last line (the sub and end sub lines) already filled in. All you need to do is copy the remaining lines form above into the space between these two lines.
You will also need to change the table names to those you have used as well as changing the field names I've used (I.e. surname, firstname and address) to those you want. Finally you need to change the control names (I.e. Text1 and Text2) to the names of the controls on your form.

If I've misunderstood then can you give more details and I'll try again. If not then this should work fine, but let me know if you need anything clarifying,
Richard