DLookup 2

[cer5yc]

I have created a form (Contract Data) that has fields for Supplier Information. When the user types in the supplier's tax ID number into the Taxid text box I created I want the Supplier Name to automatically populate in the SupplierName text box that is on my Contract Data form. I have an underlying table named "Supplier Information" where my supplier info is located. I have A LOT of suppliers so I don't want to use a combo box with a drop down to select the TAXID (primary field). Does anyone have any suggestions on how to write the code in VBA?

 

[JerryKlmns]

So,

you either use a left join to your Supplier Information table on TAXID and bound the text box to the Supplier Name

or on the txtTaxID LostFocus event if TAXID is numeric, put this

txtSupplierName.Value = DLookup("Supplier Name", "Supplier Information", "TAXID=" & txtTaxID.Value )

if it is of text data type
txtSupplierName.Value = DLookup("Supplier Name", "Supplier Information", "TAXID='" & txtTaxID.Value & "'" )

 

[cer5yc]

Jerry -

Thanks so much for the information - but I still can't get it to work! (I'm new at this so bear with me please...) Let me give you some more information and hopefully you can tell me where I'm going wrong. I have two tables in my database. The database name is "Contract Management" and my table names are "Data" and "Supplier Information". I also have a query, "FrmContractData" whose fields are pulled from both the "Supplier Information" and "Data" tables. I created a form named "Contract Data" to use for data entry so that no one but myself will see the underlying tables. The Record Source for the "Contract Data" form is the "FrmContractData" query. My tables are joined by a their Tax ID fields. TAX_ID is the primary key in the "Supplier Information" table. On the form "Contract Data" I have the following text boxes:

TAXID - number
SupplierName - text
Address - text
City - text
State - text
ZipCode - number
DunsNumber - number

(All of this info is in the "Supplier Information" table)

My goal is for the data entry person to be able to enter the supplier's Tax ID number in the "TAXID" control on the form. If the Tax ID number they enter matches one of the Tax ID #'s from the "Supplier Information" primary field "TAX_ID" I want the controls for Supplier Name, Address, City, State, ZipCode, and DunsNumber to populate with the data associated with that Tax ID#. However, if they enter a Tax ID# that is not currently in the "Supplier Information" table I want them to be able to enter the Supplier Name, Address, etc. on the "Contract Data" form and have the information added to the "Supplier Information" table so that next time that Tax ID gets entered the info populates on the form.

Any help or suggestions you can provide would be GREATLY appreciated. I'm a Business Analyst not a Programmer and am still getting my feet wet with this stuff - so detailed info would be wonderful. Thanks again!

 

[cer5yc]

This is what I have written in the On Lost Focus event of the "TaxID" control on the "Contract Data" form.

=[SupplierName].[Value]=DLookUp("Supplier Name","Supplier Information","TAXID=" & [TaxID].[Value])

This is the error I am getting:

The expression On Lost Focus you entered as the event property produced the following error:Syntax error (missing operator) in query expression "Supplier Name".
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

 

[PHV]

In the AfterUpdate event procedure of TaxID:

Me!SupplierName = DLookUp("[Supplier Name]", "[Supplier Information]", "TAXID='" & Me!TaxID & "'")

If TAXID is defined as numeric in the [Supplier Information] table then get rid of the single quotes.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[cer5yc]

HOLY COW IT WORKED!!!!!!!!! You are fantastic!

 

[cer5yc]

Okay one more question -

(1) If the Tax ID field is updated on the form, and the new Tax ID # doesn't match one in the Supplier Info table, how do I get the Supplier Name that previously populated (the one tied to the first tax id) to go away?

 

[cer5yc]

Never Mind my last question. I got it to work. Thanks again!

 

[cer5yc]

New problem -

If the user enters a Tax ID number in the "Tax ID" control of the "Contract Data" form, which is bound to the Supplier Information Tax ID field, they get an error that states "The current field must match the join key '?' in the table that serves as the 'one' side of the one to many side relationship". I need a way that the use can enter the Tax ID number and have the form populate the associated data if it's already in the "Supplier Information " table (got the code for that), but if the Tax ID they enter in the form isn't in the "Supplier Information" table I want a msgbox to pop up that says "Supplier Not Found. Enter Supplier Information" - then they type in the Supplier Info (into a seperate "Supplier Info" form) - the "Supplier Information" table is updated - the query that is the record source for the "Contract Data" form is updated - and finally the "Contract Data" form is updated with the new supplier information. Can anyone provide some detailed insight on how to do this the right way?? Thanks!!

 

[JerryKlmns]

Dim mySupplierName As String
mySupplierName = DLookUp("[Supplier Name]", "[Supplier Information]", "TAXID='" & Me!TaxID & "'") & ""
If mySupplierName = "" Then
MsgBox "New Supplier TAXID entered."
'Do whatever you need
....
Else
' You have it a Supplier here
End IF

 

[cer5yc]

I keep getting this error - any ideas on how to get around this? I need the Tax ID entered in the text box stored in the Tax ID field of the Data table.

You have tried to enter a value into the join field in the many-only table of an outer join with no corresponding value in the join field of the "one" side table.

The message includes the following instruction: Enter a record in the "one" side table with the desired key value, and then make the entry with the desired join field in the "many-only" table.

 

[JerryKlmns]

cer5yc

I suggest when you have a "New Supplier TAXID entered.", ask the user to confirm the valididy of typed TAXID and if it is accepted silently add this value to the table Supplier Information, or open the form for adding the value to the table Supplier Information, and then go on with the rest.