423,851 Members | 1,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

Run Time error 3061

P: n/a
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?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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


Nov 13 '05 #2

P: n/a
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.
\__\

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.