Avatar of btgtech
btgtech
Flag for United States of America asked on

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
Microsoft Access

Avatar of undefined
Last Comment
btgtech

8/22/2022 - Mon
Ryan Chong

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

ASKER
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
Ryan Chong

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Norie

Which version of Access are you using?
btgtech

ASKER
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?
Ryan Chong

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...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
btgtech

ASKER
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.
Ryan Chong

pls make sure you attached the screenshot...
Dale Fye

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)
Your help has saved me hundreds of hours of internet surfing.
fblack61
btgtech

ASKER
Here is the attachment
screenshot-macro.jpg
btgtech

ASKER
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.
Dale Fye

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Robert Sherman

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)
btgtech

ASKER
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?
Dale Fye

"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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
btgtech

ASKER
The line below still has a too few parameters error

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

what about
Set rstTo = dbsProduction.OpenRecordset(strSQL)

Open in new window

?
Dale Fye

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
btgtech

ASKER
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?
Dale Fye

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.
btgtech

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
btgtech

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

How would I create that?
Dale Fye

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

btgtech

ASKER
What if I want to count the number of records in the rstFrom dataset?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
btgtech

ASKER
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

btgtech

ASKER
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

btgtech

ASKER
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Dale Fye

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.
btgtech

ASKER
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.
ASKER CERTIFIED SOLUTION
Dale Fye

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
btgtech

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.