Run Time error 3061

[Jon]

Hello all:

I'm trying to modify an existing db for someone who wants to set
appointments in a customer db. They want to prevent someone from
double booking appts.

I've developed a few lines of code to do this, but I keep getting hung
on the SQL string. Here is the code:
------
Private Function CheckAppt(SALES MAN)
Dim oAppt
Dim strMsg

This line is the one that returns the 3061 error---->
Set oAppt = Application.Cur rentDb.OpenReco rdset("SELECT * FROM
[Lead-Appiontments] WHERE SALESMAN=" & SALESMAN)

If (APPT_DATE & APPT_TIME = LEAD - Appointments.AP PT_DATE & LEAD -
Appointments.AP PT_TIME) Then
strMsg = MsgBox("This Salesman already has a appointment set for
this date/time. Please choose another.", vbCritical, "Appointmen t
Scheduler")
End If

End Function
------------
The function is called from here:

Private Sub cboSalesman_Los tFocus()
CheckAppt cboSalesman.Col umn(0, cboSalesman.Tex t)
End Sub
------------
The Select statement runs fine. I think the problem is in the WHERE
clause. WHen I try to debug, the SALESMAN clause receives the current
record name so I know that is working fine. Any clues?

[Eric Schittlipz]

"Jon" <jf********@hot mail.com> wrote in message
news:17******** *************** ***@posting.goo gle.com...

Hello all:

I'm trying to modify an existing db for someone who wants to set
appointments in a customer db. They want to prevent someone from
double booking appts.

I've developed a few lines of code to do this, but I keep getting hung
on the SQL string. Here is the code:
------
Private Function CheckAppt(SALES MAN)
Dim oAppt
Dim strMsg

This line is the one that returns the 3061 error---->
Set oAppt = Application.Cur rentDb.OpenReco rdset("SELECT * FROM
[Lead-Appiontments] WHERE SALESMAN=" & SALESMAN)

If (APPT_DATE & APPT_TIME = LEAD - Appointments.AP PT_DATE & LEAD -
Appointments.AP PT_TIME) Then
strMsg = MsgBox("This Salesman already has a appointment set for
this date/time. Please choose another.", vbCritical, "Appointmen t
Scheduler")
End If

End Function
------------
The function is called from here:

Private Sub cboSalesman_Los tFocus()
CheckAppt cboSalesman.Col umn(0, cboSalesman.Tex t)
End Sub
------------
The Select statement runs fine. I think the problem is in the WHERE
clause. WHen I try to debug, the SALESMAN clause receives the current
record name so I know that is working fine. Any clues?

If the argument SALESMAN is a string value then why not say so in your
function?
Private Function CheckAppt(strSa lesman As String)

It is then clear you need some surrounding quotes:
strSQL = strSQL & " WHERE SALESMAN=""" & strSalesman & """"
Assuming that strSalesman could not itself contain quotes (you could check /
correct this)

And while you giving the argument a type, you could go wild and give your
function a return type, eg:
Private Function CheckAppt(strSa lesman As String) As Boolean

But I still can't see it quite working well. Your current function seems to
open up a recordset based on every single record in the appointments table
for that salesman. Ignoring for the moment how you would actually implement
it, surely a better function might be:

Private Function IsHeBusy(strSal esman As String, dteDateTime As Date) As
Boolean

[Trevor Best]

Jon wrote:

Hello all:

I'm trying to modify an existing db for someone who wants to set
appointments in a customer db. They want to prevent someone from
double booking appts.

I've developed a few lines of code to do this, but I keep getting hung
on the SQL string. Here is the code:
------
Private Function CheckAppt(SALES MAN)
Dim oAppt
Dim strMsg

This line is the one that returns the 3061 error---->
Set oAppt = Application.Cur rentDb.OpenReco rdset("SELECT * FROM
[Lead-Appiontments] WHERE SALESMAN=" & SALESMAN)

Assuming SALESMAN is a string (hint: naming conventions help around here
:-) then...

Set oAppt = Application.Cur rentDb.OpenReco rdset("SELECT * FROM
[Lead-Appiontments] WHERE SALESMAN='" & SALESMAN & "'")
--

\\\\\\
\\ \\ Windows is searching
\ \ For your sig.
\ \ Please Wait.
\__\