Private Sub Command343_Click()
Dim dbsProduction As DAO.Database
Dim rstquotesetup As DAO.Recordset
Dim rstFrom As Recordset
Dim rstTo As Recordset
Dim fld As Field
Dim tempnumber As Integer
Dim QuoteID As Integer
Set dbsProduction = CurrentDb
Set rstquotesetup = dbsProduction.OpenRecordset("tblQuoteDetails")
MsgBox (Me.TemplateID.Column(0))
tempnumber = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!TemplateID
MsgBox (tempnumber)
QuoteID = DMax("QuoteID", "tblQuoteDetails")
MsgBox QuoteID
End Sub
Private Sub Command343_Click()
Dim dbsProduction As DAO.Database
Dim rstquotesetup As DAO.Recordset
Dim rstFrom As Recordset
Dim rstTo As Recordset
Dim fld As Field
Dim tempnumber As Integer
Dim QuoteID As Integer
Set dbsProduction = CurrentDb
Set rstquotesetup = dbsProduction.OpenRecordset("tblQuoteDetails")
MsgBox (Me.TemplateID.Column(0))
tempnumber = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!TemplateID
MsgBox (tempnumber)
rstquotesetup.AddNew
rstquotesetup!UnitDescription = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!templatenumber
rstquotesetup.Update
QuoteID = DMax("QuoteID", "tblQuoteDetails")
MsgBox QuoteID
End Sub
strSQL = "Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID
debug.print strsql
Set rstTo =dbsProduction.OpenRecordset(strsql, , dbfailonerror)
This does two things. Â Set rstTo = dbsProduction.OpenRecordset(strSQL)
?
[Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!WeldNotes
it is far easier to read (and type) if you declare a form object and instantiate it, Dim frm as form
set frm = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form
rstquotesetup!WeldNotes = frm!WeldNotes
3. Â The fields in your rstFROM and rstTo are not the same. Â If you are going to try to loop through the fields and add the values based on the field names, the field name must match (exactly). Â Furthermore, if you are going to use this technique with only three fields, it is simpler and easier to assign the values explicitly rather than in the loop.
strSQL = "Select DesignHours, DesignRate, DesignNotes From tblTemplateLabor Where TemplateID = " & tempnumber
Set rstFrom = dbsProduction.OpenRecordset(strsql, dbOpenDynaset)
If rstFrom.EOF Then
MsgBox "No match in From Table"
Else
strSQL = "Select [DesignHours-User], DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID
Set rstTo = dbsProduction.OpenRecordset(strSQL, , dbFailOnError)
WHILE not rstFrom.EOF
'not sure whether this is the unique field in this table, but just a guess.
'have provided two lines below depending on whether [DesignHours-User] is # or text
'rstTo.FindFirst "[DesignHours-User] = " & rstFrom![DesignHours-User]
rstTo.FindFirst "[DesignHours-User] = " & chr$(34) & rstFrom![DesignHours-User] & chr$(34)
if rstTo.NoMatch = false then
msgbox "Already exists"
else
rstTo.AddNew
For Each fld In rstTo.Fields
rstTo(fld.Name) = rstFrom(fld.Name)
Next
rstTo.Update
End If
rstFrom.movenext
WEND
End Sub
QuoteID = DMax("QuoteID", "tblQuoteDetails")
MsgBox QuoteID
strSQL = "Select Quantity, Description, Vendor, Leadtime, CostPerUnit, PurchasedPartsNotes from tblTemplatePurchasedParts where TemplateID = " & tempnumber
MsgBox (strSQL)
Set rstFrom = dbsProduction.OpenRecordset(strSQL)
rstFrom.MoveLast
n = rstFrom.RecordCount
MsgBox (n)
If rstFrom.EOF Then
MsgBox "No match in From Table"
Else
strSQL = "Select Quantity, Description, Vendor, Leadtime, CostPerUnit, PurchasedPartsNotes From tblQuotePurchasedParts Where QuoteID = " & QuoteID
MsgBox (strSQL)
Debug.Print strSQL
Set rstTo = dbsProduction.OpenRecordset(strSQL, , dbFailOnError)
If rstTo.EOF Then
rstTo.AddNew
For Each fld In rstTo.Fields
MsgBox (fld)
rstTo(fld.Name) = rstFrom(fld.Name)
Next
rstTo.Update
Else
MsgBox "Already exists"
End If
End If
strSQL = "Select Quantity, Description, Vendor, Leadtime, CostPerUnit, PurchasedPartsNotes from tblTemplatePurchasedParts where TemplateID = " & tempnumber
MsgBox (strSQL)
Set rstFrom = dbsProduction.OpenRecordset("strSQL", OpenDynaset)
If rstFrom.EOF Then
MsgBox "No match in From Table"
Else
'strSQL = "Select Quantity, Description, Vendor, Leadtime, CostPerUnit, PurchasedPartsNotes From tblQuotePurchasedParts Where QuoteID = " & QuoteID
'MsgBox (strSQL)
'Debug.Print strSQL
'Set rstTo = dbsProduction.OpenRecordset(strSQL, , dbFailOnError)
While Not rstFrom.EOF
If rstTo.EOF Then
rstTo.AddNew
rstTo(Quantity) = rstFrom(Quantity)
rstTo(Description) = rstFrom(Description)
rstTo(Vendor) = rstFrom(Vendor)
rstTo(LeadTime) = rstFrom(LeadTime)
rstTo(CostPerUnit) = rstFrom(CostPerUnit)
rstTo(PurchasedPartsNotes) = rstFrom(PurchasedPartsNotes)
rstTo.Update
Else
MsgBox "Already exists"
End If
rstFrom.MoveNext
Wend
End If