Dlookup not returning value (1 Viewer)

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
Hey experts!
Im recycling code from another database I have where I ask the user to enter the project number they want to go to and it either goes to it or has an error message. The only difference is that the project number is a string in this new database (at least thats what I think the error is)...

Im asking the input box to return PrjNo
the table JobManagement has the ProjectNumber I want to match it to... I think it will just be something simple I cannot see for looking.....


Private Sub cmdGotoJob_Click()
'Runs the a query to prompt the user to type a Project Number
'If the Project Number does not exist a message box appears

On Error GoTo Handler
Dim PrjNo As String

PrjNo = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = " & PrjNo & "")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = " & PrjNo
End If

Handler:
'Some Error message
If Err = 13 Then
Exit Sub
End If
End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
Hi. If project number is a text field, then you should enclose the variable in single quotes.
 

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
I do dont I? Or am I missing somewhere?
 

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
Sorry if I am being thick.. Might need a little more guidance...

"ProjectNumber = " & PrjNo & ""))
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
Sorry if I am being thick.. Might need a little more guidance...

"ProjectNumber = " & PrjNo & ""))
First, I said, "if" ProjectNumber is a Text field, but you didn't confirm it. So, is it?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
AHA. The ball drops. Yes
Okay then, if it's a Text field, then this is what I meant:
Code:
..."ProjectNumber = '" & PrjNo & "'"))
Please notice the single quotes I added.
 

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
Bummer! I thought that was it but it is like that :-(

PrjNo = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = " & PrjNo & "")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = " & PrjNo
End If
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
Bummer! I thought that was it but it is like that :-(

PrjNo = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = " & PrjNo & "")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = " & PrjNo
End If
Hi. But it is not like that though. Unless I am blind, I don't see a single quote in your code. All I see is a space after the equal sign and an empty string before the closing parens.
 

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
lol. I am half blind and I couldnt see yours. Once I put the screen up to 150% I can but cant really determine which sides they go.
 

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
Got them but am I still missing somewhere else?

Private Sub cmdGotoJob_Click()
'Runs the a query to prompt the user to type a Project Number
'If the Project Number does not exist a message box appears

On Error GoTo Handler
Dim ProjectNumber As String

ProjectNumber = InputBox("Type Project Number", "Search By Project Number")

If IsNull(DLookup("ProjectNumber", "tblJobManagement", _
"ProjectNumber = '" & ProjectNumber & "'")) Then
MsgBox "The specified Project Number does not exist.", vbExclamation, "Error!"
'DoCmd.Close
Else
DoCmd.OpenForm "frmJobManagement", acNormal, , "ProjectNumber = "" & ProjectNumber"
End If
 

vegemite

Registered User.
Local time
Tomorrow, 06:06
Joined
Aug 5, 2019
Messages
64
Sorry that was an old version I think I have it. I used to do this before kids 15 odd years ago I never should have accepted this job!!!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:06
Joined
Oct 29, 2018
Messages
21,358
Sorry that was an old version I think I have it. I used to do this before kids 15 odd years ago I never should have accepted this job!!!
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

fertooos

New member
Local time
Today, 12:06
Joined
Dec 8, 2019
Messages
8
Hi. If project number is a text field, then you should enclose the variable in single quotes.
 

Users who are viewing this thread

Top Bottom