Why runtime error 3219?

[Guest]

openrecordset line highlighted in debugger.

Thank you!


Private Sub btnActiveMeds_Click()

Dim dbPractice As DAO.Database
Dim rcdActiveMeds As DAO.Recordset
Dim stDocName As String
Dim stMeds As String
Dim memoPlan
Set dbPractice = CurrentDb
memoPlan = Me![Plan]
stDocName = "qryActiveMeds"
DoCmd.Echo False
DoCmd.SetWarnings False
DoCmd.OpenQuery (stDocName)
Set rcdActiveMeds = dbPractice.OpenRecordset(stDocName)
Do Until rcdActiveMeds.EOF
stMeds = stMeds & rcdActiveMeds![Medication] & " " &
rcdActiveMeds![Strength] & " " & rcdActiveMeds![Instructions] & "; "
rcdActiveMeds.MoveNext
Loop
Me![Plan] = Me![Plan] & stMeds
DoCmd.SetWarnings True
DoCmd.Echo True
On Error GoTo Err_btnActiveMeds_Click


Exit_btnActiveMeds_Click:
Exit Sub

Err_btnActiveMeds_Click:
MsgBox Err.Description
Resume Exit_btnActiveMeds_Click

End Sub

 

[Guest]

the error code would seem to suggest there's an error in the SQL.

However, you've already opened this query the line before.... with
DoCmd.OpenQuery.

If it isn't an update query, remove the docmd.openquery line and try again.
If it still doesn't work, post the SQL and we'll have a look at that.

 

[Guest]

Still having compile errors when I use the SQL form of the select query, and
run time error when I put the query name in the set statement.

Thank you in advance. Still a novice.


Private Sub btnActiveMeds_Click()

Dim dbPractice As DAO.Database
Dim rcdActiveMeds As DAO.Recordset
Dim stDocName As String
Dim stMeds As String
Dim memoPlan
Set dbPractice = CurrentDb
memoPlan = Me![Plan]
stDocName = "qryActiveMeds"
'DoCmd.Echo False
'DoCmd.SetWarnings False
'DoCmd.OpenQuery (stDocName)

Set rcdActiveMeds = dbPractice.OpenRecordset(SELECT
Prescriptions.ClientID, Prescriptions.Medication, Prescriptions.Strength,
Prescriptions.Instructions, Prescriptions.[D/C] FROM Prescriptions WHERE
(((Prescriptions.ClientID)=[forms]![frmPatients]![ClientID]) AND
((Prescriptions.[D/C]) Is Null))
Do Until rcdActiveMeds.EOF
stMeds = stMeds & rcdActiveMeds![Medication] & " " &
rcdActiveMeds![Strength] & " " & rcdActiveMeds![Instructions] & "; "
rcdActiveMeds.MoveNext
Loop
Me![Plan] = Me![Plan] & stMeds
'DoCmd.SetWarnings True
'DoCmd.Echo True

 

[Tim Ferguson]

Set rcdActiveMeds = dbPractice.OpenRecordset(SELECT
Prescriptions.ClientID, Prescriptions.Medication,
Prescriptions.Strength, Prescriptions.Instructions,
Prescriptions.[D/C] FROM Prescriptions WHERE
(((Prescriptions.ClientID)=[forms]![frmPatients]![ClientID]) AND
((Prescriptions.[D/C]) Is Null))

This is not even legal VBA and I don't see how you got to type it in....
If you do quote code here, it is very helpful to do an actual copy-paste
rather than re-introducing more typos for us to find.

Try this approach:

jetSQL = "SELECT ClienID, Medication, Strength, Instructions, " & _
"[D/C] " & vbNewLine & _
"FROM Prescriptions " & vbNewLine & _
"WHERE ClientID = """ & Forms!frmPatients!ClientID & """" & _
" AND [D/C] IS NULL"

' this code assumes that ClientID is a text value; if it's a number
' then you need to drop the embedded quotes like this:
' "WHERE ClientID = " & Forms!frmPatients!ClientID & _

' this code also assumes that you have checked for an empty ClientID
' text box

' by the way, why are we selecting ClientID and [D/C] when we already
' know their values? Looking at your code, you only need the
' Medication, Strength, and Instructions fields.
'
' by the way (2), [D/C] is a really crummy field name

' Now see what you have done
debug.assert vbYes = MsgBox(jetSQL, vbYesNo, "Is this okay?")

' and send it to the db engine
Set rcdActiveMeds = _
db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)


' and so on


In a word, Keep It Simple, Stoopid... and also note the ratio of comment
lines to code lines. You'll be grateful six months later, when it's time
to review your programming...

Hope it helps


Tim F

 

[Guest]

Thank you. I did cut and paste from the SQL view of the query editor. This
was my first attempt at using SQL. You've given me a lot to study and learn
from.

Set rcdActiveMeds = dbPractice.OpenRecordset(SELECT
Prescriptions.ClientID, Prescriptions.Medication,
Prescriptions.Strength, Prescriptions.Instructions,
Prescriptions.[D/C] FROM Prescriptions WHERE
(((Prescriptions.ClientID)=[forms]![frmPatients]![ClientID]) AND
((Prescriptions.[D/C]) Is Null))

This is not even legal VBA and I don't see how you got to type it in....
If you do quote code here, it is very helpful to do an actual copy-paste
rather than re-introducing more typos for us to find.

Try this approach:

jetSQL = "SELECT ClienID, Medication, Strength, Instructions, " & _
"[D/C] " & vbNewLine & _
"FROM Prescriptions " & vbNewLine & _
"WHERE ClientID = """ & Forms!frmPatients!ClientID & """" & _
" AND [D/C] IS NULL"

' this code assumes that ClientID is a text value; if it's a number
' then you need to drop the embedded quotes like this:
' "WHERE ClientID = " & Forms!frmPatients!ClientID & _

' this code also assumes that you have checked for an empty ClientID
' text box

' by the way, why are we selecting ClientID and [D/C] when we already
' know their values? Looking at your code, you only need the
' Medication, Strength, and Instructions fields.
'
' by the way (2), [D/C] is a really crummy field name

' Now see what you have done
debug.assert vbYes = MsgBox(jetSQL, vbYesNo, "Is this okay?")

' and send it to the db engine
Set rcdActiveMeds = _
db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)


' and so on


In a word, Keep It Simple, Stoopid... and also note the ratio of comment
lines to code lines. You'll be grateful six months later, when it's time
to review your programming...

Hope it helps


Tim F

 

[Tim Ferguson]

Thank you. I did cut and paste from the SQL view of the query editor.
This was my first attempt at using SQL.

Yes: the SQL was valid SQL[1], but the VBA it was embedded in was not
valid VBA. It is not hard to get hold of the basics of VBA programming,
but you do need to understand how to manipulate strings and variables
properly.


[1] but it's still horrid SQL. Because the query editor has no way of
knowing what is ambiguous or overly complex, it has to chuck unneccessary
brackets round everything etc. Hand-crafted SQL can be -- and should be
-- easily human readable.

SELECT ALL FirstName, LastName
FROM Employees
WHERE Department="Sales"

What could be more natural than that?


All the best


Tim F

 

[Guest]

The prescriptions table includes records for unwanted clientid's, and for the
wanted clientid records with discontinuation dates(these records are also
unwanted).

How do I set up a query without the "where" items, then?

Don't I need a parameters clause to get the needed clientid from the open
form?

Thanks. I AM having a **** of a time making use of the runtime and compile
error messages.

Set rcdActiveMeds = dbPractice.OpenRecordset(SELECT
Prescriptions.ClientID, Prescriptions.Medication,
Prescriptions.Strength, Prescriptions.Instructions,
Prescriptions.[D/C] FROM Prescriptions WHERE
(((Prescriptions.ClientID)=[forms]![frmPatients]![ClientID]) AND
((Prescriptions.[D/C]) Is Null))

This is not even legal VBA and I don't see how you got to type it in....
If you do quote code here, it is very helpful to do an actual copy-paste
rather than re-introducing more typos for us to find.

Try this approach:

jetSQL = "SELECT ClienID, Medication, Strength, Instructions, " & _
"[D/C] " & vbNewLine & _
"FROM Prescriptions " & vbNewLine & _
"WHERE ClientID = """ & Forms!frmPatients!ClientID & """" & _
" AND [D/C] IS NULL"

' this code assumes that ClientID is a text value; if it's a number
' then you need to drop the embedded quotes like this:
' "WHERE ClientID = " & Forms!frmPatients!ClientID & _

' this code also assumes that you have checked for an empty ClientID
' text box

' by the way, why are we selecting ClientID and [D/C] when we already
' know their values? Looking at your code, you only need the
' Medication, Strength, and Instructions fields.
'
' by the way (2), [D/C] is a really crummy field name

' Now see what you have done
debug.assert vbYes = MsgBox(jetSQL, vbYesNo, "Is this okay?")

' and send it to the db engine
Set rcdActiveMeds = _
db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)


' and so on


In a word, Keep It Simple, Stoopid... and also note the ratio of comment
lines to code lines. You'll be grateful six months later, when it's time
to review your programming...

Hope it helps


Tim F

 

[Guest]

now I get a syntax error in my parameters statement:


Private Sub btnActiveMeds_Click()

Dim dbPractice As DAO.Database
Dim rcdActiveMeds As DAO.Recordset
Dim stDocName As String
Dim stMeds As String
Dim SQLMeds As String
'Dim cPatientNumber As Long
'cPatientNumber = Forms![frmPatients]![ClientID]
SQLMeds = "PARAMETERS Forms![frmPatients]![ClientID] long" & vbCr _
& " SELECT Prescriptions.ClientID, Prescriptions.Date," & vbCr _
& " Prescriptions.Medication, Prescriptions.Strength,
Prescriptions.Instructions, Prescriptions.[D/C]" & vbCr _
& " FROM Prescriptions" & vbCr _
& " WHERE (Prescriptions.ClientID=Forms![frmPatients]![ClientID])"
'AND ((Prescriptions.[D/C]) Is Null))"
Dim qActiveMeds As AccessObject
Dim memoPlan
Set dbPractice = CurrentDb
memoPlan = Me![Plan]
'DoCmd.Echo False
'DoCmd.SetWarnings False
'DoCmd.OpenQuery (stDocName)
Set rcdActiveMeds = dbPractice.OpenRecordset(SQLMeds)
Do Until rcdActiveMeds.EOF
stMeds = stMeds & rcdActiveMeds![Medication] & " " &
rcdActiveMeds![Strength] & " " & rcdActiveMeds![Instructions] & "; "
rcdActiveMeds.MoveNext
Loop
Me![Plan] = Me![Plan] & stMeds
'DoCmd.SetWarnings True
'DoCmd.Echo True
On Error GoTo Err_btnActiveMeds_Click


Exit_btnActiveMeds_Click:
Exit Sub

Err_btnActiveMeds_Click:
MsgBox Err.Description
Resume Exit_btnActiveMeds_Click

End Sub

Set rcdActiveMeds = dbPractice.OpenRecordset(SELECT
Prescriptions.ClientID, Prescriptions.Medication,
Prescriptions.Strength, Prescriptions.Instructions,
Prescriptions.[D/C] FROM Prescriptions WHERE
(((Prescriptions.ClientID)=[forms]![frmPatients]![ClientID]) AND
((Prescriptions.[D/C]) Is Null))

This is not even legal VBA and I don't see how you got to type it in....
If you do quote code here, it is very helpful to do an actual copy-paste
rather than re-introducing more typos for us to find.

Try this approach:

jetSQL = "SELECT ClienID, Medication, Strength, Instructions, " & _
"[D/C] " & vbNewLine & _
"FROM Prescriptions " & vbNewLine & _
"WHERE ClientID = """ & Forms!frmPatients!ClientID & """" & _
" AND [D/C] IS NULL"

' this code assumes that ClientID is a text value; if it's a number
' then you need to drop the embedded quotes like this:
' "WHERE ClientID = " & Forms!frmPatients!ClientID & _

' this code also assumes that you have checked for an empty ClientID
' text box

' by the way, why are we selecting ClientID and [D/C] when we already
' know their values? Looking at your code, you only need the
' Medication, Strength, and Instructions fields.
'
' by the way (2), [D/C] is a really crummy field name

' Now see what you have done
debug.assert vbYes = MsgBox(jetSQL, vbYesNo, "Is this okay?")

' and send it to the db engine
Set rcdActiveMeds = _
db.OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)


' and so on


In a word, Keep It Simple, Stoopid... and also note the ratio of comment
lines to code lines. You'll be grateful six months later, when it's time
to review your programming...

Hope it helps


Tim F

 

[Tim Ferguson]

There seems to be a number of problems with this code: I'll try to pick
up some of them as we go...

Private Sub btnActiveMeds_Click()

Dim dbPractice As DAO.Database
Dim rcdActiveMeds As DAO.Recordset
Dim stDocName As String
Dim stMeds As String
Dim SQLMeds As String
'Dim cPatientNumber As Long
'cPatientNumber = Forms![frmPatients]![ClientID]

I've reformatted the next bit, non-lossy I think...

SQLMeds = "PARAMETERS Forms![frmPatients]![ClientID] long" & vbCr _

You need a semicolon after the PARAMETERS clause to separate it from the
SELECT clause. Off-hand I don't know if the square brackets are legal
here as I very rarely use this conformation.

& " SELECT ClientID, Date," & vbCr _

Date is a reserved word in VBA and in SQL - it sucks as a field name. If
you really feel that you have to use it, it has to be covered in
[square brackets].

& " Medication, Strength, Instructions, [D/C]" & vbCr _
& " FROM Prescriptions" & vbCr _
& " WHERE (Prescriptions.ClientID=Forms![frmPatients]![ClientID])"
'AND ((Prescriptions.[D/C]) Is Null))"

The syntax here is messed up: you have a 'single quote where you should
have a double one, and there are no ampersands joining up the bits. Try

& " WHERE ClientID = Forms!frmPatients!ClientID " & vbNewLine _
& " AND [D/C] IS NULL"

When you are creating in-line SQL, it's really helpful to be able to
check what you have just done. Include either a debug.print or a MsgBox
and _inspect_ it, otherwise you really don't stand a chance.

Dim qActiveMeds As AccessObject

What is an AccessObject?

Dim memoPlan

This declares a Variant - is that what you want?

Set dbPractice = CurrentDb
memoPlan = Me![Plan]
'DoCmd.Echo False
'DoCmd.SetWarnings False
'DoCmd.OpenQuery (stDocName)
Set rcdActiveMeds = dbPractice.OpenRecordset(SQLMeds)

It's really not a good idea to use defaults on OpenRecordset.. always
pass the parameters you really want. In this case, you need a forward
only snapshot.

Second, I really don't think that the Forms!etc!etc reference will be
picked up in this way even using the PARAMETERS clause: it works for
things going through the Access UI, like .OpenQuerydef and .OpenReport,
but this code works directly at the db engine.

You haven't initialised stMeds as far as I can see.

Do Until rcdActiveMeds.EOF
stMeds = stMeds & rcdActiveMeds![Medication] & " " & _
rcdActiveMeds![Strength] & " " & _
rcdActiveMeds![Instructions] & "; "
rcdActiveMeds.MoveNext
Loop
Me![Plan] = Me![Plan] & stMeds

I'm not quite sure what I can do to help. You seem to have taken no
notice of any of the problems I tried to point out previously, and
instead you've stuck in a whole load of new bugs and complications. All
without the benefit of a single line of comment -- you are going to weep
when you try to read this again in six months' time.

Best of luck.

Tim F

 

[Guest]

The following code now works:

Dim dbPractice As DAO.Database
Dim rcdActiveMeds As DAO.Recordset
Dim stMeds As String
Dim SQLMeds As String
Set dbPractice = CurrentDb
'DoCmd.Echo False
'DoCmd.SetWarnings False
'(Prescriptions.ClientID=[forms]![frmPatients]![ClientID]) AND
stMeds = ""
SQLMeds = "SELECT Prescriptions.ClientID,
Prescriptions.PrescriptionDate, Prescriptions.Medication," _
& " Prescriptions.Strength, Prescriptions.Instructions,
Prescriptions.DCDate" _
& " FROM Prescriptions" _
& " WHERE IsNull(Prescriptions.DCDate)"
'Debug.Assert vbYes = MsgBox(SQLMeds, vbYesNo, "Is this Okay?")
Set rcdActiveMeds = dbPractice.OpenRecordset(SQLMeds) ' dbOpenDynaset,
dbOpenForwardOnly)
Do Until rcdActiveMeds.EOF
stMeds = stMeds & rcdActiveMeds![Medication] &
rcdActiveMeds![Strength] & rcdActiveMeds![Instructions]
rcdActiveMeds.MoveNext
Loop
Me![Plan] = Me![Plan] & stMeds
'DoCmd.SetWarnings True
'DoCmd.Echo True

However, I need to add a criterion which refers to a control on the form
containing this code. When I include the reference, there is a runtime
error, even though the immediate window correctly evaluates the reference.
So the problem is in my WHERE clause.

" WHERE IsNull(Prescriptions.DCDate)" works

" WHERE (Prescriptions.ClientID=[forms]![frmPatients]![ClientID]) AND
IsNull(Prescriptions.DCDate)"


Does NOT work.
Can someone explain why this addition causes the runtime errors?
Where can I find the rules for this kind of code?

thank you!

 

[John Spencer (MVP)]

Try

" WHERE (Prescriptions.ClientID=" & [forms]![frmPatients]![ClientID]) & _
" AND IsNull(Prescriptions.DCDate)"

This gets the value and puts it into the SQL string. Jet (the relational
database used with Access) does not know about the controls in the forms, so you
have to tell it the value.

If Prescriptions.ClientID is a string and not a number then you will need to add
the quote or apostrophe string delimiter. I prefer the quote - Chr(34)- as it
is less likely to appear in the string.

" WHERE (Prescriptions.ClientID=" & _
Chr(34) &[forms]![frmPatients]![ClientID]) & Chr(34) & _
" AND IsNull(Prescriptions.DCDate)"