Conditional forms

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I am a beginning Access user. I have both 2003 and 2007 versions. I
want to create a database where depending on the item selected in a
dropdown list or checkbox, the user is navigated to a different form
which is specific to that selection. In other words, if a patient has
a pacemaker procedure the next form would ask for information specific
to that procedure as opposed to another procedure. I have read 2
books and none of them cover this type of programming.
Any help would be greatly appreciated.
Frank
 
You can use a combo box to load a form by having the rowsource of the combo
box point to a table that contains the list of procedures and forms you want
to load. For instance, it might look like this:

Table tblProcedureForms:

FieldName Description
---------------------------------------------------
SystemID AutoNumber field (primary key)
Procedure The procedure the person had
FormName Name of the form for that procedure

You then create a form called frmPickProcedure that has the following
controls:

1. A combo box (cboProcedure) that queries the tblProcedureForms table
using this SQL string:

SELECT tblProcedureForms.SystemID, tblProcedureForms.Procedure,
tblProcedureForms.FormName
FROM tblProcedureForms
ORDER BY tblProcedureForms.Procedure;

The second column (tblProcedureForms.Procedures) is the bound column and is
also the only column shown in the combo box (column widths = 0";2.1042";0")

2. A "Cancel" button so the user can close the form if they change their mind

3. An "OK" command button that has the following code in it's OnClick event:

******** Begin Code ************
Private Sub cmdOK_Click

Dim strFormName as String
Dim strProcedureName as String

' Grab the procedure that the user selected
strProcedureName = Me.cboProcedure

' If the user clicks OK without picking a procedure
' send them back
If IsNull(strProcedureName) Then
prompt = "You must select a procedure"
style = vbOKOnly + vbCritical
title = "No procedure selected!"
MsgBox prompt, style, title
Me.cboProcedure.SetFocus
Else
' The user has selected a procedure
' Load that form
strFormName = Me.cboAvailableReports.Column(2)
DoCmd.OpenForm strFormName
End If

End Sub
******** Begin Code ************

(Note: I have not included error handling for the sake of brevity. As
well, you can add other things to this, like closing the form after the
procedure form has loaded.)

This all may sound very complicated, but it really isn't. It shouldn't take
long to create the table and form. You could also create another form to
manage the tblProcedures table (i.e. if you want to add, delete or edit a
procedure). Then, it's just a matter of building the various forms for all
the procedures.

Btw, the above will become somewhat unmanageable if you have alot of
procedures, mainly because of the number of procedure forms you'll have to
design. It might be possible to create a generic procedure form that's
populated with different controls (text boxes, etc.) depending on the
procedure selected by the user. In that case, you'd use a design similar to
the above, but would load one form and use the table to contain the controls
to populate that form with. However, doing that is beyond my current skill
level.

Hope this has helped, or at least gotten you thinking about how you can do it.

Regards, Chris
 
You can use a combo box to load a form by having the rowsource of the combo
box point to a table that contains the list ofproceduresand forms you want
to load.  For instance, it might look like this:

Table tblProcedureForms:

FieldName         Description
---------------------------------------------------
SystemID          AutoNumber field (primary key)
Procedure         The procedure the person had
FormName        Name of the form for that procedure

You then create a form called frmPickProcedure that has the following
controls:

1.  A combo box (cboProcedure) that queries the tblProcedureForms table
using this SQL string:

SELECT tblProcedureForms.SystemID, tblProcedureForms.Procedure,
tblProcedureForms.FormName
FROM tblProcedureForms
ORDER BY tblProcedureForms.Procedure;

The second column (tblProcedureForms.Procedures) is the bound column and is
also the only column shown in the combo box (column widths = 0";2.1042";0")

2.  A "Cancel" button so the user can close the form if they change their mind

3.  An "OK" command button that has the following code in it's OnClick event:

******** Begin Code ************
Private Sub cmdOK_Click

    Dim strFormName as String
    Dim strProcedureName as String

    ' Grab the procedure that the user selected
    strProcedureName = Me.cboProcedure

    ' If the user clicks OK without picking a procedure
    ' send them back
    If IsNull(strProcedureName) Then
        prompt = "You must select a procedure"
        style = vbOKOnly + vbCritical
        title = "No procedure selected!"
        MsgBox prompt, style, title
        Me.cboProcedure.SetFocus
    Else
        ' The user has selected a procedure
        ' Load that form
        strFormName = Me.cboAvailableReports.Column(2)
        DoCmd.OpenForm strFormName
    End If

End Sub
******** Begin Code ************

(Note:  I have not included error handling for the sake of brevity.  As
well, you can add other things to this, like closing the form after the
procedure form has loaded.)

This all may sound very complicated, but it really isn't.  It shouldn'ttake
long to create the table and form.  You could also create another form to
manage the tblProcedures table (i.e. if you want to add, delete or edit a
procedure).  Then, it's just a matter of building the various forms forall
theprocedures.

Btw, the above will become somewhat unmanageable if you have alot ofprocedures, mainly because of the number of procedure forms you'll have to
design.  It might be possible to create a generic procedure form that's
populated with different controls (text boxes, etc.) depending on the
procedure selected by the user.  In that case, you'd use a design similar to
the above, but would load one form and use the table to contain the controls
to populate that form with.  However, doing that is beyond my current skill
level.

Hope this has helped, or at least gotten you thinking about how you can do it.

Regards,  Chris





- Show quoted text -
Click to expand...

Thank you. That was very helpful. A little over my head but I'm a
quick learner!
 
Back
Top