Run-time error '3265'

T

Ted

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("Append To Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("Update Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("Update Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("Update Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub
 
B

Barry Gilbert

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry
 
T

Ted

It returned a run time error 91?

Barry Gilbert said:
Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry
Click to expand...
 
B

Barry Gilbert

If you have an On Error statement at the beginning of the procedure, comment
it out.

Barry
 
T

Ted

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted
 
B

Barry Gilbert

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry
 
T

Ted

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub
 
B

Barry Gilbert

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry
 
T

Ted

i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.
 
B

Barry Gilbert

Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

Ted said:
i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


Barry Gilbert said:
Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry
Click to expand...
Click to expand...
 
T

Ted

let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

Barry Gilbert said:
Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

Ted said:
i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


Barry Gilbert said:
Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Click to expand...
Click to expand...
Click to expand...
 
B

Barry Gilbert

Yes and no. The SetWarnings commands will affect warnings like "You about to
delete a record..." or "Your baby is about to be throw out with the
bathwater.". It also will prevent the user from seeing errors from things
like Referential Integrity. The dbFailOnError option is more robust and will
allow you to trap those errors, but the code required for using a querydef is
a little more complex. The choice of your approach depends on the nature of
your app and this function.

If you choose to pursue the querydef parameters method, there are plenty of
examples on the web in in the Access help file on how to do it.

Barry

Ted said:
let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

Barry Gilbert said:
Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

Ted said:
i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


:

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

well, i guess it's the old devil or the deep blue sea again isn't it. i'm
like really glad i didn't not make a backup copy of my first version of the
file, the one which uses the docmd.macro with the setwarnings but having come
this far, what's the point of using this latest 'approach' if i can't get the
robust errors then all this will've been for nada/zilch/jack/squat/diddly.
question now is where do all these helpful files you thought exist lurk and
how do i hunt them down. keywords to use?

Barry Gilbert said:
Yes and no. The SetWarnings commands will affect warnings like "You about to
delete a record..." or "Your baby is about to be throw out with the
bathwater.". It also will prevent the user from seeing errors from things
like Referential Integrity. The dbFailOnError option is more robust and will
allow you to trap those errors, but the code required for using a querydef is
a little more complex. The choice of your approach depends on the nature of
your app and this function.

If you choose to pursue the querydef parameters method, there are plenty of
examples on the web in in the Access help file on how to do it.

Barry

Ted said:
let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

Barry Gilbert said:
Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

:

i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


:

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

i wanted to add i tried looking up QueryDefs in my A2K VBA's help doc's and
found that altough the search term showed up there was nothing that appeared
when i selected it. i think this is an example of the way the MIS geniuses
who install these things installed A2K on all desktops.

-ted


Barry Gilbert said:
Yes and no. The SetWarnings commands will affect warnings like "You about to
delete a record..." or "Your baby is about to be throw out with the
bathwater.". It also will prevent the user from seeing errors from things
like Referential Integrity. The dbFailOnError option is more robust and will
allow you to trap those errors, but the code required for using a querydef is
a little more complex. The choice of your approach depends on the nature of
your app and this function.

If you choose to pursue the querydef parameters method, there are plenty of
examples on the web in in the Access help file on how to do it.

Barry

Ted said:
let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

Barry Gilbert said:
Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

:

i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


:

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus
Click to expand...
Click to expand...
Click to expand...
 
B

Barry Gilbert

I've lived through that. The probable cause is that you've, at some point,
upgraded MDAC. WHen this happens, the MDAC help file isn't updated. You can
download the MDAC SDK, which includes help files and copy the .chm's to you
C:\WINNT\Help folder.

Try searching for:

querydef execute parameters

in Google. You'll get a lot of hits. In your case, you're looking for a way
to execute a non-record-returning (action) query. This uses a different
technique from one that returns records through a recordset object, which you
don't need.

Barry


Ted said:
i wanted to add i tried looking up QueryDefs in my A2K VBA's help doc's and
found that altough the search term showed up there was nothing that appeared
when i selected it. i think this is an example of the way the MIS geniuses
who install these things installed A2K on all desktops.

-ted


Barry Gilbert said:
Yes and no. The SetWarnings commands will affect warnings like "You about to
delete a record..." or "Your baby is about to be throw out with the
bathwater.". It also will prevent the user from seeing errors from things
like Referential Integrity. The dbFailOnError option is more robust and will
allow you to trap those errors, but the code required for using a querydef is
a little more complex. The choice of your approach depends on the nature of
your app and this function.

If you choose to pursue the querydef parameters method, there are plenty of
examples on the web in in the Access help file on how to do it.

Barry

Ted said:
let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

:

Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

:

i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


:

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From Px's F/U--Edit Form")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

i actually have three 'action' queries, all different to work with. one
handles updating a table. the others deal with appending a record and
deleting a record from the same table. each query has several parameter
requests which are passively passed from the active form 'Screening Log'.

-ted

Barry Gilbert said:
I've lived through that. The probable cause is that you've, at some point,
upgraded MDAC. WHen this happens, the MDAC help file isn't updated. You can
download the MDAC SDK, which includes help files and copy the .chm's to you
C:\WINNT\Help folder.

Try searching for:

querydef execute parameters

in Google. You'll get a lot of hits. In your case, you're looking for a way
to execute a non-record-returning (action) query. This uses a different
technique from one that returns records through a recordset object, which you
don't need.

Barry


Ted said:
i wanted to add i tried looking up QueryDefs in my A2K VBA's help doc's and
found that altough the search term showed up there was nothing that appeared
when i selected it. i think this is an example of the way the MIS geniuses
who install these things installed A2K on all desktops.

-ted


Barry Gilbert said:
Yes and no. The SetWarnings commands will affect warnings like "You about to
delete a record..." or "Your baby is about to be throw out with the
bathwater.". It also will prevent the user from seeing errors from things
like Referential Integrity. The dbFailOnError option is more robust and will
allow you to trap those errors, but the code required for using a querydef is
a little more complex. The choice of your approach depends on the nature of
your app and this function.

If you choose to pursue the querydef parameters method, there are plenty of
examples on the web in in the Access help file on how to do it.

Barry

:

let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

:

Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

:

i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


:

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Click to expand...
Click to expand...
Click to expand...
 
T

Ted

i guess i oughta have added they use a highly inflexible configuration of the
ms 2000 professional operating system which effectively rules out downloading
the files in your mention.



Barry Gilbert said:
I've lived through that. The probable cause is that you've, at some point,
upgraded MDAC. WHen this happens, the MDAC help file isn't updated. You can
download the MDAC SDK, which includes help files and copy the .chm's to you
C:\WINNT\Help folder.

Try searching for:

querydef execute parameters

in Google. You'll get a lot of hits. In your case, you're looking for a way
to execute a non-record-returning (action) query. This uses a different
technique from one that returns records through a recordset object, which you
don't need.

Barry


Ted said:
i wanted to add i tried looking up QueryDefs in my A2K VBA's help doc's and
found that altough the search term showed up there was nothing that appeared
when i selected it. i think this is an example of the way the MIS geniuses
who install these things installed A2K on all desktops.

-ted


Barry Gilbert said:
Yes and no. The SetWarnings commands will affect warnings like "You about to
delete a record..." or "Your baby is about to be throw out with the
bathwater.". It also will prevent the user from seeing errors from things
like Referential Integrity. The dbFailOnError option is more robust and will
allow you to trap those errors, but the code required for using a querydef is
a little more complex. The choice of your approach depends on the nature of
your app and this function.

If you choose to pursue the querydef parameters method, there are plenty of
examples on the web in in the Access help file on how to do it.

Barry

:

let me just explore something with you for a minute...before i started out
plumbing the murky depths of vba, i had a pair of those setwarnings false and
true sandwiching a docmd.runmacro "macro name having query" in my vba. the
'thing' about that is/was that it would override the error message in the
worst case (or at least that is my understanding). does the current solution
you just proposed involve throwing out the baby with the bathwater so to
speak. i feel that if there is an error that the user oughta know about it.

-ted

:

Instead of using the querydef's execute command, you could use this:
DoCmd.RunSQL CurrentDb.QueryDefs("AppendToFUEditForm").SQL

If you want to turn off the nag message before this code runs, add:
Docmd.SetWarnings False

before the statement and

Docmd.SetWarnings True

after the statement.

Barry

:

i guess the plot si thickening .... here's the SQL for *that* query to help
point out the parameters needing to get passed -- basically they're three
values which need to get passed from the open form called 'Screening Log' the
user is viewing at the time the radio button is selected.

INSERT INTO [Patients on Follow-Up] ( Dummy, [Study#], [Reg#], [IRB Number],
MR_Number, [Pt Initials], [On-Study Date], Site, [Px Status] )
SELECT 1 AS Dummy, [Screening Log].[Sponsor ID Nbr], [Screening
Log].SequenceNum, [Screening Log].[IRB Number], [Screening Log].MR_Number,
Left([First Name],1) & Left([Last Name],1) AS [Pt Initials], [Screening
Log].OffStudyDate AS [On-Study Date], [Screening Log].Campus AS Site,
lkpStatus.Status AS [Px Status]
FROM lkpStatus INNER JOIN [Screening Log] ON lkpStatus.Code = [Screening
Log].Outcome_
WHERE ((([Screening Log].[IRB Number])=[Forms]![Screening Log (Edit
Only)].[IRB Number]) AND (([Screening Log].MR_Number)=[Forms]![Screening Log
(Edit Only)].[MR Number]) AND (([Screening
Log].OffStudyDate)=[Forms]![Screening Log (Edit Only)].[OffStudyDate]) AND
(([Screening Log].Outcome_) In (5)));

newbie me here....how would you go about appending the parameters that the
code is calling for in the vba in question. the documentation so far as i can
determine has little help for me.


:

Your query named AppendToFUEditForm has three parameters in it; your code is
not passing any. If your query does need parameters, you'll have to create
and append parameter objects to the querydef object before executing it.

Barry

:

i've improved on the query's names a bit and modified the vba to comport with
the new ones as well. there's still some buggyness somewhere in all this
though. the qd.execute line in Case 5 is yellowed out and seems to generate
an error message about a run-time error 3061. too few parameters. 3 expected.
make any sense to you?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb


Select Case Frame1.Value
Case 1
Me.Onlistdate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 2
Me.REgisteredDAte.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 3
Me.OnStudyDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 _
Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("DeleteFromFUEditForm")
qd.Execute dbFailOnError
End If

Case 5
Me.OffStudyDate.SetFocus


If myvarOldValue = 1 Or myvarOldValue = 2 Or myvarOldValue =
3 Or myvarOldValue = 4 _
And Not IsNull(OffStudyDate) And Not IsNull(SequenceNum)
Then
DoCmd.RunCommand acCmdSaveRecord
If Flag >= 0 Then
Set qd = db.QueryDefs("AppendToFUEditForm")
qd.Execute dbFailOnError <<<<<< line has problem !!!
End If
ElseIf myvarOldValue = 6 Or myvarOldValue = 7 And Not
IsNull(OffStudyDate) _
And Not IsNull(SequenceNum) Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 6
Me.LTFUDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 7 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case 7
Me.DateDth.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Then
DoCmd.RunCommand acCmdSaveRecord
If Flag = 0 Then
Set qd = db.QueryDefs("UpdateFUEditForm")
qd.Execute dbFailOnError
End If
End If

Case Else

End Select

myvarOldValue = Me.Frame1

End Sub


:

Yes, it's not finding that query in your database. I'm not sure if this is
the issue, but I would rename the query to:

DeleteFromFUEditForm

and change the code to match. See if this change makes any difference.

Barry

:

when my radio button was on case 5 and i clicked case 4's button, the
following line


Case 4
Me.TXEndedDate.SetFocus

If myvarOldValue = 5 Or myvarOldValue = 6 Or myvarOldValue =
7 Then
DoCmd.RunCommand acCmdSaveRecord
Set qd = db.QueryDefs("Delete From F/U--Edit Form")
<<<<<---THIS ONE
qd.Execute dbFailOnError
End If


was highlighted in yellow. does this help? when i hovered over 'querydefs'
it began to spell out the name of the query in the code above and set it = <
item not found in this collection. note that i removed the "Px's" from the
name(s).

-ted


:

Turn off error handling and let us know which line it fails on.

Barry

:

It returned a run time error 91?

:

Which line is it failing on? I would assume on one of he qd.Execute lines.
The only thing that I can see it that the name of the query is misspelled. By
the way, it's considere a bad practice to have spaces or punctuation marks in
database object names.

One way to tell if it's choking on the querydef is to break before it hits
the execute line. Then, in the immediate window, type:

?qd.sql

It should show you the sql statement in the query. If it returns the same
error, it is an issue with the name of the query.

Barry

:

i'm using a2k and am starting to get the error in the heading about 'Item not
found in the collection' when i execute the following VBA code. i've read a
few questions posed of suppressing error messages in the queries group and
thought i'd finally 'gotten' how to pull this off w/o disabling all error
messages including the warnings, so i'll be darned if i understand why it
can't find/use the queries i've created.....i feel really certain that the
names i'm using for them are true to the ones under which i saved 'em. can
anyone offer a theory?

-ted


Private Sub Frame1_AfterUpdate()
Dim Response As Long
Dim Results As Integer
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

why is my VBA runmacro not working 2
VBA works in orignal but fails in copy of database 1
VBA query help - I've never done this before... 2
type mismatch error in macro in excel 0
Issues Template downloaded from MS Access template page 1
Please give me some advice for Update, Uppend and Delete query. 0
Error 13 Type Mismatch 2
Error 94 Invalid Use of Null 2

Top