besides error 3021 the vba isn't working

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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?
 
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



pcc DaveF said:
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?

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"). 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.
Click to expand...
Click to expand...
 
Back
Top