Making boxes availble depending on combo box entry

T

Thorson

So I really have no clue how to write this code. I tried copying a similar
code from a different form, however that is for a check box and this is a
little different.

I currently have a combo box where the user selects what report they would
like to view. Depending on which report they view they need to fill in 1 or
more of the 4 boxes of criteria. I want the boxes to only be available if
the information is required so I tried writing this code but it obviously
isn't working:


Private Sub cboViewReport_AfterUpdate()
Select Case cboViewReport
Case "Current Inventory By Unit"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Case "Current Inventory"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "Open Therapeutic Cases"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Case "Check Withdrawal Dates"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Case "Monthly Protocol Report"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.cboMonth.Enabled = (Me.cboViewReport)
Me.cboYear.Enabled = (Me.cboViewReport)
Case "ASA Calf Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "AAA Pre-Birth Breeding Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "ASA Pre-Birth Breeding Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "AAA Calf Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "Monthly Inventory Report"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Me.cboMonth.Enabled = (Me.cboViewReport)
Me.cboYear.Enabled = (Me.cboViewReport)
Case Else
MsgBox "Invalid entry in View Report"
End Select
End Sub
 
J

Jeff Boyce

You may be confusing what is displayed in your combobox with what the
combobox STORES. Check the underlying table to see what values are being
stored.

You may need to change your Select Case statement to reflect that stored
value...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
B

Beetle

Assuming that the report name is the bound column of
the combo box, you could enter the appropriate report
name in the Tag property of each of the relevant controls,
then use code like the following;

Private Sub cboViewReport_AfterUpdate()

Dim ctl As Control

For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox
If ctl.Tag <> "" Then
ctl.Enabled = (ctl.Tag = Me!cboViewReport)
End If
End Select
Next ctl

End Sub
 
T

Thorson

This is a form that is used to open reports, there are 4 combo boxes
available for the users, however, not all are required for each form. I want
it to be obvious to my users which combo boxes need to be have data (ex.
month, unit etc).

I want it so that if the user selects to see "current Inventory By Unit"
then only the boxes "txtdate" and "lstUnit" are available and the other 2
boxes will be grayed out.
 
J

Jeff Boyce

I don't see a response to my question about what data is actually being
stored...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

Thorson

I guess I don't understand your question. I am very new to Access. The form
was not created from a table, and from what I understand there is therefore
no underlying table where values are stored. The values in the combo boxes
are constant, one is the months (month: 1,2,3,...,12).
 
J

Jeff Boyce

If the form has no underlying table (or query), the data on it is not being
stored, right?

So you're only using this to identify the report to be run? What is the
source for your combobox? Is it simply a list of report names?

Are those "report names" the user-friendly version, or are they the version
as named in your Access database (these could be the same)?

The code you provided to start with appears to be trying to enable certain
selection criteria for each report.

So why are you setting the value of [YourControl].Enabled to equal the name
of the report? Don't you want to be setting those to either true or false?

And how are you "clearing" those controls after running the report (i.e.,
where are you setting them to false)?

One more thing. I don't see how/where you are telling Access to actually
run a report. I see the 'set the selection criteria', but what do you do
with what the user enters there? How do you use those criteria to filter
the report?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

Thorson

Yes, there is no underlying table or query.

Yes I am only using the form to identify the report to be run.

The source of the comboboxes is simply a list of report names.

The report names are a User-Friendly version, I have code written in for the
"Ok" button onclick that when a specific user-friendly report name is chosen
that it will open up the Access database report.

I don't really understand what the code is "saying" or "doing", for example
this is what I assumed for this code:
Case "Monthly Protocol Report"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.cboMonth.Enabled = (Me.cboViewReport)
Me.cboYear.Enabled = (Me.cboViewReport)

That if the user selected "Monthly Protocol Report" in the cboViewReport
then the text box txtDate and combo boxes cboMonth and cboYear would all be
enabled and the fourth box on the form lstUnit would not be enabled. I'm not
sure what happens when you change it to true or false as opposed to the name
of the report... like I said I am very new at all this.

The selection in the form remains there until the user changes it, so I
guess the controls are not "cleared". I used to have the form close after
the report was opened, however, if the user wanted to change the report to a
PDF they would have to re-enter everything in the form since the report had
to re-run the queries.

The queries that are used to make the reports have the controls in this form
listed in their criteria of the query. On the form there is a "Ok" button,
this button has the code written to open the reports, this is seen below:
Private Sub cmdOK_Click()
If IsNull(cboViewReport) Then
MsgBox "Please Select Report to View"
Else
Select Case cboViewReport
Case "Current Inventory By Unit"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenQuery "qryCurrentInventory2"
End If
Case "Current Inventory"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenQuery "qryCurrentInventory2AllAnimals"
End If
Case "Open Therapeutic Cases"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenReport "rptOpenTherapeuticCases", acViewPreview
End If
Case "Check Withdrawal Dates"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenReport "rptWithdrawalCheckDates", acViewPreview
End If
Case "Monthly Protocol Report"
If IsNull(txtDate) Or IsNull(cboMonth) Or IsNull(cboYear) Then
MsgBox "Please Select Current Inventory Date, Month and Year"
Else
DoCmd.OpenReport "rptMonthlyReportPG2Protocol", acViewPreview
End If
Case "ASA Calf Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptCalfPedigree", acViewPreview
End If
Case "AAA Pre-Birth Breeding Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptAAAPreBirthEPDPedigree", acViewPreview
End If
Case "ASA Pre-Birth Breeding Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptASAPreBirthEPDPedigree", acViewPreview
End If
Case "AAA Calf Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptCalfPedigreeAAA", acViewPreview
End If
Case "Registered Female ASA EPDs"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptRegisteredFemaleEPD", acViewPreview
End If
Case "Registered Female AAA EPDs"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptRegisteredFemaleEPDAAA", acViewPreview
End If
Case "Purebred Breeding Herd Info"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptPurebredBreedingHerdInfo", acViewPreview
End If
Case "Monthly Inventory Report"
If IsNull(txtDate) Or IsNull(lstUnit) Or IsNull(cboMonth) Or
IsNull(cboYear) Then
MsgBox "Please Select Current Inventory Date, Unit, Month and Year"
Else
Select Case lstUnit
Case "DSAC"
MsgBox "You are about to run a Monthly Inventory Report for DSAC; it
may take a few moments for the report to open."
DoCmd.OpenReport "rptMonthlyReportPG1DSAC", acViewPreview
Case "ORR"
MsgBox "You are about to run a Monthly Inventory Report for ORR; it
may take a few moments for the report to open."
DoCmd.OpenReport "rptMonthlyReportPG1ORR", acViewPreview
Case "URB"
MsgBox "You are about to run a Monthly Inventory Report for Urbana;
it may take a few moments for the report to open."
DoCmd.OpenReport "rptMonthlyReportPG1URB", acViewPreview
Case Else
MsgBox "Invalid entry in Unit"
End Select
End If
Case Else
MsgBox "Invalid entry in View Report"
End Select
End If
End Sub


Hopefully this provides enough info, please let me know if something doesn't
make sense. I appreciate your help.
Thank you!

--
Thorson


Jeff Boyce said:
If the form has no underlying table (or query), the data on it is not being
stored, right?

So you're only using this to identify the report to be run? What is the
source for your combobox? Is it simply a list of report names?

Are those "report names" the user-friendly version, or are they the version
as named in your Access database (these could be the same)?

The code you provided to start with appears to be trying to enable certain
selection criteria for each report.

So why are you setting the value of [YourControl].Enabled to equal the name
of the report? Don't you want to be setting those to either true or false?

And how are you "clearing" those controls after running the report (i.e.,
where are you setting them to false)?

One more thing. I don't see how/where you are telling Access to actually
run a report. I see the 'set the selection criteria', but what do you do
with what the user enters there? How do you use those criteria to filter
the report?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
J

Jeff Boyce

You need to set the .Enabled property to either True or False. Setting it
to the name of the selected report does nothing to help.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Thorson said:
Yes, there is no underlying table or query.

Yes I am only using the form to identify the report to be run.

The source of the comboboxes is simply a list of report names.

The report names are a User-Friendly version, I have code written in for
the
"Ok" button onclick that when a specific user-friendly report name is
chosen
that it will open up the Access database report.

I don't really understand what the code is "saying" or "doing", for
example
this is what I assumed for this code:
Case "Monthly Protocol Report"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.cboMonth.Enabled = (Me.cboViewReport)
Me.cboYear.Enabled = (Me.cboViewReport)

That if the user selected "Monthly Protocol Report" in the cboViewReport
then the text box txtDate and combo boxes cboMonth and cboYear would all
be
enabled and the fourth box on the form lstUnit would not be enabled. I'm
not
sure what happens when you change it to true or false as opposed to the
name
of the report... like I said I am very new at all this.

The selection in the form remains there until the user changes it, so I
guess the controls are not "cleared". I used to have the form close after
the report was opened, however, if the user wanted to change the report to
a
PDF they would have to re-enter everything in the form since the report
had
to re-run the queries.

The queries that are used to make the reports have the controls in this
form
listed in their criteria of the query. On the form there is a "Ok"
button,
this button has the code written to open the reports, this is seen below:
Private Sub cmdOK_Click()
If IsNull(cboViewReport) Then
MsgBox "Please Select Report to View"
Else
Select Case cboViewReport
Case "Current Inventory By Unit"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenQuery "qryCurrentInventory2"
End If
Case "Current Inventory"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenQuery "qryCurrentInventory2AllAnimals"
End If
Case "Open Therapeutic Cases"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenReport "rptOpenTherapeuticCases", acViewPreview
End If
Case "Check Withdrawal Dates"
If IsNull(txtDate) Or IsNull(lstUnit) Then
MsgBox "Please Select Current Inventory Date and Unit"
Else
DoCmd.OpenReport "rptWithdrawalCheckDates", acViewPreview
End If
Case "Monthly Protocol Report"
If IsNull(txtDate) Or IsNull(cboMonth) Or IsNull(cboYear) Then
MsgBox "Please Select Current Inventory Date, Month and Year"
Else
DoCmd.OpenReport "rptMonthlyReportPG2Protocol", acViewPreview
End If
Case "ASA Calf Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptCalfPedigree", acViewPreview
End If
Case "AAA Pre-Birth Breeding Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptAAAPreBirthEPDPedigree", acViewPreview
End If
Case "ASA Pre-Birth Breeding Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptASAPreBirthEPDPedigree", acViewPreview
End If
Case "AAA Calf Pedigree"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptCalfPedigreeAAA", acViewPreview
End If
Case "Registered Female ASA EPDs"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptRegisteredFemaleEPD", acViewPreview
End If
Case "Registered Female AAA EPDs"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptRegisteredFemaleEPDAAA", acViewPreview
End If
Case "Purebred Breeding Herd Info"
If IsNull(txtDate) Then
MsgBox "Please Select Current Inventory Date"
Else
DoCmd.OpenReport "rptPurebredBreedingHerdInfo", acViewPreview
End If
Case "Monthly Inventory Report"
If IsNull(txtDate) Or IsNull(lstUnit) Or IsNull(cboMonth) Or
IsNull(cboYear) Then
MsgBox "Please Select Current Inventory Date, Unit, Month and Year"
Else
Select Case lstUnit
Case "DSAC"
MsgBox "You are about to run a Monthly Inventory Report for DSAC;
it
may take a few moments for the report to open."
DoCmd.OpenReport "rptMonthlyReportPG1DSAC", acViewPreview
Case "ORR"
MsgBox "You are about to run a Monthly Inventory Report for ORR; it
may take a few moments for the report to open."
DoCmd.OpenReport "rptMonthlyReportPG1ORR", acViewPreview
Case "URB"
MsgBox "You are about to run a Monthly Inventory Report for Urbana;
it may take a few moments for the report to open."
DoCmd.OpenReport "rptMonthlyReportPG1URB", acViewPreview
Case Else
MsgBox "Invalid entry in Unit"
End Select
End If
Case Else
MsgBox "Invalid entry in View Report"
End Select
End If
End Sub


Hopefully this provides enough info, please let me know if something
doesn't
make sense. I appreciate your help.
Thank you!

--
Thorson


Jeff Boyce said:
If the form has no underlying table (or query), the data on it is not
being
stored, right?

So you're only using this to identify the report to be run? What is the
source for your combobox? Is it simply a list of report names?

Are those "report names" the user-friendly version, or are they the
version
as named in your Access database (these could be the same)?

The code you provided to start with appears to be trying to enable
certain
selection criteria for each report.

So why are you setting the value of [YourControl].Enabled to equal the
name
of the report? Don't you want to be setting those to either true or
false?

And how are you "clearing" those controls after running the report
(i.e.,
where are you setting them to false)?

One more thing. I don't see how/where you are telling Access to actually
run a report. I see the 'set the selection criteria', but what do you do
with what the user enters there? How do you use those criteria to filter
the report?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thorson said:
I guess I don't understand your question. I am very new to Access. The
form
was not created from a table, and from what I understand there is
therefore
no underlying table where values are stored. The values in the combo
boxes
are constant, one is the months (month: 1,2,3,...,12).
--
Thorson


:

I don't see a response to my question about what data is actually
being
stored...

Regards

Jeff Boyce
Microsoft Office/Access MVP

This is a form that is used to open reports, there are 4 combo boxes
available for the users, however, not all are required for each
form.
I
want
it to be obvious to my users which combo boxes need to be have data
(ex.
month, unit etc).

I want it so that if the user selects to see "current Inventory By
Unit"
then only the boxes "txtdate" and "lstUnit" are available and the
other
2
boxes will be grayed out.

--
Thorson


:

You may be confusing what is displayed in your combobox with what
the
combobox STORES. Check the underlying table to see what values are
being
stored.

You may need to change your Select Case statement to reflect that
stored
value...

Regards

Jeff Boyce
Microsoft Office/Access MVP


So I really have no clue how to write this code. I tried copying
a
similar
code from a different form, however that is for a check box and
this
is
a
little different.

I currently have a combo box where the user selects what report
they
would
like to view. Depending on which report they view they need to
fill
in
1
or
more of the 4 boxes of criteria. I want the boxes to only be
available
if
the information is required so I tried writing this code but it
obviously
isn't working:


Private Sub cboViewReport_AfterUpdate()
Select Case cboViewReport
Case "Current Inventory By Unit"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Case "Current Inventory"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "Open Therapeutic Cases"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Case "Check Withdrawal Dates"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Case "Monthly Protocol Report"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.cboMonth.Enabled = (Me.cboViewReport)
Me.cboYear.Enabled = (Me.cboViewReport)
Case "ASA Calf Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "AAA Pre-Birth Breeding Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "ASA Pre-Birth Breeding Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "AAA Calf Pedigree"
Me.txtDate.Enabled = (Me.cboViewReport)
Case "Monthly Inventory Report"
Me.txtDate.Enabled = (Me.cboViewReport)
Me.lstUnit.Enabled = (Me.cboViewReport)
Me.cboMonth.Enabled = (Me.cboViewReport)
Me.cboYear.Enabled = (Me.cboViewReport)
Case Else
MsgBox "Invalid entry in View Report"
End Select
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code using list box and multi select option 8
Need help making query with criteria based on combo box selections 18

Top