Open Form based on value of control

  • Thread starter Thread starter Rajesh Nathani
  • Start date Start date
R

Rajesh Nathani

Access 2000. I have a form in which I have a control
diagnosisID. I also have a command button on the form
that when clicked should open other forms depending on
the value in the diagnosisID. The type of control in the
underlying table is AutoNumber. I have so far worked out
the following code:
'---------------------------------------------------------
---
' cmdDetails_Click
'
'---------------------------------------------------------
---
Private Sub cmdDetails_Click()
On Error GoTo cmdDetails_Click_Err

If "[Forms]![frmFollowUpsubform]![diagnosis ID]= 6"
Then
DoCmd.OpenForm "frmHirschsprung's", , , ,
acFormEdit, acWindowNormal
Else
Exit Sub
End If

cmdDetails_Click_Err:
MsgBox Err.Number & vbCr & Err.Description
End Sub

This code however gives me a type mismatch error.
Appreciate any help
 
Rajesh,

Try changing this:

If "[Forms]![frmFollowUpsubform]![diagnosis ID]= 6"

to this:

If [Forms]![frmFollowUpsubform]![diagnosis ID]= 6 then

Post back and let us know if that works.
 
Ok, you can either employ the following, fairly static approach, by
hard-coding in your forms:
Select Case Me.DiagnosisID
Case = 6
DoCmd.OpenForm "frmHirschsprungs"
Case Else
'Do something here
End Select

Or, you can employ a more dynamic approach & actually store the form name in
a column of your table:
Within your diagnosis table, add a column named DetailsForm. Add this column
to the rowsrouce for your diagnosis combo, although it can remain hidden.

Then add code along the lines of the following to your command button:

If IsNull(Me.DiagnosisID.Column(2) )
Msgbox "No form associated with this diagnosis!"
Exit Sub
Else
DoCmd.OpenForm Me.DiagnosisID.Column(2)
End If

This approach is far more dynamic & means no new code needs to be written as
you add new diagnoses (sp?), because you add the associated form name to the
diagnosis table.
 
Thank you Cheryl and James for your prompt replies. I
decided to go with the elegant dynamic approach of James.
However after doing all that you have suggested, I get
the following message. The object doesn't contain the
automation object tblFollowUp etc etc. This is way beyond
my capabilities. Kindly help.
Regards,
Rajesh
 
Ok,

I presume you have a table called diagnosis, which contains the data for any
given DiagnosisID?
This being so, you need to add a column, called DetailsForm. Make its
datatype text.
I then presume on your form you have a combo box which allows the user to
select a text diagnosis, but stores the DiagnosisID in the associated
column?
This being so, the rowsource for the DiagnosisID combo box will probably be
two columns from the Diagnosis table (DiagnosisID & Diagnosis?). You need to
add the newly created column to the combo box, so that its rowsource is
something like:
SELECT DiagnosisID, Diagnosis, DetailsForm from tblDiagnosis
You then need to modify the column count property to 3 (assuming it had 2
previously).

This should then, in a hidden way associate all records with a DiagnosisID
with a corresponding details form.

The code I then posted should work.

If it doesnt, you will need to be more specific about your setup...
 
Thanks again for your input.
I have a table called DIAGNOSIS. It has 3 columns:
diagnosisID Autonumber
DIAGNOSIS Text
DetailsForm Text
The rowsource of the combo box is
SELECT DISTINCTROW [DIAGNOSIS].[diagnosis ID],
[DIAGNOSIS].[DIAGNOSIS], [DIAGNOSIS].[DetailsForm] FROM
[DIAGNOSIS];
and this is the code on the onclick event of the command
button on the form frmFollowUpsubform:
Private Sub cmdDetails_Click()
If IsNull(Me.DiagnosisID.Column(3)) Then
MsgBox "No form associated with this diagnosis!"
Exit Sub
Else
DoCmd.OpenForm Me.DiagnosisID.Column(3)
End If




End Sub
I still get the message: The object does not contain the
Automation Object 'tblFollowUp.'
I am sorry to continue to bother you with this. My
thoughts: In the msgbox notice the full stop at the end
of tblFollowUp. Could that in anyway be responsible?
Otherwise it seems exactly like you have suggested.
 
Back
Top