Hi Everyone
It's been a while since I've been on here, or have been doing any coding for that matter, so I apologize up front if I forget to post something correctly. I have "inherited" a database and despite all my research I am not sure how to proceed, what I am doing wrong/missing logically, or if what I am trying to do can even be done...
I have a form that is set to copy data from and into multiple tables and uses a lot of INSERT INTO coding.
The frmCopyJob has the following fields:
cmb_cust Unbound
cmb_Company Unbound
OldQuote_Nbr Bound[QuoteNbr]
CopyBtn Button (OnClick)
The first part of the code for the copy button, looks like this:
This code is clear to me except for the logic behind where the " marks are placed in both the SELECT and WHERE lines. I understand the basic concept and syntax for simple inserts, but this is beyond my level of knowledge. And this leads to my question. I have 2 other fields that are part of the table tbQuote that I would like to have copied or updated at the time the record is copied as well (as neither default value is set on the INSERTED record currently).
QStatID (integer) Default value = 1
DateCreated (Date/Time Extended) Default value =Date()
Is there a way to insert or update these fields - either using an unbound field on the form, or in the code directly? I did try a lot of different lines using examples found in my searches, but almost all returned expression errors so it became very obvious to me that I didn't understand the logical order of the code and where the " go, and why. If you have the patience to try and help me understand, as always your guidance would be greatly appreciated.
I have included the full source code for the onclick below as a reference, but the section above directly affects the tbQuote portion I am trying to update.
It's been a while since I've been on here, or have been doing any coding for that matter, so I apologize up front if I forget to post something correctly. I have "inherited" a database and despite all my research I am not sure how to proceed, what I am doing wrong/missing logically, or if what I am trying to do can even be done...
I have a form that is set to copy data from and into multiple tables and uses a lot of INSERT INTO coding.
The frmCopyJob has the following fields:
cmb_cust Unbound
cmb_Company Unbound
OldQuote_Nbr Bound[QuoteNbr]
CopyBtn Button (OnClick)
The first part of the code for the copy button, looks like this:
Code:
mysql = " INSERT INTO tbQuote ( CustID, Description, Company)" & _
" SELECT " & Me.cmb_cust & ",Description," & Me.cmb_Company & " FROM tbQuote" & _
" WHERE tbQuote.QuoteNbr=" & Me.OldQuote_Nbr
DoCmd.RunSQL mysql
QStatID (integer) Default value = 1
DateCreated (Date/Time Extended) Default value =Date()
Is there a way to insert or update these fields - either using an unbound field on the form, or in the code directly? I did try a lot of different lines using examples found in my searches, but almost all returned expression errors so it became very obvious to me that I didn't understand the logical order of the code and where the " go, and why. If you have the patience to try and help me understand, as always your guidance would be greatly appreciated.
I have included the full source code for the onclick below as a reference, but the section above directly affects the tbQuote portion I am trying to update.
Code:
Private Sub Command7_Click()
If IsNull(Me.cmb_cust) = False And IsNull(Me.cmb_Company) = False Then
' insert quote into tblQuote
mysql = " INSERT INTO tbQuote ( CustID, Description, Company)" & _
" SELECT " & Me.cmb_cust & ",Description," & Me.cmb_Company & " FROM tbQuote" & _
" WHERE tbQuote.QuoteNbr=" & Me.OldQuote_Nbr
DoCmd.RunSQL mysql
Dim NewQuote_Nbr As Integer
NewQuote_Nbr = DMax("QuoteNbr", "tbQuote")
'insert quotedetails
mysql2 = "INSERT INTO tbQuoteItems ( QuoteNbr, ItemNbr, Description, Qty, GPM )" & _
"SELECT " & NewQuote_Nbr & ",ItemNbr, Description, Qty, GPM FROM tbQuoteItems" & _
" WHERE tbQuoteItems.QuoteNbr=" & Me.OldQuote_Nbr
DoCmd.RunSQL mysql2
'insert material details
mysql2 = "INSERT INTO tbMaterialDetail ( QuoteNbr, ItemNbr, Description, Material, Type, [Size], FtRequired, LbPerFt, PricePerFt, [Mark-up] )" & _
"SELECT " & NewQuote_Nbr & ",ItemNbr,Description,Material,Type,Size,FtRequired,LbPerFt,PricePerFt,[Mark-up] FROM tbMaterialDetail" & _
" WHERE tbMaterialDetail.QuoteNbr=" & Me.OldQuote_Nbr
DoCmd.RunSQL mysql2
'insert labour details
mysql = "INSERT INTO tbLabourDetail ( QuoteNbr, ItemNbr, ProcessType, RunTime, [Set-UpCharge], HourlyRate )" & _
"SELECT " & NewQuote_Nbr & ",ItemNbr, ProcessType, RunTime, [Set-UpCharge], HourlyRate FROM tbLabourDetail" & _
" WHERE tbLabourDetail.QuoteNbr=" & Me.OldQuote_Nbr
DoCmd.RunSQL mysql
'insert outsource
mysql2 = "INSERT INTO tbOutSourceDetail ( QuoteNbr, ItemNbr, Description, Cost, QTY, [Mark-up], Notes )" & _
"SELECT " & NewQuote_Nbr & ",ItemNbr,Description,Cost,QTY,[Mark-up],Notes FROM tbOutSourceDetail" & _
" WHERE tbOutSourceDetail.QuoteNbr=" & Me.OldQuote_Nbr
DoCmd.RunSQL mysql2
msg = MsgBox("Quote has been Copied, please view quote #" & NewQuote_Nbr & " under the new customers account.", vbInformation)
Forms![frmMain].lstQuote.Requery
DoCmd.Close acForm, "frmQuoteMain"
DoCmd.Close acForm, "frm_quote_copy"
Else
msg = MsgBox("You must select who to copy the quote to and/or which company.", vbCritical)
Me.cmb_cust.SetFocus
End If
End Sub
Comment