Adding all records from a subform to table

[Garrett!]

Hi. I am working with a database that deals with trailers. What is happening is a salesman takes an order for a make and model for a trailer for a customer. The customer can then add some customization to the trailer such as more tail lights or tie down straps. They add the customization on a form called frmCustomQuote. On frmCustomQuote there is a subform called sfQuoteConfigs. On sfQuoteConfigs there is fields such as category and sub catergory that get populated bases on what was selected. On frmCustomQuote there is a button called "Copy Quote" this will allow the salesman to make an exact copy of the trailer and customization. The quotes are held in a table called tblQuotes and the customization is held in tbQuoteConfigs. Now my problem is when I click on Copy Quote it only copies the first record into tbQuoteConfigs. I can't figure out a way to tell my code to move to the next record within sfQuoteConfigs.

My code:

Option Compare Database
Option Explicit

'Setup ADODB connection to the tblQuotes
Dim adoQuotesCustomQuote As New ADODB.Recordset

'setup ADODB connection to the tbQuoteConfigs
Dim adoQuoteConfigsCustom As New ADODB.Recordset

'Dim the Variables
Dim strSQLCustom As String
Dim strSQLConfigs As String
Dim iQuoteNum As Integer


Private Sub Form_Load()

'SQL statement for the ADODB connection adoQuotesCustomQuote
strSQLCustom = "SELECT * FROM [tblQuotes]"
'Configure the ADODB connection adoQuotesCustomQuote
adoQuotesCustomQuote.Open strSQLCustom, Form_frm_SysControlForm.connDB, adOpenDynamic, adLockOptimistic

'SQL statement for the ADODB connection adoQuoteConfigsCustom
strSQLConfigs = "SELECT * FROM [tbQuoteConfigs]"
'Configure the ADODB connection adoQuoteConfigsCustom
adoQuoteConfigsCustom.Open strSQLConfigs, Form_frm_SysControlForm.connDB, adOpenDynamic, adLockOptimistic


End Sub



Private Sub cmdCopy_Click()

' THis function should create a new quote using the existing quote,
' Then using the new quote number, copy all the cusumiztion lines from the source quote
' reload the form using the new quote# and information.


With adoQuotesCustomQuote
.AddNew
.Fields("PlantCode").Value = Me.PlantCode
.Fields("DateCreated").Value = Now()
.Fields("BaseCost").Value = Me.BaseCost
.Fields("ModelNum").Value = Me.ModelNum
.Fields("ModelName").Value = Me.txtModelName
.Fields("CreatedBy").Value = Form_frm_SysControlForm.txtUserID
.Fields("DealerID").Value = Me.DealerID
.Fields("Discount").Value = Me.Discount
.Fields("ExportFee").Value = Me.ExportFee
.Fields("Notes").Value = Me.Notes
.Fields("CompanyCode") = Me.CompanyCode
.Update
iQuoteNum = .Fields("QuoteNum")
End With


'This is code to add other features. For example, if a trailer has three items in sfQuoteConfigs this adds all three

With adoQuoteConfigsCustom
.AddNew
.Fields("QuoteNum").Value = iQuoteNum
.Fields("ModelNum").Value = Form_sfQuoteConfigs.ModelNum
.Fields("Category").Value = Form_sfQuoteConfigs.Category
.Fields("SubCategory").Value = Form_sfQuoteConfigs.SubCategory
.Fields("Description").Value = Form_sfQuoteConfigs.Description
.Fields("Cost").Value = Form_sfQuoteConfigs.Cost
.MoveNext
.Update

End With
Form_frmLookupQuote.Requery
Form_frmCustomQuote.Requery
DoCmd.Close acForm, "frmCustomQuote"


End Sub


Private Sub Form_Close()

'Close the ADODB connection

adoQuotesCustomQuote.Close
Set adoQuotesCustomQuote = Nothing

adoQuoteConfigsCustom.Close
Set adoQuoteConfigsCustom = Nothing

End Sub

Sorry if this was too long or not clear. I can't figure out a way to do this. I've tried loops but that just adds infinite records. I don't know how much customization the salesman will add since there could be any amount. Thanks!

 

[pbaldy]

Sounds like this would be appropriate:

http://allenbrowne.com/ser-57.html

 

[Garrett!]

Thanks for the link. It didn't work for me though. It just inserts a new record with all fields blank except for the primary key. I guess I will keep looking.

 

[RainLover]

Try UPDATE before MOVENEXT.

Untested.

 

[Garrett!]

Well I figured it out. I took out the second block of code with my adoQuoteConfigsCustom. I added this SQL instead:

' Build sql to copy config to the new quote
sSQL = "INSERT INTO tbQuoteConfigs ( QuoteNum, ModelNum, Category, SubCategory, Description, Cost ) "
sSQL = sSQL & "SELECT " & CStr(iQuoteNum) & ",ModelNum, Category, SubCategory, Description, Cost "
sSQL = sSQL & " FROM tbQuoteConfigs where QuoteNum = " & Me.QuoteNum & ";"

Form_frm_SysControlForm.connDB.Execute CommandText:=sSQL

Thanks for the help.