When I call this function Access gives me error msg "Too few parameters. Expected 3. [3061]" on the Set rstDAO = qryDef.OpenReco rdset. Then I changed to
Set rstDAO = qryDef.OpenReco rdset(dbOpenSna pshot, dbOpenSnapshot, dbOpenForwardOn ly)
Now it gives me error "Invalid Arguments [3001]"...pls help...
here is my code: - Public Function GetTotalAmt(strAcct_Code As String) As Long
-
Dim currDB As DAO.Database
-
Dim rstDAO As DAO.Recordset
-
Dim qryDef As DAO.QueryDef
-
Dim strQry As String
-
-
Set currDB = CurrentDb()
-
strQry = "Sum_Amt_CashierDirect"
-
Set qryDef = currDB.QueryDefs("Sum_Amt_CashierDirect")
-
qryDef.Parameters("acct_code") = "9ARTNC"
-
Set rstDAO = qryDef.OpenRecordset(dbOpenSnapshot, dbOpenSnapshot) ' ERROR MESSAGE EXPECTED 3 PARAMETERS
-
-
MsgBox "[" & rstDAO![SumOfAMOUNT] & "]"
-
-
GetTotalAmt = rstDAO![SumOfAMOUNT]
-
-
'rstDAO.Close
-
'qryDef.Close
-
-
End Function
9 4359
Hi. Please don't duplicate part of your post (see thread linked here) - it wastes the time of the expert volunteers who staff this site.
You are using a querydef object to allow parameter passing. In more normal circumstances OpenRecordset applies to the database object, not a querydef object. You can use the CurrentDb qualifier which is intrinsic in Access to refer to the currently-active database object. With it, the recordset open should become
Set rstDAO = Currentdb.OpenR ecordset(strQry )
but if you need the parameter passing then retry your querydef based solution with OpenRecordset(s trQry), as you have not supplied the correct arguments to OpenRecordset in the example you quote.
-Stewart
thanks Stewart....sorr y for double posting because I'm too desperate and nobody can help me....
hi,
if I use:
Set rstDAO = Currentdb.OpenR ecordset(strQry )
what I know, I need to use:
Set qryDef = currDB.QueryDef s("Sum_Amt_Cash ierDirect")
to pass parameter (acct_code-> parameter of my query and supplied them with "9ARTNC" value )
qryDef.Paramete rs("acct_code" ) = "9ARTNC"
how can I pass the parameter from my access query? if I do this?
Set rstDAO = Currentdb.OpenR ecordset(strQry )
pls correct me if I'm wrong....pls help...
thanks
...help please...
can anybody give me the syntax or example how can I implement the OpenRecordSet() method using MS Access query?
i'm still having "Too few parameters. Expected 3 [3061]"
still waiting who can give solution to my problem
Hi. As we have all already said, the error message does NOT relate to the syntax of the OpenRecordset method; it results from a failure to open the recordset, possibly related to references to form control values in the SQL of the underlying query.
Please post the SQL for the query you are trying to use OpenRecordset upon.
Thank you
-Stewart
Hi Stewart,
Thanks for the response, here is the content of the query - SELECT dbo_SALFLDGDSN.ACCNT_CODE,
-
dbo_SALFLDGDSN.PERIOD,
-
dbo_SALFLDGDSN.JRNAL_NO,
-
Sum(dbo_SALFLDGDSN.AMOUNT) AS SumOfAMOUNT,
-
dbo_SALFLDGDSN.D_C
-
FROM dbo_qrySSRFADDDSN INNER JOIN dbo_SALFLDGDSN ON dbo_qrySSRFADDDSN.ADD_CODE = dbo_SALFLDGDSN.ACCNT_CODE
-
GROUP BY dbo_SALFLDGDSN.ACCNT_CODE,
-
dbo_SALFLDGDSN.PERIOD,
-
dbo_SALFLDGDSN.JRNAL_NO,
-
dbo_SALFLDGDSN.D_C
-
HAVING (( (dbo_SALFLDGDSN.PERIOD) = Format(SunToSqlServerPeriod([Forms]![frmPayInvFileGen]![txtRepPeriod]),"General Number"))
-
AND ((dbo_SALFLDGDSN.JRNAL_NO)=[Forms]![frmPayInvFileGen]![txtJrnlNo] ));
where:
SunToSqlServerP eriod() returns => "2008005"
[Forms]![frmPayInvFileGe n]![txtJrnlNo] => 6735
Hi. There are two form field references in the HAVING clause (the equivalent of a WHERE clause in a grouped query). Access is inconsistent in the way it treats form field references in such clauses, and there are known issues with their use (including the parameter error message arising with OpenRecordset). It is these problems that Adezii was referring to in his response.
When I use such form field references I do so through the use of a simple global function in VBA to fetch the value of the form field from the relevant form. Access has no problems with including function calls in SQL statements.
Open any public code module (any module shown in the Modules tab of the database), or create a new one if you don't have any. You can give it any suitable name (the default is Module 1).
Paste the following code in to the module and save the changes: - Public Function FormFieldValue(FormName As String, FieldName As String)
-
FormFieldValue = Forms(FormName).Controls(FieldName)
-
End Function
Then try the revised HAVING part of the SQL for your query below, where the form field references are retrieved using the new function in place of direct references: -
HAVING (( (dbo_SALFLDGDSN.PERIOD) = Format(SunToSqlServerPeriod(FormfieldValue("frmPayInvFileGen", "txtRepPeriod")),"General Number"))
-
AND ((dbo_SALFLDGDSN.JRNAL_NO)=FormFieldValue("frmPayInvFileGen", "txtJrnlNo") ));
-Stewart
Hi Stewart,
Thanks for your response....I'l l try your solution to my problem...Thank s again..
EVH316
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: the other john |
last post by:
This should be fairly basic but I can't think of how to do this and I'm
running out of time!
I am developing a picture gallery and I can't figure out how to select
"one" picture from each gallery. My DB is configured like this...
tbl_pictures
this contains all pictures in the picture gallery.
tbl_pictures_galleries
|
by: Raghuraman |
last post by:
Hai
I have a designed Crystal report .rpt file which has 4 access tables
.. After designing the report i put a parameter field which filteres
the desired records quite naturally. Every thing works fine in the
crystal report design time as well as in the preview asking parameters
..
But if i call it thru my VB form it works nice after removing the
parameteres.
|
by: Don Seckler |
last post by:
I have an update query that runs when a report closes. I have several
reports that will need to run the update query with diferent criteria.
I'd like to simply make the criteria change in the report vba instead
of making different queries.
Here's my query sql:
UPDATE Draw SET Draw.Billed = Yes
WHERE (((Draw.Billed)=No) AND ((Draw.WholesalerName)="Hudson"));
|
by: Steve Patrick |
last post by:
Hi All
You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.
I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4....
|
by: Tcs |
last post by:
I've been stumped on this for quite a while. I don't know if it's so simple
that I just can't see it, or it's really possible. (Obviously, I HOPE it IS
possible.)
I'm trying to get my queries to run from VB. My pass-thru query retrieves
data from our AS/400 that I use to build a local table (on my PC). My pass-thru
and local do in fact work together when I run them interactively. But I want,
no make that NEED, to run them from VB.
...
| |
by: Maciek Zywno |
last post by:
Hi,
I managed to do this by using a query and !! syntax,
but it does not work with SPs. So I created a procedure with "@" to
define a paraemter, but how can I pass a value to this procedure from a
form?
Thanks for any suggestions.
Regards,
|
by: Dud Bug |
last post by:
I have a query in an Access db that prompts the user for info. (e.g. a primary key representing a company) before compiling the relevant table of results. This works fine in Access but I want to use this query from within VB.Net. But how do I, in code, pass a parameter to the Access query?
|
by: Bill |
last post by:
I have a 200 record database that includes a date/time field,
AnnivDate, for a wedding anniversary. AnnivDate has nulls and some
incorrect year data. I have been creating the Access database using
data imported from an Excel file and the person entering into Excel
only cared about the month and day portion and Excel added the current
year to the field value. I want to produce a report that shows
upcoming anniversaries using a parameter...
|
by: gumby |
last post by:
I would like to call this stored procedure, but I am unable to pass
parameters to the @Start and @End.
Is thier a way to pass parameters to a pass through query from MS
Access?
SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS ,
.fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2)
AS
FROM dbo.tblPersActionLog INNER JOIN
|
by: vijaykumardahiya |
last post by:
Hello Sir,
I have a simple Issue but It is not resolve by me i.e input parameter
are not store in Ms-Access.
I store the input parameter through Standard Action <jsp:useBean>.
jsp:useBean call a property IssueData. this property exist in
SimpleBean which create a connection from DB and insert the data.
At run time servlet and server also show that loggging are saved in DB.
But when I open the table in Access. Its empty.
Ms-Access have...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |