Show controls when value is selected in combo box

J

jped

I have a form where I would like to show a combo box and a text field only
when a value is selected from a seperate combo box. I have the the combo box
and text box hidden when the form is loaded, but I would like them to show up
when the user selects an item in another combo box. Any ideas?

Thanks!
 
6

'69 Camaro

Hi.
I have a form where I would like to show a combo box and a text field only
when a value is selected from a seperate combo box. I have the the combo box
and text box hidden when the form is loaded, but I would like them to show up
when the user selects an item in another combo box.
Click to expand...

In the other (visible) combo box's OnAfterUpdate( ) event, place code such
as the following:

If (Me!MyCombo.Column(1) = "Show All") Then
Me!cboHidden.Visible = True
Me!txtHidden.Visible = True
Else
Me!cboHidden.Visible = False
Me!txtHidden.Visible = False
End If

.... where MyCombo is the name of the other (visible) combo box, cboHidden
and txtHidden are the names of the hidden combo box and text box,
respectively, Column(1) is the 2nd column of the record source of the combo
box as the displayed column, and "Show All" is what is displayed in the other
(visible) combo box.

The hidden combo box and text box will be revealed when "Show All" is
selected in the other (visible) combo box, but otherwise hidden when any
other selection is made in that combo box.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are [email protected] and [email protected]

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.
 
S

Sprinks

Hi, jped.

Set the Visible property in the AfterUpdate event procedure of the first
combo box:

If YourFirstComboBox = YourValue Then
Me!YourSecondComboBox.Visible = True
Me!YourTextBox.Visible = True
End If

You will also need to reset the statuses as appropriate in the form's
OnCurrent event:

If YourFirstComboBox = YourValue Then
Me!YourSecondComboBox.Visible = True
Me!YourTextBox.Visible = True
Else
Me!YourSecondComboBox.Visible = False
Me!YourTextBox.Visible = False
End If

Also, remember that the *value* of a combo box is determined by its
BoundColumn property. This may be different from what is *displayed*, which
is determined by the ColumnWidths property. An easy way to determine the
combo box value is to temporarily insert a Msgbox statement in the
AfterUpdate event.

MsgBox Me!MyFirstComboBox

Sprinks
 
6

'69 Camaro

Hi, Kevin.

Good catch on the form's OnCurrent( ) event to return the hidden combo box
and text box to the state they should be in for that record. I was using an
unbound form to play with and forgot about navigating through bound records.

Gunny
 
J

jped

Thanks for your help! It works, but if I choose a value other than the one
that displays the hidden combo box and text field, I get an error that says:

You cannot add or change a record because a related record is required in
'tblDisbursement'.

tblDisbursement is the record source for the hidden combo box. Any idea on
how to correct this?

Thanks!
 
J

jped

Thanks for your help! It works, but if I choose a value other than the one
that displays the hidden combo box and text field, I get an error that says:

You cannot add or change a record because a related record is required in
'tblDisbursement'.

tblDisbursement is the record source for the hidden combo box. Any idea on
how to correct this?

Thanks!
 
S

Sprinks

Hi, jped. Sorry you're having trouble.

This error normally occurs when it is attempted to post a child record when
no corresponding parent record exists.

When you say "tblDisbursement is the record source for the hidden combo
box", it is confusing because a combo box doesn't have a RecordSource
property. I presume you meant the RowSource.

Please post the RowSource and ControlSource properties of the first combo
box, the RecordSource of the main form, the LinkMasterFields, LinkChildFields
of any subform controls, and the RecordSource properties of any subforms.

Sprinks
 
J

jped

I'm sorry, I meant row source. I caught that after I made the post!

First Combo box (not hidden):
Row and control source are from tblPaymentMethods (ltable with list of
payment methods)

RecordSource for Main Form:
All fields for tblPurchaseOrder (main table)

Subform:
LinkMasterField and ChildMasterField is PurchaseOrderID
RecordSource is tblOrderDetails

Just an FYI, when I removed Enforce Referential Integrity from the
relationship between tbleDisbursement (table behind hidden combo box) and
tblPurchaseOrder (main table) I didn't get the error and it seemed to work
fine. I'm a little wary of this though. Hope this helps! Thanks again for
all your help so far! Let me know if you need any more infor.

Thanks!
 
S

Sprinks

A couple of things:

- The ControlSource of a control on the main form is either an expression
(i.e., a calculated field) or a *field* in the table or query specified in
the form's RecordSource property into which data entered into the control is
to be stored. So it is likely a PaymentMethod field in the PurchaseOrder
table, not a field in tblPaymentMethods nor the table itself as you indicated.

- What do you mean by saying that tblDisbursement is "behind" the hidden
combo box? Are these hidden controls on a subform or the main form? Please
post the ControlSource, RowSource, and BoundColumn properties, and the. What
is this table, and what data are you trying to store in the purchase order
table?

I agree with you--if there IS a relationship between tblDisbursement and
tblPurchaseOrder, then you have compromised referential integrity by deleting
it. After we solve the underlying problem, you should be able to redefine it.

Sprinks
 
J

jped

I'm sorry, the hidden combo box gets its values from tblDisbursements, but
the value that is selected is stored in Disbursements in the
tblPurchaseOrders (main table). These controls are on the main form, not the
subform.

I am trying to store the value selected from the Disbursement combo box,
PaymentMethod combo box, as well as some other general text boxes on the main
form, which all work correctly. The hidden Disbursement combo box is a new
addition to the database.

Disbursement combo box (hidden)
ControlSource= DisbursementID (This is the ID number associated with the
Disbursement value from the combo box and is being store in the PurchaseOrder
table. The field in tblPurchaseOrder is called DisbursementID)
RowSource= SELECT tblDisbursement.DisbursementID, tblDisbursement.Disbursement
FROM tblDisbursement;
(It's selecting the values from tblDisbursement, which I'm sure you can see)
BoundColumn= 1

Do you need more info? Thank you so much for helping me out!
 
S

Sprinks

Hi, jped.

OK, I get it now--sometimes takes me a while!

Since tblPurchaseOrder was on the many side of a one-to-many with
Disbursements, Access would not let you enter a child record without
establishing to which parent in Disbursements it should belong.

Not all PurchaseOrder records will have a disbursement associated with it;
only those of the certain type. One solution is the one you implemented. If
you'd like to enforce referential integrity, every record must have a
Disbursement value, so I suggest adding a record to the Disbursement table
with the description "No Disbursement", and setting the value in the payment
combo box' AfterUpdate event.

I also realized that it's possible for the user to change the payment type,
so you should set the visibility status either way, just like as in the On
Current event:

If YourFirstComboBox = YourValue Then
Me!YourSecondComboBox.Visible = True
Me!YourTextBox.Visible = True
Else
Me!YourSecondComboBox.Visible = False
Me!YourTextBox.Visible = False
Me!YourSecondComboBox = YourNoDisbursementID
End If

One other condition that could occur is that your user picks the payment
type that requires a disbursement, and they do not enter a value, which would
violate referential integrity. To avoid Access' cryptic message, intercept
it with the form's BeforeUpdate event. If you wish to permit the user to
enter no disbursement in this case, show a message box. If they confirm no
disbursement, you can set the value to the ID associated with "No
Disbursement" and continue, otherwise, cancel and return him to the combo box:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intRetVal As Integer
If Nz([cboDisbursement]) = 0 Then
intRetVal = MsgBox("You did not enter a disbursement. Press Cancel
to enter a disbursement or OK to continue.", vbOKCancel)
If intRetVal = vbCancel Then
Cancel = True
Me!cboDisbursement.SetFocus
Else
' Set to "No Disbursement"
Me!cboDisbursement = YourNoDisbursementID
End If
End If
End Sub

Sprinks
 
J

jped

That worked great! Thanks! But now (of course) my other combo boxes are
having errors. I have a form where the user chooses a vendor to create the
purchase order. Once they choose the vendor, it takes them to that main
PurchaseOrder form. Now when that opens, I have an error that states:

You cannot add or change a record because a related record is required in
table 'tblEmployees'.

This is the same problem. I have two other drop down boxes, one where they
choose the employee that is creating the order and the other one where they
choose which department the order is for. Once I choose one for each, the
system works fine. The values come from seperate tables and are stored in
the PurchaseOrder table, just like PaymentMethods and Disbursement. Any
ideas?

But the Disbursement part of it works great! I can't thank you enough! Let
me know if you need any more info.

Thanks!

Sprinks said:
Hi, jped.

OK, I get it now--sometimes takes me a while!

Since tblPurchaseOrder was on the many side of a one-to-many with
Disbursements, Access would not let you enter a child record without
establishing to which parent in Disbursements it should belong.

Not all PurchaseOrder records will have a disbursement associated with it;
only those of the certain type. One solution is the one you implemented. If
you'd like to enforce referential integrity, every record must have a
Disbursement value, so I suggest adding a record to the Disbursement table
with the description "No Disbursement", and setting the value in the payment
combo box' AfterUpdate event.

I also realized that it's possible for the user to change the payment type,
so you should set the visibility status either way, just like as in the On
Current event:

If YourFirstComboBox = YourValue Then
Me!YourSecondComboBox.Visible = True
Me!YourTextBox.Visible = True
Else
Me!YourSecondComboBox.Visible = False
Me!YourTextBox.Visible = False
Me!YourSecondComboBox = YourNoDisbursementID
End If

One other condition that could occur is that your user picks the payment
type that requires a disbursement, and they do not enter a value, which would
violate referential integrity. To avoid Access' cryptic message, intercept
it with the form's BeforeUpdate event. If you wish to permit the user to
enter no disbursement in this case, show a message box. If they confirm no
disbursement, you can set the value to the ID associated with "No
Disbursement" and continue, otherwise, cancel and return him to the combo box:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim intRetVal As Integer
If Nz([cboDisbursement]) = 0 Then
intRetVal = MsgBox("You did not enter a disbursement. Press Cancel
to enter a disbursement or OK to continue.", vbOKCancel)
If intRetVal = vbCancel Then
Cancel = True
Me!cboDisbursement.SetFocus
Else
' Set to "No Disbursement"
Me!cboDisbursement = YourNoDisbursementID
End If
End If
End Sub

Sprinks

jped said:
I'm sorry, the hidden combo box gets its values from tblDisbursements, but
the value that is selected is stored in Disbursements in the
tblPurchaseOrders (main table). These controls are on the main form, not the
subform.

I am trying to store the value selected from the Disbursement combo box,
PaymentMethod combo box, as well as some other general text boxes on the main
form, which all work correctly. The hidden Disbursement combo box is a new
addition to the database.

Disbursement combo box (hidden)
ControlSource= DisbursementID (This is the ID number associated with the
Disbursement value from the combo box and is being store in the PurchaseOrder
table. The field in tblPurchaseOrder is called DisbursementID)
RowSource= SELECT tblDisbursement.DisbursementID, tblDisbursement.Disbursement
FROM tblDisbursement;
(It's selecting the values from tblDisbursement, which I'm sure you can see)
BoundColumn= 1

Do you need more info? Thank you so much for helping me out!
Click to expand...
Click to expand...
 
Top