runtime error-2113, please help

[dave]

following is my code and is giving me runtime error-2113, please suggest any
modications needed to the code.


Private Sub doctorid_GotFocus()
Dim doctorid As Long
Dim SQL As String

If VISITTYPE = "First Visit" Then
Forms!marketers!doctorid = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) + 1
Else
Forms!marketers!doctorid = "SELECT MARKETERS.DOCTORID FROM marketers WHERE
(((MARKETERS.LAST)=FORMS!MARKETERS.LAST) And
((MARKETERS.FIRST)=FORMS!MARKETERS.FIRST))"

End If
End Sub

 

[Douglas J. Steele]

I suspect DoctorId is a numeric field, and you're trying to assign it a
string.

Assuming what you really want is to look up DoctorId in table Marketers, you
need:

Private Sub doctorid_GotFocus()
Dim doctorid As Long
Dim SQL As String

If VISITTYPE = "First Visit" Then
Forms!marketers!doctorid = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) + 1
Else
Forms!marketers!doctorid = DLookup("[DOCTORID]", _
"marketers", "MARKETERS.LAST = " & Chr(34) & _
FORMS!MARKETERS.LAST & Chr(34) & _
" And MARKETERS.FIRST = " & Chr(34) & _
FORMS!MARKETERS.FIRST & Chr(34))
End If
End Sub

 

[dave]

Dear mr.Douglas,
thank you for reply,
yes you are right, doctorid filed is numeric and i dont want to assign it a
string,
i just want it to simply bring me a doctorid number from tabel "marketers"
based on current forms last and first name value.
hope this will clarify a little bit.
--
dave

I suspect DoctorId is a numeric field, and you're trying to assign it a
string.

Assuming what you really want is to look up DoctorId in table Marketers, you
need:

Private Sub doctorid_GotFocus()
Dim doctorid As Long
Dim SQL As String

If VISITTYPE = "First Visit" Then
Forms!marketers!doctorid = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) + 1
Else
Forms!marketers!doctorid = DLookup("[DOCTORID]", _
"marketers", "MARKETERS.LAST = " & Chr(34) & _
FORMS!MARKETERS.LAST & Chr(34) & _
" And MARKETERS.FIRST = " & Chr(34) & _
FORMS!MARKETERS.FIRST & Chr(34))
End If
End Sub


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

following is my code and is giving me runtime error-2113, please suggest
any
modications needed to the code.


Private Sub doctorid_GotFocus()
Dim doctorid As Long
Dim SQL As String

If VISITTYPE = "First Visit" Then
Forms!marketers!doctorid = Nz(DMax("[DOCTORID]", "MARKETERS"), 0) + 1
Else
Forms!marketers!doctorid = "SELECT MARKETERS.DOCTORID FROM marketers WHERE
(((MARKETERS.LAST)=FORMS!MARKETERS.LAST) And
((MARKETERS.FIRST)=FORMS!MARKETERS.FIRST))"

End If
End Sub