Open form with specific criteria

[Dave Elliott]

I have module that is called from my form to open another form with specific
criteria.
Here is an example of what I am trying to do;
I need to open my form named TimeCards from the main form named
frmAutoPayrollReport which uses unbound
drop down list boxes for criteria.
I placed the control / field in my query name Due and on the form.
If the control Due is true, then the record Needs to be invoiced.
Also if (Due) is true then I need to open TimeCards and show all records
that Due (Value) is true.


Dim db As DAO.Database
Dim qd As QueryDef
Dim Mywhere As Variant, Var1 As Variant
Var1 = [Forms]![frmAutoPayrollReport]
Set db = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
db.QueryDefs.Delete ("QDynamicQuery")
On Error GoTo 0

Mywhere = Null
Mywhere = Mywhere & (" And [Due] Like '" + Var1![Status].Column(0) + "'")
Set qd = db.CreateQueryDef("QDynamicQuery", "Select * From QDates " &
("Where " + Mid(Mywhere, 6) + "ORDER BY [TimeCounter] ASC ;"))

DoCmd.OpenForm "TimeCards"

 

[Nikos Yannacopoulos]

Dave,

Am I to understand that form TimeCards 's recordsource is query
QDynamicQuery? Are you doing all this just to filter the form on the current
selection of Status combo box? If this is the case then you are making this
way too complicated. You don't need to delete and remake the query every
time, just make it read the value of the bombo right off the form. Start
making a new query, add no table, switch to SQL view and paste this:

SELECT * FROM QDates

WHERE [Due] Like [Forms]![frmAutoPayrollReport]![Status].Column(0)

ORDER BY [TimeCounter]

So long as the form is open and a selection is made in the combo, this
should return what you want. If you are happy with this, delete the
existing(?) QDynamicQuery and save this one under the same name. Now the
TimeCards form should be functioning as intended, and all you need is to
open it through a:

DoCmd.OpenForm "TimeCards"

By the way, events call procedures (subs, functions) which live in modules;
they don't call the modules themselves.

HTH,
Nikos

 

[Dave Elliott]

Only one thing, the status of Due is either a Yes/No value Default status
of False
on the form it is a check box. If the record needs to be invoiced, then it
will be True, or Yes, checked off.
I only need to filter by a Yes or True value.
QDynamic query has QDates query in its grid with only two criteria;
one is just a sort and the other is Due with a criteria of Like '1' which
is used on the main form that is used to open the form TimeCards.
i.e. Status, a drop down list box with three choices, 1 active, 2 archived,
3 due
QDates query itself just has all the fields in it with no criteria.
TimeCards form has two option buttons on it for active and archived.

Dave,

Am I to understand that form TimeCards 's recordsource is query
QDynamicQuery? Are you doing all this just to filter the form on the
current
selection of Status combo box? If this is the case then you are making
this
way too complicated. You don't need to delete and remake the query every
time, just make it read the value of the bombo right off the form. Start
making a new query, add no table, switch to SQL view and paste this:

SELECT * FROM QDates

WHERE [Due] Like [Forms]![frmAutoPayrollReport]![Status].Column(0)

ORDER BY [TimeCounter]

So long as the form is open and a selection is made in the combo, this
should return what you want. If you are happy with this, delete the
existing(?) QDynamicQuery and save this one under the same name. Now the
TimeCards form should be functioning as intended, and all you need is to
open it through a:

DoCmd.OpenForm "TimeCards"

By the way, events call procedures (subs, functions) which live in
modules;
they don't call the modules themselves.

HTH,
Nikos

I have module that is called from my form to open another form with specific
criteria.
Here is an example of what I am trying to do;
I need to open my form named TimeCards from the main form named
frmAutoPayrollReport which uses unbound
drop down list boxes for criteria.
I placed the control / field in my query name Due and on the form.
If the control Due is true, then the record Needs to be invoiced.
Also if (Due) is true then I need to open TimeCards and show all records
that Due (Value) is true.


Dim db As DAO.Database
Dim qd As QueryDef
Dim Mywhere As Variant, Var1 As Variant
Var1 = [Forms]![frmAutoPayrollReport]
Set db = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
db.QueryDefs.Delete ("QDynamicQuery")
On Error GoTo 0

Mywhere = Null
Mywhere = Mywhere & (" And [Due] Like '" + Var1![Status].Column(0) + "'")
Set qd = db.CreateQueryDef("QDynamicQuery", "Select * From QDates " &
("Where " + Mid(Mywhere, 6) + "ORDER BY [TimeCounter] ASC ;"))

DoCmd.OpenForm "TimeCards"

 

[Nikos Yannacopoulos]

Dave,

Sorry, you're losing me. What eaxctly is the problem?

This is getting confusing. If the subform is based on a query
(QDynamicQuery) which is already filtered on Due Like 1, what is the point
of (a) having the checkbox on the main form, and (b) trying to link the
subform to it?

Nikos

Only one thing, the status of Due is either a Yes/No value Default status
of False
on the form it is a check box. If the record needs to be invoiced, then it
will be True, or Yes, checked off.
I only need to filter by a Yes or True value.
QDynamic query has QDates query in its grid with only two criteria;
one is just a sort and the other is Due with a criteria of Like '1' which
is used on the main form that is used to open the form TimeCards.
i.e. Status, a drop down list box with three choices, 1 active, 2 archived,
3 due
QDates query itself just has all the fields in it with no criteria.
TimeCards form has two option buttons on it for active and archived.

Dave,

Am I to understand that form TimeCards 's recordsource is query
QDynamicQuery? Are you doing all this just to filter the form on the
current
selection of Status combo box? If this is the case then you are making
this
way too complicated. You don't need to delete and remake the query every
time, just make it read the value of the bombo right off the form. Start
making a new query, add no table, switch to SQL view and paste this:

SELECT * FROM QDates

WHERE [Due] Like [Forms]![frmAutoPayrollReport]![Status].Column(0)

ORDER BY [TimeCounter]

So long as the form is open and a selection is made in the combo, this
should return what you want. If you are happy with this, delete the
existing(?) QDynamicQuery and save this one under the same name. Now the
TimeCards form should be functioning as intended, and all you need is to
open it through a:

DoCmd.OpenForm "TimeCards"

By the way, events call procedures (subs, functions) which live in
modules;
they don't call the modules themselves.

HTH,
Nikos

I have module that is called from my form to open another form with specific
criteria.
Here is an example of what I am trying to do;
I need to open my form named TimeCards from the main form named
frmAutoPayrollReport which uses unbound
drop down list boxes for criteria.
I placed the control / field in my query name Due and on the form.
If the control Due is true, then the record Needs to be invoiced.
Also if (Due) is true then I need to open TimeCards and show all records
that Due (Value) is true.


Dim db As DAO.Database
Dim qd As QueryDef
Dim Mywhere As Variant, Var1 As Variant
Var1 = [Forms]![frmAutoPayrollReport]
Set db = DBEngine.Workspaces(0).Databases(0)
On Error Resume Next
db.QueryDefs.Delete ("QDynamicQuery")
On Error GoTo 0

Mywhere = Null
Mywhere = Mywhere & (" And [Due] Like '" + Var1![Status].Column(0) + "'")
Set qd = db.CreateQueryDef("QDynamicQuery", "Select * From QDates " &
("Where " + Mid(Mywhere, 6) + "ORDER BY [TimeCounter] ASC ;"))

DoCmd.OpenForm "TimeCards"