I am trying to open a recordset object using a reasonably complex query. The query uses other queries to get data and some of these queries are totaliser queries (I dont know the proper terminology but they are the ones that group and sum up data in the query).
Dim D As Database, R As Recordset, R2 As DAO.Recordset Set D = DBEngine.Workspaces(0).Databases(0) Set R2 = D.OpenRecordset("Qry Daily Losses", dbOpenSnapshot)
The error Message I get is:
Run-time error "3061" Too few parameters. Expected 4.
Most of the information I have seen on this error message talks about 1 parameter expected not 4.
I can open the query directly by clicking on it in the objects list and it looks fine. It is only when I try to open it in my code that it goes wrong.
I have a error I need help with. Here is my code for my Global, and My Form Current: See the red for the Error Line, that show up in the VBE and the Blue is the code related to that line. I am using access 2003
'Declare all variables for right-side record counter Dim bdg As DAO.Recordset Dim swr As DAO.Recordset Dim wtr As DAO.Recordset Dim dmo As DAO.Recordset ''Dim dvt As DAO.Recordset Dim occ As DAO.Recordset Dim fre As DAO.Recordset Dim swrlat As DAO.Recordset Dim wrtlat As DAO.Recordset
Dim bdgCount As Integer Dim swrcount As Integer Dim wtrcount As Integer Dim dmocount As Integer Dim dvtcount As Integer Dim occcount As Integer Dim frecount As Integer Dim countswr As Integer 'laterial counter Dim countwtr As Integer 'laterial counter
Dim sqlbdg As String Dim sqlswr As String Dim sqlwtr As String Dim sqldmo As String ''Dim sqldvt As String Dim sqlocc As String Dim sqlfre As String Dim sqlswrlat As String Dim sqlwtrlat As String
Dim db As DAO.Database
Private Sub Form_Current() Set db = CurrentDb() 'Use SQL strings to pull data from the tables sqlbdg = "SELECT [Building].[PIN] FROM Building WHERE [Building].[PIN]='" & Me![ADDRESS3] & "' ;" sqlswr = "SELECT [Sewerform].[PIN] FROM [SEWER SERVICE LATERALS] WHERE [Sewerform].[PIN]='" & Me![ADDRESS3] & "' ;" sqlwtr = "SELECT [water].[PIN] FROM [WATER SERVICE LATERALS] WHERE [water].[PIN]='" & Me![ADDRESS3] & "' ;" sqlswrlat = "SELECT [SewerMain].[PIN] FROM [SEWER MAIN PRBLEMS] WHERE [SewerMain].[PIN]='" & Me![ADDRESS3] & "' ;" sqlwtrlat = "SELECT [WaterMain].[PIN] FROM [WATER MAIN PROBLEMS] WHERE [WaterMain].[PIN]='" & Me![ADDRESS3] & "' ;" sqldmo = "SELECT [Demolition Permits].[PID] FROM [Demolition Permits] WHERE [Demolition Permits].[PID]='" & Me![ADDRESS3] & "' ;" ''There is no PIN field in the development table ==> sqlwtr = "SELECT [Development Permits].[PIN] FROM [Development Permits] WHERE [Development Permits].[PIN]='" & Me![ADDRESS3] & "' ;" sqlocc = "SELECT [Occupancy].[PIN] FROM Occupancy WHERE [Occupancy].[PIN]='" & Me![ADDRESS3] & "' ;" sqlfre = "SELECT [Freeze].[PIN] FROM Freeze WHERE [FREEZE].[PIN]='" & Me![ADDRESS3] & "' ;"
Set bdg = db.OpenRecordset(sqlbdg, dbOpenSnapshot) Set swr = db.OpenRecordset(sqlswr, dbOpenSnapshot) Set wtr = db.OpenRecordset(sqlwtr, dbOpenSnapshot) Set dmo = db.OpenRecordset(sqldmo, dbOpenSnapshot) ''Set dvt = db.OpenRecordset(sqldvt, dbOpenSnapshot) Set occ = db.OpenRecordset(sqlocc, dbOpenSnapshot) Set fre = db.OpenRecordset(sqlfre, dbOpenSnapshot) Set swrlat = db.OpenRecordset(sqlswrlat, dbOpenSnapshot) Set wrtlat = db.OpenRecordset(sqlwtrlat, dbOpenSnapshot)
'Building recordset On Error Resume Next If bdg.EOF And bdg.BOF = True Then bdgCount = 0 Else
With bdg .MoveFirst .MoveLast bdgCount = .RecordCount End With
End If
'Sewer recordset On Error Resume Next If swr.EOF And swr.BOF = True Then swrcount = 0 Else
With swr .MoveFirst .MoveLast swrcount = .RecordCount End With
End If
'Water recordset On Error Resume Next If wtr.EOF And wtr.BOF = True Then wtrcount = 0 Else
With wtr .MoveFirst .MoveLast wtrcount = .RecordCount End With
End If 'Sewer laterial recordset On Error Resume Next If swrlat.EOF And swrlat.BOF = True Then countswr = 0 Else
With swrlat .MoveFirst .MoveLast countswr = .RecordCount End With
End If
'Water laterial recordset On Error Resume Next If wrtlat.EOF And wrtlat.BOF = True Then countwtr = 0 Else
With wrtlat .MoveFirst .MoveLast countwtr = .RecordCount End With
End If
'Demolition recordset On Error Resume Next If dmo.EOF And dmo.BOF = True Then dmocount = 0 Else
With dmo .MoveFirst .MoveLast dmocount = .RecordCount End With
End If
'Development recordset ''On Error Resume Next ''If dvt.EOF And dvt.BOF = True Then dvtcount = 0 ''Else
Hello Access friends, What is wrong with the following code (modified the module from http://members.iinet.net.au/~allenbrowne/AppInventory.html ): I Keep getting a runtime error 3061 Too few parameters . Expected 1 on the line highlight below. I have the reference MS DAO 3.6 selected and I am using access 2000 and calling the module from a command button in a form. Thanks in advance for taking the time to help me out.
Dim db As DAO.Database 'CurrentDb() Dim rs As DAO.Recordset 'Various recordsets. Dim strProduct As String 'vProductID as a string. Dim strAsOf As String 'vAsOfDate as a string. Dim strSTDateLast As String 'Last Stock Take Date as a string. Dim strDateClause As String 'Date clause to use in SQL statement. Dim strSQL As String 'SQL statement. Dim lngQtyLast As Long 'Quantity at last transaction. Dim lngQtyAcq As Long 'Quantity acquired since incoming transaction. Dim lngQtyUsed As Long 'Quantity used since outgoing transaction.
If Not IsNull(vProductID) Then 'Initialize: Validate and convert parameters. Set db = CurrentDb() strProduct = vProductID If IsDate(vAsOfDate) Then strAsOf = "#" & Format$(vAsOfDate, "mm/dd/yyyy") & "#" End If
'Get the last transaction date and quantity for this product. If Len(strAsOf) > 0 Then strDateClause = " AND ([TransacDate] <= " & strAsOf & ")" End If strSQL = "SELECT TOP 1 [TransacDate], [Quantity] FROM [Transactions] " & _ "WHERE ((ProductID = " & strProduct & ")" & strDateClause & _ ") ORDER BY TransacDate DESC;"
Set rs = db.OpenRecordset(strSQL) With rs If .RecordCount > 0 Then strSTDateLast = "#" & Format$(![TransacDate], "mm/dd/yyyy") & "#" lngQtyLast = Nz(!Quantity, 0) End If End With rs.Close
'Build the Date clause If Len(strSTDateLast) > 0 Then If Len(strAsOf) > 0 Then strDateClause = " Between " & strSTDateLast & " And " & strAsOf Else strDateClause = " >= " & strSTDateLast End If Else If Len(strAsOf) > 0 Then strDateClause = " <= " & strAsOf Else strDateClause = vbNullString End If End If
'Get the quantity acquired since then. strSQL = "SELECT Sum([Transactions].[Quantity]) AS QuantityAcq " & _ "FROM [Transactions]" & _ "WHERE (([Transactions].[ProductID] = " & strProduct & ") AND ([Transactions].[TransacType] = 'Incoming')" If Len(strDateClause) = 0 Then strSQL = strSQL & ");" Else strSQL = strSQL & " AND ([Transactions].[TransacDate] " & strDateClause & "));" End If
Set rs = db.OpenRecordset(strSQL) If rs.RecordCount > 0 Then lngQtyAcq = Nz(rs!QuantityAcq, 0) End If rs.Close
'Get the quantity used since then. strSQL = "SELECT Sum([Transactions].[Quantity]) AS QuantityUsed " & _ "FROM [Transactions]" & _ "WHERE (([Transactions].[ProductID] = " & strProduct & ") AND ([Transactions].[TransacType] = 'Outgoing')" If Len(strDateClause) = 0 Then strSQL = strSQL & ");" Else strSQL = strSQL & " AND ([Transactions].[TransacDate] " & strDateClause & "));" End If
Set rs = db.OpenRecordset(strSQL) If rs.RecordCount > 0 Then lngQtyUsed = Nz(rs!QuantityUsed, 0) End If rs.Close
'Assign the return value OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed End If
Set rs = Nothing Set db = Nothing Exit Function End Function
Hello. I am trying to sort this out and hope someone can help.
Using Access 2002 front end, sqlserver 2000 back end.
In my front end database I have a query called "queEEGBilling" that is used to populate a report. It is quite a complex query which I did using the query designer, NOT with a WHERE sql command. I am not very good with SQL syntax.
The report works fine, and displays the records I want it to display.
However, after the report is displayed, I want to modify one field in each record to indicate that the report for hat record has been printed out (it is a billing package, and I want to indicate that the bill has been sent).
In order to do this, I need to access the recordset that was used to create the report. As I understand it, I cannot do this with a report (no equivalent of the recordsetclone property or anything similar). What I have to do is create the recordset that was used to create the report in the first place. No problem right?
set mydb=dbengine(0)(0) set rs1=mydb.openrecordset("queEEGBilling")
However, here I get the infamous error 3061. Too Few Parameters. Expected 1.
I have googled this up and down, and it seems to be related to my query being too complex. However, I did not write this query by hand, it came from Access2002. And this query worked fine to generate my report. So, why does it crash now??
I am running this code, and i am getting this error:
Code:Private Sub SendFormToConsultants_Click() On Error GoTo Err_SendFormToConsultants_Click Dim stWhere As String '-- Criteria for DLookup Dim varTo As Variant '-- Address for SendObject Dim stText As String '-- E-mail text Dim stSubject As String '-- Subject line of e-mail Dim stCOFNumber As String '-- The COF Number from form Dim stCustomerID As String '-- The Customer ID from form Dim stCompanyName As String '-- The Company Name from form Dim stContactName As String '-- The Contact Name from form Dim stAddress As String '-- The Company Address from form Dim stTRDW As String '-- The TRDW from form Dim stPreReq As String '-- The PreReq from form Dim stWorkLoc As String '-- The Location of Work from form Dim stDelivActiv As String '-- The Deliverables/Activities from form Dim stStartDate As Date '-- The Start Date from Subform Dim stEndDate As Date '-- The End Date from Subform Dim stWho As String '-- Reference to Resources Dim strSQL As String '-- Create SQL update statement Dim errLoop As Error '-- Combo of names to assign COF to stWho = Me.COF_Scheduled__Assigned_Resources__Subform1!Res ourceName stWhere = "Resources.ResourceName = " & "'" & stWho & "'" '-- Looks up email address from Resources varTo = DLookup("[ResourceEmail]", "Resources", stWhere) stCOFNumber = Me!COFNumber stCustomerID = Me.Consultancy_Order_Form_CustomerID stCompanyName = Me.CompanyName stContactName = Me!COFContact stAddress = Me.Address stTRDW = Me.TRDW stPreReq = Me.PreRequisites stWorkLoc = Me.WorkLocation stDelivActiv = Me.DeliverablesActivities stStartDate = Me.COF_Scheduled__Assigned_Resources__Subform1!Sta rtDate stEndDate = Me.COF_Scheduled__Assigned_Resources__Subform1!End Date stSubject = ":: New Consultancy Order Assigned ::" stText = "You have been assigned a new Consultancy Order." & vbCrLf & _ "Consultancy Order Form Number: " & stCOFNumber & _ vbCrLf & _ "Company ID: " & stCustomerID & _ vbCrLf & _ "Company Name: " & stCompanyName & _ vbCrLf & _ "Contact Name: " & stContactName & _ vbCrLf & _ "Address: " & stAddress & _ vbCrLf & _ "Terms of Reference / Description of Work: " & stTRDW & _ vbCrLf & _ "Pre-Requisites: " & stPreReq & _ vbCrLf & _ "Location of Work: " & stWorkLoc & _ vbCrLf & _ "Deliverables / Activities: " & stDelivActiv & _ vbCrLf & _ "Start Date: " & stStartDate & _ vbCrLf & _ "End Date: " & stEndDate & _ vbCrLf & _ "Please reply to confirm Consultancy Order Assignment." 'Write the e-mail content for sending to Consultant DoCmd.SendObject , , acFormatTXT, varTo, , , stSubject, stText, -1 'Set the update statement to disable command button once e-mail is sent strSQL = "UPDATE [Consultancy Order Form] SET [Consultancy Order Form].COFSentToConsultants = 0 " & _ "Where [Consultancy Order Form].COFNumber = " & Me!COFNumber & ";" On Error GoTo Err_Execute CurrentDb.Execute strSQL, dbFailOnError On Error GoTo 0 'Requery checkbox to show checked 'after update statement has ran 'and disable send mail command button Me!COFSentToConsultants.Requery Me!COFSentToConsultants.SetFocus Me.SendFormToConsultants.Enabled = False Exit SubErr_Execute: ' Notify user of any errors that result from ' executing the query. If DBEngine.Errors.Count > 0 Then For Each errLoop In DBEngine.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If Resume NextExit_SendFormToConsultants_Click: Exit SubErr_SendFormToConsultants_Click: MsgBox Err.Description Resume Exit_SendFormToConsultants_ClickEnd Sub
What does it mean? it doesn't say where i have a problem in my code. What do you think?
I tried to get help elsewhere with this but it didn't quite work out, so I thought I'd pick a few other brains. So here's the problem from the beginning.
I made a table with three fields:
Field Name----Data Type Category------Text Item----------Text Cost----------Number
I set up a form with two combo boxes and two text boxes. I want to pick an Item from the combo box, and have the Cost appear in the corresponding text box. I set up the combo box to show each field when I click on the arrow.
I was given the following code to put in the After Update section under the Event tab for the combo box:
Private Sub Combo8_AfterUpdate() Me.Text2 = Me.Combo8.Column(2) End Sub
That works. I pick an item from the combo box, and its value appears in the text box next to it. Ultimately, I want to edit the value in the text box and have it update the table. I was given the following code for the text box:
Private Sub MyTextbox_AfterUpdate() Dim strSQL As String strSQL = "UPDATE MyTable " & _ "SET Myfield = " & Me.MyTextbox & _ " WHERE Id = " & Me.MyCombo CurrentDb.Execute strSQL, dbFailOnError End Sub
So far, that hasn't worked. I believe I've made the correct substitutions:
Private Sub Text2_AfterUpdate() Dim strSQL As String strSQL = "UPDATE [Primary Table] " & _ "SET Cost = " & Me.Text2 & _ " WHERE Id = " & Me.Combo8 CurrentDb.Execute strSQL, dbFailOnError End Sub
When I type a different value into the text box and hit enter, I get the following error:
Run-time error '3061': Too few parameters. Expected 2.
I was given a different line of code in the event that I'm "trying to pass text":
" WHERE Id = '" & Me.Combo8 & "'"
When I use that, the error is the same except it says:
Not sure how to work in the '* ROLL *' into this SQL statement. The query statement works fine ... I have tried different quotation methods ( Not Like " & " ' * ROLL * & ' " & " ) AND .... )
sql = "SELECT DISTINCTROW Sum(CDbl([Scrap Factor])) AS SumOFScrap FROM [RT: Signpro1: Costs] LEFT JOIN [DT: InventoryExtend] ON [RT: Signpro1: Costs].[Part Number] = [DT: InventoryExtend].[Part#] GROUP BY [DT: InventoryExtend].CategoryID, [DT: InventoryExtend].Description, [forms]![signpro sign estima parameters]![combo14] HAVING ((([DT: InventoryExtend].CategoryID)=30) AND (([DT: InventoryExtend].Description) Not Like '* ROLL *') AND (([forms]![signpro sign estima parameters]![combo14])=1));"
I have been struggling with getting the syntax right for the ConcatRelate function. I have looked at other peoples examples and mine seems to have exactly the same syntax but it is giving me an error.
My Sql is
SELECT qr_RiverGroup.River, ConcatRelated("Site_ID","qr_RiverGroup","[River] = '" & [River] & "'") AS Expr1 FROM qr_RiverGroup;
I have a library function that will allow the user to nominate a query (as one of its arguments) in the calling application which must have an email field. The function will then Do Loop the email field, concatenating it before creating an email and addressing it. The intended functionality is that a developer can easily create a group email, just by creating a query.
This works fine if the query is filtered "statically" - i.e. I specify which group of people by typing in their "Site_ID" in the criteria. However I want developers to be able to creating dynamically filtered queries (perhaps by the group's ID on a calling form). Within the query (to test it), the filter is therefore [Forms]![Test Function Calls]![Site_id]. When I run the code, I am then presented with "Run-time error 3061: Too few parameters. Expected 1". The code in question is:
Dim rst As DAO.Recordset Dim stTo As String 'one of the function's arguments received from the calling function. Dim stToString As String 'the built up concatenated emails
Set rst = CurrentDb.OpenRecordset(stTo, dbOpenDynaset, dbSeeChanges)
Im new to asp and access and have been having this problem for serveral weeks.
Every couple of days, all the asp pages on my site that communicate with the database start having 500 internal errors. i turned off the "Show friendly error messages" and one page gave me this specific error:
Microsoft JET Database Engine error '80004005'
Unspecified error
/admin/submitlogin.asp, line 8
I have tried a million things and have no idea why this is happening. Im not sure what other information i should post in order to see the problem. Any help would be greatly appreciated. Thank you,
Private Sub NextApplication_Click() On Error GoTo Err_NextApplication_Click DoCmd.GoToRecord , , acNext
Exit_NextApplication_Click: Exit Sub
Err_NextApplication_Click: If Err.Number = 2105 Then MsgBox "Cannot navigate to the next record. This is the last record." Else MsgBox Err.Description End If
Resume Exit_NextApplication_Click
End Sub
but even when this error occurs nothing is being properly handled the way i specified - any ideas ?
Does anybody know what this error message refers to?
"Reserved Error (-1517); there is no message for this error."
It just started happening today, and I haven't even made any changes to the database. It occurs when I hit a button I have to run a macro.
the macro does the following: 1) Shows all records 2) Requery 3) ApplyFilter. The Where Condition for the filter is: Right([tblContracts].[JobNum],4)=Right([Forms]![FrmContProc].[txtFindJobNum],4)
The weird thing is that it only occurs if the Form window is taller than 1/2 of my viewable area. If the Form window is 1/2 the viewable area or shorter, it works OK. This was running fine earlier today, but about 4:00 pm (03/31/06) this started happening.
If anybody knows what this error means, or how to get rid of it (I really need to use this window in full-screen) then please let me know.
Okay, I'm kind of stumped here.I have a subform that has a button that sends a user to a "sibling" subform on another tab page, pass some information to ensure they are adding more details to the same records rather than creating two separate record.First time I programmed it, I got an error 3022 (keys cannot have duplicate values). I checked the query of the sibling subform and saw that the ID is from the one side table. I changed it so many table's foreign key is used. Second try, I got an error 3341 (there isn't a matching key in one side table).After some thinking, it also occured to me that I had set the query this way to allow addition of new record which wouldn't be possible if I had the query pulling the many side key, not the one side key.How do I get the subform to accept the ID that is being passed and create a new record using that ID?:confused:
Private Sub Consolidate_Click() Dim temp As Variant Excel.Application.Visible = True temp = Dir(CurrentProject.Path & "Inputs") Do While temp <> vbNullString
[Code] ....
From the second iteration its not picking the error.
Is it enough for error handling to code the on error property for each form? With enough I mean error handling which lets you resume the program.
Ontherwise I have to code (or call a procedure) for each coded event which i wouldn't prefer
For instance now I'm putting error handling in each event but would consider it more efficient if it can be placed once in each form Private Sub cmdReport_Click() On Error GoTo Err_cmdReport_Click
hi guys i was wondering if you can help me this is my code: i have a main form with this code, this form contains a subform linked by the All_PricingID
Set rst = CurrentDb.OpenRecordset("tblAll_Pricing") 'main table ' adding data to the table rst.AddNew ' Main table rst!All_PricingID = Me.txtPricingID 'Main table pk rst!MainContract_ID = Me.cmbMainContract 'combo box in parent form rst!ItemNumber = Me.txtItem 'Main form text rst.Update
'sub Table Set rst2 = CurrentDb.OpenRecordset("tblPricing") 'sub table For varItem = 0 To Me.lstsubContracts.ListCount - 1 'this is a list in the main form '--- loop through all the items in the list box and create a new row in the subform for each subcontract in the listbox lstSubcontracts. rst2.AddNew rst2!ID = Me.All_PricingID 'sub table foreign key rst2!SubContractID = Me.lstsubContracts.Column(0, varItem) 'sub table rst2.Update Next varItem '--- close the tables rst.Close rst2.Close Set rst = Nothing Set rst2 = Nothing
the subform appears correctly with the rows i wanted added but i need the user to be able to edit a column in the subform for the rows just created (my form is on datasheet view). but everytime i move to cursor into the subform, i can't even scroll up and down.
i keep getting an error that says :
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. (Error 3022)
but when i check my tables tblAll_Pricing and tblPricing , everything is inserted correctly according to my recorset above, do you know why this is happening? and why i am not able to edit my subform. my subform allowsedits and additions.
hi, I am new to this forum and to MS Acess. i am not a software engineer or in the field of software. I had to learn as much as i could about MS Acess because of a project i worked on. I have a standalone MSacess database and one of the forms is giving me trouble when I try to enter a new record
The form is called frm_fragrances and has information about a fragrance. the frm_fragrances has 4 fields in it and a sub form. the sub form has details about the fragrance
In a new record when I enter the 4 fields and attemt to go to the subform which has details about this fragrance I face this error.
an unexpected error haas occurred @2465: application defined or object defined error.
additional information Active form:<frm_fragrances> Active control:<txt_VendorName> Previous control:<frm_fragrances>@Please call the developer for further instructions
I have looked in the FAQ and on this forum for help on error 2465 and cannot find anything to help me. I can work with forms to an extent but cannot write too much code.
I would really appreciate some help in this matter Thank you
I'm from Vietnam and my English is bad. In my database, I have only a table named tblEmployees, which contains these following fields: Employee_ID (data type: text), Name (text), Salary (number) and Allowance (number). I build a form bound to this table. In the footer of this form, I have a textbox control named txtSumSalary that contains the expression: =Sum([Salary]). Everythings is OK. But when I build an another textbox named txtSumAllowance that contains the expression: =Sum([Allowancess]) (I misspelled this field intentionally), the result are error #error in both textbox controls. I don't understand why the textbox txtSumAllowance wrong, the textbox txtSumSalary is wrong too. Help me!!! Thanks a lot!
Hi. The following message appears when an append query is run in one of my databases "Error No:6; Description: Overflow". I've never seen it before, can't find it in Help - nor anywhere else on the net. All assistance greatly appreciated!