Loop help

[Qwert0000]

I have a table where I have the "where" clause of my sql statement stored as one of the fields. I can append to a table via a form when running my sql clause based on the current record.

I would like to run this code as a loop on a on dirty trigger on a seperate form. Im not realy sure how to loop though each record of the recordset and run the code one record at a time.

The table I am appending to uses a derived key so I am not worried about writing duplicate records, but am just adding revenent records based on new status.


Below is the code Im playing with, if some one can point me in the right direction

Dim SQLINSERT, WhereSQL As String
Dim rs As Object
Dim School, Target as Long

Set rs = [RequiredTrainingList].RecordsetClone

Loop

School = [RequiredTrainingList].SchoolID
Target = [RequiredTrainingList].TargetAdjust
WhereSQL = [RequiredTrainingList].SQL




SQLINSERT = "INSERT INTO [TrainingReqs] ( RecSchoolID, SSN, SchoolRecNumber, TargetDate ) " & _
"SELECT (([Personnel].RecNumber*1000) + [RequiredSchoolList].SchoolID), [Personnel}.SSN, " & School & ", (DateAdd(""d"", " & Target & ", Date$()))" & _
"FROM [Personnel] " & _
"Where " & Wheresql & " and [Personnel}.SSN = '" & Me.SSNtxt & "' ;"

DoCmd.RunSQL SQLINSERT


End Loop

 

[jsteph]

To address the Loop syntax:
Set rs = [RequiredTrainingList].RecordsetClone

Do Until rs.eof
<Do your stuff>
rs.MoveNext
Loop
--Jim

 

[Zion7]

Just for the record, the variables you delared, I don't believe are dimensioned as you anticipated...

Dim SQLINSERT, WhereSQL As String
Dim rs As Object
Dim School, Target as Long

The default dimension is Variant, so any undeclared datatype becomes variant, ie "SQLINSERT", "School".

Just because they're on the same line, doesn't mean same data type.

Secondly, decalre rs As either DAO.Recordset or ADO.recordset, not Object.

I'm not sure which recordset you're trying to open, but if it is the same as the current form, then

Set rs = Me.RecordsetClone
Otherwise Set rs = New ADODB.Recordset
rs.Open "tblName", CurrentProject.conn....

QWert. Sorry, I really didn't quite understand your set up.
You're trying to append records from a form which has Table1 as it's recordsource, to table2. And it's the forms records(table1), you want to loop thru, to add relavent records to Table2?

 

[Qwert0000]

Thx for the pointer on the Dim Statements

The recodset I am tring to use is not part of the current form I am using.

I have 3 tables I am dealing with.

Table 1 [Personnel] is the record source for the current form . Also has the information I am using to select records in my where statement.

Table 2 [RequiredTrainingList] is the record set I want to loop though. Has no releationship to the current form. The sqlwhere field compares different fields on table 1. Records from this table will be appeneded to Table 3 that meet certain criteria.

Table 3 [TrainingReqs]is the append table. Stores data related to table both table 1 and table 2


I hope this clears up any confusion. I think I understand how to set the loop up, But I guess I cant use the recordsetclone command.

Thanks to both of you for pointing me in the right direction.

 

[idbr]

Hi Qwert0000,

Records from this table will be appeneded to Table 3 that meet certain criteria.

This is adapted from a sub I use to append data to tables using arrays. It relies on being fed a single row of data and then appends it to a target table. The flexibility comes from testing each field value and deciding if you want to add it to the update query.

Should give you a push in the right direction...

Sub AppendToTableFromArray()

'declare two variables to become dynamic arrays
Dim arrDataToAppend
Dim arrFieldsToAppendTo

'general declarations
Dim strDataExtract As String
Dim strSQLFields As String
Dim strSQLValues As String
Dim strUpdate As String

Dim i As Integer
Dim intCount As Integer

Dim rst As DAO.Recordset

Dim dbs As DAO.Database
Set dbs = CurrentDb


'Set the SQL that extracts the data
strDataExtract = "SELECT [YourFieldList] FROM [YourTableName] WHERE [YourCriteria]"

Set rst = dbs.OpenRecordset(strDataExtract)

'Loop through the recordset fields til we get to the end
Do Until i = rst.Fields.Count

    '------------------------------------------------------
    'Check the recordset value - run your own test here
    'I've just run a simple test for nulls, you may want
    'to check the data value based on the field name with
    'SELECT CASE rst(i).Name combined with IF...THEN...ELSE
    'to check that the value is acceptable for the field
    '------------------------------------------------------
    
    If IsNull(rst(i)) = False Then

        'Has the array already instantiated?
        If Not IsArray(arrDataToAppend) Then
            
            'Instantiate the array for first use, indexing at zero
            ReDim arrDataToAppend(0)
            ReDim arrFieldsToAppendTo(0)
        
        Else
            
            'Preserve the values and elements already in the arrays adding another element to the end
            'You may want to explicitly declare data types for each element
            ReDim Preserve arrDataToAppend(UBound(arrDataToAppend) + 1)
            ReDim Preserve arrFieldsToAppendTo(UBound(arrFieldsToAppendTo) + 1)
            
        End If
        
        'Add the data to the data array
        arrDataToAppend(UBound(arrDataToAppend)) = rst(i)
        'Add the field name to the fields array
        arrFieldsToAppendTo(UBound(arrFieldsToAppendTo)) = rst(i).Name
    
    End If
    
    'increase the counter
    i = i + 1

'Next field
Loop

'Cleanup
Set rst = Nothing
Set dbs = Nothing
    
    'All done, parse the arrays into strings for the update query
    For intCount = LBound(arrDataToAppend) To UBound(arrDataToAppend) 'first element to last element
    
        strSQLValues = strSQLValues & ",'" & arrDataToAppend(intCount) & "'" 'build the string
        
    Next 'move on
    
    'rinse and repeat
    intCount = 0
    
    For intCount = LBound(arrFieldsToAppendTo) To UBound(arrFieldsToAppendTo)
    
        strSQLFields = strSQLFields & "," & arrFieldsToAppendTo(intCount)
        
    Next
    
    'Trim the extra commas
    strSQLFields = Right(strSQLFields, Len(strSQLFields) - 1)
    strSQLValues = Right(strSQLValues, Len(strSQLValues) - 1)
    
    Debug.Print strSQLFields
    Debug.Print strSQLValues
    
'Run the append query
strUpdate = "INSERT INTO [YourTargetTableName] (" & strSQLFields & ") VALUES (" & strSQLValues & ");"
    
DoCmd.RunSQL strUpdate

End Sub

HTH, Iain