I have a compile error that I cannot solve

Below is some code that I am using on an access form for an event.  When I try to use this there is a compile error which states that there is a type mismatch.

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!MarkUp = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!MarkUp
   rstquotesetup!StructuralSteelDropPercent = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!DropPercentageStructural
   rstquotesetup!PlateSteelDropPercent = [Forms]![frmQuote/JobEntry]!sfrmTemplateListing.Form!DropPercentagePlate
   rstquotesetup.Update
   
         
    QuoteID = DMax("QuoteID", "tblQuoteDetails")
    MsgBox QuoteID

------- THE CODE BEFORE THIS DOES NOT HAVE AN ERROR -----------
   
    Set rstFrom = ("Select * From tblTemplateLabor Where TemplateID = " & tempnumber)
   
    If rstFrom.EOF Then
        MsgBox "No match in From Table"
    Else
    Set rstTo = ("Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID)
    If rstTo.EOF Then
        rstTo.AddNew
        For Each fld In rstTo.Fields
            rstTo(fld.Name) = rstFrom(fld.Name)
        Next
        rstTo.Update
    Else
        MsgBox "Already exists"
    End If
   
    End If



End Sub
btgtechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
are you manage to find out which line causing that error?
0
btgtechAuthor Commented:
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
0
Ryan ChongCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NorieVBA ExpertCommented:
Which version of Access are you using?
0
btgtechAuthor Commented:
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?
0
Ryan ChongCommented:
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

Open 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

Open in new window


and so on...
0
btgtechAuthor Commented:
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.
0
Ryan ChongCommented:
pls make sure you attached the screenshot...
0
Dale FyeCommented:
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)
1
btgtechAuthor Commented:
Here is the attachment
screenshot-macro.jpg
0
btgtechAuthor Commented:
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.
0
Dale FyeCommented:
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) 

Open 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.
0
Robert ShermanOwnerCommented:
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)
0
btgtechAuthor Commented:
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?
0
Dale FyeCommented:
"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.
0
btgtechAuthor Commented:
The line below still has a too few parameters error

Set rstTo = dbsProduction.OpenRecordset(strSQL, , dbFailOnError)
0
Ryan ChongCommented:
what about
Set rstTo = dbsProduction.OpenRecordset(strSQL)

Open in new window

?
0
Dale FyeCommented:
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.
0
btgtechAuthor Commented:
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?
0
Dale FyeCommented:
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.
0
btgtechAuthor Commented:
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
0
btgtechAuthor Commented:
Also, there may be multiple records in the rstfrom that need to move to the rstto.

How would I create that?
0
Dale FyeCommented:
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

Open 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

Open 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

Open in new window

1
btgtechAuthor Commented:
What if I want to count the number of records in the rstFrom dataset?
0
btgtechAuthor Commented:
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
                

Open in new window

0
btgtechAuthor Commented:
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

Open in new window

0
btgtechAuthor Commented:
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
0
Dale FyeCommented:
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.
0
btgtechAuthor Commented:
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.
0
Dale FyeCommented:
where are you setting the value of QuoteID?

Create your query like this and then save it as qryTo (or something like that).
Select Quantity, Description, Vendor, Leadtime, CostPerUnit, PurchasedPartsNotes 
From tblQuotePurchasedParts Where QuoteID = [Tempvars]![QuoteID]

Open in new window

To test that, go to the immediate window and type the following line (replacing the 3 with a value for QuoteID which you know is in tblQuotePurcaseParts), then hit return.  This creates a TempVar which is a collection of variant datatype values that you can use anywhere in your code.  I like them because I can set their value in the immediate window and then test queries that need those values.  The other advantage is that, unlike global variables, they will not lose their values when your code encounters an unhandled error.

Tempvars.Add "QuoteID", 3

Then run your query.  It should show you the data in your tblQuotePurchasedParts table which relates to that quote, or not if no data has been pushed to that table yet.

Since you now have a saved query, you no longer need set the value of strSQL, and can change your code like:
Tempvars!QuoteID = QuoteID
Set rstTo = dbsProduction.OpenRecordset("qryTo", , dbFailOnError)

Open in new window

That should open your recordset.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
btgtechAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.