Okay, I've enclosed a sample of the kind of data and I'm dealing with in this database. There's more to it, but for our purposes I just wanted to show the main fields.
The fields are:
WACCOUNT (Note W stands for "West")
EACCOUNT (Note E stands for "East")
I need the user to be able to input the WACCOUNT, the WSUBACCOUNT, and WSUBLEDGER (where this exists) and receive output on:
There are no direct relationships between WACCOUNTs and EACCOUNTS, WSUBACCOUNTs & ESUBACCOUNTs, or even the combination of WACCOUNT/WSUBACCOUNT & EACCOUNT/ESUBACCOUNT.
It is vital that the user input all three variables: WACCOUNT, WSUBACCOUNT, and WSUBLEDGER.
It's been suggested that I might use cascading combo-boxes--and I'll look into that. In the meantime, if someone could please offer some recommendations I would greatly appreciate this.
In an attempt to prevent some confusion....
The first field selected (WACCOUNT) should determine what the user's options are for the second (WSUBACCOUNT), and likewise the second determines the available entries for the third field (WSUBLEDGER). After all of this is entered, the user hits Enter and all of the East data is populated.
You're gonna need to bite the bullet and write some VB code attached to the combo box's OnChange event. The code should do this:
Set box B datasource. (You may also need to requery it.)
Clear box B's value.
Set boxB Enabled = True
Clear box C's value.
Set boxC Enabled = False
Set box C datasource. (You may also need to requery it.)
Clear box C's value.
Set BoxC Enabled = True
B and C should be disabled by default.
The only way I can think to do it without VB would be to have three nested layers of linked subforms showing all distinct possible options for each individual element. Your query would filter on whatever records the user had selected in each level of the form. That way Access would do the filtering and refreshing for you.
Still, the VB code will be less trouble in the long run.
And here is a way to have a second combo box (perhaps listing products) based on another combo box (perhaps listing companies). Thus after the user chooses a company, he then sees only the products for that company in the second combo box.
For criteria in the 2nd combo box (and to get to the query design mode of the combo box click on the 3 dots just to the right of the combo box's RowSource property):
And for the OnEnter property of the 2nd combo box:
And in any module:
Public Function ctlRequery()
' Purpose: Use this in OnEnter property of a combobox that's based on another control
' that may have changed or based on records that may have changed
' since the form was first opened.
On Error GoTo Err_ctlRequery
' Dim var.
Dim ctl As Control
' Set var.
Set ctl = Screen.ActiveControl
' Requery control.
For an example of dependent combo boxes you can download my sample invoices database currently at the link toward the bottom of http://www.bullschmidt.com/login.asp and then on the Invoices Dialog check out the Invoice # combo box which is based on the Rep # combo box.