By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

How to Run Parameter Query in VBA???

100+
P: 131
Hi all!!
I have a simple database with 56-Queries that runs by passing parameters from specific forms. It works perfect!!!
The problem is, I`m running out of space in the navigation pane, with 56 queries and more parameter queries to come.
I would like to build parameter query in VBA so as to have user friendly database using the following code!
Expand|Select|Wrap|Line Numbers
  1. Private sub parameterquery()
  2. Dim stringSQL As DAO.Workspace   'Current workspace (for transaction).
  3.   Dim db As DAO.Database    'Inside the transaction.
  4.   Dim bInTrans As Boolean   'Flag that transaction is active.
  5.   Dim stringSql11 As String      'Action query statements.
  6.     Dim strMsg As String      'MsgBox message.
  7.     'Step 1: Initialize database object inside a transaction.
  8.   Set stringSQL = DBEngine(0)
  9.   stringSQL.BeginTrans
  10.   bInTrans = True
  11.   Set db = stringSQL(0)
  12. stringSql11 = "Delete tblMytableName.Field1, tblMytableName.Field2, tblMytableName.Field3 FROM tblMytableName WHERE (((tblMytableName.Field1)=Me![FormsField1]));"
  13. db.Execute stringSql11, dbFailOnError
  14. stringSQL.CommitTrans
  15. End Sub
  16.  
when running this code, it gives RunTime error 3061 "Too Few Parameters Expected 1"
Where am I doing Wrong???
Please help!!!!!
Dec 23 '11 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,709
Your SQL is invalid. It refers to Me, which only has a meaning within report or form object modules - not in SQL.

Also, please read How to Debug SQL String so you know how to post such questions in future.
Dec 23 '11 #2

Rabbit
Expert Mod 10K+
P: 12,421
What are these 56 queries? I suspect they are similar with a slight change.
Dec 23 '11 #3

100+
P: 131
Thanks Neo and Rabbit, the queries runs with respect to the forms, the key word Me refers to the current Fields in a forms that referenced by the query. May be it is not the right place to be thus why I have asked for your hint!!!
Rabbit, these queries varies in structures and differ one another; I have Append queries, Delete Queries, Update queries and simple queries, but all of them runs with parameters taken from either FormsComboBox or formstextBox.
The programs is running perfectly but I want to improve by adding more queries and thus am running out of space in the navigation pane and is very hard to spot the query in the pane.
Please help!!!
Dec 24 '11 #4

NeoPa
Expert Mod 15k+
P: 31,709
Mr Key:
May be it is not the right place to be thus why I have asked for your hint!!!
Indeed. But when I gave you a hint (two in fact) you seem to have ignored it (them).

Reading the linked article and following the instructions there will help to save you wasting so much time on things which are easily handled by following those instructions. From there, we might have something a little more sensible to work with than VBA code that creates what you think ought to be SQL.
Dec 24 '11 #5

100+
P: 131
Ok thanks NeoPa!
You always have constructive ideas but very hard to grasp!!!
I have read the article as suggested but coldnt solve my problem.
I have a parameter query which is working properly so far, I just want to change the design towards VBA from ordinary queries. Should I run my first SQL through VBA then I can go ahead debugging my SQL string!!!!
You know, I can make it on my own should there be no choice behind but it might take longer than under your assistance or hint!
Dec 26 '11 #6

NeoPa
Expert Mod 15k+
P: 31,709
The first thing I'd do, if I were you, would be to use the linked article to get the exact contents of the SQL string immediately prior to execution posted here in this thread. That way we are looking directly at the SQL and don't need to get ourselves confused with VBA things.
Dec 27 '11 #7

100+
P: 759
Hi !
Mr Key take a look here. Maybe this is also an answer for you:
http://bytes.com/topic/access/answer...y-filter-query
Dec 27 '11 #8

100+
P: 131
Thanks Mihal and Neopa for your time!
I will work it out for two days and post the answer right here!
Dec 27 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.