Need help to amend : SQL Statement to loop through fields in a subform (1 Viewer)

Nyanko

Registered User.
Local time
Today, 11:18
Joined
Apr 21, 2005
Messages
57
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 :

Code:
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 :)
 

Attachments

  • Repeating.jpg
    Repeating.jpg
    84.3 KB · Views: 369

Nyanko

Registered User.
Local time
Today, 11:18
Joined
Apr 21, 2005
Messages
57
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 ?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:18
Joined
Jul 9, 2003
Messages
16,245

Users who are viewing this thread

Top Bottom