can somebody help a complete idiot with the DLookup function (2nd edit - sorry)

[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] = 1")

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.

 

[Jack Cowley]

I was a bit confused by exactly what you are trying to do so I may be on the wrong track here. You say you want to show the most recent addition to the field Mini ID. If Mini ID is an Autonumber or other sequential numbering system then using DMax to tell you that number is may be the way to go. Something like DMax("[Mini ID]","tblcar") will return the highest, and therefore the latest, Mini ID.

If this is not what you are after then a bit more detail may help....

[This message has been edited by Jack Cowley (edited 11-21-2000).]

 

[Karl K]

What do you want to do? Have a textbox on a form that shows the latest ID number? If it is an autonumber function, as Jack suggested, don't put this in code, but his code DMax("[Mini ID]","tblcar") use it as the Control Source. Then it will always show the latest ID number.

What is the click? Do you want to click on it and see the latest ID number? Then make the code

Private Sub Mini_ID_Click()

msgbox "The Latest ID number is: " & DMax("[Mini ID]","tblcar")


Try these.

 

[IanC]

If I understand you correctly, I suspect that you are quite close. You don't need to use a module for what you are doing.

I assume that the textbox is unbounded (not tied to any field in an underlying table).

Set the "control source" of the textbox on the form (or simply type into the textbox) to the Dlookup function.

The part I don't understand about your question relates to knowing which MiniID value is the most recent. If it is an autonumberd value, you could use DMAX instead.

The criteria part of the Dlookup (Dmax Dmin ...) may need to contain an expression to narrow the search to the one MiniID value for which you are looking.

Supply me with some details, and I'll gladly help you get it working correctly.

Ian

[This message has been edited by IanC (edited 11-21-2000).]

 

[simeon_rose]

hello and thank you. i haven't made any alterations yet, but i'll let you know how they go when i do.
in reply to you IanC, the textbox is unbounded and you saying i don't need a module is great news. however, is what you're saying this:
in control source i should type the DLookup expression with its criteria set as DMax? if so, can you please give a quick example of how to set the criteria as DMax...
once again, thanks.

 

[simeon_rose]

hi...and thanks. i think DMax is the route to follow, but although i have decided that this is what i need by getting the text box to display the Mini ID with the highest value in order to enter it into a seperate table, i'm not sure if i've got the syntax right. i've entered the syntax suggested and this seems to match the one shown in the help file, but when i try to enter data into the textbox in order to make it active (even though the box is in fact locked so no data can be added) the status bar says that the textbox is bound to an unknown field and then displays the criteria that i put in as the control source. could somebody tell me what is going on?
thanks again...