Need help to amend : SQL Statement to loop through fields in a subform

[Nyanko]

Hi,

Hope this makes sense. I have adapted a code to allow me to create a report entry form for invoices.

Original code : http://windowssecrets.com/forums/showthread.php/37448-looping-through-fields-(Access-2000-SR-1)

I have a subform (created from tbl_RepeatTemp) with a single field:
ScheduleDate

for every row entered in this subform a row should be entered in the main table. This is the code that is currently working :

Private Sub btn_CreateInvoices_Click()
'------------------------------------------------------------
' Create the repeat invoices
' http://windowssecrets.com/forums/showthread.php/37448-looping-through-fields-(Access-2000-SR-1)
'------------------------------------------------------------
Dim StrSQL As String
Dim RecordIDValue As Long

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field

Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tbl_RepeatTemp")
Set rst = tdf.OpenRecordset

'Find the next RecordID to use
RecordIDValue = DMax("[RecordID]", "[tbl_MainData]") + 1
RowIDValue = DMax("[RowID]", "[tbl_MainData]")
UpdateCount = 0
If Me.FormProformaStatus = "" Then myStatus = "" Else myStatus = "Scheduled to Raise"


' Loop through the acheduled dates entered
    Do Until rst.EOF
            For Each fld In rst.Fields
                '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                'Insert fields in to tbl_MainData
                '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                StrSQL = ""
                StrSQL = StrSQL & " INSERT INTO tbl_MainData"
                StrSQL = StrSQL & " (RecordID, ScheduledDate, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, ProjectCode, ServiceCode, InvoiceValue, RPIIncrease, Terms, Status, InvFreq)"
                StrSQL = StrSQL & " VALUES"
                StrSQL = StrSQL & " (" & RecordIDValue & ", #" & Format(fld.Value, "Medium Date") & "#, '" & Me.FormInvoiceID & "', '" & Me.FormProformaID & "', 'Invoice', '" & Me.FormInputBy & "', #" & Format(Me.InputDate, "Medium Date") & "#, '" & Me.FormProjectCode & "', '" & Me.FormProductCode & "', " & Me.FormInvoiceValue & ", '" & Me.RPIIncrease & "', '" & Me.FormTerms & "','" & myStatus & "', '" & Me.FormInvFreq & "')"
                Debug.Print StrSQL          'Print values to immediate window for debugging
                CurrentDb.Execute StrSQL    'Execute SQL
                
                '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                'Insert fields into tbl_DebtTracker
                '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                'UpdateRow ID to match tbl_MainData
                RowIDValue = RowIDValue + 1
                StrSQL = ""
                StrSQL = StrSQL & " INSERT INTO tbl_DebtTracker"
                StrSQL = StrSQL & " (RowID,RecordID, ProjectCode, InvoiceID)"
                StrSQL = StrSQL & " VALUES"
                StrSQL = StrSQL & " (" & RowIDValue & ", " & RecordIDValue & ", '" & Me.FormProjectCode & "', '" & Me.FormInvoiceID & "');"
                Debug.Print StrSQL          'Print values to immediate window for debugging
                CurrentDb.Execute StrSQL    'Execute SQL
                UpdateCount = UpdateCount + 1
            Next fld
        rst.MoveNext
    Loop
    rst.Close
    
MsgBox UpdateCount & " Records successfully created.", , "Success !"
    
    Set rst = Nothing
    Set fld = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
Me.Refresh
End Sub

I now want to add 2 further fields to the tbl_Repeat temp table

RPIIncrease replacing Me.RPIIncrease
RPIDate as a new field

From what I can see the variable fld worked when there was one field - which is why the ScheduledDate is fld.value - how can I add these other 2 fields ? I attach an image of my subform which might help to visualise what I am doing

 

[Ranman256]

You don't need any code. Use a query.

 

[Nyanko]

Thank you for the suggestion.

However, I have the above working fine in it's current state and just need to add 2 fields to it. I'd rather not have to start over and create a large query from scratch that deals with adding specific fields to different tables. I wouldn't know where to start - which is why the above works for me.

... unless you can expand on your suggestion ?

 

[Nyanko]

Solution found here :

http://stackoverflow.com/questions/...statement-to-loop-through-fields-in-a-subform

 

[Uncle Gizmo]

Hi,

Hope this makes sense. I have adapted a code to allow me to create a report entry form for invoices.

Original code : http://windowssecrets.com/forums/showthread.php/37448-looping-through-fields-(Access-2000-SR-1)

I think the code on my website here:-

https://sites.google.com/site/msaccess457966vmfjg/articles-by-toeny-hine-awf-vip/excel-in-access

does something similar to the code in the link above. Thought I should add it as an alternative.