Writing a query in VBA to populate text box

[Beeps]

Hi

I've looked thoughout this forum, but I'm having a hard time understanding how to write a query in VBA to populate a text box.

The reason is I need to have a value from a table to be entered into a text box to facilitate data entry. I have sync'd combo boxes on the form as criteria.

I just don't know how to look up a value in a table with the two combo boxes as criteria and then enter the result in the text box.

(I did use a list box with the rowsource set up with the query, but it does not select the value so that it gets transmitted to another table.

Thanks in advance!

 

[mymou]

Hi Beeps

Straight from help - and looks like what you need:

You can use the DLookup function to get the value of a particular field from a specified set of records (a domain). Use the DLookup function in Visual Basic, a macro, a query expression, or a calculated control on a form or report.

Stew "Even a stopped clock tells the right time twice a day."

 

[DougP]

here is a good place to start
Copy this and keep it handy I use it for very one I create.

------------------------------------------------------
for Access 2000 databases newset tight code
------------------------------------------------------

Dim Rs1 As ADODB.Recordset
Dim SQLCode As String
Set Rs1 = New ADODB.Recordset

SQLCode = "Select * From yourTable Where YourField = " & SomeValue
Rs1.Open SQLCode, CurrentProject.Connection
' Now in here is where you do your work
Rs1.Movefirst ' goes to the first record of the Rs1.recordset
Do while Not Rs1.EOF
a = rs1!yourfield
Rs1.Movenext ' goes to the next record
Loop

'close it up
Set Rs1 = Nothing
Set Conn2 = Nothing

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[DougP]

Dlookup is Slow if you have 10,000 records or more

Learn the VBA it is very powerful and will serve you well
Luke DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Beeps]

Thanks for the help, I really appriciate your time and patience DougP and Mymou!

I'll give this a shot later today.

Thanks again!

 

[Beeps]

I have a quick followup question. I'm using the DLookup function to get the value I need as the table to look in is small in size. I'm getting a "Criteria Mismatch Error" in the criteria section, here's my code:

bill = DLookup("[Billing ID]", "Billing", "[Employee ID]=" _
& Forms![Entry]!txtEmployeeID _
And "[Project ID Number]=" & Forms![Entry]![SubHours]! cboProjectIDNumber)

Any ideas?

 

[bjj]

Is the employee ID a number? If it's a string, you need to include "'" characters before and after the text. So, you would type:

bill = DLookup("[Billing ID]", "Billing", "[Employee ID]='" & Forms![Entry]!txtEmployeeID & "' AND [Project ID Number]=" & Forms![Entry]![SubHours]! cboProjectIDNumber)

(note that you need an "&" after the txtEmployeeID)

Also make sure that the bill variable is defined correctly.

 

[Beeps]

Hi bjj,

It works great! But now I have to have the Project ID Number as text(to include a -, ex 000-32), not a number and now the code does not work when Employee ID is a number and Project ID Number is text. Any sugesstions for DLookup when the criteria has both text and numbers??

 

[Dustman]

I've got a suggestion. Generally you wouldn't store the - in the table. If you are, you might look at taking the - out and using a format or input mask. You can store numbers with input masks. This could also be your problem.. if the combo box has a format mask and the table doesn't, you will get any matches. Am I helping any? If not, just ignore me and go on .
-Dustin
Rom 8:28