VBA Question for Comparing data

[Guest]

I want the following code to evaluate txtJobName in my current form and
compare it to the value of current job in the table Current Jobs. If it finds
the match then I want it to run another Private Sub, if not it should return
the error message (which I am pretty sure is working). Could someone please
help me finish it. Thanks so much for your help. Tony


Private Sub Command39_Click()
On Error GoTo ErrHandler

Dim ans As Integer

If (DLookup("Current Job", "[Current Jobs]", "Current Job = '" & _
Me!txtJobName.Value & "'") = ?????) Then
Dim stDocName As String

Would Like To Run Another Private Sub Like -- Private Sub
Command15_Click()

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

 

[Guest]

Private Sub Command39_Click()
On Error GoTo ErrHandler

Dim stDocName As String 'Variable Declaration should alway be the first
thing in
'your subs and functions

If Not IsNull (DLookup("Current Job", "[Current Jobs]", "Current Job =
'" & _
Me!txtJobName & "'") Then
Command15_Click()
Else
If 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") = 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

 

[Tim Ferguson]

=?Utf-8?B?VG9ueSBSYW1pcmV6?= <[email protected]>
wrote in
' your naming system _really_ needs attention!!

Private Sub Command39_Click()

' the time to add error handlers is _after_ you've
' got everything debugged, not before. Please remove
' this line

On Error GoTo ErrHandler

Dim ans As Integer

' the next line either returns NULL or the same value as
' txtJobName.Value. A return of NULL will fail here because
' null does not equal anything at all.

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

' if you just want to test for the existence of a record, then
' use the DCount like this. Note that it will always return a
' number; zero if there is no such record, one or more if they
' do exist

ans = DCount("*", "[Current Jobs], _
"[Current Job] = """ & txtJobName & """")

If ans > 0 Then

' Call is, strictly speaking, unneccessary, but it makes
' the flow of control more obvious, to me at least

Call Command15_Click

' simplify what follows by collapsing the commands:
' incidentally if you specify vbOkOnly then you won't get
' many Yes answers...

ElseIf vbYes = MsgBox( c_strWarning, _
vbInformation OR vbYesNo, _
"Employee Not Available") Then

' Respond to Yes answer

Else
' Respond to No answer

End If

Hope that helps


Tim F

 

[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.

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

 

[Tim Ferguson]

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.

Could you please help me fix it.

I cannot see that you have taken any notice of the suggestions offered,
which is not a very encouraging sign.

The immediate cause of this error message is your illegal field name:
'Current Job'. If you insist of putting spaces in field names, you'll need
to wrap them up in square brackets:

DLookup("etc", "etc", "[Current Job] = """ & etc )

but there are bigger problems ahead of you too...

B Wishes


Tim F