cmd button to Filter a form based on a subform field

  • Thread starter Thread starter Wissam
  • Start date Start date
W

Wissam

Hi,
I would greatly appreciate any help with the following,as I have been
stuggling for the past 3 days to get it done without succces. I am trying to
apply a filter on a form based on a subform field control value that equals
to a that in a control box on another form.

Description:
I have an access database which has the following:
-"tblPatients" : has a Primary key field called [Pt-ID] + other fields like
[Pt-name], [Pt-race],..
-"tblAppointments": has a primary key field called [App-tNumber] (random
auto number), a field called [Pt-ID], and other field such as [Appt-Date]
(for date of appointment; format is short date) and [Appt-Result],...
There is a one-many relationship between "tblPatients" and "tblAppointments"
based on [Pt-ID].

I have a form called "frmChart" (recordsource="tblPatients"; has controls
for fields like [Pt-ID],[Pt-name],...); in it there is a subform called
"sfrmAppointments" (recordsource= "tblAppointments"; has controls for fields
like [Appt-Date]).
I have a "mainswitch" form with a control box called [cboDate].

I want to do be able to do the following:
Enter a date in the [cboDate] box, and then through a click of a command
button called "filterfrmCharts" on the "mainswitch" form get the following:
Open "frmChart"
Apply a filter based on : sfrmAppointments.[Appt-Date] = [cboDate] value.

I am relatively new to access, and any details on the VBA code would be
greatly appreciated.

Thank you very much for your help.
 
Wissam,
use the where condition of the open form statement.
Something like this, where users choose the date they want from the combo on
the main form.

DoCmd.OpenForm, "TheMainSwitchForm", , , "[Appt-Date] = #"& me.cboDate & "#"

The code goes on the On Click event of the mainswitch.

I'm not understanding how the appt date on the subform has any relation to
the main form called the main switch form.

Jeanette Cunningham
 
Thank you Jeanette,
I have the access database set to automatically open to a "mainswitch" form.
The user would enter a date in a [cboDate] box that is found on the
mainswitch form and then click a button that would do the following:
1) Open another form (called "frmChart") that has the patient records ; in
that form I have a subform that has appointments for each patient (called
"sfrmAppointments"). One of the fields in that subform is [Appt-Date]
2) Filter the form "frmChart" to keep patients who have an appointment on
the date that was specified by the date entered in the cboDate.

I adopted the code that you kindly suggested as follows:
DoCmd.OpenForm, "frmChart", , , "[Appt-Date] = #"& me.cboDate & "#"

What this did is opened the "frmChart" to the first record, but no filter
applied. I suspect that this is due to the fact that [Appt-Date] is not
present in the "frmChart" but rather in a subform (sfrmAppointments) on the
frmChar form.
Is there an easy way to adjust the code for that?
Thanks a lot.
Wissam


Jeanette Cunningham said:
Wissam,
use the where condition of the open form statement.
Something like this, where users choose the date they want from the combo on
the main form.

DoCmd.OpenForm, "TheMainSwitchForm", , , "[Appt-Date] = #"& me.cboDate & "#"

The code goes on the On Click event of the mainswitch.

I'm not understanding how the appt date on the subform has any relation to
the main form called the main switch form.

Jeanette Cunningham

Wissam said:
Hi,
I would greatly appreciate any help with the following,as I have been
stuggling for the past 3 days to get it done without succces. I am trying
to
apply a filter on a form based on a subform field control value that
equals
to a that in a control box on another form.

Description:
I have an access database which has the following:
-"tblPatients" : has a Primary key field called [Pt-ID] + other fields
like
[Pt-name], [Pt-race],..
-"tblAppointments": has a primary key field called [App-tNumber] (random
auto number), a field called [Pt-ID], and other field such as [Appt-Date]
(for date of appointment; format is short date) and [Appt-Result],...
There is a one-many relationship between "tblPatients" and
"tblAppointments"
based on [Pt-ID].

I have a form called "frmChart" (recordsource="tblPatients"; has controls
for fields like [Pt-ID],[Pt-name],...); in it there is a subform called
"sfrmAppointments" (recordsource= "tblAppointments"; has controls for
fields
like [Appt-Date]).
I have a "mainswitch" form with a control box called [cboDate].

I want to do be able to do the following:
Enter a date in the [cboDate] box, and then through a click of a command
button called "filterfrmCharts" on the "mainswitch" form get the
following:
Open "frmChart"
Apply a filter based on : sfrmAppointments.[Appt-Date] = [cboDate] value.

I am relatively new to access, and any details on the VBA code would be
greatly appreciated.

Thank you very much for your help.
Click to expand...
Click to expand...
 
Wissam,
1. when you open frmChart, use code to pass the value of appt-date as open
args.
2. when frmChart opens, use the value of appt-date in the open args to apply
a filter to the subform.

Warning - untested air code.

DoCmd.OpenForm, "frmChart", , , , , ,me.cboDate

On frmChart on its load event put code like this:

If Not IsNull(Me.OpenArgs) Then
With Me.SubformControlName.Form
Filter = "[Appt-Date] = "Me.OpenArgs
FilterOn = True
End With
End If

Jeanette Cunningham



Wissam said:
Thank you Jeanette,
I have the access database set to automatically open to a "mainswitch"
form.
The user would enter a date in a [cboDate] box that is found on the
mainswitch form and then click a button that would do the following:
1) Open another form (called "frmChart") that has the patient records ; in
that form I have a subform that has appointments for each patient (called
"sfrmAppointments"). One of the fields in that subform is [Appt-Date]
2) Filter the form "frmChart" to keep patients who have an appointment on
the date that was specified by the date entered in the cboDate.

I adopted the code that you kindly suggested as follows:
DoCmd.OpenForm, "frmChart", , , "[Appt-Date] = #"& me.cboDate & "#"

What this did is opened the "frmChart" to the first record, but no filter
applied. I suspect that this is due to the fact that [Appt-Date] is not
present in the "frmChart" but rather in a subform (sfrmAppointments) on
the
frmChar form.
Is there an easy way to adjust the code for that?
Thanks a lot.
Wissam


Jeanette Cunningham said:
Wissam,
use the where condition of the open form statement.
Something like this, where users choose the date they want from the combo
on
the main form.

DoCmd.OpenForm, "TheMainSwitchForm", , , "[Appt-Date] = #"& me.cboDate &
"#"

The code goes on the On Click event of the mainswitch.

I'm not understanding how the appt date on the subform has any relation
to
the main form called the main switch form.

Jeanette Cunningham

Wissam said:
Hi,
I would greatly appreciate any help with the following,as I have been
stuggling for the past 3 days to get it done without succces. I am
trying
to
apply a filter on a form based on a subform field control value that
equals
to a that in a control box on another form.

Description:
I have an access database which has the following:
-"tblPatients" : has a Primary key field called [Pt-ID] + other fields
like
[Pt-name], [Pt-race],..
-"tblAppointments": has a primary key field called [App-tNumber]
(random
auto number), a field called [Pt-ID], and other field such as
[Appt-Date]
(for date of appointment; format is short date) and [Appt-Result],...
There is a one-many relationship between "tblPatients" and
"tblAppointments"
based on [Pt-ID].

I have a form called "frmChart" (recordsource="tblPatients"; has
controls
for fields like [Pt-ID],[Pt-name],...); in it there is a subform called
"sfrmAppointments" (recordsource= "tblAppointments"; has controls for
fields
like [Appt-Date]).
I have a "mainswitch" form with a control box called [cboDate].

I want to do be able to do the following:
Enter a date in the [cboDate] box, and then through a click of a
command
button called "filterfrmCharts" on the "mainswitch" form get the
following:
Open "frmChart"
Apply a filter based on : sfrmAppointments.[Appt-Date] = [cboDate]
value.

I am relatively new to access, and any details on the VBA code would be
greatly appreciated.

Thank you very much for your help.
Click to expand...
Click to expand...
Click to expand...
 
Thank you Jeanette,
The following is what I used and it works well:

Dim db As DAO.Database
Dim frm As Form
Set db = CurrentDb()
DoCmd.OpenForm "fChart", , , , , , Me.cboDate
Set frm = Application.Screen.ActiveForm
frm.Filter = "(EXISTS (SELECT 1 FROM [tblAppointments] WHERE ([ID-Sub]
= [ID]) AND ([DateApt]= #" & Me.cboDate & "#)))"
frm.FilterOn = True


Thanks.
______________________-
 
Back
Top