I have a compile error that I cannot solve

are you manage to find out which line causing that error?

It does not tell me that.  But if I only include the first set command line and compile, I get the error so I suspect it is in the first set statement

so... is your scripts able to pass through all the messageboxes when you do a test run?

>>MsgBox (Me.TemplateID.Column(0))
>>MsgBox (tempnumber)
>>MsgBox QuoteID

Which version of Access are you using?

Access 2013

And All of the Msgboxes passed if I only run the code above the following line

------- THE CODE BEFORE THIS DOES NOT HAVE AN ERROR -----------

I tried running the code with only the first line after the above message and it did not pass any of the msgboxes because of the compile error.

Does this make sense?

since you claim that we can't determine which line causing the error, do you think can "hide" the scripts and then diagnose which line cause the error?

for example, try if you can compile this?

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

Select allOpen in new window

and then this...

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

Select allOpen in new window

and so on...

I can compile the first one (as I stated) and the second one now comes up with a Run Macro screen.

See the attachmentfor the screen.

pls make sure you attached the screenshot...

In your original code, you have these two lines.  

Set rstFrom = ("Select * From tblTemplateLabor Where TemplateID = " & tempnumber)
Set rstTo = ("Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID)

Why did you drop the OpenRecordset method from the front of these?  Those lines should read:

Set rstFrom =dbsProduction.OpenRecordset("Select * From tblTemplateLabor Where TemplateID = " & tempnumber)
Set rstTo =dbsProduction.OpenRecordset("Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID)

Here is the attachment
screenshot-macro.jpg

Set rstTo =dbsProduction.OpenRecordset("Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID)

Received an error which stated Runtime error 3061 - Too few parameters expected 2 and then when I go to debug it highlighted the line above.

I generally use a syntax that looks like:

strSQL = "Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID
debug.print strsql
Set rstTo =dbsProduction.OpenRecordset(strsql, , dbfailonerror) 

Select allOpen in new window

This does two things.  
1.  It lets me see the value of the string before executing it, which I can then copy from the immediate window and paste in a query to ensure it is a valid query.
2.  It will raise an error message (the dbfailonerror) if the query cannot be run properly.

Try specifying the recordset type, as follows:


Set rstFrom =dbsProduction.OpenRecordset("Select * From tblTemplateLabor Where TemplateID = " & tempnumber, dbOpenDynaset)

Set rstTo =dbsProduction.OpenRecordset("Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID, dbOpenDynaset)

Still has the too few parameters issue.

If I use the strSQL statement - are there other Dim or lines that I need to add to the code?

"If I use the strSQL statement - are there other Dim or lines that I need to add to the code? "

Yes, you should:

Dim strSQL as string

Did it print the SQL string to the immediate window?  If so, copy that string, open a new query, change to the SQL view and paste the text.  Then try to run the query.  If you are getting the too few parameters issue than it is likely that you will also get an error running the query, and the message you get there will provide more details.

The line below still has a too few parameters error

Set rstTo = dbsProduction.OpenRecordset(strSQL, , dbFailOnError)

what about

Set rstTo = dbsProduction.OpenRecordset(strSQL)

Select allOpen in new window

?

did you try opening that recordset in a stand-alone query, as I recommended above?  If so, did it work?

Go back to your Dim statements:

    Dim rstquotesetup As DAO.Recordset
    Dim rstFrom As Recordset
    Dim rstTo As Recordset

You need to declare those recordsets as DAO

    Dim rstFrom As DAO.Recordset
    Dim rstTo As DAO.Recordset

Otherwise, by default, they will be ADO.

The rstfrom and the rstto queries now work.  I added brackets around the designHours-user.

Now it completes the code, but I get the "Already Exists" message.

I am looking to take values that are in the rstfrom fields and set the rstto field equal to those fields.

It is not doing that...

Thoughts?

OK, go back and copy your current code and post it back here so we can see all of the changes.

Chances are, you are violating a unique index in rstTo table by adding a record which already exists.  You will probably need to do a search of that recordset using the FindFirst method and if it already exists, either skip it or update the record in rstTo; if it doesn't exist, then you would add a new record in rstTo.

Here is the current Code.  The commented out lines are the issue.

Private Sub Command343_Click()

    Dim dbsProduction As DAO.Database
    Dim rstquotesetup As DAO.Recordset
    Dim rstFrom As DAO.Recordset
    Dim rstTo As DAO.Recordset
    Dim fld As Field
    Dim tempnumber As Integer
    Dim QuoteID As Integer
    Dim strSQL As String
   
   


   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!MarkUp = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!MarkUp
   rstquotesetup!StructuralSteelDropPercent = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!DropPercentageStructural
   rstquotesetup!PlateSteelDropPercent = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!DropPercentagePlate
   rstquotesetup![DesignHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!DesignHours
   rstquotesetup!DesignRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!DesignRate
   rstquotesetup!DesignNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!DesignNotes
      rstquotesetup![CutSawHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!CutSawHours
   rstquotesetup!CutSawRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!CutSawRate
   rstquotesetup!CutSawNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!CutSawNotes
      rstquotesetup![BrakeHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!BrakeHours
   rstquotesetup!BrakeRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!BrakeRate
   rstquotesetup!BrakeNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!BrakeNotes
      rstquotesetup![PlasmaHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!PlasmaHours
   rstquotesetup!PlasmaRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!PlasmaRate
   rstquotesetup!PlasmaNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!PlasmaNotes
      rstquotesetup![DrillHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!DrillHours
   rstquotesetup!DrillRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!DrillRate
   rstquotesetup!DrillNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!DrillNotes
      rstquotesetup![WeldHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!WeldHours
   rstquotesetup!WeldRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!WeldRate
   rstquotesetup!WeldNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!WeldNotes
      rstquotesetup![CleanHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!CleanHours
   rstquotesetup!CleanRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!CleanRate
   rstquotesetup!CleanNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!CleanNotes
      rstquotesetup![PaintHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!PaintHours
   rstquotesetup!PaintRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!PaintRate
   rstquotesetup!PaintNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!PaintNotes
      rstquotesetup![AssemblyHours-User] = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!AssemblyHours
   rstquotesetup!AssemblyRate = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!AssemblyRate
   rstquotesetup!AssemblyNotes = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!AssemblyNotes
   rstquotesetup.Update
   
         
    QuoteID = DMax("QuoteID", "tblQuoteDetails")
    MsgBox QuoteID
   
    'Set rstFrom = dbsProduction.OpenRecordset("Select DesignHours, DesignRate, DesignNotes From tblTemplateLabor Where TemplateID = " & tempnumber, dbOpenDynaset)
   
    'If rstFrom.EOF Then
    '    MsgBox "No match in From Table"
    'Else
    'Set rstTo = dbsProduction.OpenRecordset("Select [DesignHours-User], DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID, dbOpenDynaset)
    'strSQL = "Select [DesignHours-User], DesignRate, DesignNotes From tblQuoteDetails 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
                                       

End Sub

Also, there may be multiple records in the rstfrom that need to move to the rstto.

How would I create that?

Just a couple of notes to start with.

1.  When you post code segments, they are easier to read if you encapsulate them as Code (highlight the text then click the "Code" option on the formatting bar at the top of the comment area.

2.  When referring to forms as you have done at the top of this code segment:

[Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form!WeldNotes

Select allOpen in new window

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

Select allOpen in new window

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

Select allOpen in new window

What if I want to count the number of records in the rstFrom dataset?

Below is the current code - I receive an Run Time Error. Too Few Parameters on the Set rstFrom line in the code below

Thoughts?

    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
                

Select allOpen in new window

Here is the updated Code:

Thoughts Please...

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

Select allOpen in new window

Attached are the results of the rstfrom.

I would like to be able to write all of these records to rstTo.

The number of records is variable each time this code is run.

Thoughts?
rstFrom-reaults.jpg

Are you still getting an error when you try to open the rstTo recordset?

What fields are in your [tblQuotePurchasedParts] table?  Are you sure that all of the fields listed in the code above actually exist in that table?

What is the purpose of rstTo?  are you using this in a report, another form, ....?  Generally, you would also add a PurchaseOrderID or some other field that looks like it would relate these fields to a PO or something.

The fields in the tblWuotePurcgasedParts table are correct

I have a set of template tables and when the user selects the template from the template listing, I need to copy the records from the template tables to the quote tables.

IN the code- I am looking up the QuoteID that was created from adding the main record to the quote table.

I need to assign the QuoteID to the records in the other quote tables.

The structure of the table is a main table called tblquoteDetails and then there is a table for each part of the quote since there can be multiple records for each
1. tblquotestructuralsteel
2. tblquoteplatesteel
3. tblquotelabor
4. etc

I will be adding template data to the main table first.  I then look up the quote id created and then use that in each of the subtables