Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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.OpenRecordse
MsgBox (Me.TemplateID.Column(0))
tempnumber = [Forms]![frmQuote/JobEntry
MsgBox (tempnumber)
rstquotesetup.AddNew
rstquotesetup!UnitDescript
rstquotesetup!MarkUp = [Forms]![frmQuote/JobEntry
rstquotesetup!StructuralSt
rstquotesetup!PlateSteelDr
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
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.OpenRecordse
MsgBox (Me.TemplateID.Column(0))
tempnumber = [Forms]![frmQuote/JobEntry
MsgBox (tempnumber)
rstquotesetup.AddNew
rstquotesetup!UnitDescript
rstquotesetup!MarkUp = [Forms]
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
>>MsgBox (Me.TemplateID.Column(0))
>>MsgBox (tempnumber)
>>MsgBox QuoteID
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?
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?
and then this...
and so on...
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
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
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.
See the attachmentfor the screen.
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.OpenRecords
Set rstTo =dbsProduction.OpenRecords
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.OpenRecords
Set rstTo =dbsProduction.OpenRecords
Set rstTo =dbsProduction.OpenRecords
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.
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:
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.
strSQL = "Select DesignHours-User, DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID
debug.print strsql
Set rstTo =dbsProduction.OpenRecordset(strsql, , dbfailonerror)
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.OpenRecords
Set rstTo =dbsProduction.OpenRecords
Set rstFrom =dbsProduction.OpenRecords
Set rstTo =dbsProduction.OpenRecords
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?
"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.
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.OpenRecordse
Set rstTo = dbsProduction.OpenRecordse
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.
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?
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.
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.OpenRecordse
MsgBox (Me.TemplateID.Column(0))
tempnumber = [Forms]![frmQuote/JobEntry
MsgBox (tempnumber)
rstquotesetup.AddNew
rstquotesetup!UnitDescript
rstquotesetup!MarkUp = [Forms]![frmQuote/JobEntry
rstquotesetup!StructuralSt
rstquotesetup!PlateSteelDr
rstquotesetup![DesignHours
rstquotesetup!DesignRate = [Forms]![frmQuote/JobEntry
rstquotesetup!DesignNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![CutSawHours
rstquotesetup!CutSawRate = [Forms]![frmQuote/JobEntry
rstquotesetup!CutSawNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![BrakeHours-
rstquotesetup!BrakeRate = [Forms]![frmQuote/JobEntry
rstquotesetup!BrakeNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![PlasmaHours
rstquotesetup!PlasmaRate = [Forms]![frmQuote/JobEntry
rstquotesetup!PlasmaNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![DrillHours-
rstquotesetup!DrillRate = [Forms]![frmQuote/JobEntry
rstquotesetup!DrillNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![WeldHours-U
rstquotesetup!WeldRate = [Forms]![frmQuote/JobEntry
rstquotesetup!WeldNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![CleanHours-
rstquotesetup!CleanRate = [Forms]![frmQuote/JobEntry
rstquotesetup!CleanNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![PaintHours-
rstquotesetup!PaintRate = [Forms]![frmQuote/JobEntry
rstquotesetup!PaintNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![AssemblyHou
rstquotesetup!AssemblyRate
rstquotesetup!AssemblyNote
rstquotesetup.Update
QuoteID = DMax("QuoteID", "tblQuoteDetails")
MsgBox QuoteID
'Set rstFrom = dbsProduction.OpenRecordse
'If rstFrom.EOF Then
' MsgBox "No match in From Table"
'Else
'Set rstTo = dbsProduction.OpenRecordse
'strSQL = "Select [DesignHours-User], DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID
'MsgBox (strSQL)
'Debug.Print strSQL
'Set rstTo = dbsProduction.OpenRecordse
'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
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.OpenRecordse
MsgBox (Me.TemplateID.Column(0))
tempnumber = [Forms]![frmQuote/JobEntry
MsgBox (tempnumber)
rstquotesetup.AddNew
rstquotesetup!UnitDescript
rstquotesetup!MarkUp = [Forms]![frmQuote/JobEntry
rstquotesetup!StructuralSt
rstquotesetup!PlateSteelDr
rstquotesetup![DesignHours
rstquotesetup!DesignRate = [Forms]![frmQuote/JobEntry
rstquotesetup!DesignNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![CutSawHours
rstquotesetup!CutSawRate = [Forms]![frmQuote/JobEntry
rstquotesetup!CutSawNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![BrakeHours-
rstquotesetup!BrakeRate = [Forms]![frmQuote/JobEntry
rstquotesetup!BrakeNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![PlasmaHours
rstquotesetup!PlasmaRate = [Forms]![frmQuote/JobEntry
rstquotesetup!PlasmaNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![DrillHours-
rstquotesetup!DrillRate = [Forms]![frmQuote/JobEntry
rstquotesetup!DrillNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![WeldHours-U
rstquotesetup!WeldRate = [Forms]![frmQuote/JobEntry
rstquotesetup!WeldNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![CleanHours-
rstquotesetup!CleanRate = [Forms]![frmQuote/JobEntry
rstquotesetup!CleanNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![PaintHours-
rstquotesetup!PaintRate = [Forms]![frmQuote/JobEntry
rstquotesetup!PaintNotes = [Forms]![frmQuote/JobEntry
rstquotesetup![AssemblyHou
rstquotesetup!AssemblyRate
rstquotesetup!AssemblyNote
rstquotesetup.Update
QuoteID = DMax("QuoteID", "tblQuoteDetails")
MsgBox QuoteID
'Set rstFrom = dbsProduction.OpenRecordse
'If rstFrom.EOF Then
' MsgBox "No match in From Table"
'Else
'Set rstTo = dbsProduction.OpenRecordse
'strSQL = "Select [DesignHours-User], DesignRate, DesignNotes From tblQuoteDetails Where QuoteID = " & QuoteID
'MsgBox (strSQL)
'Debug.Print strSQL
'Set rstTo = dbsProduction.OpenRecordse
'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?
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:
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
Dim frm as form
set frm = [Forms]![frmQuote/JobEntry]!sfrmTemplateLaborData.Form
rstquotesetup!WeldNotes = frm!WeldNotes
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
Below is the current code - I receive an Run Time Error. Too Few Parameters on the Set rstFrom line in the code below
Thoughts?
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
Here is the updated Code:
Thoughts Please...
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
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
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.
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.
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.
where are you setting the value of QuoteID?
Create your query like this and then save it as qryTo (or something like that).
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:
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]
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)
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
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial