Re: Error : Item not found in this collection



"spowell15@xxxxxxxxxxx" wrote:

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

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

Relevant Pages

  • Re: Very Basic Excel Object Questions
    ... Thanks Jim ... Set a reference to the primary Excel objects used in your program. ... > Dim xlApp As Excel.Application ...
    (microsoft.public.excel.programming)
  • Re: All Subdirectories
    ... I had the first sub but I did not have the recursive section... ... Jim Thomlinson ... > Dim objFSO As Scripting.FileSystemObject ... > Dim objFolder As Scripting.Folder ...
    (microsoft.public.excel.programming)
  • Re: How to Select a relative range with Using "Find" and Offset()
    ... 'Once the range "MyRange" is computed, ... 'Assistance from Jim Rech 7/26/2005 Excel.General ... Dim MyRange As Range ... Dim FirstDataColumn As Integer ...
    (microsoft.public.excel.misc)
  • Re: Speed this up for me, please
    ... Many thanks Jim. ... Function Sinfo_R1(ByRef lookupKey As String, ByRef lookupVal As String, _ ... Dim rng As Excel.Range ... The first row of the sheet contains titles for the columns. ...
    (microsoft.public.excel.programming)
  • Re: Error : Item not found in this collection
    ... > Thanks for such a quick response Jim. ... >>> I am trying to run an Update query in SQL to a memo field. ... >>> Dim qd As DAO.QueryDefs ... >> form data, and then from this routine, do not delete the querydef, only ...
    (microsoft.public.access.queries)