RecordSet (I guess) woes

[Ted]

I am interested in incrementing a control on my subform (called ‘DaysView’)
every time a user clicks a button on the main form (called ‘Screening Log
(Edit Only)’. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name, middle
initial, medical record number, and irb number (which represents which
experiment the said patient’s been screened for). I would really like to get
more VBA training and the discussion group has been a good stand-in so I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn’t work as yet and is
based upon the need to increment a field in a similar way in the child form
of another parent form. When I click the button to add a record (on the
mainform) the following message shows up:

Oncology Screening Log can’t find the field ‘|’ referred to in your expression

That code did work and is pasted below the string of “=â€

It would be great if this could work. I’m sure if I could understand the
principle(s) involved which made it increment Visit by 1 each time, that that
would move me a notch up the ‘learning curve’. Any help’d be appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And [MI] = """
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & _
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname & """
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) & "
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " & CStr(mrnum) &
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " & CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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

 

[Roger Carlson]

I don't know if this addresses everything here, but the issue of FindFirst
working in one A2K database and not another is a matter of References. In
the one in which FindFirst does not work, add a reference to Microsoft DAO
3.6 Object Library. If you are not using ADO at all, remove the reference
to Microsoft ActiveX Data Objects Library.

(The one where it works was probably upgraded from an Access 97 database.
The conversion process automatically sets these references.)

For more info, see here: http://www.rogersaccesslibrary.com/References.htm

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

I am interested in incrementing a control on my subform (called 'DaysView ')
every time a user clicks a button on the main form (called 'Screening Log
(Edit Only)'. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name, middle
initial, medical record number, and irb number (which represents which
experiment the said patient's been screened for). I would really like to get
more VBA training and the discussion group has been a good stand-in so I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn't work as yet and is
based upon the need to increment a field in a similar way in the child form
of another parent form. When I click the button to add a record (on the
mainform) the following message shows up:

Oncology Screening Log can't find the field '|' referred to in your expression

That code did work and is pasted below the string of "="

It would be great if this could work. I'm sure if I could understand the
principle(s) involved which made it increment Visit by 1 each time, that that
would move me a notch up the 'learning curve'. Any help'd be appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And [MI] = """
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ & _
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname & """
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) & "
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " & CStr(mrnum) &
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " & CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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

 

[Ken Snell [MVP]]

The problem you're having may be because you didn't set a reference to the
DAO library in the 2000 version database that doesn't have "FindFirst"
option. 2000 and 2002 versions are set to default ADO library for
recordsets. Open Visual Basic Editor and use Tools | References to select
the DAO library.

Then, disambiguate the declaration of the recordset in your code:
Dim rs As DAO.Recordset

A DAO recordset has the FindFirst method. An ADO recordset has the Find
method.

Then, change this line of code
Set rs = Me.Recordset.Clone
to this:
Set rs = Me.RecordsetClone


--

Ken Snell
<MS ACCESS MVP>

I am interested in incrementing a control on my subform (called 'DaysView')
every time a user clicks a button on the main form (called 'Screening Log
(Edit Only)'. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name, middle
initial, medical record number, and irb number (which represents which
experiment the said patient's been screened for). I would really like to
get
more VBA training and the discussion group has been a good stand-in so I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn't work as yet and
is
based upon the need to increment a field in a similar way in the child
form
of another parent form. When I click the button to add a record (on the
mainform) the following message shows up:

Oncology Screening Log can't find the field '|' referred to in your
expression

That code did work and is pasted below the string of "="

It would be great if this could work. I'm sure if I could understand the
principle(s) involved which made it increment Visit by 1 each time, that
that
would move me a notch up the 'learning curve'. Any help'd be appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And [MI] =
"""
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
_
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname &
"""
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) &
"
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " & CStr(mrnum)
&
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " &
CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient
Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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

 

[Ted]

thanks ken and roger. i managed to disambiguate the situation and
successfully compile the code which has taken me to the lurch i mentioned in
my posting (i.e. the message about the '|' ). there must be something else
that's giving it hearburn.

-ted

The problem you're having may be because you didn't set a reference to the
DAO library in the 2000 version database that doesn't have "FindFirst"
option. 2000 and 2002 versions are set to default ADO library for
recordsets. Open Visual Basic Editor and use Tools | References to select
the DAO library.

Then, disambiguate the declaration of the recordset in your code:
Dim rs As DAO.Recordset

A DAO recordset has the FindFirst method. An ADO recordset has the Find
method.

Then, change this line of code
Set rs = Me.Recordset.Clone
to this:
Set rs = Me.RecordsetClone


--

Ken Snell
<MS ACCESS MVP>

I am interested in incrementing a control on my subform (called 'DaysView')
every time a user clicks a button on the main form (called 'Screening Log
(Edit Only)'. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name, middle
initial, medical record number, and irb number (which represents which
experiment the said patient's been screened for). I would really like to
get
more VBA training and the discussion group has been a good stand-in so I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn't work as yet and
is
based upon the need to increment a field in a similar way in the child
form
of another parent form. When I click the button to add a record (on the
mainform) the following message shows up:

Oncology Screening Log can't find the field '|' referred to in your
expression

That code did work and is pasted below the string of "="

It would be great if this could work. I'm sure if I could understand the
principle(s) involved which made it increment Visit by 1 each time, that
that
would move me a notch up the 'learning curve'. Any help'd be appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And [MI] =
"""
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """ &
_
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname &
"""
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) &
"
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " & CStr(mrnum)
&
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " &
CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient
Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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

 

[Ken Snell [MVP]]

Not knowing what "Oncology Screening Log" is (your main form's name?), I'm
not sure what the error may be specifically. But I'm guessing that the error
is occurring in the DMax function, or in the FindFirst action. Probably
something related to " characters -- any chance that any of your variable /
field values contain a " character in them?

I suggest that you put breakpoints on your code and see what the different
strings and the variables and the fields actually contain ... or remove
parts of the code until you find the one that keeps it from working.

--

Ken Snell
<MS ACCESS MVP>

thanks ken and roger. i managed to disambiguate the situation and
successfully compile the code which has taken me to the lurch i mentioned
in
my posting (i.e. the message about the '|' ). there must be something
else
that's giving it hearburn.

-ted

The problem you're having may be because you didn't set a reference to
the
DAO library in the 2000 version database that doesn't have "FindFirst"
option. 2000 and 2002 versions are set to default ADO library for
recordsets. Open Visual Basic Editor and use Tools | References to select
the DAO library.

Then, disambiguate the declaration of the recordset in your code:
Dim rs As DAO.Recordset

A DAO recordset has the FindFirst method. An ADO recordset has the Find
method.

Then, change this line of code
Set rs = Me.Recordset.Clone
to this:
Set rs = Me.RecordsetClone


--

Ken Snell
<MS ACCESS MVP>

I am interested in incrementing a control on my subform (called
'DaysView')
every time a user clicks a button on the main form (called 'Screening
Log
(Edit Only)'. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name, middle
initial, medical record number, and irb number (which represents which
experiment the said patient's been screened for). I would really like
to
get
more VBA training and the discussion group has been a good stand-in so
I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn't work as yet
and
is
based upon the need to increment a field in a similar way in the child
form
of another parent form. When I click the button to add a record (on the
mainform) the following message shows up:

Oncology Screening Log can't find the field '|' referred to in your
expression

That code did work and is pasted below the string of "="

It would be great if this could work. I'm sure if I could understand
the
principle(s) involved which made it increment Visit by 1 each time,
that
that
would move me a notch up the 'learning curve'. Any help'd be
appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And [MI] =
"""
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """
&
_
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname
&
"""
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " & CStr(mrnum)
&
"
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists
but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " &
CStr(mrnum)
&
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " &
CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient
Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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

 

[Ted]

i'm not sure i know what that refers to exactly just yet myself, ken. my main
form's name is "Screening Log (Edit Only)" so i'm kind of scratching my
noggin as to how it derived this name (although the general idea ofl this
database is to serve as a screening log for the medical oncology department),
but anyway, holding that aside fttb, VBA 'expert' that i am, before you
replied, i remembered the part about breakpoints and snooped around a
bit.....i found that when it came to 'visnum' it jumped to the error message
that's giving us trouble. visnum for whatever reason is being resolved to be
zero ('0')?

-ted

Not knowing what "Oncology Screening Log" is (your main form's name?), I'm
not sure what the error may be specifically. But I'm guessing that the error
is occurring in the DMax function, or in the FindFirst action. Probably
something related to " characters -- any chance that any of your variable /
field values contain a " character in them?

I suggest that you put breakpoints on your code and see what the different
strings and the variables and the fields actually contain ... or remove
parts of the code until you find the one that keeps it from working.

--

Ken Snell
<MS ACCESS MVP>

thanks ken and roger. i managed to disambiguate the situation and
successfully compile the code which has taken me to the lurch i mentioned
in
my posting (i.e. the message about the '|' ). there must be something
else
that's giving it hearburn.

-ted

The problem you're having may be because you didn't set a reference to
the
DAO library in the 2000 version database that doesn't have "FindFirst"
option. 2000 and 2002 versions are set to default ADO library for
recordsets. Open Visual Basic Editor and use Tools | References to select
the DAO library.

Then, disambiguate the declaration of the recordset in your code:
Dim rs As DAO.Recordset

A DAO recordset has the FindFirst method. An ADO recordset has the Find
method.

Then, change this line of code
Set rs = Me.Recordset.Clone
to this:
Set rs = Me.RecordsetClone


--

Ken Snell
<MS ACCESS MVP>



I am interested in incrementing a control on my subform (called
'DaysView')
every time a user clicks a button on the main form (called 'Screening
Log
(Edit Only)'. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name, middle
initial, medical record number, and irb number (which represents which
experiment the said patient's been screened for). I would really like
to
get
more VBA training and the discussion group has been a good stand-in so
I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn't work as yet
and
is
based upon the need to increment a field in a similar way in the child
form
of another parent form. When I click the button to add a record (on the
mainform) the following message shows up:

Oncology Screening Log can't find the field '|' referred to in your
expression

That code did work and is pasted below the string of "="

It would be great if this could work. I'm sure if I could understand
the
principle(s) involved which made it increment Visit by 1 each time,
that
that
would move me a notch up the 'learning curve'. Any help'd be
appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And [MI] =
"""
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] = """
&
_
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname
&
"""
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " & CStr(mrnum)
&
"
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists
but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " &
CStr(mrnum)
&
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " &
CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient
Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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

 

[Ken Snell [MVP]]

Is [MR_Number] a text field or a numeric field? If it's a text field, you're
missing the delimiting " characters around the value that you're
concatenating into the "where" clause for the DMax domain function. If that
function errors (which it would if [MR_Number] is a text field), then the Nz
function won't convert a Null value to zero because of the error.

visnum gets a default value of 0 when you Dim it as an integer.
--

Ken Snell
<MS ACCESS MVP>

i'm not sure i know what that refers to exactly just yet myself, ken. my
main
form's name is "Screening Log (Edit Only)" so i'm kind of scratching my
noggin as to how it derived this name (although the general idea ofl this
database is to serve as a screening log for the medical oncology
department),
but anyway, holding that aside fttb, VBA 'expert' that i am, before you
replied, i remembered the part about breakpoints and snooped around a
bit.....i found that when it came to 'visnum' it jumped to the error
message
that's giving us trouble. visnum for whatever reason is being resolved to
be
zero ('0')?

-ted

Not knowing what "Oncology Screening Log" is (your main form's name?),
I'm
not sure what the error may be specifically. But I'm guessing that the
error
is occurring in the DMax function, or in the FindFirst action. Probably
something related to " characters -- any chance that any of your variable
/
field values contain a " character in them?

I suggest that you put breakpoints on your code and see what the
different
strings and the variables and the fields actually contain ... or remove
parts of the code until you find the one that keeps it from working.

--

Ken Snell
<MS ACCESS MVP>

thanks ken and roger. i managed to disambiguate the situation and
successfully compile the code which has taken me to the lurch i
mentioned
in
my posting (i.e. the message about the '|' ). there must be something
else
that's giving it hearburn.

-ted


:

The problem you're having may be because you didn't set a reference to
the
DAO library in the 2000 version database that doesn't have "FindFirst"
option. 2000 and 2002 versions are set to default ADO library for
recordsets. Open Visual Basic Editor and use Tools | References to
select
the DAO library.

Then, disambiguate the declaration of the recordset in your code:
Dim rs As DAO.Recordset

A DAO recordset has the FindFirst method. An ADO recordset has the
Find
method.

Then, change this line of code
Set rs = Me.Recordset.Clone
to this:
Set rs = Me.RecordsetClone


--

Ken Snell
<MS ACCESS MVP>



I am interested in incrementing a control on my subform (called
'DaysView')
every time a user clicks a button on the main form (called
'Screening
Log
(Edit Only)'. The two forms are linked in a child (DaysView) /parent
(Screening Only (Edit Only) style using last name, first name,
middle
initial, medical record number, and irb number (which represents
which
experiment the said patient's been screened for). I would really
like
to
get
more VBA training and the discussion group has been a good stand-in
so
I
count myself as a non-expert VBA-er: far from it.

The code I wrote to perform the above (see below) doesn't work as
yet
and
is
based upon the need to increment a field in a similar way in the
child
form
of another parent form. When I click the button to add a record (on
the
mainform) the following message shows up:

Oncology Screening Log can't find the field '|' referred to in your
expression

That code did work and is pasted below the string of "="

It would be great if this could work. I'm sure if I could understand
the
principle(s) involved which made it increment Visit by 1 each time,
that
that
would move me a notch up the 'learning curve'. Any help'd be
appreciated.

-ted


Private Sub Add_Record_Click()
On Error GoTo Err_Add_Record_Click

Dim sql As String
Dim lname As String
Dim fname As String
Dim MI As String
Dim mrnum As Long
Dim irbnum As String
Dim visnum As Integer

lname = [Forms]![Screening Log (Edit Only)]![Last Name]
fname = [Forms]![Screening Log (Edit Only)]![First Name]
m__i = [Forms]![Screening Log (Edit Only)]![MI]
mrnum = [Forms]![Screening Log (Edit Only)]![MR Number]
irbnum = [Forms]![Screening Log (Edit Only)]![IRB Number]

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """
& _
[LastName] & """ And [First Name] = """ & [First_Name] & """ And
[MI] =
"""
& _
[M_I] & """ And [MR_Number] = " & [MRNumber] & " And [IRB Number] =
"""
&
_
[IRBNumber] & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ &
lname
&
"""
AS FN, """ & fname & """ AS FN, """ & m__i & """ AS MI, " &
CStr(mrnum)
&
"
AS MR, """ & irbnum & """ AS IRB, " & visnum & " AS VIS;"

MsgBox sql

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

Me.Requery

Dim rs As Recordset
Set rs = Me.Recordset.Clone
'For some reason, in my current A2K mdb database Find (below) exists
but
FindFirst does not!
rs.Find "[Last Name] = """ & lname & """ And [First Name] = """ & _
fname & """ And [MI] = """ & m__i & """ And [MR_Number] = " &
CStr(mrnum)
&
"" _
And [IRB Number] = """ & irbnum & """ And [RecordNumber] = " &
CStr(visnum)
& "
Me.Bookmark = rs.Bookmark

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


(This is the code I wrote in another A2K mdb database which does
work)

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


pn = [Forms]![RECIST Disease Evaluation: Target Lesions]![Patient
Number]

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

upd = "New record added on " & Date & " at " & Time & " by " &
LAS_GetUserName() & ";"

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

' MsgBox sql

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