Re: Error : Item not found in this collection

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.

"spowell15@xxxxxxxxxxx" wrote:

> 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
>
> Jim C. wrote:
> > "spowell15@xxxxxxxxxxx" wrote:
> >
> > > 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
> > >
> > >
> >
> > 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.
>
>
.

Relevant Pages

  • Re: Can I perform a DCount of records returned by SQL statement (not a saved query)
    ... a recordset has parameters that you aren't feeding to the query. ... you'll need create a temporary querydef that is based on ... Dim rst As Recordset ...
    (comp.databases.ms-access)
  • Re: question about CreateQueryDef
    ... Can anyone help me to make this query shop up as one of the queries in my ... Dim cn As ADODB.Connection ... Dim db As Database ... Dim qdfNew As New QueryDef ...
    (microsoft.public.access.formscoding)
  • Re: Query Know Thyself?
    ... The QueryDef has Fields. ... An example of looking at all fields in a query: ... Dim qdf As DAO.QueryDef ... into one destination table. ...
    (microsoft.public.access.queries)
  • RE: Create multiple queries in Access with a single sql statement
    ... You can also create a new query by using the CreateQueryDef method. ... Here is an example where I create a querydef based on an original querydef. ... Dim strSQL As String ... Dim qdf As QueryDef ...
    (microsoft.public.access.queries)
  • Re: QueryDef vs. Recordset Errors
    ... the query is probably not quite correct. ... You can open a recordset based on the QueryDef after you ... Dim qdf As QueryDef ...
    (microsoft.public.access.formscoding)