Darrell-
When you run a query in code, you're invoking the query engine directly.
You could do the same from Excel or Word. The query engine does not "see"
your form. When you run the query in the Access space, Access grabs the
values for you from the open form behind the scenes and passes them to the
query engine. If you try to run the query in code, you must do the same.
Sorta like this:
Dim db As DAO.Database, qd As DAO.QueryDef
Set db = CurrentDB
Set qd = db.QueryDefs("NameOfYourQuery")
qd.Parameters("[Beginning Date]") = Forms!MyForm!txtBeginDate
qd.Parameters("[Ending Date]") = Forms!MyForm!txtEndDate
qd.Execute
If you're trying to execute some SQL that references parameter queries,
then you'll have to create a temporary QueryDef, assign the SQL, set the
parameters, then execute it.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: D <embreydl@yahoo.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Wednesday, November 28, 2012 10:01 PM
To: <MS_Access_Professionals@yahoogroups.com>
Subject: [MS_AccessPros] Append Query Too Few Parameters Error
Greetings All,
I am using Access 2007 on Win 7. I am attempting to add records to a
temporary table in code with the following append query:
INSERT INTO tmpCheckWriting ( CheckDate, SubsPdServ, SubsNPServ,
SubsPdClaim, SubsNPClaim, SubsDollarPayout )
SELECT sd.CheckDate, sd.SubsPdServ, sd.SubsNPServ, sd.SubsPdClaim,
sd.SubsNPClaim, sd.SubsDollarPayout
FROM qrySubscriber_Detail AS sd;
When i try to Execute this query from code, I receive a run-time error
3061 "Too few parameters. Expected 2." I built this query using the query
design grid and then copied the SQL to my procedure. The query runs fine
without an error when run from the query design window.
The query in the FROM statement joins two other queries that select the
dates and values for subscriber services and claim that were paid or not
paid. Those two queries each have a where clause that selects data based
on the beginning end ending date displayed on the form where the user will
run the append query. I have the form open and the date fields are
filled-in.
Why will the query run from the query design window but not run through
vba code?
Darrell
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar