Create new Form Based on 'Filter By Selection'

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have cerated a Form, ExamsFrm, which is used to input details of
exams taken at several Centres. It dosplays details from StudentTbl
and has a SubForm which displays details from ExamsTble.

I have incorporated some VB6 code as well as some Conditional
Formatting. The code automatically updates CheckBoxes in the SubForm,
depending on exam results, and the Conditional Formatting changes
background colours, depending on CheckBox datum.

I have created a Main Switchboard with minimal Startup
options, for security reasons, which means that the user is not
allowed
to filter records.

I intend incorporating switchboard controls to access exam data
specific to each centre e.g. ExamFrmCtr1, ExamfrmCtr2, etc. and
therefore need to create a new Form for each Centre.

Is it be possible to create a new Form for each Centre by saving the
results of a 'Filter By Selection' (from ExamsFrm, Centre), as a new
Form which retains all codes, formatting, layout, etc?

Or will I have to create each Form from scratch?

Terry
 
I do not recommend creating separate forms simply to filter data! Instead of
creating a new form for each centre just create a hidden control on the
switchboard which contains the name or Id of the current Centre and then
pass that value into the ExamsFrm using the WhereCondition parameter of
docmd.Openform - this will filter the data that is visible when the form is
opened.

docmd.OpenForm "ExamsFrm",,,"CentreID=" & me.CentreID
 
I accept you logic. Especially after doing it the hard way by creating
new Queries, Macro's and Forms, (which, after much trial and error and
having to download SR3 Service Pack) took me hours of work. If I ever
have to add new Centres it might get a bit messy.

By the way I omitted to declare that I am a Newbie and, although I had
a good go, I couldnt implement your suggestion as, I havn't yet
acquired the knowlege required to do so. Theres so much to know but
I'm enjoying learning new things every day!

I would be very grateful if you could simplify by sugesting what kind
of Control I should create on the switchboard, how to include the name
of the Centre, pass the value into the ExamsFrm and how to use the
WhereCondition parameter of the docmd.Openform. A step by step IDIOT
LIST would be great!

Terry

Sandra Daigle said:
I do not recommend creating separate forms simply to filter data! Instead of
creating a new form for each centre just create a hidden control on the
switchboard which contains the name or Id of the current Centre and then
pass that value into the ExamsFrm using the WhereCondition parameter of
docmd.Openform - this will filter the data that is visible when the form is
opened.

docmd.OpenForm "ExamsFrm",,,"CentreID=" & me.CentreID


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have cerated a Form, ExamsFrm, which is used to input details of
exams taken at several Centres. It dosplays details from StudentTbl
and has a SubForm which displays details from ExamsTble.

I have incorporated some VB6 code as well as some Conditional
Formatting. The code automatically updates CheckBoxes in the SubForm,
depending on exam results, and the Conditional Formatting changes
background colours, depending on CheckBox datum.

I have created a Main Switchboard with minimal Startup
options, for security reasons, which means that the user is not
allowed
to filter records.

I intend incorporating switchboard controls to access exam data
specific to each centre e.g. ExamFrmCtr1, ExamfrmCtr2, etc. and
therefore need to create a new Form for each Centre.

Is it be possible to create a new Form for each Centre by saving the
results of a 'Filter By Selection' (from ExamsFrm, Centre), as a new
Form which retains all codes, formatting, layout, etc?

Or will I have to create each Form from scratch?

Terry
Click to expand...
Click to expand...
 
Hi Terry,

Let's say your "switchboard" opens a simple form which has a command button
to open this form. Next to the command button you would add an Unbound
(ControlSource is empty) combo box which allows the user to first select the
Centre. The rowsource for this combo would be based on the Centre Table (the
lookup table for defining the valid centres). Lets call this combo
cboCentreID. I would also guess/suggest that the Centre table have a numeric
primary key field and that this field is the field used as the foreign key
field in your Exams table.

tblCentre
------------
CentreID (Primary Key field)
CentreName
.. . . Other details on the Centre

tblExams
--------------
Examid
CentreID (foreign key field)
.. . . other exam details

Now the click event of your command button can use cboCenterId to filter the
data shown in ExamsFrm

docmd.OpenForm "ExamsFrm",,,"CentreID=" & me.cboCentreID

When I initially read your post I was thinking that you wanted to restrict
the records based on which Centre has the switchboard open, on re-reading I
think you simply want to have a method for filtering the data by making the
proper selection from the switch board. If I am correct, then the above
will hopefully help you.

If you are using the built-in "Switchboard" features it might be easiest to
have an intermediate dialog form as I've describe above. I don't use the
switchboard so in my applications, I would probably do all of the above on
one of my menu forms. Using the builtin switchboards you could create
separate buttons which call a subroutine with the CentreId as the parameter
but you would still have to do additional design work every time you add a
new Centre.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I accept you logic. Especially after doing it the hard way by creating
new Queries, Macro's and Forms, (which, after much trial and error and
having to download SR3 Service Pack) took me hours of work. If I ever
have to add new Centres it might get a bit messy.

By the way I omitted to declare that I am a Newbie and, although I had
a good go, I couldnt implement your suggestion as, I havn't yet
acquired the knowlege required to do so. Theres so much to know but
I'm enjoying learning new things every day!

I would be very grateful if you could simplify by sugesting what kind
of Control I should create on the switchboard, how to include the name
of the Centre, pass the value into the ExamsFrm and how to use the
WhereCondition parameter of the docmd.Openform. A step by step IDIOT
LIST would be great!

Terry

Sandra Daigle said:
I do not recommend creating separate forms simply to filter data!
Instead of creating a new form for each centre just create a hidden
control on the switchboard which contains the name or Id of the current
Centre and then pass that value into the ExamsFrm using the
WhereCondition parameter of docmd.Openform - this will filter the data
that is visible when the form is opened.

docmd.OpenForm "ExamsFrm",,,"CentreID=" & me.CentreID


--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have cerated a Form, ExamsFrm, which is used to input details of
exams taken at several Centres. It dosplays details from StudentTbl
and has a SubForm which displays details from ExamsTble.

I have incorporated some VB6 code as well as some Conditional
Formatting. The code automatically updates CheckBoxes in the SubForm,
depending on exam results, and the Conditional Formatting changes
background colours, depending on CheckBox datum.

I have created a Main Switchboard with minimal Startup
options, for security reasons, which means that the user is not
allowed
to filter records.

I intend incorporating switchboard controls to access exam data
specific to each centre e.g. ExamFrmCtr1, ExamfrmCtr2, etc. and
therefore need to create a new Form for each Centre.

Is it be possible to create a new Form for each Centre by saving the
results of a 'Filter By Selection' (from ExamsFrm, Centre), as a new
Form which retains all codes, formatting, layout, etc?

Or will I have to create each Form from scratch?

Terry
Click to expand...
Click to expand...
Click to expand...
 
docmd.OpenForm "ExamsFrm",,,"CentreID=" & me.CentreID

Thanks Sandra, that did the job but an anoying Error Dialogue Box is
displayed if the button is pressed without selecting a Centre:-

Run-time error '3075'
Sytax error (Missing operator) 'CentreId='.

I understand why this is but is there any way that I can either stop
the error message or change the text it displays?

Terry
 
Hi Terry,

I would build in a little validation so you can display your own error:

if isnull(me.CentreID) then
msgbox "You must select a Centre before continuing!"
me.centreid.setfocus
else
docmd.OpenForm "ExamsFrm",,,"CentreID=" & me.CentreID
endif
 
Thanks Sandra,

Works Great! Did as you suggested with only slight adjustments:-

Private Sub Option1_Click()
If IsNull(Me.cbo1Centre) Then
MsgBox "You must select a Centre before continuing!"
Me.cbo1Centre.SetFocus
Else
DoCmd.OpenForm "ExamsFrm", acFormDS, , "CentreID=" & Me.cbo1Centre
End If
End Sub

Tried doing simmilar with a Report:-

Private Sub Option6_Click()
If IsNull(Me.cbo1Centre) Then
MsgBox "You must select a Centre before continuing!"
Me.cbo1Centre.SetFocus
Else
DoCmd.OpenReport "TrackingRpt", acPreview, , "CentreID=" &
Me.cbo3Centre
End If
End Sub

BUT!

A Message Box appears saying:
Runtime Error '3075'
Enter Parameter Value
CentreID

I click on OK and get the body of the Report but it shows no data.

My database has three tables:
----------------
StudentsTbl
PK = StudentID, (linked to StudentID ExamsTbl)
FK = CentreID, (linked to CentresTbl CentreID)
Text Field Centre, (contains centre name)
Plus other fields
----------------
ExamsTbl
PK = ExamID
FK = StudentID, (linked to StudentsTbl StudentID)
Plus other fields
----------------
CentresTbl
PK = CentreID (linked to StudentsTbl CentreID)
Text Field Centre, (contains centre name)
No other fields
----------------

TrackingRpt is based on TrackingFrm (data for TrackingFrm comes
directly from TrackingQry).
TrackingQry is based on StudentsTbl & ExamsTbl and works well.

I've tried changing the Centre data source in TrackingQry from
StudentsTbl to CentresTbl. The Report still works well but get the
same result. Any Ideas?

Terry
 
Hi Sandra,
Problem solved:-) I had to add the field for CentreID to the TrackingQry.

Thanks for all your help.

Happy New Year

Terry
 
Back
Top