Need help with DLookup syntax: (1 Viewer)

[jread]

For the life of me, I cannot get this function to work. It either gives me "Invalid use of Null" or "You canceled this operation... blah blah" every time. I am just going to lay out what I'm trying to do and hopefully somebody can point me in the right direction. BTW, I am using Access 2003:

1. I have a form called: frmLogAccepted

2. On that form is a textbox called: txtProjectNumber

3. There is a table in the databased called: tblMaintTrack

4. tblMaintTrack has a ProjectNumber field as well

5. I want the user to be able to enter a project number into frmLogAccepted's textbox called txtProjectNumber, then on the "lost focus" event of the textbox DLookup will be used to see if the ProjectNumber entered has a matching ProjectNumber record in tblMaintTrack

6. If the corresponding project number IS found, then I want it to populate txtIDMT on frmLogAccepted with the IDMT value (long integer) that is found in the record with the matching ProjectNumber in tblMaintTrack

7. In plain English, "Select IDMT From tblMaintTrack Where tblMaintTrack.ProjectNumber = frmLogAccepted.txtProjectNumber"

8. The following is what I have so far. I've driven myself to madness trying to figure this stupid thing out:

Private Sub txtProjectNumber_LostFocus()

Me.txtIDMT = DLookup("[IDMT]", "tblMaintTrack", "[ProjectNumber]=" & Forms![frmLogAccepted]![txtProjectNumber])
Me.Refresh

End Sub

Thanks in advance for any help you may be able to provide.

 

[PeterF]

First, I wouldn't use the lost focus event but the after update event of the txtProjectNumber control. The code only has to run when the value is changed and not when you leave the field when tabbing trough.

I'm not sure why but I have had some problems with square brackets and using the form! method in Dlookup functions before, I think the following works:

Me.txtIDMT = DLookup("IDMT", "tblMaintTrack", "ProjectNumber=" & me.txtProjectNumber)

 

[jread]

Thank you very much for your response. I tried the suggested format and ended up with my old friend, the "You canceled the previous operation." error message.

Private Sub txtProjectNumber_AfterUpdate()
    Me.txtIDMT = DLookup("IDMT", "tblMaintTrack", "ProjectNumber=" & Me.txtProjectNumber)
End Sub

EDIT: Ah! I figured it out! I needed single quotes around the txtProjectNumber control in the criteria as it's a text field while IDMT is a number. Thanks again

 

[rainman89]

also, here is a nifty little helper for syntax

http://www.mvps.org/access/general/gen0018.htm

 

[jread]

Ooh, nice! Thanks

 

[BadScript]

I'm trying to do something similar.

1. I have a textbox called txt_awb
2. My table is named tblStorage
3. The field name is awb
4. I have a textbox called txt_warning

When I type in a number in txt_awb (f.e. 001-2004 3543) I want access to check if this number already exists in my database. If it exists I would like the date field of that record to display in the textbox 'txt_warning'.

What I have so far from searching the internet:

Me.txt_warning = DLookup("date", "tblStorage", "awb=" & Me.txt_awb)

But I keep getting the same error: Runtime error 3075
Syntax error (missing operator) in query.

I'm starting to go nuts, I'm PMS-ing and I have a headache. (j/k)
Could someone please tell me what I'm doing wrong?

 

[wazz]

Me.txt_warning = DLookup("date", "tblStorage", "awb= ' " & Me.txt_awb & " ' ")

- surround text variables with single quotes wrapped in dbl-quotes.
- this might do it if awb is a text field (looks like it is);
- if awb is not unique you might not get the result you want (looks like it is unique).

 

[BadScript]

Thanks and yes, awb is a text field..
Unfortunately the date doesn't show in the txt_warning textbox though, probably because the awb field is not unique?
Is there a way to go around this? Don't necesarily need the date, would also be happy with a default message this number is already in the dbase...

 

[rainman89]

FYI you should not have a field called Date in your table. it is an access reserved word. could be causing your problem

Don't necesarily need the date, would also be happy with a default message this number is already in the dbase...

then use a dcount

If DCount("*", "tblStorage", "awb= ' " & Me.txt_awb & " ' ") > 0 Then
            MsgBox "Your Message Here", vbOKOnly
            Else
    End If

 

[BadScript]

Thanks.. That's good to know, will change the date in a bit, I assume same counts for my time field then?

Haven't tried either yet but I will in a bit.. Thanks..

 

[BadScript]

Didn't work either, changed the date and time fields as well.
My form is not bound, could that be the problem?

 

[wazz]

have a look at this and compare. check field types and names, etc...

 

[BadScript]

I got it to work, thanks for the sample!
I Finally finished my form and would like to thank everyone for their help, I doubt I would have been able to finish it without the people on this forum

*thumbs up*