When the SQL is executed I created a msgbox strSQL to see what is entered in the strSQL and I get this message:
INSERT INTO [Invoice-Order] ([Number], [Your Order #], [Sales Rep], [Job Number], [Terms]) VALUES (SELECT invoiceID FROM Invoice-Address WHERE CustomerNumber = 11, '333', '0', 'Another fancy tes','0')
When the above SQL statement is executed from the command DoCMD.runSQL strSQL
I get this error message:
Run-time error '3075':
Syntax error, in query expression 'SELECT invoiceID FROM Invoice-Address WHERE CustomerNumber =11'.
What I wanted to do from the statement:
INSERT INTO [Invoice-Order] ([Number], [Your Order #], [Sales Rep], [Job Number], [Terms]) VALUES (intNumber, '333', '0', 'Another fancy tes','0')
Where what ever the value of intNumber occure when the condition CustomerNumber = ClientID. I thought this is the way to do.
I have 6 tables in total. 3 of them are related to invoice and 3 of them are
related to estimate.
The invoice table have:
Invoice-Address
Invoice-Order
Invoice-Description
The estimte table have:
Estimate-Address
Estimate-Order
Estimate-Description
The estimate Address:
1) EstimateID-AutoNumber AutoNumber
2) CustomerNumber - inserted and matched by clientID
The Estimate-Order:
1) EstimateOrdID - AutoNumber AutoNumber
2) DescriptionNumber-linked by relationship with [Estimate-Address] EstimateID
The Estimate-Description:
1) Estimate-DescriptionID AutoNumber
2) Estimate-DescriptionNumber - link by relationship with [Estimate-Order]
EstimateOrdID
The Invoice:
1) InvoiceID AutoNumber
2) CustomerNumber inserted and matched by ClientID
The Invoice-Order:
1) InvoiceID AutoNumber
2) Number linked by relationship with table [Invoice] invoiceID
The Invoice-Description:
1) Invoice Number AutoNumber
2) InvoiceDescriptionID link by relationship with table [Invoice-Order]
invoiceID
I have three functions separetly I created, called:
ExportAddressDestination
ExportOrderDestination
ExportDescriptionDestination
Below are the codes of three functions:
Option Compare Database
' Export from Table Estimate to Table Invoice
Public Sub ExportAddressDestination(TableName As String, CustomerNumber As
String, EstimateNumber As String, TableDate As String, Attention As String,
valueCustomerNumber As Integer, valueEstimateNumber As Integer, valueDate As
String, valueAttention As String)
DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "INSERT INTO [" & TableName & "] ([" & CustomerNumber & "],[" &
EstimateNumber & "],[" & TableDate & "],[" & Attention & "]) VALUES (" &
valueCustomerNumber & "," & valueEstimateNumber & ",'" & valueDate &
"','" & valueAttention & "')"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
Public Sub ExportOrderDestination(TableName As String, intNumber As String,
YourOrderNumber As String, SalesRep As String, JobNumber As String, Terms As
String, inNumber As Integer, valueOrderNumber As String, valueSalesRep As
String, valueJobNumber As String, valueTerms As String)
DoCmd.SetWarnings False
Dim strSQL As String
Dim recordSet As Database
Dim strSQLTwo
'strSQL = "INSERT INTO [" & TableName & "] ([" & intNumber & "],[" &
YourOrderNumber & "],[" & SalesRep & "],[" & JobNumber & "],[" & Terms &
"]) VALUES (" & "SELECT invoiceID FROM Invoice-Address WHERE CustomerNumber =
" & inNumber & ",'" & valueOrderNumber & "','" & valueSalesRep &
"','" & valueJobNumber & "','" & valueTerms & "')"
strSQL = "INSERT INTO [" & TableName & "] ([" & intNumber & "],[" &
YourOrderNumber & "],[" & SalesRep & "],[" & JobNumber & "],[" & Terms &
"]) SELECT invoiceID FROM Invoice-Address WHERE CustomerNumber = " & inNumber
& ",'" & valueOrderNumber & "','" & valueSalesRep & "','" &
valueJobNumber & "','" & valueTerms & "'"
MsgBox strSQL
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
Public Sub ExportDescriptionDestination(TableName As String, CustomerNumber As
String, Quantity As String, Item As String, Units As String, Description As
String, Discount As String, UnitPrice As String, intClientID As Integer,
valueQuantity As Integer, valueItem As String, valueUnits As String,
valueDescription As String, valueDiscount As Double, valueUnitPrice As Double)
DoCmd.SetWarnings False
Dim strSQL As String
strSQL = "INSERT INTO [" & TableName & "] ([" & CustomerNumber & "], [" &
Quantity & "], [" & Item & "],[" & Units & "],[" & Description & "],[" &
Discount & "],[" & UnitPrice & "]) VALUES (" & intClientID & "," &
valueQuantity & ",'" & valueItem & "','" & valueUnits & "','" &
valueDescription & "','" & valueDiscount & "','" & valueUnitPrice &
"')"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
-----------------------------------------------------------------------------
In the form Estimate there is a button called Export to Invoice:
Below is the code for that button:
Dim n As Integer
Dim intClientID As Integer
' Estimate Address
Dim strSQL As String
Dim intEstimateN As Integer
Dim strDate As String
Dim strAtt As String
' Estimate Order
Dim strYourOrderN As String
Dim strSalesRep As String
Dim strJobN As String
Dim strTerms As String
' Estimate Description
Dim intQuantity As Integer
Dim strItem As String
Dim strUnits As String
Dim strDescription As String
Dim dblDiscount As Double
Dim dblUnitPrice As Double
Dim intInvoiceDescriptionID As Integer
Me.Requery
Me.Refresh
If vbYes = MsgBox("Are you sure you wish to export the contents from the
Estimate into the Invoice. The procedure cannot be reversed?", vbYesNo +
vbInformation) Then
ExportAddressDestination "Invoice-Address", "CustomerNumber", "Invoice
Number", "Invoice Date", "Attention", ValidateAction(intClientID,
txtClientID), ValidateAction(intEstimateN, txtEstimate), ValidateAction(strDate,
txtEstimateDate), ValidateAction(strAtt, txtAtt__)
ExportOrderDestination "Invoice-Order", "Number", "Your order #",
"Sales Rep", "Job Number", "Terms", txtClientID,
ValidateAction(strYourOrderN, txtOrderN), ValidateAction(strSalesRep,
txtSalesRep), ValidateAction(strJobN, txtJobN), ValidateAction(strTerms,
txtTerms)
Exit Sub
End If
Call MsgBox("Operation have been cancelled.", vbInformation)
End Sub
When I click the button it is able to take the information from
ExportAddressDestination to the Invoice table
the next one is not working because I need to link Number from the invoice-order
table with invoice table, to get that number is where I am facing a problem
with. Since InvoiceID is autonumber and it is linked to number from
Invoice-Order and the autonumber could for example be 120, but I don't know if
it is 120, or 30, or whatever so I need the program to be able to get the number
InvoiceID from the table [invoice] and link it with the table [invoice-order]
"number".
Do you understand my situation now?
