can somebody please help a complete idiot with the DLookup function?

[simeon_rose]

hi, i've got a text box in a form and i want this textbox to generate a value from a table that the form it is on is not assigned to. i've been told to use DLookup and have read the 'bible', looked at help and still don't know what i'm doing. i want it to show the most recent addition to a field called Mini ID in table called tblcar. this is what i've got

Private Sub Mini_ID_Click()

Dim varX As Variant
varX = DLookup("[Mini ID]", "tblcar" "[Mini ID] = )

End Sub

now, obviously this is wrong (although it doesn't generate a syntax errot) because it does nothing at all. first of all, i don't want it to operate when it is clicked, i want it to just happen anyway, secondly the form opens with a 0 in the box, and then when the box is clicked it stays as 0.

please help a person who used to consider himself fairly intelligent, but has been demoted to the rank of 'complete idiot' and is in desperate (and urgent) need of real help.

 

[simeon_rose]

no sorry, the code i have is:

Private Sub Mini_ID_Click()

Dim varX As Variant
varX = DLookup("[Mini ID]", "tblcar", "[Mini ID] = 1")

End Sub

the code i posted on the original topic does genrate a syntax error, the code above is the actual code i've got and this doesn't generate an error, but still doesn't work

 

[A real begineer]

The code as posted should look up the value okay. As a suggestion what I do when I can't work something out, to try and save some hair, is I add it to a field that doesn't matter, run it stepwise on click events such as msgboxes, until I have it doing what I want it to do. THen I simply copy the code back to where I want it.

Looking at your code do you wnat to lookup the Mini ID that matches the Mini ID, or do you want to update another field like say client name? In the latter case,you need a code modification.

set focus=clientname
txtClientName=Dlookup("[Client Name]","tblcar","[MiniID]="&Mini_ID)

This will look up the client name from a table called tblcar, and return the name where the id equals the id on the form.

 

[kdevitt]

I use Dlookup all thetime, and even still it can take a while to figure out why it won;t return the results you want!

If you are looking up the value of a field on a form, you might want to change the code as follows (assuming the form is called Form1, and the control is called MyValue):

varX = DLookup("[Mini ID]", "tblcar" "[Mini ID] = " & forms!form1!MyValue )

Also, if the field you are looking up isn't a numeric field, you might want to add parenthesis as follows:

varX = DLookup("[Mini ID]", "tblcar" "[Mini ID] = """ & forms!form1!MyValue &""")

This should work, if it doesn't, try taking out your criteria totally, and see if the lookup is returning values at all.