Cascading Combo Box works in main form but not in a Navigation Page

[KerryAustin]

I have a cascading combo box that works perfectly ina main form but not in a navigation page subform. I have search and read many examples but just not having any luck getting mine to work. Here is what I have:
a combo box: cboDept which holds 5-6 department which is the parent of a cascadinging conbo box. I then have a child combo box: cboRmNum which is associated room number for a particular department. My SQL for cboDept ROWSOURCE is SELECT [tblRm].[DEPT] FROM tblRm GROUP BY [tblRm].[DEPT]; My SQL for cboRmNum ROWSOUCE is SELECT tblRm.Rm_Num FROM tblRm WHERE ((([tblRm].[Dept])=[Forms]![FormDelay].[NavigationSubForm]![cboDept])) ORDER BY tblRm.Rm_Num;

Here is a screenshot of my Navigation Page


I have a Form called frmDelay and the parent and child cascading combobox works as it should. Once I added it to the navigation page it failed and I get a "ENTER PARAMETER VALUE" message when I select child combo box. I have changes the WHERE CLAUSE to this following: WHERE ((([tblRm].[Dept])=[Forms]![FormDelay].[NavigationSubForm]![cboDept])). [tblRm].[Dept] gets the Department Name, tblRm.Rm gets the associated room numbers for that particular Department. Each department has about 3-7 rooms that procedures are performed in.

I know this has been addessed may times but I am unsuccessful getting it to work for me with their suggestions.

Thanks in advance,
Kerry

[June7]

Review https://www.accessforums.net/forms/t...orm-32053.html

Since the comboboxes are on same form, try omitting the form qualifier. It works on a normal form/subform.

WHERE [Dept]=[cboDept]

[KerryAustin]

I am going to play with it but that seamed to work. I also added tblRm.Dept and hid the field. It's aways the simplist least common way to do things. Thank you so much for the reply. I have been banging my head for hours. If it keep working tomorrow I'll close it out.

Thanks again.
Kerry

[KerryAustin]

Hello June 7...Your suggestion worked great. I have one other question regarding this form then I will close out. I would like to add a check box just above the textbox MRN and Assession Number and when the checkbox is check, disable MRN and Assession. Right now I have Dept, MRN and Assession Number required fields. If the checkbox is checked, disable MRN and Assession Number and go to fend of form and input delays. I have tried adding code to Form which is:

Form's BeforeUpdate

If Me.CheckBox Then
If Nz(Me.MRN,"") = "" Then
Cancel = True
End If
End If

Code in AfterUpdate

If Me.Checkbox Then
Me.MRN.Enabled = True
Else
Me.MRN.Enable = False
Me.MRN = ""
End If

Code in Form's Current Event

Me.MRN.Enabled = Me.Checkbox

This does not work, I get a message box indicating Dept, MRN and Assession is required.
I would also Like to add additional fliers to this once I get it working.

Thank you,'Kerry

[June7]

What purpose does the checkbox serve? Is it bound to a field? Why should user need option to enable/disable controls?

The code is setting MRN to an empty string. I never allow empty strings in table. I prefer Null if there is no data.

I don't use navigation forms.

[KerryAustin]

My boss wants to add a special procedure room delay reason when a room is delayed that is not associated to a patient. If check box is selected then the delay is not patient related and therefore no MRN (medical record number) or As session Number (procedure number) is needed. I have MRN, Dept, and As session required because users sometimes rush to get through the form. If there is a better way, please suggest. I am currently in a development and test phase. I currently made the check box bounded. If you recommend differently I can use NULL.

[June7]

If fields are set as required in the table, disabling the controls and preventing data entry won't negate that requirement. Will still get error if try to leave record without entering required data.

Bound is okay. This allows for code to set enabled state of controls when record is re-opened.

So you want user to be able to check the box to disable some controls just so they won't have to tab through those controls?

My comment about empty string and Null data has nothing to do with the checkbox.