Prev: Query Problem
Next: grouping ...
From: Marshall Barton on 28 Mar 2010 11:11
Avid Fan wrote:

>On 28/03/2010 5:27 AM, Marshall Barton wrote:
>> Avid Fan wrote:
>>> I know that you can create a query with sSQL = some SQL statment
>>>
>>> me.recordsource = sSQL
>>>
>>> I have looked at other threads that have talked about using
>>> forms!myform!mycontrol as a parameter this is good but limits the query
>>> to one form.
>>>
>>> I remember that there was a way of calling a stored parameter with
>>> something like.
>>>
>>> me.recordsource = qryPlanned With mdate1, mdate2
>>
>>
>> Is this the kind of thing you are looking for:
>>
>> Dim db as Database
>> Dim qdf As QueryDef
>> Set db = CurrentDb()
>> Set qdf = db.QueryDefs!yourquery
>> qdf.Parameters("date1parameter") = mdate1
>> qdf.Parameters("date2parameter") = mdate2
>>
>> Me.Recordset = qdf.OpenRecordset()
>>
>
>This one sounds like what I am looking for. I come from a VPF
>background recordsets are difficult for me.
>
>I found something similar.
>
>Set qdf = CurrentDB.OpenQueryDef(qryName)
>
>In Access 2007 OpenQueryDef is not a method of CurrentDB.
>
>> Or, maybe all you want to do is set the form's Filter
>> property??
>
>I am afraid I don't even know what that is.
>>
>> Or, if all this is part of opening the form, then the code
>> that opens the form should be using the OpenForm method's
>> WhereCondition argument to filter the form's record source.
>>
>
>Err ?????
>
>
>> To much guessing here. You should explain WHAT you are
>> trying to accomplish instead of asking about how to take the
>> next step down the path of your attempted solution.
>>
>Thank you very much for your help!
>
>Sorry I am not deliberately trying to be vague.
>
>This is my first access application I want show planned customer
>contact between two dates.
>
>a two date boxes and a form list.
>
>I am doing this to help a friend who has been handed a whole lot of new
>reporting requirements and no tools to do the job. The work computer
>is locked down so I can't write something in VFP for her. I found
>Access 2003 on her machine so I decided to write an Access program.
>
>Why do I even have Access? My wife wanted Publisher for work so I
>bought her Office Professional 2007.


OK, so tell us what you are trying to accomplish in which
version and we'll try to get you going.

Note that we normally use a form with text boxes for users
to specify filter information and command button*s to run
reports. The code behind a button for a specific report
would use the OpenReport method's WhereCondition argument
(see VBA Help) to filter the report. This approach
eliminates the need for parameters in the report's record
source query. Another benefit is you can design reports and
test them without a filter and apply different filters
without much, if any, impact on the reports.

I assume that you have a handle on the structure of the data
tables and their degree of normalization so we'll let that
ride until there's an issue.

--
Marsh
MVP [MS Access]
From: Avid Fan on 29 Mar 2010 08:21
On 29/03/2010 2:11 AM, Marshall Barton wrote:

>
> OK, so tell us what you are trying to accomplish in which
> version and we'll try to get you going.
>

Access 2007 writing application for Access 2003

OK I have two Text boxes in date format

txtStartDate txtEndDate


I have simple multiple Item form. I want the user to be able to be
able to limit the out put of the query to only the records chosen buy
the user.

This code does not work. Pop up boxes will still appear.
This parameter query still difficult.

The runtime error 3251 Operation not supported by this object
Me.Recordset = qdf.OpenRecordset()

Form_Load

Dim db As Database
Dim qdf As QueryDef


Set db = CurrentDb()
Set qdf = db.QueryDefs!qryCalendar
qdf.Parameters("date1parameter") = #3/23/2010#
qdf.Parameters("date2parameter") = #3/23/2013#

Me.Recordset = qdf.OpenRecordset()


End Sub

From: Marshall Barton on 29 Mar 2010 10:16
Avid Fan wrote:
>On 29/03/2010 2:11 AM, Marshall Barton wrote:
>> OK, so tell us what you are trying to accomplish in which
>> version and we'll try to get you going.
>>
>Access 2007 writing application for Access 2003
>
>OK I have two Text boxes in date format
>
>txtStartDate txtEndDate
>
>I have simple multiple Item form. I want the user to be able to be
>able to limit the out put of the query to only the records chosen buy
>the user.
>
>This code does not work. Pop up boxes will still appear.
>This parameter query still difficult.
>
>The runtime error 3251 Operation not supported by this object
> Me.Recordset = qdf.OpenRecordset()
>
>Form_Load
>
>Dim db As Database
>Dim qdf As QueryDef
>
> Set db = CurrentDb()
> Set qdf = db.QueryDefs!qryCalendar
> qdf.Parameters("date1parameter") = #3/23/2010#
> qdf.Parameters("date2parameter") = #3/23/2013#
>
> Me.Recordset = qdf.OpenRecordset()
>
>End Sub


I still have no idea why you are trying to manipulate a
query. If it is used as the form's record source, then I
would have to see the query's SQL view to comment on why
that isn't working. But then, I think that would be a very
roundabout way to filter the form's data. Maybe your VFP
experience is leading you away from the usual Access
approach?

Maybe, you need to forget about setting the record source or
recordset and change the parameters to a style that refers
to the text boxes on the form:
WHERE somefield Between Forms!yourform.txtStartDate
And Forms!yourform.txtEndDate
This way, Access will understand where/how to resolve the
parameters and you will not have to fool around with code
trying to resolve the parameters.

But then, the Load event is too soon for users to have
entered the filtering dates. That should wait until users
have actually entered the dates. At that point, if this
activity is taking place on one form, all you would need is
to Requery the form.

--
Marsh
MVP [MS Access]
First  |  Prev  | 
Pages: 1 2
Prev: Query Problem
Next: grouping ...