Can you Show/Hide fields in a form based on data in another table?

[Foyer]

Hi,

I want to show a field based on data in another table. On my order form, if I select a customer name, and that customer is listed as a distributor in the customers table (Distributor checkbox = Yes), then I would like a particular field to be visible on my order form.

I have found tutorials where you can show or hide fields based on data in the current table. Can you do it with data from a different table?



Thank you.

[davegri]

Check out the DLookup function.

[Foyer]

Thanks. So, would the process be 1) add the distributor checkbox as a column to the customer name combobox on my form 2) add a text box using DLookup to extract the distributor checkbox 3) use the value in the text box as the basis for my visible code?

[davegri]

foyer-davegri-v01.zip

Have a look at the quick and dirty attachment.

[Foyer]

This wasn't exactly what I needed but your code helped me build my code with just a little tweak, so thank you!

Private Sub cboCustomer_AfterUpdate()
If Me.cboCustomer.Column(2) = True Then
Me.txtAnother.Visible = True
Else
Me.txtAnother.Visible = False
End If
End Sub

Although this works in the database your shared, it unfortunately doesn't work in my database because I already have a requery line in AfterUpdate for another field, and I don't know how to combine the two so they both work.

[Micron]

Or maybe just

Private Sub cboCustomer_AfterUpdate()
Me.txtAnother.Visible = Me.cboCustomer.Column(2)
End Sub

If you post the other relevant code and explain how the other field update is relevant we might be able to help with that.

[Foyer]

Private Sub CustomerCB_AfterUpdate()
RepCB.Requery
End Sub

I have another combobox that will list names of representatives for the customer (the customer is usually a company).

[Micron]

I still don't understand this

it unfortunately doesn't work in my database because I already have a requery line in AfterUpdate for another field, and I don't know how to combine the two so they both work.

It seems you have 2 cascading combo boxes with very similar names (CustomerCB and cboCustomer). Or is it CustomerCB and RepCB?

Maybe you don't need to worry about it because you have your solution.

[Gicu]

I think the OP simply wants to add to the existing code in the customer combo, maybe like this:

Private Sub CustomerCB_AfterUpdate()
Me.txtAnother.Visible = Me.CustomerCB.Column(2)
Me.RepCB.Requery 'requery the rep combo
End Sub

Cheers,

[Foyer]

Yes, Gicu - that works, thanks!

Sorry, Micron. I was using davegri's table/field names at first in an attempt to not confuse things, but switched back to my own later rendering that effort null!

[Micron]

Glad you got it figured out in the end.