besides error 3021 the vba isn't working

[Guest]

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"). even when
there are other records for a given "Patient Number" in the underlying table,
this code doesn't seem to want to create a new one with the selected "Patient
Number" and the value of "Medication Number" = to 1 more than the previous in
the 1,2,3,4,.... series.


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.

 

[Guest]

Hi Ted -

When I'm having problems like this, I comment out the error handler ("on
error goto") so that I can see what line is giving me the problem.

If you do that, which line in your code is bombing? That might give a clue
of what's wrong.

Dave
ps - what's error 3021?

 

[Guest]

hi dave,

well, the plot seems to have gotten a little thicker :-(

i have a2k on my office desktop and a2003 on my home one where i'm writing
you from now.

i brought my mdb file to work w/ over the wknd and now when i open the vba
editor in line w/ your recommendations, i find the compiler is 'choking' when
it gets to the line w/ "rs.FindFirst" in it IRREGARDLESS of whether it occurs
in the errant vba code or the noble vba code which was doing a fine job i
contrasted with it???!!

where do i go from here, assuming this is going to get my attention over the
wknd?

-ted

Hi Ted -

When I'm having problems like this, I comment out the error handler ("on
error goto") so that I can see what line is giving me the problem.

If you do that, which line in your code is bombing? That might give a clue
of what's wrong.

Dave
ps - what's error 3021?

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"). even when
there are other records for a given "Patient Number" in the underlying table,
this code doesn't seem to want to create a new one with the selected "Patient
Number" and the value of "Medication Number" = to 1 more than the previous in
the 1,2,3,4,.... series.


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.