What is wrong with this DLookup syntax?

[buratti]

I have a form where I select or type an account number from a dropdown combo list field and it navigates to that record. I'm trying to add another field (the address field) to do the same. I've come up short on all other means of trying to accomplish this, so I came up with trying to lookup the account number based on the address entered (dlookup) and have the DB enter it in the account number field, hence will navigate to the correct record.

Private Sub Address_AfterUpdate()

Me.CustomerID = DLookup("[ID]", "Customers", "address = " & Address)

End Sub

After the field is updated, it gives me rather the error "Runtime Error 3464 Data type mismatch in criteria expression" or if I change a few things "runtime Error 3075 Syntax Error (missing operator) in query expression '[address] = 123 Main St'

Correct me if I am worng, but this should translate to: enter the value of the [ID] field in the "customers" table into the CustomerID field of the form where the "Address" field of the customers table is the same as the Address field i just entered in the form.
I don't know what I'm doing wrong here. The data types in the table and form are both set to text.

 

[pbaldy]

This should help:

http://www.mvps.org/access/general/gen0018.htm

 

[buratti]

Thanks, the Dlookup function now works. Now lets just see if my theory of how to use it will work....

 

[pbaldy]

No problem; post back if you're still having trouble. In what sounds like a similar situation, I have 2 combos users can use to pick an account. One shows number and the other shows name. They can use either one to pick an account, and the two stay in sync with each other.

 

[buratti]

that is exactly what I am trying to do!!! I've posted several posts about how i'm trying to do that and haven't gotten much success. I've gotten a lot of tips and tricks, but nothing that really helps im my situation. It would be great if you can supply me with those instructions on how to do it. Thanks

 

[pbaldy]

Both basically have the same row source and bound column, but the "names" combo hides the account number (zero column width). Code like this keeps them in sync:

Private Sub cboAcctName_AfterUpdate()
  Me.cboAcctNum = Me.cboAcctName
End Sub

Private Sub cboAcctNum_AfterUpdate()
  Me.cboAcctName = Me.cboAcctNum
End Sub

 

[buratti]

Both basically have the same row source and bound column, but the "names" combo hides the account number (zero column width). Code like this keeps them in sync:

Private Sub cboAcctName_AfterUpdate()
  Me.cboAcctNum = Me.cboAcctName
End Sub
 
Private Sub cboAcctNum_AfterUpdate()
  Me.cboAcctName = Me.cboAcctNum
End Sub

WOW, I'm actually embarrassed to actually figure out how simple that turned out to be. Thank You! I did figure out a different, way more complicated, way to accomplish it, but I'm almost positive that the way I did it is very poor design. If you want a good laugh let me know, and I'll post back how I came about doing it.

I don’t understand the need for the after update code above though. I gave both the Account number and Address combo box fields the same control source (customerID), changed the row source for each one to display the correct data, adjusted column widths, etc and that’s it. When I update or change one field the other is updated also to reflect the change. Am I missing something about this?

 

[pbaldy]

No, I forgot that the form I was looking at is unbound, so the code was needed. You wouldn't need it with a bound form.

 

[buratti]

Thanks for all your help pbaldy. I'm going a little off topic here and don’t know if I should just start a new thread, but you helped me a lot with my last problem, and maybe you can help with this new one.
I did the changes you suggested and it works great!!! BUT, now the strangest thing is happening. In my orders form (the same one I was working on before) whenever I open it it's telling me that there are no records when there actually are some. If I open the underlying query from what the form is made of, it shows all the records, but I can’t view them in the form. No matter how I open the form, it is always blank. It's like it's stuck in add mode, or being filtered for new records, but no filter is present. I changed nothing in VBA, reverted back to the original fields before your earlier suggestion and even tried to just close and reopen the DB but nothing has seemed to solve my new problem. Any suggestions on why it's doing this now?

 

[pbaldy]

Check the Data Entry property of the form. It might have been changed to Yes accidentally.

 

[buratti]

Thanks again that did it. Got scared for a second there!

 

[pbaldy]

Glad we got it sorted out.