cascading combo box setup

[tjones]

I have been reading about how to set the table up for cascading combo boxes. Some recommend breaking down into several tables, others say keep it to one table.

Mine is broken down to 5 tables (and work) but if you go to the form (split form) and make a change in design view - even something as simple as changing the width of a field - then the split table looses 2 fields information (the middle fields) but the information is still saved in the main (studentcourse) record table.

My question is how do i make it more stable so that the split form table section does not lose the information?

 

[llkhoutx]

If the split forms are not related by the master/child properties. Requery the child combo boxes on the appropriate parent form event.

 

[tjones]

Here is what I did in Relationships

StudentInfo (parent record) student ID number

CourseTaken (child record) linked by Student ID number to Parent


CourseTaken CourseID (first combo cascading combo box) linked to
CourseType table


I tried to requery after every combo in the VB but that did not help.

 

[tjones]

I tried doing me.requery for "on current" and "on load" all it did was mess up other code that was working and freeze the database.

Private Sub FilterDescriptionList()

Dim strRS As String

' Filter the list box appropriately based on the combo box selection(s)
strRS = "SELECT qryCourseDescList.UnitsAssigned, qryCourseDescList.Offered, qryCourseDescList.Prereqs FROM qryCourseDescList"
If Not IsNull(Me.cboCourseNameID) Then
strRS = strRS & " WHERE CourseNameID = " & Me.cboCourseNameID
ElseIf Not IsNull(Me.cboCourseNoID) Then
strRS = strRS & " WHERE CourseNoID = " & Me.cboCourseNoID
ElseIf Not IsNull(Me.cboDepartmentID) Then
strRS = strRS & " WHERE DepartmentID = " & Me.cboDepartmentID
ElseIf Not IsNull(Me.cboCourseTypeID) Then
strRS = strRS & " WHERE CourseTypeID = " & Me.cboCourseTypeID
End If

strRS = strRS & " ORDER BY qryCourseDescList.Offered;"

Me.lstDescriptionID.RowSource = strRS this is where it hung up

Me.lstDescriptionID.Requery

 

[llkhoutx]

Debug the code, with a break point on the strRS=strRS+.... Get the strRS value and paste it in the SQL view of a query and see if it runs.

Altgernatively, I think that you should add the table name in each of the where clauses.

I also assume that the various ID's are numeric. If not, that's a big problem.

 

[tjones]

Sorry but your are talking way over my knowledge level.

I only managed to get this working by carefully following a demo of a cascading combo box.

I understand adding the table to each of the WHERE clauses and that yes each of the various IDs are numeric.