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

Question

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/sho...cess-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()
'------------------------------------------------------------
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

for every row entered in this subform a row should be entered in the main table...please explain this need as your form absolutely counters it. Main forms to sub forms (e.g., Invoices to InvoiceItems) share the one-to-many link not other way around. — Parfait
– Parfait, Commented Mar 17, 2016 at 13:40
Thank you for you reply. My logic is that in the subform several "Schedule Dates" can be added ie. 01/04/2016, 01/05/2016 etc... and for every date that is entered a new record is entered to tbl_maindata. It's a way to add repeating invoices without having to retype all the standard information project/value/terms etc... — CatParky
– CatParky, Commented Mar 17, 2016 at 14:05

Matt Hall · Accepted Answer · 2016-03-17 13:50:16Z

3

I think the nested loop on the fields in rst will make it hard to build up your SQL string effectively. I'd recommend just writing the whole loop like this instead (remove the fld loop and explicitly reference each field in your rst recordset):

Do Until rst.EOF

    StrSQL = ""
    StrSQL = StrSQL & " INSERT INTO tbl_MainData"
    StrSQL = StrSQL & " (RecordID, ScheduledDate, myNewField1, myNewField2, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, ProjectCode, ServiceCode, InvoiceValue, RPIIncrease, Terms, Status, InvFreq)"
    StrSQL = StrSQL & " VALUES"
    StrSQL = StrSQL & " (" & RecordIDValue & ", #" & rst![Schedule Date] & "#, '" & rst!myNewField1 & "', '" & rst!myNewField2 & "', '" & 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          
    CurrentDb.Execute StrSQL    

    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          
    CurrentDb.Execute StrSQL    
    UpdateCount = UpdateCount + 1

    rst.MoveNext
Loop

To make this work with 2 new fields, you obviously need to add them to both your tbl_MainData and tbl_RepeatTemp tables first.

edited Mar 17, 2016 at 13:50

answered Mar 17, 2016 at 13:40

Matt Hall

2,4227 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andre Over a year ago

@CatParky: Yes, the rst.Fields loop makes no sense here. In addition to this, you need to check for SQL injection, be it deliberate or accidental. i.e. escape single quotes in input values. Or use a safe DAO approach with Recordset.AddNew, -> example

HansUp Over a year ago

Either Recordset.AddNew or a executing parameter query could avoid delimiter (quotes and #) issues.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related