I am new to using VBA w/ Access, At anyrate I am trying to Have the user enter in a Quote Number (txtQuoteNumber) I labled it on the form. I am trying to take that value, and search a table(QuoteTable) to see if it is = to Quote_Number in my QuoteTable. If it is I want to extract The Dealer_Number so basically I want If txtQuoteNumber = QuoteTable[Quote_Number] Then QuoteTable[Dealer_Number] = txtDealerNumber.text

All of this code I have embedded into a Command Button using the Code builder I am using VB6 As well.

Basically the end result that I am trying to accomplish is that I want to be able to Autofill in some text boxes to make the form a lot easier. Any help would be appreciated a ton. I need to know how this would be done.

There has to be an easy way to do this. Can anyone HELP PLEASE!!!!

Dim QuoteNumber As String
Dim strSQL As String
QuoteNumber = Me.txtQuoteNumber

DoCmd.OpenTable ("Quote Table")

strSQL = "SELECT Dealer_Number FROM Quote Table WHERE Quote_Number = '" & QuoteNumber & "'"

This so far seems to be working. It will open the table but I can not tell if it is Taking the Dealer_Number field and if so. How could i Possibly post it back into a text box on my current form and hide the table.

txtDealerNumber = DLookup("Dealer_Number", "QuoteTable", "Quote_Number=" & txtQuoteNumber)
This line of code will look in table "QuoteTable" in the "Quote_Number" field for a match to field "txtQuoteNumber" from your form. If that "txtQuoteNumber" is found, then the matching "Dealer_Number" will be written into "txtDealerNumber" on the form.
Hope this helps,

Vic

Amazing! thanks vic works like a charm!

Apparently you can not do 2 DLookup functions at a time?? I have these trying to run back to back to fill out more information
Me.txtDealerNumber = DLookup("Dealer_Number", "Quote Table", "Quote_Number=" & txtQuoteNumber)
Me.txtCustomerName = DLookup("Dealer_Name", "Dealers Table", "Dealer_Number=" & txtDealerNumber)

Im trying to autopopulate
DealerName
Dealer Address
Dealer City
Dealer State
Dealer zip
Dealer phone
Dealer Contact

All refrenced off of the Dealer Number that I had retrieved from the last DLookup

Any suggestions?

When you say "Apparently..." I assume you have tried this code that you have shown and it does not work. If that is the case, it is most helpful to show what the error message is that you received, or what the actual outcome of running the code is. Much easier to help when we know what the situation really is.

I'm going to suggest that the Dealer_Number could be a text field, rather than defined in the table as a Number. If that is the case, then you will need single quotes around the number when it is used in the Criteria parameter of the DLookup. To put the single quotes around the Dealer_Number the criteria code would look like this:
   ..., "Dealer_Number='" & txtDealerNumber & "'")

If that is not the situation, then post back with what is actually happening when you run the code you already showed us.
Vic

Vic

Thanks again vic. I actually Had to put quotation marks around the last field in each statement to delimit it. ... "Quote_Number=" & "txtQuoteNumber" works like a charm now.

Thanks for the help.