Microsoft Jet Database error/message

[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 with all of this?

-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"

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 with all of
this?

You don't say which line is giving the error, which might help, but it
sounds like there is no field in "DaysView" named "RecordNumber".

 

[Ted]

dirk,

thanks for picking this up.....

i 2ble-checked the 'DaysView' table which contains RecordNumber (the last
element of a complex PK comprised of Last Name, First Name, MI, MR_Number,
IRB Number, and RecordNumber).

i also checked the (datasheet viewable) subform which uses 'RecordNumber' as
the control source for the text control named 'Visit'.

then i put a breakpoint on fname = part of code and F8-ed my way through.
the "MsgBox Err.description" is delivering this message just after it
executes the line beginning with

rs.FindFirst"[Last Name]= .....And [RecordNumber] = "CStr(visnum) & ""

hth,

-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 with all of
this?

You don't say which line is giving the error, which might help, but it
sounds like there is no field in "DaysView" named "RecordNumber".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

dirk,

thanks for picking this up.....

i 2ble-checked the 'DaysView' table which contains RecordNumber (the
last element of a complex PK comprised of Last Name, First Name, MI,
MR_Number, IRB Number, and RecordNumber).

That sounds good.

i also checked the (datasheet viewable) subform which uses
'RecordNumber' as the control source for the text control named
'Visit'.

then i put a breakpoint on fname = part of code and F8-ed my way
through. the "MsgBox Err.description" is delivering this message just
after it executes the line beginning with

rs.FindFirst"[Last Name]= .....And [RecordNumber] = "CStr(visnum) & ""

Where is this code executing, on the main form or on the subform? If
it's on the main form, it's the main form's recordset you're searching,
and quite likely the main form's recordsource doesn't contain the field
[RecordNumber].

 

[Ted]

thanks, yes you're quite right, it's executed when a button on the mainform
("Screening Log (Edit Only)") is clicked. i guess i don'tunderstand all this
recordset clone business all that well just yet....i was under the apparently
mistaken assumption that i was adding the inforamtion indicated to fields on
the 'DaysView' subtable for display in the subform of the same name. your
response leaves me wonering how to do that.

with regards,

-ted

dirk,

thanks for picking this up.....

i 2ble-checked the 'DaysView' table which contains RecordNumber (the
last element of a complex PK comprised of Last Name, First Name, MI,
MR_Number, IRB Number, and RecordNumber).

That sounds good.

i also checked the (datasheet viewable) subform which uses
'RecordNumber' as the control source for the text control named
'Visit'.

then i put a breakpoint on fname = part of code and F8-ed my way
through. the "MsgBox Err.description" is delivering this message just
after it executes the line beginning with

rs.FindFirst"[Last Name]= .....And [RecordNumber] = "CStr(visnum) & ""

Where is this code executing, on the main form or on the subform? If
it's on the main form, it's the main form's recordset you're searching,
and quite likely the main form's recordsource doesn't contain the field
[RecordNumber].

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

thanks, yes you're quite right, it's executed when a button on the
mainform ("Screening Log (Edit Only)") is clicked. i guess i
don'tunderstand all this recordset clone business all that well just
yet....i was under the apparently mistaken assumption that i was
adding the inforamtion indicated to fields on the 'DaysView' subtable
for display in the subform of the same name. your response leaves me
wonering how to do that.

From the looks of it, your code is successfully adding a record to the
DaysView table, and then requerying the subform based on that table.
That part is probably correct. Then I *think* it is your intention to
make that newly added record become the current record on the subform.
However, your code ...

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

.... is searching the recordset of the main form ("Me"), not that of the
subform. If you revise it to refer to the Form object of the subform,
not to Me (the main form), it will probably work. Assuming that you are
using Access 2000 or later (and I think you said that) then code similar
to this should work

Me!sfDaysView.Form.Recordset.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)

You could use that without any lines like these:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Me.Bookmark = rs.Bookmark

Note, though, that the code I suggested assumes that the subform control
(on the main form) is named "sfDaysView". You'll have to check the name
of the subform control and change the code accordingly.

 

[Ted]

hi dirk, the latest version of the vba code is below and ran w/o any errors
or messages, however it failed to inject a new record into the underlying
table of the 'DaysView' subform.


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] = """ & 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

Me!DaysView.Form.Recordset.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)

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

thanks, yes you're quite right, it's executed when a button on the
mainform ("Screening Log (Edit Only)") is clicked. i guess i
don'tunderstand all this recordset clone business all that well just
yet....i was under the apparently mistaken assumption that i was
adding the inforamtion indicated to fields on the 'DaysView' subtable
for display in the subform of the same name. your response leaves me
wonering how to do that.

From the looks of it, your code is successfully adding a record to the
DaysView table, and then requerying the subform based on that table.
That part is probably correct. Then I *think* it is your intention to
make that newly added record become the current record on the subform.
However, your code ...

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

.... is searching the recordset of the main form ("Me"), not that of the
subform. If you revise it to refer to the Form object of the subform,
not to Me (the main form), it will probably work. Assuming that you are
using Access 2000 or later (and I think you said that) then code similar
to this should work

Me!sfDaysView.Form.Recordset.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)

You could use that without any lines like these:

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
Me.Bookmark = rs.Bookmark

Note, though, that the code I suggested assumes that the subform control
(on the main form) is named "sfDaysView". You'll have to check the name
of the subform control and change the code accordingly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

hi dirk, the latest version of the vba code is below and ran w/o any
errors or messages, however it failed to inject a new record into the
underlying table of the 'DaysView' subform.

Look in the DaysView table to verify that the records weren't actually
added. Check the subform's recordsource query and/or filter to see if
it has criteria that exclude the record you just added.

Change this line:

db.Execute sql

to this:

db.Execute sql, dbFailOnError

so that, if the append query has an error, you'll get an error message.

 

[Ted]

wish i were at my desk at the office to run it and get the feedback
(hopefully positive) to send you. as it is, it looks like it's going to have
to wait until tomorrow am -- the suspense's almost killin' me!

-ted

 

[Ted]

bingo, dirk! that worked, albeit w/ a minor hitch -- it doesn't seem to want
to stay on the subform when the new record is added. is there some way to
tell it that you want it to move to a certain control on the latest/added
record on the subform, e.g. the 'VisitType' control?

all the best,

-ted

 

[Dirk Goldgar]

bingo, dirk! that worked, albeit w/ a minor hitch -- it doesn't seem
to want to stay on the subform when the new record is added. is there
some way to tell it that you want it to move to a certain control on
the latest/added record on the subform, e.g. the 'VisitType' control?

Oh, I see. Once you've clicked the button on the main form, the focus
isn't on the subform any more. You can add code to send the focus back
there, after you've requeried and repositioned the subform. You need to
set the focus first to the subform, and then to the specific control on
the subform; like this:

Me!DaysView.SetFocus
Me!DaysView!VisitType.SetFocus

 

[Ted]

hi dirk, perhaps this is going a little outside the original intent of my
posting and belongs in a new one.

in my DaysView form i have this bit of VBA to handle automatic generation of
two values, the author of the updated value and the datetime of the same:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Updated_by = LAS_GetUserName()
Me.Last_edited = Now()
End If
End Sub

with this new code, i noticed after i'd written that it popped a zero ('0')
in each of the two fields and while waiting to hear about the focus issue
have been trying to get around it.

my code as it stands, w/o the addition of your setfocus code reads thus:

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
Dim updt As String <---- NEW

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]
updt = LAS_GetUserName() <---- New

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 & """ & updt & """ As UPD;" <---
New

' MsgBox sql

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

Set db = Nothing

Me.Requery

Me!DaysView.Form.Recordset.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) & " And [UpdatedBy] = """ & updt &
"""" <-- New

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

leaving aside the question i was going to ask of you wrt where to place the
focus code, you see the "<-- New" above indicating where the new code to
handle (or try to handle) the generation of the UpdatedBy value, which when
the button's clicked results in this message:

Query input must contain at least one table or query.

i suppose i could simplify the DaysView table/form by deleting the UpdatedBy
and time fields and the problem would just simplify to identifying where to
place the focus commands, but this'd be kind of neat if it were do-able.

all the best,

-ted

 

[Dirk Goldgar]

hi dirk, perhaps this is going a little outside the original intent
of my posting and belongs in a new one.

in my DaysView form i have this bit of VBA to handle automatic
generation of two values, the author of the updated value and the
datetime of the same:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Updated_by = LAS_GetUserName()
Me.Last_edited = Now()
End If
End Sub

with this new code, i noticed after i'd written that it popped a zero
('0') in each of the two fields and while waiting to hear about the
focus issue have been trying to get around it.

my code as it stands, w/o the addition of your setfocus code reads
thus:

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
Dim updt As String <---- NEW

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]
updt = LAS_GetUserName() <---- New

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 & """ & updt & """ As UPD;" <--- New

' MsgBox sql

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

Set db = Nothing

Me.Requery

Me!DaysView.Form.Recordset.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) & " And [UpdatedBy] = """ &
updt & """" <-- New

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

leaving aside the question i was going to ask of you wrt where to
place the focus code, you see the "<-- New" above indicating where
the new code to handle (or try to handle) the generation of the
UpdatedBy value, which when the button's clicked results in this
message:

Query input must contain at least one table or query.

i suppose i could simplify the DaysView table/form by deleting the
UpdatedBy and time fields and the problem would just simplify to
identifying where to place the focus commands, but this'd be kind of
neat if it were do-able.

It looks like there are couple of small errors to fix. Change these
lines ...

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 & """ & updt & """ As UPD;" <--- New

.... to these:

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

Later, when you are searching the requeried subform for the record you
just added, I don't think there's any need to include the [Updated_by]
field in the search, so I'd change these lines ...

Me!DaysView.Form.Recordset.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) & " And [UpdatedBy] = """ &
updt & """" <-- New

.... back to the way they were before.

Note: I'm not entirely sure whether your field name is "UpdatedBy" or
"Updated_by". You may need to adjust the code accordingly.

 

[Ted]

i am pasting the (yet) latest add record button's code below inasmuch as the
message that's now emerging says:

Data type mismatch in criteria expression

whatever that means!

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
Dim updt As String
' Dim whn As Date

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]
updt = LAS_GetUserName()
' whn = Now()

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], " & _
"[Updated_By]) " & "SELECT """ & lname & """ AS LN, """ & fname
& _
""" AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) & _
" AS MR, """ & irbnum & """ AS IRB, " & visnum & _
" AS VIS, """ & updt & """ As UPD;"


MsgBox sql

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

Set db = Nothing

Me.Requery

' message from group -- maybe this pair of focus lines goes here

Me!DaysView.SetFocus
Me!DaysView!VisitType.SetFocus


Me!DaysView.Form.Recordset.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)

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub


i believe the []-ed fields in the SQL expression should be the control
sources in the DaysView form and not the names of the controls themselves,so
i have decided to use [Updated_By] as it's the name in the DaysView table for
the control named "UpdatedBy".

best,

-ted

ps: in addition to the Updated_By field there is another one named in the
DaysView table "Last_Edited" which is a date field and which i would want the
code to automatically generate on the "DaysView" datasheet, but one thing at
a time, i suppose?

hi dirk, perhaps this is going a little outside the original intent
of my posting and belongs in a new one.

in my DaysView form i have this bit of VBA to handle automatic
generation of two values, the author of the updated value and the
datetime of the same:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Dirty Then
Me.Updated_by = LAS_GetUserName()
Me.Last_edited = Now()
End If
End Sub

with this new code, i noticed after i'd written that it popped a zero
('0') in each of the two fields and while waiting to hear about the
focus issue have been trying to get around it.

my code as it stands, w/o the addition of your setfocus code reads
thus:

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
Dim updt As String <---- NEW

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]
updt = LAS_GetUserName() <---- New

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 & """ & updt & """ As UPD;" <--- New

' MsgBox sql

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

Set db = Nothing

Me.Requery

Me!DaysView.Form.Recordset.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) & " And [UpdatedBy] = """ &
updt & """" <-- New

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

leaving aside the question i was going to ask of you wrt where to
place the focus code, you see the "<-- New" above indicating where
the new code to handle (or try to handle) the generation of the
UpdatedBy value, which when the button's clicked results in this
message:

Query input must contain at least one table or query.

i suppose i could simplify the DaysView table/form by deleting the
UpdatedBy and time fields and the problem would just simplify to
identifying where to place the focus commands, but this'd be kind of
neat if it were do-able.

It looks like there are couple of small errors to fix. Change these
lines ...

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 & """ & updt & """ As UPD;" <--- New

.... to these:

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

Later, when you are searching the requeried subform for the record you
just added, I don't think there's any need to include the [Updated_by]
field in the search, so I'd change these lines ...

Me!DaysView.Form.Recordset.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) & " And [UpdatedBy] = """ &
updt & """" <-- New

.... back to the way they were before.

Note: I'm not entirely sure whether your field name is "UpdatedBy" or
"Updated_by". You may need to adjust the code accordingly.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

i am pasting the (yet) latest add record button's code below inasmuch
as the message that's now emerging says:

Data type mismatch in criteria expression

whatever that means!

What line raises the error?

' message from group -- maybe this pair of focus lines goes here

Me!DaysView.SetFocus
Me!DaysView!VisitType.SetFocus


Me!DaysView.Form.Recordset.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)

Actually, those two lines would go after the
"Me!DaysView.Form.Recordset.FindFirst ..." statement.

i believe the []-ed fields in the SQL expression should be the control
sources in the DaysView form and not the names of the controls
themselves,so i have decided to use [Updated_By] as it's the name in
the DaysView table for the control named "UpdatedBy".

That sounds correct to me.

ps: in addition to the Updated_By field there is another one named in
the DaysView table "Last_Edited" which is a date field and which i
would want the code to automatically generate on the "DaysView"
datasheet, but one thing at a time, i suppose?

Good plan.

 

[Ted]

devavualloveragain....here's the code i just ran, i commented the line that
seems to spark the message 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
Dim updt As String
' Dim whn As Date

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]
updt = LAS_GetUserName()
' whn = Now()

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], " & _
"[Updated_By]) " & " SELECT """ & lname & """ AS LN, """ & fname
& _
""" AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) & _
" AS MR, """ & irbnum & """ AS IRB, " & visnum & _
" AS VIS, """ & updt & """ As UPD;"


MsgBox sql

Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError <---- when i F8 thru the code, right after
hitting this line, i get the Data type mismatch message

Set db = Nothing

Me.Requery


Me!DaysView.Form.Recordset.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)

' message from group -- maybe this pair of focus lines goes here

Me!DaysView.SetFocus
Me!DaysView!VisitType.SetFocus

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.description
Resume Exit_Add_Record_Click

End Sub

i am pasting the (yet) latest add record button's code below inasmuch
as the message that's now emerging says:

Data type mismatch in criteria expression

whatever that means!

What line raises the error?

' message from group -- maybe this pair of focus lines goes here

Me!DaysView.SetFocus
Me!DaysView!VisitType.SetFocus


Me!DaysView.Form.Recordset.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)

Actually, those two lines would go after the
"Me!DaysView.Form.Recordset.FindFirst ..." statement.

i believe the []-ed fields in the SQL expression should be the control
sources in the DaysView form and not the names of the controls
themselves,so i have decided to use [Updated_By] as it's the name in
the DaysView table for the control named "UpdatedBy".

That sounds correct to me.

ps: in addition to the Updated_By field there is another one named in
the DaysView table "Last_Edited" which is a date field and which i
would want the code to automatically generate on the "DaysView"
datasheet, but one thing at a time, i suppose?

Good plan.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

devavualloveragain....here's the code i just ran, i commented the
line that seems to spark the message below: [...]
sql = "INSERT INTO [DaysView] ([Last Name], [First Name], " & _
"[MI],[MR_Number], [IRB Number], [RecordNumber], " & _
"[Updated_By]) " & " SELECT """ & lname & """ AS LN, """
& fname & _
""" AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) & _
" AS MR, """ & irbnum & """ AS IRB, " & visnum & _
" AS VIS, """ & updt & """ As UPD;"


MsgBox sql

Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError <---- when i F8 thru the code, right
after hitting this line, i get the Data type mismatch message

Okay, so there's something wrong with the SQL string, but I can't see
what. I trust that the [Updated_By] field is actually defined in the
table as a text field, and that IRB_Number is also a text field.

I see that you're displaying the SQL statement for yourself in a message
box. If you can't figure what's wrong from looking at it, change the
line

MsgBox sql

to

Debug.Print sql

and then when you run the code, copy the SQL statement from where it was
printed in the Immediate Window and paste it into a reply to this
message so I can have a look at it.

 

[Ted]

here's the contents of the immediate window's:

INSERT INTO [DaysView] ([Last Name], [First Name], [MI],[MR_Number], [IRB
Number], [RecordNumber], [Updated_By]) SELECT "Adams" AS LN, "Mary" AS FN,
"--" AS MI, 1234567 AS MR, "04-04-083" AS IRB, 10 AS VIS, "albertn" As UPD;

because of the sensitive personal nature of the information that would be
given here, i have had to alter the identifying information (after carefully
checking the material actually given in it for accuracy); suffice it to say,
the values for last/first/mi/mr which are shown comport with their original's
in appearance)

lastly, you are right, both those fields you cited should be defined as text
fields in the underlying DaysView table, HOWEVER i found that the Updated_By
field had been changed inadvertantly to number field --- which i corrected to
be Text.

the code ran w/o the data mismatch error! BUT following the focus lines
another message emerge ....

Object doesn't support property or method

following this line in the code below:

Me!DaysView!VisitType.SetFocus

-ted

devavualloveragain....here's the code i just ran, i commented the
line that seems to spark the message below: [...]
sql = "INSERT INTO [DaysView] ([Last Name], [First Name], " & _
"[MI],[MR_Number], [IRB Number], [RecordNumber], " & _
"[Updated_By]) " & " SELECT """ & lname & """ AS LN, """
& fname & _
""" AS FN, """ & m__i & """ AS MI, " & CStr(mrnum) & _
" AS MR, """ & irbnum & """ AS IRB, " & visnum & _
" AS VIS, """ & updt & """ As UPD;"


MsgBox sql

Dim db As Database
Set db = CurrentDb
db.Execute sql, dbFailOnError <---- when i F8 thru the code, right
after hitting this line, i get the Data type mismatch message

Okay, so there's something wrong with the SQL string, but I can't see
what. I trust that the [Updated_By] field is actually defined in the
table as a text field, and that IRB_Number is also a text field.

I see that you're displaying the SQL statement for yourself in a message
box. If you can't figure what's wrong from looking at it, change the
line

MsgBox sql

to

Debug.Print sql

and then when you run the code, copy the SQL statement from where it was
printed in the Immediate Window and paste it into a reply to this
message so I can have a look at it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

here's the contents of the immediate window's:

INSERT INTO [DaysView] ([Last Name], [First Name], [MI],[MR_Number],
[IRB Number], [RecordNumber], [Updated_By]) SELECT "Adams" AS LN,
"Mary" AS FN, "--" AS MI, 1234567 AS MR, "04-04-083" AS IRB, 10 AS
VIS, "albertn" As UPD;

because of the sensitive personal nature of the information that
would be given here, i have had to alter the identifying information
(after carefully checking the material actually given in it for
accuracy); suffice it to say, the values for last/first/mi/mr which
are shown comport with their original's in appearance)

The statement looks fine, but I see from your next sentence why it was
failing.

lastly, you are right, both those fields you cited should be defined
as text fields in the underlying DaysView table, HOWEVER i found that
the Updated_By field had been changed inadvertantly to number field
Oops.

--- which i corrected to be Text.

the code ran w/o the data mismatch error!

Hurrah! It's fixed!

BUT following the focus
lines another message emerge ....

Object doesn't support property or method

following this line in the code below:

Me!DaysView!VisitType.SetFocus

I would guess that there is no control on the DaysView subform that is
actually named "VisitType". Check the name. "VisitType" may be the
name of the field the control is bound to, but not the name of the
control.

 

[Ted]

tomorrow i go for my eye test, i promise!

i corrected the VisitType field's name and now this runs w/o that error, YET
when it does add a record to a patient's DaysView datasheet, the screen
scoots up to the mainform of the very first patient in the database and props
the cursor in the VisitType control.

-ted

here's the contents of the immediate window's:

INSERT INTO [DaysView] ([Last Name], [First Name], [MI],[MR_Number],
[IRB Number], [RecordNumber], [Updated_By]) SELECT "Adams" AS LN,
"Mary" AS FN, "--" AS MI, 1234567 AS MR, "04-04-083" AS IRB, 10 AS
VIS, "albertn" As UPD;

because of the sensitive personal nature of the information that
would be given here, i have had to alter the identifying information
(after carefully checking the material actually given in it for
accuracy); suffice it to say, the values for last/first/mi/mr which
are shown comport with their original's in appearance)

The statement looks fine, but I see from your next sentence why it was
failing.

lastly, you are right, both those fields you cited should be defined
as text fields in the underlying DaysView table, HOWEVER i found that
the Updated_By field had been changed inadvertantly to number field
Oops.

--- which i corrected to be Text.

the code ran w/o the data mismatch error!

Hurrah! It's fixed!

BUT following the focus
lines another message emerge ....

Object doesn't support property or method

following this line in the code below:

Me!DaysView!VisitType.SetFocus

I would guess that there is no control on the DaysView subform that is
actually named "VisitType". Check the name. "VisitType" may be the
name of the field the control is bound to, but not the name of the
control.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)