Cascading Combo Boxes (1 Viewer)

Status
Not open for further replies.

ajetrumpet

Banned
Local time
Today, 15:46
Joined
Jun 22, 2007
Messages
5,638
There have been quite a few questions about this issue, so I thought I would delve into it and see if I could provide any more insight on the subject...

Apparently, there are many different ways to achieve a cascading action with form controls. All of the codes below serve the same purpose, and function in exactly the same manner...
Code:
Private Sub Control1_AfterUpdate()

  Me.Control2.RowSource = "SELECT Table.Control2Field FROM table WHERE " & _
  "Table.Control1Field = [Control1]"

End Sub
is the same as...
Code:
Private Sub Control1_AfterUpdate()

  Me.Control2.RowSource = "SELECT Table.Control2Field FROM table WHERE " & _
  "Table.Control1Field = '" & [Control1] & "'"

End Sub
is the same as...
Code:
Private Sub Control1_AfterUpdate()

  Me.Control2.RowSource = "SELECT Table.Control2Field FROM table WHERE " & _
  "Table.Control1Field = '" & Forms!FormName!Control1 & "'"

End Sub
You can also cascade a control simply by using the "Requery" action if the Rowsource is specified in the control's properties...
Code:
Private Sub Control1_AfterUpdate

  Me.Control2.Requery

End Sub
These codes are written for the sake of simplicity, but they can be expanded upon to serve any number of controls on a form.


Other issues that may arise when attempting to create cascades...

The above versions of code are associated with the form controls. However, the code populates the controls with data from recordsets (tables or queries). If a cascaded control is not functioning properly, there may be an error in the SQL statement (common when using one source table). However, if you are using multiple tables as a basis for the controls, there may be one or more of them that are JOINED (related) in such a way that does not compliment the criteria section (WHERE clause) of the Rowsource (SQL statment).

I have attached a sample that illustrates the above coding examples using list boxes. That was just a preference...I like using list boxes more than combo boxes. I guess I've just gotten attached to them. :) I also wrote some comments in the "Notes" module that gives some rather interesting (but not necessarily important) information about "cascadable controls".

I hope this proves to be a good resource (man, I'm starting to talk like a programmer...that's dangerous!!). :D :D
 

Attachments

  • Cascades.zip
    72.7 KB · Views: 950
Last edited:
Status
Not open for further replies.

Users who are viewing this thread

Top Bottom