Use Select Case to change field in DoCmd.RunSQL "INSERT INTO...

[TimTDP]

I have the following code

DoCmd.RunSQL "INSERT INTO tblInvoicePrepayment (InvoiceId, PrePaymentValue, PrePaymentDate, PrePaymentCash, Driver) VALUES " & _
                 "(" & Me.InvoiceId & "," & sngInvoiceBalanceOutstanding & "," & Me.InvoiceDate & ", True, True)"

In this code the field PrePaymentCash is updated.
However it could also be PrePaymentCheque or PrePaymentEFT, or PrePaymentCard!

At the moment I have 4 versions of the same code, with only the field name changing.

I want to use a Select Case statement to determine which field to update and then use that field in the code. This will be much more efficient!

How would I do this?

 

[pr2-eugin]

Create a String for the SQL.. Something along the lines of..

Dim strSQL As String, fldName As String
Select Case someThing
    Case relatedToPrePaymentValue
        fldName = "PrePaymentValue"
    Case relatedToPrePaymentDate
        fldName = "PrePaymentDate"
    Case relatedToPrePaymentCash
        fldName = "PrePaymentCash"
End Select

strSQL = "INSERT INTO tblInvoicePrepayment (" & fldName & ") VALUES " & _
            "(" & Me.someNumberControl & ")"

DoCmd.RunSQL strSQL