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

Help with qdf.OpenRecordset

P: 3
Hello,

Access VBA newbie here and need a little help getting this to work. I have a select query set up with two parameters on one field so I can use a date range:

Between [Start Date] And [End Date]

This works just fine when running the query all by itself.

I have created a form which allows the user to enter the Start Date and End Date and then runs the query when selecting a button. The problem I'm having is no results show up. The code runs through but doesn't display even an empty results page.

My code is below. The message box fires at the end but nothing displays on the screen prior to that. I've tried using ("Start Date") and ("End Date") in place of (0) and (1) for the parameters and that doesn't work either.

I've also checked the me.StartDateTB and me.EndDateTB values and they are correct.

Any help you could give me would be appreciated.


Expand|Select|Wrap|Line Numbers
  1. Private Sub RunBtn_Click()
  2.  
  3.     Dim db As DAO.Database
  4.     Dim qdf As DAO.QueryDef
  5.     Dim rst As DAO.Recordset
  6.  
  7.         Set db = CurrentDb
  8.         Set qdf = db.QueryDefs("TestStep1")
  9.             qdf.Parameters(0) = Me.StartDateTB
  10.             qdf.Parameters(1) = Me.EndDateTB
  11.         Set rst = qdf.OpenRecordset
  12.  
  13.         MsgBox "finished"
  14.  
  15. End Sub
  16.  
Feb 10 '12 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,599
What would you expect to disply on Screen? Is is a Recordset Object you are trying to create, not a Querydef Object. A recordset has no visible Interface.
Feb 10 '12 #2

P: 3
All I'm really trying to do is use entries from two textboxes in a form as the parameters for a query that is already set up in Access. Then display the results on the screen.

thanks for any and all help.
Feb 13 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
As ADezii said, opening a Recordset does Not cause the data to display on the screen.
Feb 13 '12 #4

P: 3
@NeoPa

NeoPa,

I understand that now. I am still trying to find a way to accomplish my goal. After ADezii's response, I googled around some more and tried the following but it still displays nothing.

All I would really like to know is how to display the results of TestStep1 with the parameters from the form.

Thanks in advance for the help.


Expand|Select|Wrap|Line Numbers
  1.         Dim db As DAO.Database
  2.         Dim qdf As DAO.QueryDef
  3.         Dim rst As DAO.Recordset
  4.  
  5.         Set db = CurrentDb
  6.         Set qdf = db.QueryDefs("TestStep1")
  7.             qdf.Parameters(0) = Me.StartDateTB
  8.             qdf.Parameters(1) = Me.EndDateTB
  9.         Set rst = qdf.OpenRecordset
  10.  
  11.         Do Until rst.EOF
  12.             Debug.Print rst!VENDOR_CARRIER_ID
  13.             rst.MoveNext
  14.         Loop
  15.  
  16.         MsgBox "finished"
  17.  
  18.  
Feb 13 '12 #5

NeoPa
Expert Mod 15k+
P: 31,186
I'm not aware that you can pass parameter values to a QueryDef object when you open it for display. What you can do though, is to design the QueryDef to get it's values from a form instead.
Feb 13 '12 #6

Post your reply

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