473,320 Members | 2,122 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access Querydef with 2 parameters problem.

4
I am a newbie at VBA and in need of some help with how best to create a recordset from an existing Access Query that requires 2 Parameters from an open form. This query works fine as an ordinary query but i just can't get it to work using VBA. The 2 fields in the form to be used as parameters are
[CompanyName] and [MthFwd]. I am working with Access 2003 in Win XP. I have Microsoft DAO 3.6 in the references. Thanks in advance for any help.
My code is:
Expand|Select|Wrap|Line Numbers
  1. Dim qdf as DAO.Querydef
  2. Dim Rst as DAO.Recordset
  3. Dim Dbs as DAO.Database
  4. Dim sMthFwd, sCompanyName as String
  5.  
  6. sCompanyName = ([Forms]![ViewIndents by Store]![CompanyName])
  7. sMthFwd = [Forms]![ViewIndents by Store]![IndentOrdDetails Subform]![MthFwd]
  8.  
  9. set Dbs = CurrentDb()
  10. Set qdf = Dbs.QueryDefs("qryIndentOrdersForEmailing")
  11.    qdf.Parameters![CustomerName] = sCompanyName
  12.    qdf.Parameters![MthFwd] = sMthFwd
  13.  
  14. Set Rst = qdf.OpenRecordset()
This code gives me an error '3265'
Item not found in this collection

I have tried all kinds of variations in code but no joy so far.

LSteve (L = Learner)
Apr 1 '08 #1
6 4391
ADezii
8,834 Expert 8TB
Just subscribing, be back later.
Apr 1 '08 #2
ADezii
8,834 Expert 8TB
  1. Hard code the Criteria itself within the Query Grid in the Criteria Row for each Field:
    Expand|Select|Wrap|Line Numbers
    1. 'For the Company Name, in the Criteria Row
    2. [Forms]![ViewIndents by Store]![CompanyName]
    3.  
    4. 'For the MthFwd Field in the Sub-Form, in the Criteria Row
    5. [Forms]![ViewIndents by Store]![IndentOrdDetails Subform]![MthFwd]
  2. Evaluate each Parameter in qryIndentOrdersForEmailing by using the Eval() Function.
  3. Please find the complete code below:
    Expand|Select|Wrap|Line Numbers
    1. Dim qdf As DAO.QueryDef
    2. Dim Rst As DAO.Recordset
    3. Dim Dbs As DAO.Database
    4. Dim prm As DAO.Parameter
    5.  
    6. Set Dbs = CurrentDb()
    7. Set qdf = Dbs.QueryDefs("qryIndentOrdersForEmailing")
    8.  
    9. For Each prm In qdf.Parameters
    10.   prm.Value = Eval(prm.Name)
    11. Next prm
    12.  
    13. Set Rst = qdf.OpenRecordset(dbOpenDynaset)
  4. Open the Recordset once the Parameters have been evaluated (last code line).
  5. Remember, since Rst was declared at the Procedure Level, once you exit the procedure your Recordset has no existence. If any processing of the Recordset will be done within this Procedure, all is fine, if not Rst must be declared at either the Form Level or Publicly within a Standard Code Module.
Apr 1 '08 #3
NeoPa
32,556 Expert Mod 16PB
Subscribing.
Interested in Parameters at the moment.
Apr 2 '08 #4
NeoPa
32,556 Expert Mod 16PB
Steve, you don't give the SQL for [qryIndentOrdersForEmailing].

I suspect it is missing one or other of [CustomerName] and [MthFwd] in the PARAMETERS clause.
Apr 3 '08 #5
NeoPa
32,556 Expert Mod 16PB
Rereading your first post, perhaps it should be [CompanyName] instead of [CustomerName]?
Apr 3 '08 #6
LSteve
4
Thankyou all for your replys. I solved the problem by using the code sugested by ADezii. It works well. Thankyou All
Apr 4 '08 #7

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

Similar topics

1
by: Job Lot | last post by:
I have written 6 Queries in an Access DB, which are executed in a For Each…Next loop to populate DataSet object. A new DataTable object is created in DataSet for each query, as follows Private...
1
by: Joris Kempen | last post by:
Hi people, I know that the question has come around sometimes: How to open an Access Report using ASP and export it to for example RTF. I'm trying to implement the first method of David...
3
by: Sunil Korah | last post by:
I dont know whether this is a stupid question. But does access have something akin to the pointer variable in C - or for that matter the '&' operator in xbase. I will explain my reason for...
6
by: fumanchu | last post by:
I've got to let end users (really just one person) load billing batch files into a third party app table. They need to specify the billing cycle name, the batch name, and the input file name and...
6
by: Tim Marshall | last post by:
A2003, but this behaviour also occurred during the same DAO process I'm about to describe in A97. I have a sub procedure which takes two arguments: a querydef name; and an SQL statement...
21
by: Marc DVer | last post by:
I am trying to create a query that can be loaded as a querydef object but not having to assign values to the parameters if I don't want to. Normally when using a parameter query in VBA my code...
4
by: etuncer | last post by:
Hello All, I have Access 2003, and am trying to build a database for my small company. I want to be able to create a word document based on the data entered through a form. the real question is...
9
by: pic078 via AccessMonster.com | last post by:
I need serious help - I have a frontend/backend Access database (2 MDE Files) that remains stuck in task manager after exiting the application - you can't reopen database after exiting as a result...
1
NeoPa
by: NeoPa | last post by:
Access QueryDefs Mis-save Subquery SQL Access stores its SQL for Subqueries in a strange manner :s It seems to replace the parentheses "()"with square brackets "" and (often) add an extraneous...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.