Feild visible or not visible based off of combo box

[cbende2]

Form name: TestForm
Combobox name: cboName
Names in combobox: Rick, John, Sarah
Feilds: Feild1 Feild2 Feild3

EDIT: The names in the combobox are coming from their own table, they were not manually put into the combobox.

Here is some pseudocode for what I kind of want to do...

[open TestForm]

if cboName = Rick
Field1.Visible = true
Field2.Visible = false
Field3.Visible = false
if cboName = John
Field1.Visible = false
Field2.Visible = true
Field3.Visible = false
if cboName = Sarah
Field1.Visible = false
Field2.Visible = false
Field3.Visible = true

This is really my first time working with vba, so please explain thoroughly!

Thank you all!

[pbaldy]

Does this get you started?

http://www.baldyweb.com/ConditionalVisibility.htm

With your multiple options, you'd either want ElseIf clauses or Select/Case instead of If/Then.

[cbende2]

Thanks for the reply, I think maybe the Select/Case would fit better, but I'm not really experienced at all with this stuff.

[June7]

Why 3 fields? Sounds like non-normalized structure. If data can be in only 1, why not 1 field with data options?

[cbende2]

I used 3 as an example, I really will have 18 names, and 18 fields. Each name has a field to go along with it which will allow me to run more than one word mail merge at a time. I want a form that allows the person to pick his/her name, which will then show which field his/her name is tied in with.

In short, I need it that way to run multiple mail merges of the same information from different end users.

[June7]

Doesn't make sense to me but then I've little experience with mail merge.

[cbende2]

OK I'll try to explain a little better, basically my boss does not want them using word at all except to complete the merge. So that means I had to create some way which allows the front end user to select which plans(350 records) he wants to use in the mail merge. Well... I have created that process and it works well, however, only one person at a time can choose which plans he wants to select to use in a mail merge, because if more than one person is checking yes or no for different plans, the query will then give them more/less plans than what they want to use in the merge. So I'm making several fields that will be checkboxes and every front end user will have their own field to be able to select plans. I'm going to have a form with 18 fields stacked on top of each other, all invisible. And when the front end user picks which person he is in the combobox, it will then make that other field out of the 18 invisible fields now visible, which will allow him to select which plans he wants to mail merge.

So basically, the form will look like this

[combobox] [field1..18] <--- invisible and stacked on top of each other

after a name is chosen from the combobox, it will then make whichever field it has relation to, visible. I just need to know the code to relate a combobox value with the Visible property.

[June7]

Okay, now I understand what is going on. You don't want to create records with user selections, history of user selections is not important.

Instead of hard coding the name and field association, can have a table for this. What is the table that is source for user names listed in combobox? Maybe another field for mapping to the appropriate field of Plans table?

Code can take the user name and do lookup to find the field reference.

Say Rick is assigned Field1 but Rick quits and Amy is hired and now Amy can use Field1. This should reduce design edits. Of course, if staff expands then creating new field(s) is unavoidable.

An alternative is to put the Plans table in the frontend of split database. Then there is no conflict between multiple users. However, the complication here is that every time a new plan record is needed or existing must be edited, this means updating every user's frontend.

I am also wondering if a listbox of plans to build filter criteria for a query could be a viable option. http://allenbrowne.com/ser-50.html
350 records in a listbox might be just as manageable as a continuous form.

[cbende2]

I have two tables, PlanTable and NameTable, can you please elaborate on the first part you were explaining? about mapping two fields from two different tables?

By the way, thanks a bunch June7 for working with me.

[June7]

So have field in NameTable to indicate the field in PlanTable assigned to user.

Set the 18 controls Visible property to No. Then code can do lookup and activate the appropriate control.

Me.Controls(DLookup("PlanField", "NameTable", "UserID=" & Me.cboName)).Visible = True

DOH! Actually, can have one control and set the ControlSource property.

Me.tbxPlans.ControlSource = DLookup("PlanField", "NameTable", "UserID=" & Me.cboName)


BTW, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Also, no reserved words as names.

[cbende2]

I'll let you know how it goes when I get around to trying it, working out some bugs in another part of the database right now!

Another quick question, where is that code going? Just in vb of the actual form I'm using?

[cbende2]

I got it! I ended up using cases and playing around with it, Thanks for the help though!

[June7]

Yes, VBA behind the form, possibly in combobox AfterUpdate event.

Consider code that pulls the username from network login and automatically sets the textbox. Then the user would not have to choose from combobox.

This would require storing the network username in NameTable.

Pulling the network username can be done with: Environ("USERNAME")


Did you use a single textbox and set its ControlSource property?

[cbende2]

I'll attach my sample database that i was building.Database4.zip

Check in the code for form2 I believe.

Code:

Private Sub cboName_AfterUpdate()


Select Case Me.cboName
Case "1"
MM1.Visible = True
MM2.Visible = False
MM3.Visible = False
MM4.Visible = False
Case "2"
MM2.Visible = True
MM1.Visible = False
MM3.Visible = False
MM4.Visible = False
Case "3"
MM3.Visible = True
MM1.Visible = False
MM2.Visible = False
MM4.Visible = False
Case "4"
MM4.Visible = True
MM1.Visible = False
MM2.Visible = False
MM3.Visible = False
Case Else
MM1.Visible = False
MM2.Visible = False
MM3.Visible = False
MM4.Visible = False
End Select
Me.Refresh


End Sub




Private Sub Form_Current()
Select Case Me.cboName
Case "1"
MM1.Visible = True
MM2.Visible = False
MM3.Visible = False
MM4.Visible = False
Case "2"
MM2.Visible = True
MM1.Visible = False
MM3.Visible = False
MM4.Visible = False
Case "3"
MM3.Visible = True
MM1.Visible = False
MM2.Visible = False
MM4.Visible = False
Case "4"
MM4.Visible = True
MM1.Visible = False
MM2.Visible = False
MM3.Visible = False
Case Else
MM1.Visible = False
MM2.Visible = False
MM3.Visible = False
MM4.Visible = False
End Select


End Sub

[June7]

Okay, so do you want to simplify the code? Then follow suggestion to modify Staff table.

Then the combobox can include the PlanField info as another column (hidden or visible).
SELECT ID, StaffName, PlanField FROM Staff ORDER BY StaffName;

Code can then refer to that column for the field in PlanTable (instead of the suggested DLookup) to set checkbox ControlSource. Need only 1 checkbox on form.

Why two forms? Why is the combobox on Form1 hidden? Put it in form Header section and remove ControlSource expression, make it visible and put code in its AfterUpdate event.
Me.chkMM.ControlSource = Me.cboName.Column(2)

Note that the checkbox edit is not committed to table until moving to another record or closing form.