dlookup Error: #name?

[xicana]

I've already looked through a bunch of threads dealing with dlookup...alot of them have helped but I'm still coming up with this error: #name?

I have a form based off of an invoice table. I have a combo box that looks up a customer.

cmbCustomer:
Column(1) = CustomerID
Column(2) = CustomerName
Column(3) = TaxCode

I want to have a text box that uses the TaxCode from cmbCustomer (3rd column) and reference's the tax table to lookup up the actual tax rate. I want this tax rate to display in a textbox.

this is what I have:

control source for txtTax: =

DLookUp("[TaxRate]","[Tax Table]","[TaxCode]= '" & Me!cmbCustomer.Column(3) & "'")

I tried using this same lookup with the criteria hard-coded (making "[TaxCode]= 'CA'") and it worked, so I know it's looking at the table...there must be something wrong with my reference to the combo box. Can anyone tell me what it is?

Sandy

 

[RoyVidar]

You reach the third column in a combo with the index 2, not 3 (zero based).

Roy-Vidar

 

[xicana]

Thanks for that! I changed the index but I'm still getting the same error...

Sandy

 

[RoyVidar]

Sorry, missed the keyword "controlsource" - then you'll need to drop of the me keyword, probably something like this:

[tt]=DLookUp("[TaxRate]","[Tax Table]","[TaxCode]= '" & cmbCustomer.Column(2) & "'")[/tt]

- where Access probably will add some [brackets]

If it doesn't work, experiment a little with adding another text control, and just try to reference the combo in it

[tt]=cmbCustomer
=cmbCustomer.column(1)[/tt]
...

Roy-Vidar

 

[xicana]

Thanks Roy!

After playing around with this some more, I put some code in the after-update event of the Customer ComboBox - and it works! (*I renamed some controls...)

Private Sub Customer_AfterUpdate()
    Dim strFilter As String
    
    ' Evaluate filter before it's passed to DLookup function.
    strFilter = "TaxCode = '" & Me!Customer.Column(2) & "'"
    
    Me!Tax = DLookup("TaxRate", "Tax Table", strFilter)
End Sub

Sandy