Autopopulate form fields based on combo box value using Dlookup?

[feltonam]

I have 2 tables called Contacts and Meeting Details, both with fields called Contact_Name and Service_Area. They are joined by Contact_Name. I have created a form based on the Meeting Details table. It uses a Combo box to look up the Customer name. I want it to then auto-populate the Service_area field from the Contacts table.

The combo box has Control Source: Contact_Name and Row Source: Contact. I have placed the following code in the combo box 'after update' event:

Private Sub Contact_Name_AfterUpdate()
=DLookUp("Service_Area","Contacts","Contact_Name =" & Contact_Name)
End Sub

This generates a compile error "Expected: Line number or label or statement or end of statement"

I have tried several variations of this but can't get it to work. If possible I would also like for users to be able to then overtype the Service area if it has since changed, and for this to be written back to the Contacts table. (jam on it eh!). I should say i'm fairly new to Access.

 

[dhookom]

Isn't the Row Source of the combo box built off the same table as your DLookup()? If so, just add the Service_Area field to the Row Source query so you can reference it your code. You might need to increase the number of columns in the combo box properties. Your code might then look like:

  Private Sub Contact_Name_AfterUpdate()
      Me.Service_Area = Me.Contact_Name.Column(1)
  End Sub

This all assumes you have a good reason for storing a value that can be queried from the contacts table.

Duane
Hook'D on Access
MS Access MVP