Results 1 to 15 of 15
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    SQL Query loop through all records

    Hi Guys



    I am really struggling with how to do the following

    I have two tables

    tblTestsRequired (Which Holds Information about part number tests that Are required)
    tblTestresults (will hold the test results per part and reel number)

    i want to copy all the records from "tblTestsRequired" where all the ProductID's = 4 from a form called "frmPartNumber" into the tblTestResults Table when a button is clicked


    i guess i need to loop through all the records to do this but i am really struggling with how this works

    the structure of the two tables is similar with the exception that the "tblTestResults" table has this additional field "ReelNumber" that i want to add into the "tblTestResults" field, the reelnumber is contained in a textbox on the "frmPartNumber" form

    I hope this makes sence

    Many thanks

    Steve

  2. #2
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi All

    I have used this code

    Code:
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    '
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset("tblTestsRequired") 'myTable is a MS-Access table created previously
    '
    ''populate the table
    'rs.MoveLast
    'rs.MoveFirst
    '
    'Do While Not rs.EOF
    '   DoCmd.RunSQL "INSERT INTO tblTestsResults(ReelNumber, PartNumberID, PartNumber, Area, Test, Max, Min, Nom) VALUES(FORMS!frmPartNumber!reelNumber, FORMS!frmPartNumber!PartNumberID, FORMS!frmPartNumber!PartNumber, FORMS!frmPartNumber![frmPartNumberSubForm].Form![Area], FORMS!frmPartNumber![frmPartNumberSubForm].Form![TestName], FORMS!frmPartNumber![frmPartNumberSubForm].Form![MAx], FORMS!frmPartNumber![frmPartNumberSubForm].Form![Min],FORMS!frmPartNumber![frmPartNumberSubForm].Form![Nom])"
    '   rs.MoveNext             'press Ctrl+G to see debuG window beneath
    'Loop
    '
    'MsgBox ("End of Table")
    but this code copys the first entry 6 times all with the same test name

    this part number has the ID of 1 and has 6 different tests required

    does anyone know how i can loop through all the records and insert these into the tblTestresults table

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    does anyone know how i can loop through all the records and insert these into the tblTestresults table
    You have two options: An Append query or VBA code.

    But first, be aware that "Max" and "Min" are reserved words in Access and shouldn't be used as object names.


    *** OK, the following might not work right immediately because I don't know your table structures or field types. You'll probably have to do some tweaking.

    So, the query option.
    You can create an append query that adds the records all at once.

    It would look something like:
    Code:
    INSERT INTO tblTestsResults ( ReelNumber, PartNumberID, PartNumber, Area, Test, [Max], [Min], Nom )
    SELECT tblTestsRequired.ReelNumber, tblTestsRequired.PartNumberID, tblTestsRequired.PartNumber, tblTestsRequired.Area, tblTestsRequired.Test, tblTestsRequired.Max, tblTestsRequired.Min, tblTestsRequired.Nom
    FROM tblTestsRequired
    WHERE (((tblTestsRequired.PartNumber) = FORMS!frmPartNumber!PartNumberID));
    "FORMS!frmPartNumber!PartNumberID" is the control on the form that limits (or selects) the records that will be appended to the table "tblTestsResults". (I may have picked the wrong control)
    The query can be executed from a button click using something like:
    Code:
    Private MyButtonName_Click()
        CurrentDb.Execute "MyAppendQuery", dbfailonerror.
    End Sub


    VBA Code option
    I think you are getting forms and tables/recordsets a little mixed up.

    Forms display data. They do not "have" data the way tables do.
    In your example code, the data on the form does not change even though record set "rs" moves from record to record.
    The record source of the form is independent of the record set in code.... so the data in the form doesn't change. And since you are referencing the controls that are on the form that don't change, all records appended/inserted have the same values.

    Here is the code (UNTETSTED) I came up with. For the text type fields, you will need to add delimiters to the code.
    I named the button "cmdAddTests"
    Code:
    Private Sub cmdAddTests_Click()
        Dim db As DAO.database
        Dim rs As DAO.Recordset
        Dim sSQL As String
    
        Set db = CurrentDb
    
        'Set rs = db.OpenRecordset("tblTestsRequired") 'myTable is a MS-Access table created previously
    
        'open a record set of the records to be added to the other table
        ' limited by "PartNumberID"
        sSQL = "SELECT ReelNumber, PartNumberID, PartNumber, Area, Test, Max, Min, Nom FROM tblTestsRequired"
        sSQL = sSQL & " WHERE PartNumberID = " & Me.PartNumberID  'Me.PartNumberID is the control on the form
        '            Debug.Print sSQL
        'create the recordset
        Set rs = db.OpenRecordset(sSQL)
        '
        'check to see if there are records in the recordset
        If Not rs.bof And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
    
            Do While Not rs.EOF
    
                sSQL = "INSERT INTO tblTestsResults(ReelNumber, PartNumberID, PartNumber, Area, Test, Max, Min, Nom) "
                sSQL = sSQL & " VALUES( " & rs!reelNumber & ", " & rs!PartNumberID & ",  " & rs!PartNumber & ", " & rs![Area] & ", "
                sSQL = sSQL & rs!TestName & ", " & rs![MAX] & ", " & rs![Min] & ", " & rs![Nom] & ")"
                '            Debug.Print sSQL
    
                'insert the record
                db.Execute sSQL, dbfailonerror
                rs.MoveNext             'press Ctrl+G to see debuG window beneath
    
            Loop
        End If
    
        'clean up - close recordsets and destroy objects created
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
        MsgBox ("Done")
    End Sub

  4. #4
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi ssanfu

    Thank you so much dor the really detailed reply you are a start

    i have follwed your advise and changed the Max and Min fields so avoiding any reserved word issues

    I have decided on the VBA route "To help me learn"

    The code i have been playing with is this



    Code:
    Private Sub AddTest_Click()
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim sSQL As String
    
        Set db = CurrentDb
    
        'Set rs = db.OpenRecordset("tblTestsRequired") 'myTable is a MS-Access table created previously
    
        'open a record set of the records to be added to the other table
        ' limited by "PartNumberID"
        sSQL = "SELECT PartNumberID, PartNumber, Area, TestName, MaxValue, MinValue, NomValue FROM tblTestsRequired"
        sSQL = sSQL & " WHERE PartNumberID = " & Me.PartNumberID  'Me.PartNumberID is the control on the form
        '            Debug.Print sSQL
        'create the recordset
        Set rs = db.OpenRecordset(sSQL)
        '
        'check to see if there are records in the recordset
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
                 
            Do While Not rs.EOF
    
                sSQL = "INSERT INTO tblTestsResults(PartNumberID, PartNumber, Area, TestName, MaxValue, MinValue, NomValue) "
                sSQL = sSQL & " VALUES(" & rs!PartNumberID & ",  " & rs!PartNumber  & ", " & rs![Area]  & ", " & rs!TestName  & ", " & rs![MaxValue] & ", " & rs![MinValue] & ", " & rs![NomValue] & ")"
                            'Debug.Print sSQL
            
                'insert the record
                db.Execute sSQL, dbFailOnError
                
                rs.MoveNext             'press Ctrl+G to see debuG window beneath
    
            Loop
        End If
    
        'clean up - close recordsets and destroy objects created
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
        MsgBox ("Done")
        
        
    End Sub

    when run this produces a Run-Time '3061': Too Few Parameters. Expected 3 error

    so then i thought about the PartNumber, Area and testname field names being text values so i changed the code to this

    Code:
    Private Sub AddTest_Click()
        
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim sSQL As String
    
        Set db = CurrentDb
    
        'Set rs = db.OpenRecordset("tblTestsRequired") 'myTable is a MS-Access table created previously
    
        'open a record set of the records to be added to the other table
        ' limited by "PartNumberID"
        sSQL = "SELECT PartNumberID, PartNumber, Area, TestName, MaxValue, MinValue, NomValue FROM tblTestsRequired"
        sSQL = sSQL & " WHERE PartNumberID = " & Me.PartNumberID  'Me.PartNumberID is the control on the form
        '            Debug.Print sSQL
        'create the recordset
        Set rs = db.OpenRecordset(sSQL)
        '
        'check to see if there are records in the recordset
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
            
              Do While Not rs.EOF
    
                sSQL = "INSERT INTO tblTestsResults(PartNumberID, PartNumber, Area, TestName, MaxValue, MinValue, NomValue) "
                sSQL = sSQL & " VALUES(" & rs!PartNumberID & ",  " & rs!PartNumber = "''" & ", " & rs![Area] = "''" & ", " & rs!TestName = "''" & ", " & rs![MaxValue] & ", " & rs![MinValue] & ", " & rs![NomValue] & ")"
                            'Debug.Print sSQL
            
                'insert the record
             db.Execute sSQL, dbFailOnError
                
                rs.MoveNext             'press Ctrl+G to see debuG window beneath
    
            Loop
        End If
    
        'clean up - close recordsets and destroy objects created
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
        MsgBox ("Done")
    
    End Sub
    this time when run i get a "False" msgbox at the db.execute sSql line

    any ideas, sorry to pester you

    Many thanks

    Steve

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    Wow getting their now

    this code works
    Code:
    Private Sub AddTest_Click()
    
    Dim db                                                     As DAO.Database
    Dim rs                                                     As DAO.Recordset
    Dim sSQL                                                   As String
        'Dim PNumber As Integer
        Set db = CurrentDb
    
        'open a record set of the records to be added to the other table
        ' limited by "PartNumberID"
    
        sSQL = "SELECT PartNumberID, PartNumber, Area, TestName, MaxValue, MinValue, NomValue FROM tblTestsRequired"
        sSQL = sSQL & " WHERE PartNumberID = " & Me.PartNumberID    'Me.PartNumberID is the control on the form"
    
        'Debug.Print sSQL
        'create the recordset
        Set rs = db.OpenRecordset(sSQL)
        '
        'check to see if there are records in the recordset
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
    
            Do While Not rs.EOF
    
                sSQL = "INSERT INTO tblTestsResults(PartNumberID,PartNumber, Area, TestName, MaxValue, MinValue, NomValue) "
                sSQL = sSQL & " VALUES(" & rs!PartNumberID & ",   " & "'" & rs!PartNumber & "'" & ",   " & "'" & rs!Area & "'" & ",   " & "'" & rs!TestName & "'" & ", " & rs![MaxValue] & ", " & rs![MinValue] & ", " & rs![NomValue] & ")"
                'Debug.Print sSQL
               
                'insert the record
                db.Execute sSQL, dbFailOnError
                rs.MoveNext             'press Ctrl+G to see debuG window beneath
    
            Loop
        End If
    
        'clean up - close recordsets and destroy objects created
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
        MsgBox ("Done")
    
    End Sub
    the only issue i have now is that if any of the MAxValue, MinValue or NomValue are null i get
    Run-Time Error '3134': Syntax Error in Insert Into Statement


    any ideas
    Steve

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Progress!!


    if any of the MAxValue, MinValue or NomValue are null i get Run-Time Error '3134': Syntax Error in Insert Into Statement
    Two options:
    1) If value is NULL, you could use the NZ() function to change the NULL to a number.
    Code:
    ....& Nz(rs![MaxValue], 0) & ", " & Nz(rs![MinValue], 0) & ", " & Nz(rs![NomValue], 0) & ")"
    2) You could create the SQL statement on-the-fly. First check if a value is NULL, and, if it is, don't include the field in the statement. More code, but the field will remain without a number.

    Option 1 is the easiest, if you can have a zero as the field value.



    When construction a string that requires delimiters (date or text fields), you don't need to have a separate string for the delimiters.
    You have this:
    Code:
    <snip>" VALUES(" & rs!PartNumberID & ",   " & "'" & rs!PartNumber & "'" & ",   " & "'" & rs!Area .. <snip>
    You can write it like this:
    Code:
    <snip>" VALUES(" & rs!PartNumberID & ", '" & rs!PartNumber & "', '" & rs!Area.. <snip>

  7. #7
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi

    thinking about this, option 2 would be better although harder for me to get to grasps with
    would you have any pointers as to how I would do this

    Steve

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Have a look at this. Trace through the code
    Code:
    Private Sub AddTest_Click()
    
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim sSQL As String
        Dim sSQLBase As String
        Dim sSQLValues As String
        Dim sSQLTmp As String
    
        'Dim PNumber As Integer
        Set db = CurrentDb
    
        'open a record set of the records to be added to the other table
        ' limited by "PartNumberID"
    
        sSQL = "SELECT PartNumberID, PartNumber, Area, TestName, MaxValue, MinValue, NomValue"
        sSQL = sSQL & " FROM tblTestsRequired"
        sSQL = sSQL & " WHERE PartNumberID = " & Me.PartNumberID    'Me.PartNumberID is the control on the form"
        '    Debug.Print sSQL
    
        'create the recordset
        Set rs = db.OpenRecordset(sSQL)
        '
        'check to see if there are records in the recordset
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
    
    
            'these are the base SQL statements
            sSQLBase = "INSERT INTO tblTestsResults(PartNumberID,PartNumber, Area, TestName"
            sSQLTmp = " VALUES(" & rs!PartNumberID & ", '" & rs!PartNumber & "', '" & rs!Area & "', '" & rs!TestName & "'"
    
    
            Do While Not rs.EOF
                sSQL = sSQLBase
                sSQLValues = sSQLTmp
    
                'start checking if fields are null
                If Len(Trim(rs![MaxValue] & "")) > 0 Then     ' <<-- MaxValue
                    sSQL = sSQL & ", MaxValue"
                    sSQLValues = sSQLValues & ", " & Nz(rs![MaxValue], 0)
                End If
    
                If Len(Trim(rs![MinValue] & "")) > 0 Then     ' <<-- MinValue
                    sSQL = sSQL & ", MinValue"
                    sSQLValues = sSQLValues & ", " & Nz(rs![MinValue], 0)
                End If
    
                If Len(Trim(rs![NomValue] & "")) > 0 Then     ' <<-- NomValue
                    sSQL = sSQL & ", NomValue"
                    sSQLValues = sSQLValues & ", " & Nz(rs![NomValue], 0)
                End If
    
                'create the full SQL string
                sSQL = sSQL & ")" & sSQLValues & ")"
                '    Debug.Print sSQL
    
                'insert the record
                db.Execute sSQL, dbFailOnError
                rs.MoveNext             'press Ctrl+G to see debuG window beneath
    
            Loop
        End If
    
        'clean up - close recordsets and destroy objects created
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    
        MsgBox ("Done")
    
    End Sub

  9. #9
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi ssanfu

    you are brill, that works wonderfully, thank you so very much for the help and the really detailed reply's

    you are a life saver

    Kind regards

    Steve

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help....

  11. #11
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi mate

    sorry, I noticed after closing the thread that the everything is working really well, the only issue I have is that the name of the first test is being copied for every insert.

    The data is correct for partnumberid, partnumberid, and all the values, it's just the name of the "first test" that's being inserted repeatedly

    if I have 3 tests for example

    twist
    weight
    length

    when the code is run the values for all three tests is correct but the first test name "twist" in this example is the only one that's inserted, I have been looking at the code but can't seem to find out why

    i hate to ask but would you have any idea why

    steve

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Well that shouldn't happen!!

    Would you post an example dB of the data for analysis? The two tables, and the form with the code - enough records to demonstrate the problem.....

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Got it!!! Change the BLUE lines location
    FROM this:
    Code:
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
            '        MsgBox rs.RecordCount
    
            'these are the base SQL statements
            sSQLBase = "INSERT INTO tblTestsResults(PartNumberID,PartNumber, Area, TestName"
            sSQLTmp = " VALUES(" & rs!PartNumberID & ", '" & rs!PartNumber & "', '" & rs!Area & "', '" & rs!TestName & "'"
    
            Do While Not rs.EOF
                sSQL = sSQLBase
                sSQLValues = sSQLTmp
    
                'start checking if fields are null
                If Len(Trim(rs![MaxValue] & "")) > 0 Then     ' <<-- MaxValue
                    sSQL = sSQL & ", MaxValue"
                    sSQLValues = sSQLValues & ", " & Nz(rs![MaxValue], 0)
                End If
    TO this
    Code:
        If Not rs.BOF And Not rs.EOF Then
            rs.MoveLast
            rs.MoveFirst
            '        MsgBox rs.RecordCount
    
            Do While Not rs.EOF
    
                'these are the base SQL statements
                sSQLBase = "INSERT INTO tblTestsResults(PartNumberID,PartNumber, Area, TestName"
                sSQLTmp = " VALUES(" & rs!PartNumberID & ", '" & rs!PartNumber & "', '" & rs!Area & "', '" & rs!TestName & "'"
    
                sSQL = sSQLBase
                sSQLValues = sSQLTmp
    
                'start checking if fields are null
                If Len(Trim(rs![MaxValue] & "")) > 0 Then     ' <<-- MaxValue
                    sSQL = sSQL & ", MaxValue"
                    sSQLValues = sSQLValues & ", " & Nz(rs![MaxValue], 0)
                End If

  14. #14
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi mate

    wow that's brill, many thanks will test when back in the office tomorrow

    you are pure class, many thanks
    steve

  15. #15
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi Mate

    thats brill, many many thanks
    Steve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 04-07-2014, 07:48 PM
  2. Replies: 3
    Last Post: 03-10-2013, 07:04 AM
  3. VBA Loop to Combine Records
    By admessing in forum Queries
    Replies: 23
    Last Post: 03-06-2012, 11:37 AM
  4. Loop through records
    By sam10 in forum Programming
    Replies: 12
    Last Post: 07-07-2011, 02:30 PM
  5. Loop through Records and Make ID
    By rob4465 in forum Programming
    Replies: 3
    Last Post: 01-14-2010, 10:46 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums