Replacement for DLookup to autofill a form with multple values in Access

Question

I currently have a form which contains a seperate combo box that is related to each table I have created. These include:
- Business Process Area
- Title
- ReportDescription
- ProcessChain
- MultiProvider
- InfoProvider

I'm having multiple issues, but for now I'll just narrow it down to the one.
I want the form to autofill if you select a lower combo box, so for example if you select a Report Description it will fill Title and Business Process Area. I am using DLookup to do this currently and this is working fine. The issue occurs further down the form.
There are many-to-many relationships between ReportDescription and ProcessChain as well as between ProcessChain and MultiProvider. So currently you can select a MultiProvider, and it will just select the first ProcessChain and keep filling the form from there.
My Current DLookup code on my bottom combo box is:

If IsNull(cmbMultiProvider) Then
    cmbMultiProvider = DLookup("MultiProviderID", "MultiProvider", "MultiProviderID =" & Me.cmbInfoProvider.Column(2))
End If

If IsNull(cmbProcessChain) Then
    cmbProcessChain = DLookup("ProcessChainID", "ProcessChainMultiProvider", "ProcessChainID =" & Me.cmbMultiProvider.Column(2))
End If

If IsNull(cmbReportDesc) Then
    cmbReportDesc = DLookup("ReportID", "ReportDescription", "ReportID =" & Me.cmbProcessChain.Column(2))
End If

And so fourth.
So I would like to replace these DLookup statements with something that will stop at the combo box if the selection below relates to multiple of the field above, as in if a MultiProvider selected in a combo box relates to many Process Chains then the combo box will drop down and only contain the related fields.

Thank you in advance for any help.

Scott McDaniel Scott McDaniel · Accepted Answer · 2012-09-17T14:30:58

So you're going "backwards" up the cascading combos? That is, in most cases you would select these items in descending order, but you're moving up in Ascending order. For example, if you wanted to drill down to an automobile, you generally first select the Year, then the Make, then the Model, etc etc.

Instead, you want to select the Make, and have the Model auto-populate (assuming there is only one Make for that specific Model), and then have the Year left blank (since there could be several Years for a Make and Model).

If so, can you tell us more about your data structure? How are Report Description, Process Chain and MultiProvider related in your tables?

Replacement for DLookup to autofill a form with multple values in Access

1 Answers