VBA Code II

[Guest]

From what I got from my responses on the site, I hacked this together. It
isnt working saying I have a missing operator in Query expresion 'Current Job.

This code should look at the field txtJobName in the form, and compare it to
see if the data exists in the Table Current Jobs, Field Current Job.

Could you please help me fix it.



Private Sub Command40_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If (DLookup("Current Job", "[Current Jobs]", "Current Job = '" & _
Me!txtJobName.Value & "'")) Then
ans = MsgBox("GOOD")
Else
ans = MsgBox("You are not currently not clocked into this job.Please
check your daily activity by clicking the Current button." & vbCrLf & _
"Click OK to continue", _
vbInformation + vbOKOnly, "Employee Not Available")
If (ans = vbYes) Then
' Do something else.
End If

End If

Exit Sub

ErrHandler:

MsgBox "Error in RunQueryBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub
Was this post helpful

 

[RoyVidar]

Tony Ramirez wrote in message

(DLookup("Current Job", "[Current Jobs]", "Current Job = '" & _
Me!txtJobName.Value & "'"))

Surround field names with special characters and spaces with
[brackets]

(DLookup("[Current Job]", "[Current Jobs]", "[Current Job] = '" & _
Me!txtJobName.Value & "'"))

I think you will probably get into some challenges if they are
not "clocked into the job" - i e if it returns Null:

If not IsNull(DLookup("[Current Job]", "[Current Jobs]", "[Current
Job] = '" & _
Me!txtJobName.Value & "'")) Then
MsgBox("GOOD")
Else
ans = MsgBox("You are not currently not clocked into this
job.Please
check your daily activity by clicking the Current button." & vbCrLf & _
"Click OK to continue", _
vbInformation + vbOKOnly, "Employee Not Available")
If (ans = vbYes) Then
' Do something else.
End If

End If

 

[Dirk Goldgar]

From what I got from my responses on the site, I hacked this
together. It isnt working saying I have a missing operator in Query
expresion 'Current Job.

This code should look at the field txtJobName in the form, and
compare it to see if the data exists in the Table Current Jobs, Field
Current Job.

Could you please help me fix it.



Private Sub Command40_Click()

On Error GoTo ErrHandler

Dim ans As Integer

If (DLookup("Current Job", "[Current Jobs]", "Current Job = '" & _
Me!txtJobName.Value & "'")) Then
ans = MsgBox("GOOD")
Else
ans = MsgBox("You are not currently not clocked into this
job.Please check your daily activity by clicking the Current button."
& vbCrLf & _ "Click OK to continue", _
vbInformation + vbOKOnly, "Employee Not Available")
If (ans = vbYes) Then
' Do something else.
End If

End If

Exit Sub

ErrHandler:

MsgBox "Error in RunQueryBtn_Click( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear

End Sub

Your main problem is that your field name, "Current Job", has a space in
it. You need to surround it with square brackets in both places where
you use it, so that the SQL statement that the DLookup function builds
behind the scenes can be interpreted properly. You probably also want
to be explicitly checking whether the value returned is Null. Try
something like this:

If IsNull(DLookup("[Current Job]", "[Current Jobs]", _
"[Current Job] = '" & Me!txtJobName.Value & "'")) _
Then
MsgBox _
"You are not currently not clocked into this job. " & _
"Please check your daily activity by clicking the " & _
"Current button." & vbCrLf & "Click OK to continue", _
vbInformation + vbOKOnly, _
"Employee Not Available"
Else
MsgBox "GOOD"
End If