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(SALESMAN)
Dim oAppt
Dim strMsg

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

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

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

Private Sub cboSalesman_LostFocus()
CheckAppt cboSalesman.Column(0, cboSalesman.Text)
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********@hotmail.com> wrote in message
news:17**************************@posting.google.c om...

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(SALESMAN)
Dim oAppt
Dim strMsg

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

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

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

Private Sub cboSalesman_LostFocus()
CheckAppt cboSalesman.Column(0, cboSalesman.Text)
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(strSalesman 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(strSalesman 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(strSalesman 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(SALESMAN)
Dim oAppt
Dim strMsg

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

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

Set oAppt = Application.CurrentDb.OpenRecordset("SELECT * FROM
[Lead-Appiontments] WHERE SALESMAN='" & SALESMAN & "'")
--

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