Results 1 to 4 of 4

Thread: Code Help !!!

  1. #1
    Join Date
    May 2003
    Posts
    26

    Unanswered: Code Help !!!

    I have a table (tblZones) which I would like to step through and pass the value of the ID (number) field to an append query. When I run the code I get a prompt to enter the value for Zone.

    What am I missing here?

    Set DB = CurrentDb
    Set rs = DB.OpenRecordset("tblZones", dbOpenDynaset)
    Set Zone = rs![ID]

    Do While rs.EOF = False

    DoCmd.RunSQL "INSERT INTO [tblReport Summary REGION] (Region, StatusDate, Received, Assigned, Complete )SELECT [tblReport Summary REGION TEMP].Region, [tblReport Summary REGION TEMP].StatusDate, [tblReport Summary REGION TEMP].R AS Received, [tblReport Summary REGION TEMP].A AS Assigned, [tblReport Summary REGION TEMP].C AS Complete FROM [tblReport Summary REGION TEMP] WHERE ((([tblReport Summary REGION TEMP].Region) = Zone)) ORDER BY [tblReport Summary REGION TEMP].Region, [tblReport Summary REGION TEMP].StatusDate;"

    rs.MoveNext

    Loop

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Code Help !!!

    Okay... Three things...

    1) I don't know what you were defining Zone as since I don't see Dim statements here... but you don't need it anyway... Just use rs![ID]...

    2) You were being asked for a value for Zone because you didn't concatenate the ID value into the Insert string... (Look at your Where clause)

    3) I've fixed those two parts (see code below)... but I'm confused with your SQL... You are using the Multiple record INSERT INTO syntax... while you are iterating through the other recordset... Are you sure the SQL is right?... It looks strange to me... but I don't know your needs...

    Code:
    Set DB = CurrentDb
    Set rs = DB.OpenRecordset("tblZones", dbOpenDynaset)
    
    rs.MoveFirst
    If rs.RecordCount > 0 Then
    
    Do While rs.EOF = False
    
    DoCmd.RunSQL "INSERT INTO [tblReport Summary REGION] (Region,
     StatusDate, Received, Assigned, Complete )SELECT [tblReport
     Summary REGION TEMP].Region, [tblReport Summary REGION 
    TEMP].StatusDate, [tblReport Summary REGION TEMP].R AS Received, 
    [tblReport Summary REGION TEMP].A AS Assigned, [tblReport 
    Summary REGION TEMP].C AS Complete FROM [tblReport Summary
     REGION TEMP] WHERE ((([tblReport Summary REGION TEMP].Region) 
    = " & rs![ID] & ")) ORDER BY [tblReport Summary REGION TEMP].Region, 
    [tblReport Summary REGION TEMP].StatusDate;"
    
    rs.MoveNext
    
    Loop
    
    End If
    Here's the Syntax for Single and Multiple record appends by SQL (from Access Help)...
    Syntax

    Multiple-record append query:

    INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
    SELECT [source.]field1[, field2[, ...]
    FROM tableexpression

    Single-record append query:

    INSERT INTO target [(field1[, field2[, ...]])]
    VALUES (value1[, value2[, ...])
    HTH

  3. #3
    Join Date
    Jan 2004
    Location
    Australia
    Posts
    15
    What data type is your variable Zone? You should set the variables data type to the same as the field in your table. So if your field ID in your table tblZones is a Long Integer then Dim Zone As Long.

    By dimming Zone as a long you do not need to use the Set keyword. See example below. You will have to update the value of Zone within the loop.

    Code:
    Dim DB As Database
    Dim rs As Recordset
    Dim Zone As Long
    
    Set DB = CurrentDb
    Set rs = DB.OpenRecordset("tblZones", dbOpenDynaset)
    Do While rs.EOF = False
         Zone = rs![ID]
         DoCmd.RunSQL SQLStatement
         rs.MoveNext
    Loop
    If you are dimming Zone as a field in your recordset then the following applies. You don't have to update the value of zone within the loop.
    Code:
    Dim DB As Database
    Dim rs As Recordset
    Dim Zone As Field
    
    Set DB = CurrentDb
    Set rs = DB.OpenRecordset("tblZones", dbOpenDynaset)
    Set Zone = rs.Fields("ID")
    
    Do While rs.EOF = False
         DoCmd.RunSQL SQLStatement
         rs.MoveNext
    Loop
    In your SQL statement you aren't actually using your variable. Modify to the following..

    DoCmd.RunSQL "INSERT INTO [tblReport Summary REGION] (Region, StatusDate, Received, Assigned, Complete )SELECT [tblReport Summary REGION TEMP].Region, [tblReport Summary REGION TEMP].StatusDate, [tblReport Summary REGION TEMP].R AS Received, [tblReport Summary REGION TEMP].A AS Assigned, [tblReport Summary REGION TEMP].C AS Complete FROM [tblReport Summary REGION TEMP] WHERE ((([tblReport Summary REGION TEMP].Region) = " & Zone & ")) ORDER BY [tblReport Summary REGION TEMP].Region, [tblReport Summary REGION TEMP].StatusDate;"

    Hope this helps

  4. #4
    Join Date
    May 2003
    Posts
    26
    Thanks everyone!

    I had "set" Zone = rs![ID] but somehow excluded this from my post. I had looked at this so long and hard that my eyes were crossing.

    The real problem was the concatenation of Zone in my WHERE clause .... works perfectly now.

    Sometimes it is the simpiliest of oversights, again .... THANK YOU VERY MUCH!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •