Error : Item not found in this collection

S

spowell15

Hi All,

I am trying to run an Update query in SQL to a memo field. I get the
error message 'Item not found in this collection'. I have posted my
code below. Anyone got any ideas what I am doing wrong? Any help
would be greatly appreciated.

Dim db As DAO.Database
Dim qd As DAO.QueryDefs
Dim strSQL As String

Set db = CurrentDb
Set qd = db.QueryDefs("strSQL")

qd!Parameters("ActDate") = "[Forms]![Action_Diary_frm]![Action Date]"
qd!Parameters("ActTaken") = "[Forms]![Action_Diary_frm]![Action
Taken]"
qd!Parameters("Coms") = "[Forms]![Action_Diary_frm]![Comments]"
db.QueryDefs("strSQL").Execute
strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] =
[Act], [Action_Diary].[Action Taken]=
[ActTaken],[Action_Diary].[Comments] = [Coms] WHERE
[Action_Diary].[Action_ID] = [Forms]![Action_Diary_frm]![Action_ID] "

Thanks,

Shirley
 
G

Guest

[email protected] said:
Hi All,

I am trying to run an Update query in SQL to a memo field. I get the
error message 'Item not found in this collection'. I have posted my
code below. Anyone got any ideas what I am doing wrong? Any help
would be greatly appreciated.

Dim db As DAO.Database
Dim qd As DAO.QueryDefs
Dim strSQL As String

Set db = CurrentDb
Set qd = db.QueryDefs("strSQL")

qd!Parameters("ActDate") = "[Forms]![Action_Diary_frm]![Action Date]"
qd!Parameters("ActTaken") = "[Forms]![Action_Diary_frm]![Action
Taken]"
qd!Parameters("Coms") = "[Forms]![Action_Diary_frm]![Comments]"
db.QueryDefs("strSQL").Execute
strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] =
[Act], [Action_Diary].[Action Taken]=
[ActTaken],[Action_Diary].[Comments] = [Coms] WHERE
[Action_Diary].[Action_ID] = [Forms]![Action_Diary_frm]![Action_ID] "

Thanks,

Shirley
Click to expand...

While you are on the right track, there are a number of syntax problems with
your code, beyoind the error you ar currently getting. I removed the
parameters as they are not necessary since I am recreating the query each
time through.


Dim db As Database
Dim qd As QueryDef
Dim strSQL As String

Set db = CurrentDb

'If the query used for this routine already exists, it is deleted so that it
can be
' recreated with the values from the form
For Each qd In db.QueryDefs
If qd.Name = "MemoUpdate" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next qd

strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] = " &
[Forms]![Action_Diary_frm]![Action Date] & ", [Action_Diary].[Action Taken]=
" &
[Forms]![Action_Diary_frm]![Action Taken] & ",[Action_Diary].[Comments] = "
& [Forms]![Action_Diary_frm]![Comments] & " WHERE
[Action_Diary].[Action_ID] = " & [Forms]![Action_Diary_frm]![Action_ID] & ";"

Set qd = dbs.CreateQueryDef("MemoUpdate", strSQL)
qd.Execute


An alternative to this is to save the query using parameters to receive the
form data, and then from this routine, do not delete the querydef, only
populate the parameters, and then execute the query. This assumes that no
one would have modified the querydef in some way that you don't anticipate.

Jim C.
 
S

spowell15

Thanks for such a quick response Jim.

I have changed my code to match what you have provided, I now get an
error on the If statement:

If qd.Name = "MemoUpdate" Then

I get Compile error: Methode or data member not found, the qd.Name is
highlighted.

any idea why this is happening,

Shirley
[email protected] said:
Hi All,

I am trying to run an Update query in SQL to a memo field. I get the
error message 'Item not found in this collection'. I have posted my
code below. Anyone got any ideas what I am doing wrong? Any help
would be greatly appreciated.

Dim db As DAO.Database
Dim qd As DAO.QueryDefs
Dim strSQL As String

Set db = CurrentDb
Set qd = db.QueryDefs("strSQL")

qd!Parameters("ActDate") = "[Forms]![Action_Diary_frm]![Action Date]"
qd!Parameters("ActTaken") = "[Forms]![Action_Diary_frm]![Action
Taken]"
qd!Parameters("Coms") = "[Forms]![Action_Diary_frm]![Comments]"
db.QueryDefs("strSQL").Execute
strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] =
[Act], [Action_Diary].[Action Taken]=
[ActTaken],[Action_Diary].[Comments] = [Coms] WHERE
[Action_Diary].[Action_ID] = [Forms]![Action_Diary_frm]![Action_ID] "

Thanks,

Shirley
Click to expand...

While you are on the right track, there are a number of syntax problems with
your code, beyoind the error you ar currently getting. I removed the
parameters as they are not necessary since I am recreating the query each
time through.


Dim db As Database
Dim qd As QueryDef
Dim strSQL As String

Set db = CurrentDb

'If the query used for this routine already exists, it is deleted so that it
can be
' recreated with the values from the form
For Each qd In db.QueryDefs
If qd.Name = "MemoUpdate" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next qd

strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] = " &
[Forms]![Action_Diary_frm]![Action Date] & ", [Action_Diary].[Action Taken]=
" &
[Forms]![Action_Diary_frm]![Action Taken] & ",[Action_Diary].[Comments] = "
& [Forms]![Action_Diary_frm]![Comments] & " WHERE
[Action_Diary].[Action_ID] = " & [Forms]![Action_Diary_frm]![Action_ID] & ";"

Set qd = dbs.CreateQueryDef("MemoUpdate", strSQL)
qd.Execute


An alternative to this is to save the query using parameters to receive the
form data, and then from this routine, do not delete the querydef, only
populate the parameters, and then execute the query. This assumes that no
one would have modified the querydef in some way that you don't anticipate.

Jim C.
Click to expand...
 
G

Guest

You probably missed the change in the definition of qd to be QueyDef not
QueryDefs. I should have pointed that out since it's easily missed.

Jim C.

[email protected] said:
Thanks for such a quick response Jim.

I have changed my code to match what you have provided, I now get an
error on the If statement:

If qd.Name = "MemoUpdate" Then

I get Compile error: Methode or data member not found, the qd.Name is
highlighted.

any idea why this is happening,

Shirley
[email protected] said:
Hi All,

I am trying to run an Update query in SQL to a memo field. I get the
error message 'Item not found in this collection'. I have posted my
code below. Anyone got any ideas what I am doing wrong? Any help
would be greatly appreciated.

Dim db As DAO.Database
Dim qd As DAO.QueryDefs
Dim strSQL As String

Set db = CurrentDb
Set qd = db.QueryDefs("strSQL")

qd!Parameters("ActDate") = "[Forms]![Action_Diary_frm]![Action Date]"
qd!Parameters("ActTaken") = "[Forms]![Action_Diary_frm]![Action
Taken]"
qd!Parameters("Coms") = "[Forms]![Action_Diary_frm]![Comments]"
db.QueryDefs("strSQL").Execute
strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] =
[Act], [Action_Diary].[Action Taken]=
[ActTaken],[Action_Diary].[Comments] = [Coms] WHERE
[Action_Diary].[Action_ID] = [Forms]![Action_Diary_frm]![Action_ID] "

Thanks,

Shirley
Click to expand...

While you are on the right track, there are a number of syntax problems with
your code, beyoind the error you ar currently getting. I removed the
parameters as they are not necessary since I am recreating the query each
time through.


Dim db As Database
Dim qd As QueryDef
Dim strSQL As String

Set db = CurrentDb

'If the query used for this routine already exists, it is deleted so that it
can be
' recreated with the values from the form
For Each qd In db.QueryDefs
If qd.Name = "MemoUpdate" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next qd

strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date] = " &
[Forms]![Action_Diary_frm]![Action Date] & ", [Action_Diary].[Action Taken]=
" &
[Forms]![Action_Diary_frm]![Action Taken] & ",[Action_Diary].[Comments] = "
& [Forms]![Action_Diary_frm]![Comments] & " WHERE
[Action_Diary].[Action_ID] = " & [Forms]![Action_Diary_frm]![Action_ID] & ";"

Set qd = dbs.CreateQueryDef("MemoUpdate", strSQL)
qd.Execute


An alternative to this is to save the query using parameters to receive the
form data, and then from this routine, do not delete the querydef, only
populate the parameters, and then execute the query. This assumes that no
one would have modified the querydef in some way that you don't anticipate.

Jim C.
Click to expand...
Click to expand...
 
S

spowell15

You were right Jim, but now I have a new error which doesn't mean too
much:

"Application-defined or Object defined error"

This is my code so far:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

For Each qd In db.QueryDefs
If qd.Name = "MemoUpdate" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next qd

strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date]= '" &
[Forms]![Action_Diary_frm]![Action Date] & "',
[Action_Diary].[Action Taken]='" & [Forms]![Action_Diary_frm]![Action
Taken] & "', [Action_Diary].[Comments]='" &
[Forms]![Action_Diary_frm]![Comments] & "' WHERE
[Action_Diary].[Action_ID]=" & [Forms]![Action_Diary_frm]![Action_ID] &
";"

Set qd = db.CreateQueryDef("MemoUpdate", strSQL)
qd.Execute

Shirley
 
D

Duane Hookom

Is there a reason why you delete and recreate the querydef? Consider just
changing the SQL property

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qd = db.QueryDefs("MemoUpdate")

strSQL = "UPDATE [Action_Diary] SET [Action Date]= '" & _
[Forms]![Action_Diary_frm]![Action Date] & _
"',[Action Taken]='" & _
[Forms]![Action_Diary_frm]![Action Taken] & _
"', [Comments]='" & _
[Forms]![Action_Diary_frm]![Comments] & _
"' WHERE [Action_ID]=" & [Forms]![Action_Diary_frm]![Action_ID] &";"
Debug.Print strSQL
qd.SQL = strSQL
qd.Execute

Or you can forget about the querydef and use
db.Execute strSQL , dbFailOnError

You may have issues with delimiters and embedded single quotes in comments.
Is your Action Date field a text field?
 
G

Guest

[email protected] said:
You were right Jim, but now I have a new error which doesn't mean too
much:

"Application-defined or Object defined error"

This is my code so far:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

For Each qd In db.QueryDefs
If qd.Name = "MemoUpdate" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next qd

strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date]= '" &
[Forms]![Action_Diary_frm]![Action Date] & "',
[Action_Diary].[Action Taken]='" & [Forms]![Action_Diary_frm]![Action
Taken] & "', [Action_Diary].[Comments]='" &
[Forms]![Action_Diary_frm]![Comments] & "' WHERE
[Action_Diary].[Action_ID]=" & [Forms]![Action_Diary_frm]![Action_ID] &
";"

Set qd = db.CreateQueryDef("MemoUpdate", strSQL)
qd.Execute

Shirley
Click to expand...

FIrst, the SQL statement for [ActionDate] = date, the result needs to be
enclosed in #, eg [ActionDate]=#01/01/1900#. Second, if Action_ID is text,
the where condition will need to have quotes around the result, eg Where
[Action_ID] = 'action'.

If neither of these fixes the current problem, then post back with the line
in which the error is being generated. Also, if the query MemoUpdates exists
(it will depending on where this bombed) please cut/paste the SQL that is in
there with your reply.

Jim C.
 
G

Guest

Duane Hookom said:
Is there a reason why you delete and recreate the querydef? Consider just
changing the SQL property

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb
Set qd = db.QueryDefs("MemoUpdate")

strSQL = "UPDATE [Action_Diary] SET [Action Date]= '" & _
[Forms]![Action_Diary_frm]![Action Date] & _
"',[Action Taken]='" & _
[Forms]![Action_Diary_frm]![Action Taken] & _
"', [Comments]='" & _
[Forms]![Action_Diary_frm]![Comments] & _
"' WHERE [Action_ID]=" & [Forms]![Action_Diary_frm]![Action_ID] &";"
Debug.Print strSQL
qd.SQL = strSQL
qd.Execute

Or you can forget about the querydef and use
db.Execute strSQL , dbFailOnError

You may have issues with delimiters and embedded single quotes in comments.
Is your Action Date field a text field?

--
Duane Hookom
MS Access MVP
--

You were right Jim, but now I have a new error which doesn't mean too
much:

"Application-defined or Object defined error"

This is my code so far:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String

Set db = CurrentDb

For Each qd In db.QueryDefs
If qd.Name = "MemoUpdate" Then
db.QueryDefs.Delete qd.Name
Exit For
End If
Next qd

strSQL = "UPDATE [Action_Diary] SET [Action_Diary].[Action Date]= '" &
[Forms]![Action_Diary_frm]![Action Date] & "',
[Action_Diary].[Action Taken]='" & [Forms]![Action_Diary_frm]![Action
Taken] & "', [Action_Diary].[Comments]='" &
[Forms]![Action_Diary_frm]![Comments] & "' WHERE
[Action_Diary].[Action_ID]=" & [Forms]![Action_Diary_frm]![Action_ID] &
";"

Set qd = db.CreateQueryDef("MemoUpdate", strSQL)
qd.Execute

Shirley
Click to expand...
Click to expand...

If I just want to change the SQL, my only concern is if the querydef doesn't
already exist it will generate an error. That could be handled as part of
the error logic, I've just never been sure which of the alternatives is
really best.

Jim C.
 

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

VBA query help - I've never done this before... 2
SQL UPDATE Query doesn't Update Table 5
Run-time error '3265' 19
Error 91 - with Querydefs - newbie 1
Running custom Excel query for Access 5
Accessing the Data Connection Strings and Command Text (SQL) in VB 5
Run Time Error 3075 4
Update SQLquery not updating table 6

Top