Code help to pull value from a table based on input on form

[rgdwar1]

I need some of your expertise here.

I have a form frmEnterBill. On this form I have an Account# text box and a CompanyName text box (as well as some others). I have a table: Accounts with Account # and CompanyName. I have code in the AfterUpdate event of the Account# field in the form to populate the CompanyName textbox on the same form by pulling CompanyName from the Accounts table that matches the AccountNumber entered in the form. The code reads as follows:

 
Dim varCompName As Variant
       varCompName = Lookup "CompanyName", "Accounts", "AccountNumber_
                =[AccountNumber] ")
If (Not IsNull(varCompName)) Then Me![CompanyName] = varCompName
 

No matter what Account Number I enter, I only get a CompanyName of my 2nd record in the Accounts table.

Can anyone suggest some update to my code to fix this?

Any help would be much appreciated.

Thanks,

RAY

[PEB]

Hi Ray,

Try this code:

 
Dim varCompName As Variant
       varCompName = DLookup ("CompanyName", "Accounts", "AccountNumber_
                ="+Str(Me![AccountNumber]))
If (Not IsNull(varCompName)) Then Me![CompanyName] = varCompName
 

Best regards!

I need some of your expertise here.

I have a form frmEnterBill. On this form I have an Account# text box and a CompanyName text box (as well as some others). I have a table: Accounts with Account # and CompanyName. I have code in the AfterUpdate event of the Account# field in the form to populate the CompanyName textbox on the same form by pulling CompanyName from the Accounts table that matches the AccountNumber entered in the form. The code reads as follows:

 
Dim varCompName As Variant
       varCompName = Lookup "CompanyName", "Accounts", "AccountNumber_
                =[AccountNumber] ")
If (Not IsNull(varCompName)) Then Me![CompanyName] = varCompName
 

No matter what Account Number I enter, I only get a CompanyName of my 2nd record in the Accounts table.

Can anyone suggest some update to my code to fix this?

Any help would be much appreciated.

Thanks,

RAY

[rgdwar1]

Thank you for your response. I have corrected the code, but now when I try to enter an Account # that I know is in the Accounts table, I get:

Data type mismatch in criteria expression. (Error 3464)

From Help:
The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field’s data type.

Could you provide a bit more guidance?

Thanks,

Ray

Hi Ray,

Try this code:

 
Dim varCompName As Variant
       varCompName = DLookup ("CompanyName", "Accounts", "AccountNumber_
                ="+Str(Me![AccountNumber]))
If (Not IsNull(varCompName)) Then Me![CompanyName] = varCompName
 

Best regards!

[PEB]

Dim varCompName
varCompName = DLookup ("CompanyNam e", "Accounts", "AccountNum ber_
="+Str(Me![AccountNumber]))
If (Not IsNull(varCompN ame)) Then Me![CompanyName] = varCompName


The proble may be caused by this expression: "AccountNum ber ="+Str(Me![AccountNumber])

So if your AccountNumber is defined in your database as Text you need to do:

Dim varCompName
varCompName = DLookup ("CompanyNam e", "Accounts", "AccountNum ber_
='"+Str(Me![AccountNumber])+"'")
If (Not IsNull(varCompN ame)) Then Me![CompanyName] = varCompName


Best regards!