Access form cascading combobox data doesn't display

[chholland]

First post, but have long used this site for help. I am having what I feel is a common problem, but can't find this solution on this or other forums. I have a simple form 4 combo boxes.

cboLocation
cboDiagnosis
cboApproach
cboProcedure

When selecting an option from cboLocation, the list in cboDiagnosis is filtered from tblDiagnosis based on that selection using the following code in After Update:

Private Sub cboLocation_AfterUpdate()
On Error Resume Next
cboDiagnosis.RowSource = "Select tblDiagnosis.Diagnosis, tblDiagnosis.ID " & _
"FROM tblDiagnosis WHERE tblDiagnosis.Location = " & _
"cboLocation " & _
"ORDER BY tblDiagnosis.Diagnosis;"
End Sub

This works great. Next when an item is selected from cboApproach, the choices for cboProcedure are selected back both on cboLocation and cboApproach using After Update event in cboApproach with this code:

Private Sub cboApproach_AfterUpdate()
On Error Resume Next
cboProcedure.RowSource = "Select tblProcedures.Procedure, tblProcedures.ID " & _
"FROM tblProcedures " & _
"WHERE tblProcedures.Location = cboLocation AND tblProcedures.Approach = " & _
"cboApproach " & _
"ORDER BY tblProcedures.Procedure;"
End Sub

I also use the following code to Requery the combo boxes:

Private Sub Form_Current()
Me.cboDiagnosis.Requery
Me.cboProcedure.Requery
End Sub

The main problem that I am having is that when I close the form and re-open it, the cboDiagnosis and cboProcedure combo boxes are blank even when data exists for these fields in the query and table for those records. If I tab through the fields, the data will appear. Also, it then appears for other records as well and persists when changing back and forth between records. How do I ensure that all exist data loads in these boxes when the form opens? I think I am missing something simple.

Thanks so much in advance

 

[rzw0wr]

If I underastand your problem correctly.
Your answer is in your question.

When selecting an option from cboLocation, the list in cboDiagnosis is filtered from tblDiagnosis based on that selection using the following code in After Update

And

Next when an item is selected from cboApproach, the choices for cboProcedure are selected back both on cboLocation and cboApproach using After Update event in cboApproach with this code:

The combos boxes are filtered until a selection is made in cboLocation.
Dale

 

[chholland]

Hi Dale,
Thank you so much for your reply. You are correct that the Diagnosis and Procedure combo boxes are not filtered until a selection is made in the other combo boxes. However, this is not the issue. The issue is that when I complete a record and move to the next. If I return to the prior record, the Diagnosis and Procedure fields are empty even though there is data there in the table. So it appears as though a selection has not been previously made in this field, even though it has and the data has been stored. Does this make sense?

Chris

 

[rzw0wr]

What I am saying is look at your RowSource for the combo boxes when you return to a prior record.
What is in them? Are you resetting the Rowsources to reflect the rerturn to another record.
You may (should) requery when starting over with the first combo box.

Are you getting what I am trying to say?
The queries are empty or do not contain the data you want because they are filtered.
In short, requery the first combo box in the OnClick event to see what happens.

DAle