ms a2k jet database engine won't recognize field as valid

[Ted]

when i click a button on my mainform ("Screening Log (Edit Only)") whose
purpose is to add a new record on a linked/child subform ("DaysView) which
increments the value of a control by 1 (to chronicle the Visits being entered
by users), it results in the following message:

"The Microsoft Jet database does not recognize 'RecordNumber' as a valid
field name or expression"

i paste the VBA code attached to the button's OnClick event below:

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

visnum = Nz(DMax("[RecordNumber]", "[DaysView]", "[Last Name] = """ & lname
& """ And [First Name] = """ & fname & """ And [MI] = """ & m__i & """ And
[MR_Number] = " _
& CStr(mrnum) & " And [IRB Number] = """ & irbnum & """"), 0) + 1

sql = "INSERT INTO [DaysView] ([Last Name], [First Name],
[MI],[MR_Number],[IRB Number],[RecordNumber])" & "SELECT """ & lname & """
AS LN, """ & _
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 DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[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


can anyone find the syntax that's giving a2k heartburn?
-ted

 

[Dirk Goldgar]

when i click a button on my mainform ("Screening Log (Edit Only)")
whose purpose is to add a new record on a linked/child subform
("DaysView) which increments the value of a control by 1 (to
chronicle the Visits being entered by users), it results in the
following message:

"The Microsoft Jet database does not recognize 'RecordNumber' as a
valid field name or expression"

Answered in the modulesdaovba newsgroup, to which you also posted this
question independently. That's called "multiposting", and it's
generally frowned on because others don't know what answers have already
been given, and so they duplicate the effort. Also it's harder for you
to keep track of the various replies, and it's harder for later readers
of the question, who may be looking for the same answer, to learn what
they need.

In most cases a single, well-chosen newsgroup will do. If your question
really is relevant to more than one newsgroup, the approved technique is
to "crosspost" it instead, by listing multiple newsgroups in the To: or
Newsgroups: line of a single message. If you do that, the message and
any replies will appear in all the listed newsgroups automatically,
which is beneficial to all concerned.

I understand that the web newsreader interface doesn't make it obvious
how to do this. IIRC, you have to click on the "Advanced Options" link,
or something like that.

 

[Ted]

would you mind telling me what IIRC stands for. i have never seen an
'Advanced' options available when i log into these.

 

[Dirk Goldgar]

would you mind telling me what IIRC stands for. i have never seen an
'Advanced' options available when i log into these.

IIRC = "If I Recall (or Remember) Correctly"

At the bottom of the "New Question" window, there's a little link for
"Advanced Options".