populate fields from related table

[davea300]

I have a form with a ID number field (txtproperty_ID) which the user can manually type in. When the user types the ID number I want to populate 3 other fields in the form with data from a related table.

e.g. user types "1234" and "field_address1 = 1 anywhere street", "field_address2 = Glasgow"

The table I have has all the ID Numbers and address details in it.

I am trying to do this on the afterupdate event of the ID num field and so far I have something like:

Me.Address1 = ([t_properties]![address_1]) WHERE ([t_properties]![property_id] = Me.txtproperty_ID)

I know the WHERE statement is wrong (just put it in for the sake of the example). This is what I need to fix, I know I could do it with a subform but would rather do it with code.

Any suggestions please?

 

[XLEAccessGuru]

You could use a lookup query (info on this found easily in Help or online) as your recordsource for the form, but personally, I think the best way to populate fields in a form based on a user entered search value is by using the recordset object. You'll also want to be sure you have a conditional statement to execute a message if the user enters an invalid ID number too.

Private Sub txtproperty_ID_AfterUpdate()

'***NOTE***: "MyTable" should be the name of the table whose values you want to populate the form's text boxes
' and "MyTable.IDFieldName" should be the name of the ID field in MyTable whose values
' you want to populate the text boxes

Dim rec As Recordset
Dim stSQL As String

If IsNull(Me.txtproperty_ID) Or Me.txtproperty_ID <> "" Then 'make sure user entered a value
    MsgBox "Please enter an ID Number.", vbExclamation, "Invalid Entry"
Else
    'next 2 lines opens a recordset containing the record from which you want to populate the
    'text boxes on the form.
    stSQL = "SELECT MyTable.* From MyTable WHERE (((MyTable.IDFieldName)=" & Me.txtproperty_ID & "));"
    Set rec = CurrentDb.OpenRecordset(stSQL, dbOpenSnapshot)
    
    If rec.RecordCount <> 0 Then 'if recordset contains at least one record
        With Me 'next lines populate form fields
            .txtproperty_ID.Value = rec("InsertIDFieldNameHere")
            .TextBox2.Value = rec("Field2")
            .TextBox3.Value = rec("Field3")
            'Continue to add additional textboxes here where you want to set them equal
            'to fields in the open recordset
        End With
        rec.Close 'closes recordset
    Else 'if no records in recordset, tell user they entered an invalid ID number
        MsgBox "Invalid ID Number. Please try again.", vbExclamation, "Invalid ID"
    End If
End If

End Sub