Error 3021 No Current Record

Guest · Apr 8, 2005

i got the said error when i tried to run this code and the "Patient Number"
did not exist in the underlying table ("Concomitant Medications")

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.description
Resume Exit_Add_Record_DblClick

End Sub

on the other hand, a virtually identical piece of vba intended to perform
the same task, albeit using different forms works w/o a hitch, viz:

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_Click
Dim sql As String
Dim pn As Long
Dim ln As Integer

pn = [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
ln = Nz(DMax("[Lesion Number]", "[Lesions: Non-Target]", "[Patient
Number] = " _
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]), 0) + 1

sql = "INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(ln) & " AS LN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Lesion Number]=" &
CStr(ln)

Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

i'm basically a vba newbie and can't for the life of me find the achilees'
heel in all ofl this!!?

any help would be really good.

Marshall Barton · Apr 9, 2005

Ted said:
i got the said error when i tried to run this code and the "Patient Number"
did not exist in the underlying table ("Concomitant Medications")

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

Me.Bookmark = rs.Bookmark

The logic looks OK, but there are some differences from my
usual way of doing this kind of thing. Maybe, there's a
timing issue, some unreported error, or ???

First, I use RecordsetClone instead of Recordset.Clone
This may(?) make a difference?

Second, at least when debugging, you should use the Execute
method's dbFailOnError argument so that you are notified if
the append query has a problem (e.g. type mismatch).

Third, but only an efficiency issue, there is no real need
to use CStr in the query. Are you sure the two fields in
the table are numeric type? You would need quotes around
the value of a text type field.

Fourth, I would normally use:
If rs.Nomatch Then
MsgBox "something's wrong"
End If
right after the FindFirst. In this case, where you are
inserting the record you want to find, this comment is
probably irrelevant unless the append query failed for one
reason or another and the dbFailOnError should catch that
problem.

Ken Snell [MVP] · Apr 10, 2005

In my test, I get a Type Mismatch error when trying to set the form's
Bookmark (Me.Bookmark) to the recordset's bookmark (rst.Bookmark) when rst
is the Recordset.Clone.

Marsh correctly points out that you should be using RecordsetClone, not
Recordset.Clone, in your code.

--

Ken Snell
<MS ACCESS MVP>

Marshall Barton said:
Ted said:

i got the said error when i tried to run this code and the "Patient
Number"
did not exist in the underlying table ("Concomitant Medications")

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

Me.Bookmark = rs.Bookmark

Click to expand...

The logic looks OK, but there are some differences from my
usual way of doing this kind of thing. Maybe, there's a
timing issue, some unreported error, or ???

First, I use RecordsetClone instead of Recordset.Clone
This may(?) make a difference?

Second, at least when debugging, you should use the Execute
method's dbFailOnError argument so that you are notified if
the append query has a problem (e.g. type mismatch).

Third, but only an efficiency issue, there is no real need
to use CStr in the query. Are you sure the two fields in
the table are numeric type? You would need quotes around
the value of a text type field.

Fourth, I would normally use:
If rs.Nomatch Then
MsgBox "something's wrong"
End If
right after the FindFirst. In this case, where you are
inserting the record you want to find, this comment is
probably irrelevant unless the append query failed for one
reason or another and the dbFailOnError should catch that
problem.

Ken Snell [MVP] · Apr 10, 2005

Hmmmm.... but the Help files say that the bookmarks are compatible:

"Use the Clone method to create multiple, duplicate Recordset objects. Each
Recordset can have its own current record. Using Clone by itself doesn't
change the data in the objects or in their underlying structures. When you
use the Clone method, you can share bookmarks between two or more Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of the form's
recordset. When trying to set one of the clone's bookmark to the bookmark of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone method of a
form's recordset.

Guest · Apr 10, 2005

hi,

both patient and medication number will always come in as integer valued
numbers, yes. --- in each instance where i use this pair of vba scripts.

i don't know what difference is created by RecordSetClone vs RecordSet.Clone
-- if you wouldn't mind helping me expand by vba knowledge with a little help
on this one.

how would you write it w/o Cstr in it?

how would you go about implementing your suggestion
"you should use the Execute method's dbFailOnError argument so that you are
notified if the append query has a problem (e.g. type mismatch)."

-ted

Marshall Barton said:
Ted said:

i got the said error when i tried to run this code and the "Patient Number"
did not exist in the underlying table ("Concomitant Medications")

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

Me.Bookmark = rs.Bookmark

Click to expand...

The logic looks OK, but there are some differences from my
usual way of doing this kind of thing. Maybe, there's a
timing issue, some unreported error, or ???

First, I use RecordsetClone instead of Recordset.Clone
This may(?) make a difference?

Second, at least when debugging, you should use the Execute
method's dbFailOnError argument so that you are notified if
the append query has a problem (e.g. type mismatch).

Third, but only an efficiency issue, there is no real need
to use CStr in the query. Are you sure the two fields in
the table are numeric type? You would need quotes around
the value of a text type field.

Fourth, I would normally use:
If rs.Nomatch Then
MsgBox "something's wrong"
End If
right after the FindFirst. In this case, where you are
inserting the record you want to find, this comment is
probably irrelevant unless the append query failed for one
reason or another and the dbFailOnError should catch that
problem.

Guest · Apr 10, 2005

so, ken....where's all this leave yours truly?

-ted

Ken Snell said:
Hmmmm.... but the Help files say that the bookmarks are compatible:

"Use the Clone method to create multiple, duplicate Recordset objects. Each
Recordset can have its own current record. Using Clone by itself doesn't
change the data in the objects or in their underlying structures. When you
use the Clone method, you can share bookmarks between two or more Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of the form's
recordset. When trying to set one of the clone's bookmark to the bookmark of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone method of a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

Ken Snell [MVP] · Apr 11, 2005

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

Ted said:
so, ken....where's all this leave yours truly?

-ted

Marshall Barton · Apr 11, 2005

Don't worry about the CStr comment. In your code the CStr is
unnecessary because Access with convert the numbers
automatically, but there's nothing wrong with using explicit
conversions.

Check Help on all the options you can use with the Execute
method. Using:

ds.Execute sql, dbFailOnError

will jump to your error handler code if anything goes wrong
during the execution of the query so you can find out about
the problem. Without dbFailOnError, you won't know whether
the query did anything or not.

As I said before, I don't know the nuances between
Recordset.Clone and RecordsetClone, but Ken seems to have
found a major issue. Give it a try and see what happens.
--
Marsh
MVP [MS Access]

Ted said:
both patient and medication number will always come in as integer valued
numbers, yes. --- in each instance where i use this pair of vba scripts.

i don't know what difference is created by RecordSetClone vs RecordSet.Clone
-- if you wouldn't mind helping me expand by vba knowledge with a little help
on this one.

how would you write it w/o Cstr in it?

how would you go about implementing your suggestion
"you should use the Execute method's dbFailOnError argument so that you are
notified if the append query has a problem (e.g. type mismatch)."

Ted said:

i got the said error when i tried to run this code and the "Patient Number"
did not exist in the underlying table ("Concomitant Medications")

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

Me.Bookmark = rs.Bookmark

Click to expand...

Click to expand...

Marshall Barton said:

The logic looks OK, but there are some differences from my
usual way of doing this kind of thing. Maybe, there's a
timing issue, some unreported error, or ???

First, I use RecordsetClone instead of Recordset.Clone
This may(?) make a difference?

Second, at least when debugging, you should use the Execute
method's dbFailOnError argument so that you are notified if
the append query has a problem (e.g. type mismatch).

Third, but only an efficiency issue, there is no real need
to use CStr in the query. Are you sure the two fields in
the table are numeric type? You would need quotes around
the value of a text type field.

Fourth, I would normally use:
If rs.Nomatch Then
MsgBox "something's wrong"
End If
right after the FindFirst. In this case, where you are
inserting the record you want to find, this comment is
probably irrelevant unless the append query failed for one
reason or another and the dbFailOnError should catch that
problem.

Click to expand...

Guest · Apr 11, 2005

hi ken,

using the vba (below) it matters not whether i use one or the other, it's
now giving me a "424 = Object required" error message. i put a breakpoint on
the 'pn = ' statement and i "F8"ed my way down the code where i see it jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

Ken Snell [MVP] · Apr 11, 2005

You didn't change to Me.RecordsetClone in your code; you're still using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute it
because you're not delimiting the string values with ' characters when you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

using the vba (below) it matters not whether i use one or the other, it's
now giving me a "424 = Object required" error message. i put a breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

Ken Snell said:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

Click to expand...

Guest · Apr 11, 2005

hi ken,

using my best copy and paste strategies, the code i'm using (and i'll tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn & "'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's showing
itself :-(

-ted

Ken Snell said:
You didn't change to Me.RecordsetClone in your code; you're still using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute it
because you're not delimiting the string values with ' characters when you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:

hi ken,

using the vba (below) it matters not whether i use one or the other, it's
now giving me a "424 = Object required" error message. i put a breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

Ken Snell said:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are compatible:

"Use the Clone method to create multiple, duplicate Recordset objects.
Each
Recordset can have its own current record. Using Clone by itself
doesn't
change the data in the objects or in their underlying structures. When
you
use the Clone method, you can share bookmarks between two or more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of the
form's
recordset. When trying to set one of the clone's bookmark to the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

In my test, I get a Type Mismatch error when trying to set the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark (rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Guest · Apr 11, 2005

ken, i almost forgot to add the fact that in the same a2k mdb file, this code
(which to this vba newbie is virtually identical to the 1 we're trying to
dbug) works like a charm

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_Click
Dim sql As String
Dim pn As Long
Dim ln As Integer

pn = [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]
ln = Nz(DMax("[Lesion Number]", "[Lesions: Non-Target]", "[Patient
Number] = " _
& [Forms]![RECIST Disease Evaluation: Nontarget Lesions]![Patient
Number]), 0) + 1

sql = "INSERT INTO [Lesions: Non-Target] ( [Patient Number], [Lesion
Number] )" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(ln) & " AS LN;"

Dim db As Database
Set db = CurrentDb
db.Execute sql
Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Lesion Number]=" &
CStr(ln)

Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_Click

End Sub

any theories?

-ted

Ken Snell said:
You didn't change to Me.RecordsetClone in your code; you're still using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute it
because you're not delimiting the string values with ' characters when you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:

hi ken,

using the vba (below) it matters not whether i use one or the other, it's
now giving me a "424 = Object required" error message. i put a breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

Ken Snell said:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are compatible:

"Use the Clone method to create multiple, duplicate Recordset objects.
Each
Recordset can have its own current record. Using Clone by itself
doesn't
change the data in the objects or in their underlying structures. When
you
use the Clone method, you can share bookmarks between two or more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of the
form's
recordset. When trying to set one of the clone's bookmark to the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

In my test, I get a Type Mismatch error when trying to set the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark (rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Ken Snell [MVP] · Apr 11, 2005

Let me back up.

I reviewed the thread and didn't find that you identified the exact line on
which the error 424 is occurring? Can you identify that step?

What is the RecordSource query of the form in which this code is running? If
it's a table, please indicate the fields that are in that table? Is the form
filtered in any way when it's opened (in the DoCmd.OpenForm action)?

And let me confirm.. this error is occurring only when your form has no
records in it?

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

using my best copy and paste strategies, the code i'm using (and i'll tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's
showing
itself :-(

-ted

Ken Snell said:

You didn't change to Me.RecordsetClone in your code; you're still using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute it
because you're not delimiting the string values with ' characters when
you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:

hi ken,

using the vba (below) it matters not whether i use one or the other,
it's
now giving me a "424 = Object required" error message. i put a
breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are compatible:

"Use the Clone method to create multiple, duplicate Recordset
objects.
Each
Recordset can have its own current record. Using Clone by itself
doesn't
change the data in the objects or in their underlying structures.
When
you
use the Clone method, you can share bookmarks between two or more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of the
form's
recordset. When trying to set one of the clone's bookmark to the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone
method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

message
In my test, I get a Type Mismatch error when trying to set the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark (rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using
RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Guest · Apr 11, 2005

i will try and intersperse my responses where they would naturally flow ken.

Ken Snell said:
Let me back up.

I reviewed the thread and didn't find that you identified the exact line on
which the error 424 is occurring? Can you identify that step?

w/ my breakpoint on the 'pn' statement atop this, i 'F8' my way one step @
a time and when the yellow highlight is on the " ds.Execute sql,
dbFailOnerror" line as i 'F8' it jumps to the MsgBox Err.Number & " = " &
Err.description line.

What is the RecordSource query of the form in which this code is running?

IT IS A TABLE AND ITS NAMED 'Concomitant Medications'.

If

it's a table, please indicate the fields that are in that table?

THERE ARE MANY MANY FIELDS IN THAT TABLE. "Patient Number" and "Med Number"
are the two which make up the composite PK (in that same order). DO YOU
REQUIRE A LIST OF ALL THE FIELDS TO ANSWER YOUR QUESTION. I WILL PROVIDE IT
IF ABSOLUTELY NEEDED.

Is the form

filtered in any way when it's opened (in the DoCmd.OpenForm action)?

YES IT IS FILTERED. IT'S FILTERED VIA THE CODE BELOW

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Then
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
' Stop the form from opening
Cancel = True
Else
' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient]

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub

And let me confirm.. this error is occurring only when your form has no
records in it?

THIS OCCURS WHETHER OR NOT THE FORM HAS RECORDS IN IT.

HTH

-ted

Ted said:
--

Ken Snell
<MS ACCESS MVP>

Ted said:

hi ken,

using my best copy and paste strategies, the code i'm using (and i'll tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's
showing
itself :-(

-ted

Ken Snell said:

You didn't change to Me.RecordsetClone in your code; you're still using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute it
because you're not delimiting the string values with ' characters when
you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using the vba (below) it matters not whether i use one or the other,
it's
now giving me a "424 = Object required" error message. i put a
breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] = " &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are compatible:

"Use the Clone method to create multiple, duplicate Recordset
objects.
Each
Recordset can have its own current record. Using Clone by itself
doesn't
change the data in the objects or in their underlying structures.
When
you
use the Clone method, you can share bookmarks between two or more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of the
form's
recordset. When trying to set one of the clone's bookmark to the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone
method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

message
In my test, I get a Type Mismatch error when trying to set the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark (rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using
RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Ken Snell [MVP] · Apr 11, 2005

Aha... my eyes are getting worse as I age....

Your code never sets ds variable to any object.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

db.Execute sql, dbFailOnError

Me.Requery

Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:
i will try and intersperse my responses where they would naturally flow
ken.

Ken Snell said:

Let me back up.

I reviewed the thread and didn't find that you identified the exact line
on
which the error 424 is occurring? Can you identify that step?

Click to expand...

w/ my breakpoint on the 'pn' statement atop this, i 'F8' my way one step @
a time and when the yellow highlight is on the " ds.Execute sql,
dbFailOnerror" line as i 'F8' it jumps to the MsgBox Err.Number & " = " &
Err.description line.

What is the RecordSource query of the form in which this code is running?

Click to expand...

IT IS A TABLE AND ITS NAMED 'Concomitant Medications'.

If

it's a table, please indicate the fields that are in that table?

Click to expand...

THERE ARE MANY MANY FIELDS IN THAT TABLE. "Patient Number" and "Med
Number"
are the two which make up the composite PK (in that same order). DO YOU
REQUIRE A LIST OF ALL THE FIELDS TO ANSWER YOUR QUESTION. I WILL PROVIDE
IT
IF ABSOLUTELY NEEDED.

Is the form

filtered in any way when it's opened (in the DoCmd.OpenForm action)?

Click to expand...

YES IT IS FILTERED. IT'S FILTERED VIA THE CODE BELOW

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Then
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
' Stop the form from opening
Cancel = True
Else
' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient]

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub

And let me confirm.. this error is occurring only when your form has no
records in it?

Click to expand...

THIS OCCURS WHETHER OR NOT THE FORM HAS RECORDS IN IT.

HTH

-ted

--

Ken Snell
<MS ACCESS MVP>

Ted said:

hi ken,

using my best copy and paste strategies, the code i'm using (and i'll
tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's
showing
itself :-(

-ted

:

You didn't change to Me.RecordsetClone in your code; you're still
using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute
it
because you're not delimiting the string values with ' characters when
you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" &
mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using the vba (below) it matters not whether i use one or the other,
it's
now giving me a "424 = Object required" error message. i put a
breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see
it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] =
" &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are
compatible:

"Use the Clone method to create multiple, duplicate Recordset
objects.
Each
Recordset can have its own current record. Using Clone by itself
doesn't
change the data in the objects or in their underlying
structures.
When
you
use the Clone method, you can share bookmarks between two or
more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of
the
form's
recordset. When trying to set one of the clone's bookmark to the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone
method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

message
In my test, I get a Type Mismatch error when trying to set the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark
(rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using
RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Guest · Apr 11, 2005

well ken, this is getting really interesting. we've removed the 424 error
only to have it replaced with

"3314 = The field 'Concomitant Medications.Protocol ID' cannot contain a
Null value because the Required property for the field isset to True. Enter a
value in this field".

i don't know whether this helps, hopefully it does, but in the subform-open
code i pasted in my earlier, there's some relevant looing code

Fill in Protocol ID value
-ted

Ken Snell said:
Aha... my eyes are getting worse as I age....

Your code never sets ds variable to any object.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

db.Execute sql, dbFailOnError

Me.Requery

Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn &
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:

i will try and intersperse my responses where they would naturally flow
ken.

Ken Snell said:

Let me back up.

I reviewed the thread and didn't find that you identified the exact line
on
which the error 424 is occurring? Can you identify that step?

Click to expand...

w/ my breakpoint on the 'pn' statement atop this, i 'F8' my way one step @
a time and when the yellow highlight is on the " ds.Execute sql,
dbFailOnerror" line as i 'F8' it jumps to the MsgBox Err.Number & " = " &
Err.description line.

What is the RecordSource query of the form in which this code is running?

Click to expand...

IT IS A TABLE AND ITS NAMED 'Concomitant Medications'.

If

it's a table, please indicate the fields that are in that table?

Click to expand...

THERE ARE MANY MANY FIELDS IN THAT TABLE. "Patient Number" and "Med
Number"
are the two which make up the composite PK (in that same order). DO YOU
REQUIRE A LIST OF ALL THE FIELDS TO ANSWER YOUR QUESTION. I WILL PROVIDE
IT
IF ABSOLUTELY NEEDED.

Is the form

filtered in any way when it's opened (in the DoCmd.OpenForm action)?

Click to expand...

YES IT IS FILTERED. IT'S FILTERED VIA THE CODE BELOW

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Then
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
' Stop the form from opening
Cancel = True
Else
' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient]

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID", "tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub

And let me confirm.. this error is occurring only when your form has no
records in it?

Click to expand...

THIS OCCURS WHETHER OR NOT THE FORM HAS RECORDS IN IT.

HTH

-ted

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using my best copy and paste strategies, the code i'm using (and i'll
tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's
showing
itself :-(

-ted

:

You didn't change to Me.RecordsetClone in your code; you're still
using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to execute
it
because you're not delimiting the string values with ' characters when
you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" &
mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using the vba (below) it matters not whether i use one or the other,
it's
now giving me a "424 = Object required" error message. i put a
breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i see
it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number] =
" &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are
compatible:

"Use the Clone method to create multiple, duplicate Recordset
objects.
Each
Recordset can have its own current record. Using Clone by itself
doesn't
change the data in the objects or in their underlying
structures.
When
you
use the Clone method, you can share bookmarks between two or
more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of
the
form's
recordset. When trying to set one of the clone's bookmark to the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of Clone
method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

message
In my test, I get a Type Mismatch error when trying to set the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark
(rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using
RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Ken Snell [MVP] · Apr 11, 2005

Sounds as if you need to include the Protocol ID field in your append query.
Not sure if the subform's code is generating a valid value for you to use,
as I am not understanding your form's setup.

However, assuming that you can get the necessary value for Protocol ID, your
append query should be changed to something like this (replace the string
"The Protocol ID Value" with a reference to a control that has this value,
or with the actual value as a constant, or something):

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number], [Protocol ID])" & _
"SELECT '" & pn & "', '" & mn & "', " & "The Protocol ID Value" & ";"

--

Ken Snell
<MS ACCESS MVP>

Ted said:
well ken, this is getting really interesting. we've removed the 424 error
only to have it replaced with

"3314 = The field 'Concomitant Medications.Protocol ID' cannot contain a
Null value because the Required property for the field isset to True.
Enter a
value in this field".

i don't know whether this helps, hopefully it does, but in the
subform-open
code i pasted in my earlier, there's some relevant looing code

Fill in Protocol ID value
-ted

Ken Snell said:

Aha... my eyes are getting worse as I age....

Your code never sets ds variable to any object.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

db.Execute sql, dbFailOnError

Me.Requery

Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

Ted said:

i will try and intersperse my responses where they would naturally flow
ken.

:

Let me back up.

I reviewed the thread and didn't find that you identified the exact
line
on
which the error 424 is occurring? Can you identify that step?

w/ my breakpoint on the 'pn' statement atop this, i 'F8' my way one
step @
a time and when the yellow highlight is on the " ds.Execute sql,
dbFailOnerror" line as i 'F8' it jumps to the MsgBox Err.Number & " =
" &
Err.description line.

What is the RecordSource query of the form in which this code is
running?

IT IS A TABLE AND ITS NAMED 'Concomitant Medications'.

If
it's a table, please indicate the fields that are in that table?

THERE ARE MANY MANY FIELDS IN THAT TABLE. "Patient Number" and "Med
Number"
are the two which make up the composite PK (in that same order). DO YOU
REQUIRE A LIST OF ALL THE FIELDS TO ANSWER YOUR QUESTION. I WILL
PROVIDE
IT
IF ABSOLUTELY NEEDED.

Is the form
filtered in any way when it's opened (in the DoCmd.OpenForm action)?

YES IT IS FILTERED. IT'S FILTERED VIA THE CODE BELOW

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Then
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
' Stop the form from opening
Cancel = True
Else
' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient]

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID",
"tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub

And let me confirm.. this error is occurring only when your form has
no
records in it?

THIS OCCURS WHETHER OR NOT THE FORM HAS RECORDS IN IT.

HTH

-ted

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using my best copy and paste strategies, the code i'm using (and
i'll
tell
you in one shake of a lamb's tail how it's failing) is as under
(with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" &
mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's
showing
itself :-(

-ted

:

You didn't change to Me.RecordsetClone in your code; you're still
using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to
execute
it
because you're not delimiting the string values with ' characters
when
you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]",
"[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '"
&
mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using the vba (below) it matters not whether i use one or the
other,
it's
now giving me a "424 = Object required" error message. i put a
breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i
see
it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]",
"[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number]
=
" &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone
recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are
compatible:

"Use the Clone method to create multiple, duplicate Recordset
objects.
Each
Recordset can have its own current record. Using Clone by
itself
doesn't
change the data in the objects or in their underlying
structures.
When
you
use the Clone method, you can share bookmarks between two or
more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of
the
form's
recordset. When trying to set one of the clone's bookmark to
the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of
Clone
method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

message
In my test, I get a Type Mismatch error when trying to set
the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark
(rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using
RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

Marshall Barton · Apr 11, 2005

Ted said:
using my best copy and paste strategies, the code i'm using (and i'll tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')
[]
sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

Another point. I see you removed the CStr functions, but
you added extra quotes inplace of it. The quotes should
cause a type mismatch error, since you said the two fields
are numeric.

"SELECT " & pn & ", " & mn

and the same issue in the FindFirst:

rs.FindFirst "[Patient Number]=" & pn & " and [Med
Number]=" & mn

Guest · Apr 11, 2005

i will remove the single apostrophes. but what's REALLLLLLY puzzling is that
essentially the same code 'but for' a substitution of "Command and Control
Center", Med Number, and "Concomitant Medications" for their counterparts in
it WORKS!
what's so all importantly unusual about what i'm trying to do with
"Concomitant Medications" or the environment surrounding it that's resulting
in all the 'hooplah"?

-ted

Marshall Barton said:
Ted said:

using my best copy and paste strategies, the code i'm using (and i'll tell
you in one shake of a lamb's tail how it's failing) is as under (with a
breakpoint on 'pn')
[]
sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

Click to expand...

Another point. I see you removed the CStr functions, but
you added extra quotes inplace of it. The quotes should
cause a type mismatch error, since you said the two fields
are numeric.

"SELECT " & pn & ", " & mn

and the same issue in the FindFirst:

rs.FindFirst "[Patient Number]=" & pn & " and [Med
Number]=" & mn

Guest · Apr 11, 2005

let me interject my humble question again.....what are we overlooking? i have
essentially the essentially identical code to the original i pasted when this
thread began doing its thing in the same a2k mdb database.....'but for' some
necessary switches to "Concomitant Medications", "Command and Control
Center", "Med Number" from the counterparts in the working vba. what would
need to be different about the operating conditions surrounding the one we're
debugging for the vba to run aground? perhaps there's something i'm leaving
out in all these details that needs to accounted for, responsible? i am
getting the feeling that if we keep putting out these little fires we're not
addressing the bigger picture somehow.

-ted

Ken Snell said:
Sounds as if you need to include the Protocol ID field in your append query.
Not sure if the subform's code is generating a valid value for you to use,
as I am not understanding your form's setup.

However, assuming that you can get the necessary value for Protocol ID, your
append query should be changed to something like this (replace the string
"The Protocol ID Value" with a reference to a control that has this value,
or with the actual value as a constant, or something):

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number], [Protocol ID])" & _
"SELECT '" & pn & "', '" & mn & "', " & "The Protocol ID Value" & ";"

--

Ken Snell
<MS ACCESS MVP>

Ted said:

well ken, this is getting really interesting. we've removed the 424 error
only to have it replaced with

"3314 = The field 'Concomitant Medications.Protocol ID' cannot contain a
Null value because the Required property for the field isset to True.
Enter a
value in this field".

i don't know whether this helps, hopefully it does, but in the
subform-open
code i pasted in my earlier, there's some relevant looing code

Fill in Protocol ID value

Me.Protocol_ID.DefaultValue = DLookup("ProtocolID",
"tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If

Click to expand...

-ted

Ken Snell said:

Aha... my eyes are getting worse as I age....

Your code never sets ds variable to any object.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer
Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number], [Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

db.Execute sql, dbFailOnError

Me.Requery

Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" & mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

i will try and intersperse my responses where they would naturally flow
ken.

:

Let me back up.

I reviewed the thread and didn't find that you identified the exact
line
on
which the error 424 is occurring? Can you identify that step?

w/ my breakpoint on the 'pn' statement atop this, i 'F8' my way one
step @
a time and when the yellow highlight is on the " ds.Execute sql,
dbFailOnerror" line as i 'F8' it jumps to the MsgBox Err.Number & " =
" &
Err.description line.

What is the RecordSource query of the form in which this code is
running?

IT IS A TABLE AND ITS NAMED 'Concomitant Medications'.

If
it's a table, please indicate the fields that are in that table?

THERE ARE MANY MANY FIELDS IN THAT TABLE. "Patient Number" and "Med
Number"
are the two which make up the composite PK (in that same order). DO YOU
REQUIRE A LIST OF ALL THE FIELDS TO ANSWER YOUR QUESTION. I WILL
PROVIDE
IT
IF ABSOLUTELY NEEDED.

Is the form
filtered in any way when it's opened (in the DoCmd.OpenForm action)?

YES IT IS FILTERED. IT'S FILTERED VIA THE CODE BELOW

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

' Using "ApplyFilter" and "Ted" search string
' on MS discussion groups (3/16/2005 and 3/21/05)

If IsNull(Forms![Command and Control Center]!SelectPatient) _
Then
' No Patient ID entered on main form
MsgBox "Please select a Patient Number from " _
& "the list provided before continuing." _
, vbInformation, "Which Patient?"
' Stop the form from opening
Cancel = True
Else
' Check to see if Final Answer has been entered
If DLookup("FinalAnswer", "tblDefaults") = False Then
' Missing information
MsgBox "The correct Protocol ID and Title have not " _
& "been entered into the database." & vbNewLine _
& "Notify the Administrator. At this time you can " _
& "not enter data into the database.", 64 _
, "Warning -- Read Before Proceeding!"
' Stop the form from opening
Cancel = True
Else
' All clear, continue with form opening
' Patient ID was selected on main form

' Run Security Code
LAS_EnableSecurity Me

' Maximize the form
DoCmd.Maximize

' Apply a filter to match chosen Patient Number and set
DoCmd.ApplyFilter , "[Patient Number] = " & _
[Forms]![Command and Control Center]![SelectPatient]

' Fill in Protocol ID value
Me.Protocol_ID.DefaultValue = DLookup("ProtocolID",
"tblDefaults")

' Fill in Protocol Title
Me.Protocol_Title.DefaultValue = """" & _
DLookup("ProtocolTitle", "tblDefaults") & """"
End If
End If

ExitPoint:
Exit Sub

Error_Handler:
If Err.Number = 2467 Then
' Ignore
Else
' Unexpected Error
MsgBox "An unanticipated error has occurred." & _
"The error number is " & Err.Number & _
" and the description is '" & Err.description & "'" & _
" Please contact your System Administrator."
End If
Resume ExitPoint

End Sub

And let me confirm.. this error is occurring only when your form has
no
records in it?

THIS OCCURS WHETHER OR NOT THE FORM HAS RECORDS IN IT.

HTH

-ted

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using my best copy and paste strategies, the code i'm using (and
i'll
tell
you in one shake of a lamb's tail how it's failing) is as under
(with a
breakpoint on 'pn')

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]", "[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[MedNumber])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '" &
mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

so as i "F8" my way, i find the same "424 = Object required" error's
showing
itself :-(

-ted

:

You didn't change to Me.RecordsetClone in your code; you're still
using
Me.Recordset.Clone.

Also, your INSERT INTO string is going to fail when you try to
execute
it
because you're not delimiting the string values with ' characters
when
you
build the SQL string.

Similarly, the FindFirst step will fail without ' characters.

Try this modified code:
-----------------------

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]",
"[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[Med
Number])" & _
"SELECT '" & pn & "', '" & mn & "';"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Patient Number]='" & pn & "' and [Med Number] = '"
&
mn
&
"'"

If rs.NoMatch Then
MsgBox "something's wrong"

Else
Me.Bookmark = rs.Bookmark
End If

Exit_Add_Record_DblClick:
Set rs = Nothing
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

--

Ken Snell
<MS ACCESS MVP>

hi ken,

using the vba (below) it matters not whether i use one or the
other,
it's
now giving me a "424 = Object required" error message. i put a
breakpoint
on
the 'pn = ' statement and i "F8"ed my way down the code where i
see
it
jump
to the statement which displays the said 424 msg :-(

Private Sub Add_Record_DblClick(Cancel As Integer)
On Error GoTo Err_Add_Record_DblClick
Dim sql As String
Dim pn As Long
Dim mn As Integer

pn = [Forms]![Command and Control Center]![SelectPatient]
mn = Nz(DMax("[Med Number]", "[Concomitant Medications]",
"[Patient
Number] = " _
& [Forms]![Command and Control Center]![SelectPatient]), 0) + 1

sql = "INSERT INTO [Concomitant Medications] ([Patient Number],
[Med
Number])" & _
"SELECT " & CStr(pn) & " AS PN, " & CStr(mn) & " AS MN;"

ds.Execute sql, dbFailOnError

Dim db As Database
Set db = CurrentDb
db.Execute sql

Set db = Nothing

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Patient Number]=" & CStr(pn) & " and [Med Number]
=
" &
CStr(mn)

If rs.NoMatch Then
MsgBox "something's wrong"
End If

Me.Bookmark = rs.Bookmark

Exit_Add_Record_DblClick:
Exit Sub

Err_Add_Record_DblClick:
MsgBox Err.Number & " = " & Err.description
Resume Exit_Add_Record_DblClick

End Sub

-ted

:

Use Me.RecordsetClone recordset, not Me.Recordset.Clone
recordset.

--

Ken Snell
<MS ACCESS MVP>

so, ken....where's all this leave yours truly?

-ted

:

Hmmmm.... but the Help files say that the bookmarks are
compatible:

"Use the Clone method to create multiple, duplicate Recordset
objects.
Each
Recordset can have its own current record. Using Clone by
itself
doesn't
change the data in the objects or in their underlying
structures.
When
you
use the Clone method, you can share bookmarks between two or
more
Recordset
objects because their bookmarks are interchangeable."

I then tried the setup using two separate clone recordsets of
the
form's
recordset. When trying to set one of the clone's bookmark to
the
bookmark
of
the other clone, I still get the Type Mismatch error.

So the Help files appear to be incorrect for this use of
Clone
method
of
a
form's recordset.

--

Ken Snell
<MS ACCESS MVP>

message
In my test, I get a Type Mismatch error when trying to set
the
form's
Bookmark (Me.Bookmark) to the recordset's bookmark
(rst.Bookmark)
when
rst
is the Recordset.Clone.

Marsh correctly points out that you should be using
RecordsetClone,
not
Recordset.Clone, in your code.

Click to expand...

Click to expand...

besides error 3021 the vba isn't working	2	Apr 9, 2005
duplicating control and incrementing another control	25	Apr 1, 2005
renumbering sequence when record(s) removed	2	Apr 21, 2005
RecordSet (I guess) woes	5	Aug 31, 2005
#Name? error from Default Value expression	18	Apr 2, 2005
vba code to open filtered form -- Run-time error 2448	1	Mar 31, 2005
Run-time error '94' and No current record	1	Sep 17, 2009
3 small vba routines	6	Apr 1, 2005

Error 3021 No Current Record

Guest

Marshall Barton

Ken Snell [MVP]

Ken Snell [MVP]

Guest

Guest

Ken Snell [MVP]

Marshall Barton

Guest

Ken Snell [MVP]

Guest

Guest

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Guest

Ken Snell [MVP]

Marshall Barton

Guest

Guest

Ask a Question

Similar Threads