Cascading lists column selection

  • Thread starter Thread starter maceslin
  • Start date Start date
M

maceslin

Although I have used Access for several years I am new to VBA coding
and am trying to enhance some of the dbases I have built.

I am trying to use cascading lists to show values using Case Select
rather than query parameters as some of the parameters have lots of
choices that I will use to create reports.

The problem is that the tables for Specific_Reports RowSource are
anywhere from 1-4 columns and the needed information is not in the
same relative position in each table

How do I change the code or combo box properties to take this into
account?

Code
Select Case avail_reports.Value
Case "Numbered Fleet"
Specifc_Reports.RowSource= "Numbered Fleet" 'this is a two
column table that works with combo box properties of column count 2,
column width 0 to hide ID number and bound column 1

Case "DOTMLPF"
Specific_Reports.RowSource="DOTMLPF Choices" ' this is a
one column table and nothing shows up in Specific Reports

Case "FMSR"
Specific_Reports. RowSource= ""FMSR Reps" ' this is
a 4 column field and nothing show up in Specifc_Rports

'I have 6 additional cases that I have not listed

End Select
 
Although I have used Access for several years I am new to VBA coding
and am trying to enhance some of the dbases I have built.

I am trying to use cascading lists to show values using Case Select
rather than query parameters as some of the parameters have lots of
choices that I will use to create reports.

The problem is that the tables for Specific_Reports RowSource are
anywhere from 1-4 columns and the needed information is not in the
same relative position in each table

How do I change the code or combo box properties to take this into
account?

Code
Select Case avail_reports.Value
Case "Numbered Fleet"
Specifc_Reports.RowSource= "Numbered Fleet" 'this is a two
column table that works with combo box properties of column count 2,
column width 0 to hide ID number and bound column 1

Case "DOTMLPF"
Specific_Reports.RowSource="DOTMLPF Choices" ' this is a
one column table and nothing shows up in Specific Reports

Case "FMSR"
Specific_Reports. RowSource= ""FMSR Reps" ' this is
a 4 column field and nothing show up in Specifc_Rports

'I have 6 additional cases that I have not listed

End Select
Click to expand...

You can modify all of those properties of a combo box - number of columns,
column widths, etc. - at runtime. You'll just need to know the specifics of
each table, which I assume you do because you already know which table
you're working with in your Case statement. Just change the combo box
properties prior to setting the RowSource:

Specific_Properties.ColumnCount = 4

and so on. Use intellisense to locate the properties; they are pretty much
the same as the names in the Properties window.

Carl Rapson
 
You can modify all of those properties of a combo box - number of columns,
column widths, etc. - at runtime. You'll just need to know the specifics of
each table, which I assume you do because you already know which table
you're working with in your Case statement. Just change the combo box
properties prior to setting the RowSource:

Specific_Properties.ColumnCount = 4

and so on. Use intellisense to locate the properties; they are pretty much
the same as the names in the Properties window.

Carl Rapson- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks- worked great but leads to another question.

How do I clear the old values in Specific_Reports when I change the
selection in Avail_Reports?
 
Thanks- worked great but leads to another question.

How do I clear the old values in Specific_Reports when I change the
selection in Avail_Reports?
Click to expand...

Do you mean you want to clear the selection in Specific_Reports? If so, in
the AfterUpdate event of Avail_Reports just do this:

Specific_Reports = Null

Do this before changing the properties (including the RecordSource property)
of Specific_Reports.

Carl Rapson
 
Do you mean you want to clear the selection in Specific_Reports? If so, in
the AfterUpdate event of Avail_Reports just do this:

Specific_Reports = Null

Do this before changing the properties (including the RecordSource property)
of Specific_Reports.

Carl Rapson- Hide quoted text -

- Show quoted text -
Click to expand...

I don't think I am changing the ResordSource property so I don't know
where to enter the last code you gave me

Here is my current code. Case FMSR is the only one not working as
expected, it keeps no value when you click in it:
Private Sub avail_reports_AfterUpdate()
On Error Resume Next

Select Case avail_reports.Value
Case "approval"
DoCmd.OpenReport "Approval Report", acViewPreview
Case "Numbered Fleet"
Specific_Reports.ColumnCount = "2"
Specific_Reports.ColumnWidths = "0"
Specific_Reports.RowSource = "Numbered Fleet"

Case "DOTMLPF"
Specific_Reports.ColumnCount = "1"
Specific_Reports.ColumnWidths = "1"
Specific_Reports.RowSource = "DOTMLPF Choices"

Case "Status"
Specific_Reports.ColumnCount = "2"
Specific_Reports.ColumnWidths = "1;0"
Specific_Reports.RowSource = "Status Choices"

Case "FMSR"
Specific_Reports.ColumnCount = "5"
Specific_Reports.ColumnWidths = "0;1;0;0;0" 'selects the
second column of 5
Specific_Reports.RowSource = "FMSR Reps" 'doesn't keep
anything in selection

Case "IO Rep"
Specific_Reports.ColumnCount = "4"
Specific_Reports.ColumnWidths = "1;0;0;0"
Specific_Reports.RowSource = "IO reps"

Case "Core Capability"
Specific_Reports.ColumnCount = "1"
Specific_Reports.ColumnWidths = "1"
Specific_Reports.RowSource = "Core Capability"


End Select

End Sub
 
I don't think I am changing the ResordSource property so I don't know
where to enter the last code you gave me

Here is my current code. Case FMSR is the only one not working as
expected, it keeps no value when you click in it:
Private Sub avail_reports_AfterUpdate()
On Error Resume Next

Select Case avail_reports.Value
Case "approval"
DoCmd.OpenReport "Approval Report", acViewPreview
Case "Numbered Fleet"
Specific_Reports.ColumnCount = "2"
Specific_Reports.ColumnWidths = "0"
Specific_Reports.RowSource = "Numbered Fleet"

Case "DOTMLPF"
Specific_Reports.ColumnCount = "1"
Specific_Reports.ColumnWidths = "1"
Specific_Reports.RowSource = "DOTMLPF Choices"

Case "Status"
Specific_Reports.ColumnCount = "2"
Specific_Reports.ColumnWidths = "1;0"
Specific_Reports.RowSource = "Status Choices"

Case "FMSR"
Specific_Reports.ColumnCount = "5"
Specific_Reports.ColumnWidths = "0;1;0;0;0" 'selects the
second column of 5
Specific_Reports.RowSource = "FMSR Reps" 'doesn't keep
anything in selection

Case "IO Rep"
Specific_Reports.ColumnCount = "4"
Specific_Reports.ColumnWidths = "1;0;0;0"
Specific_Reports.RowSource = "IO reps"

Case "Core Capability"
Specific_Reports.ColumnCount = "1"
Specific_Reports.ColumnWidths = "1"
Specific_Reports.RowSource = "Core Capability"


End Select

End Sub
Click to expand...

You're right, it's RowSource, not RecordSource. You would clear any
selection in Specific_Reports just before your Select Case block:

Specific_Reports = Null
Select Case avail_reports.Value ...

But it's not clear to me what you're asking. What do you mean by, "it keeps
no value when you click in it"?

Carl Rapson
 
Back
Top