Hi,
I am using the below adapted code to insert multiple invoices into a database.
http://stackoverflow.com/questions/...statement-to-loop-through-fields-in-a-subform
I have included the below function to help with the US/UK/SQL dates issue that I always run into
http://allenbrowne.com/ser-36.html
I am getting an error when a date field is empty (it is an optional field) however I think because the SQL statement is looking for a value it is failing. I have tested and with the date entered it works, when left blank it fails.
My question is : On the below code how can I have this statement allow an empty/null value ?
Successful debug result with date :
INSERT INTO tbl_MainData (RecordID, ScheduledDate, RPIDate, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, PONumber, ProjectCode, ServiceCode, InvoiceValue, Commissionable, Terms, DueDate, ExpectedDate, Status, InvFreq) VALUES (10082, #06/07/2016#, #04/01/2016#,'111', '222', 'Invoice', 'John Smith', #07/06/2016#, '', 'ENE01198', 'CAS00000', 123, 123, '30 Days',#07/07/2016#,#08/06/2016#,'Proforma Scheduled to Raise', 'TBC')
Unsuccessful debug with no date :
INSERT INTO tbl_MainData (RecordID, ScheduledDate, RPIDate, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, PONumber, ProjectCode, ServiceCode, InvoiceValue, Commissionable, Terms, DueDate, ExpectedDate, Status, InvFreq) VALUES (10081, #06/07/2016#, ,'111', '222', 'Invoice', 'John Smoth', #07/06/2016#, '', 'ENE01198', 'CAS00000', 123, 123, '30 Days',#07/07/2016#,#08/06/2016#,'Proforma Scheduled to Raise', 'TBC')
I get an error 3134 : Syntax error in INSERT INTO statement
Full Code :
Advice and suggestions gratefully received !
I am using the below adapted code to insert multiple invoices into a database.
http://stackoverflow.com/questions/...statement-to-loop-through-fields-in-a-subform
I have included the below function to help with the US/UK/SQL dates issue that I always run into
http://allenbrowne.com/ser-36.html
I am getting an error when a date field is empty (it is an optional field) however I think because the SQL statement is looking for a value it is failing. I have tested and with the date entered it works, when left blank it fails.
My question is : On the below code how can I have this statement allow an empty/null value ?
Code:
" & SQLDate(rst![RPIDate]) & "Successful debug result with date :
INSERT INTO tbl_MainData (RecordID, ScheduledDate, RPIDate, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, PONumber, ProjectCode, ServiceCode, InvoiceValue, Commissionable, Terms, DueDate, ExpectedDate, Status, InvFreq) VALUES (10082, #06/07/2016#, #04/01/2016#,'111', '222', 'Invoice', 'John Smith', #07/06/2016#, '', 'ENE01198', 'CAS00000', 123, 123, '30 Days',#07/07/2016#,#08/06/2016#,'Proforma Scheduled to Raise', 'TBC')
Unsuccessful debug with no date :
INSERT INTO tbl_MainData (RecordID, ScheduledDate, RPIDate, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, PONumber, ProjectCode, ServiceCode, InvoiceValue, Commissionable, Terms, DueDate, ExpectedDate, Status, InvFreq) VALUES (10081, #06/07/2016#, ,'111', '222', 'Invoice', 'John Smoth', #07/06/2016#, '', 'ENE01198', 'CAS00000', 123, 123, '30 Days',#07/07/2016#,#08/06/2016#,'Proforma Scheduled to Raise', 'TBC')
I get an error 3134 : Syntax error in INSERT INTO statement
Full Code :
Code:
Private Sub btn_CreateInvoices_Click()
'------------------------------------------------------------
' Procedure : Create the repeat invoices
' Author : HansV
' Website : http://windowssecrets.com/forums/showthread.php/37448-looping-through-fields-(Access-2000-SR-1)
' Amendment : http://stackoverflow.com/questions/36061026/need-help-to-amend-access-sql-statement-to-loop-through-fields-in-a-subform
' Purpose : Loop through the fields in a temp table then update the main table
'------------------------------------------------------------
Dim StrSQL As String, MyStatus As String
Dim RecordIDValue As Long, RowIDValue As Long, UpdateCount 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 = "Proforma Scheduled to Raise"
' Loop through the scheduled dates entered
Do Until rst.EOF
'Insert into tbl_MainData
StrSQL = ""
StrSQL = StrSQL & " INSERT INTO tbl_MainData"
StrSQL = StrSQL & " (RecordID, ScheduledDate, RPIDate, InvoiceID, ProformaID, TransactionType, InputBy, InputDate, PONumber, ProjectCode, ServiceCode, InvoiceValue, Commissionable, Terms, DueDate, ExpectedDate, Status, InvFreq)"
StrSQL = StrSQL & " VALUES"
StrSQL = StrSQL & " (" & RecordIDValue & ", " & SQLDate(rst![ScheduleDate]) & ", " & SQLDate(rst![RPIDate]) & ",'" & Me.FormInvoiceID & "', '" & Me.FormProformaID & "', '" & rst![TransactionType] & "', '" & Me.FormInputBy & "', #" & Format(Me.InputDate, "dd/mm/yyyy") & "#, '" & Me.FormPONumber & "', '" & Me.FormProjectCode & "', '" & Me.FormServiceCode & "', " & Me.FormInvoiceValue & ", " & Abs(Me.FormInvoiceValue) & ", '" & Me.FormTerms & "'," & SQLDate(rst![ScheduleDate] + Left(Me.FormTerms, 2)) & "," & SQLDate(rst![ScheduleDate] + Left(Me.FormTerms, 2) + 30) & ",'" & MyStatus & "', '" & Me.FormInvFreq & "')"
Debug.Print StrSQL
CurrentDb.Execute StrSQL
'Insert into tbl_DebtTracker
RowIDValue = RowIDValue + 1
StrSQL = ""
StrSQL = StrSQL & " INSERT INTO tbl_DebtTracker"
StrSQL = StrSQL & " (RowID, RecordID, ProjectCode)"
StrSQL = StrSQL & " VALUES"
StrSQL = StrSQL & " (" & RowIDValue & ", " & RecordIDValue & ", '" & Me.FormProjectCode & "');"
Debug.Print StrSQL
CurrentDb.Execute StrSQL
UpdateCount = UpdateCount + 1
rst.MoveNext
Loop
rst.Close
'Notify
DoCmd.Close acForm, "frm_MainInputRepeat"
MsgBox UpdateCount & " Records successfully created.", , "Success !"
'Clear values
Set rst = Nothing
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
End SubAdvice and suggestions gratefully received !
