Help?!

[Jazzitup]

I have a form with a series of cbo boxes. I would like to use the contents of a combo box to determine the contents of another...For instance

If combo 1 has user type - director
Combo 2 shows a list of directors...

If combo 1 has user type - client
Combo 2 shows a list of clients(companys)

 

[cranebill]

I would set the default of combo 1 to either client or director.

Build a query for combo 2 to whatever you want with criteria being Me!Combo1

In Properties of combo1 goto Event, then "after update" and then code put in Me!Combo2.requery (this will update combo box if you change it on the fly)

HTH
Bill

 

[Jazzitup]

What would happen if I were to have more than two users.

I.E. I have Officer,Director and Client

Officer generates a list, Director generates a list...etc.

Is there a way to create some sort of hidden combo box that appears only when one of the above is selected.?

 

[cranebill]

Yes. Create the combo box you want hidden. Then in the after updates where we put the Me!Combo2.requery statement. Then you could add something like:

Select Case Me!Combo1

Case = "Officer"
Me!Combo2.Visible = True

Case Else
Me!Combo2.Visible = False

End Select

I would set the default property for combo2 visible to false though unless you want it seen from when the form opens.


Bill

 

[Jazzitup]

Thank you for this Bill, something seems to be awry in my coding, as this is not causing the combo box to be invisible when the Form is loaded. this is the code that I have...

Private Sub Company_ID_AfterUpdate(Cancel As Integer)
Select Case Me!Primary_Contact_Type

Case Is = "Client"
Me!Company_ID.Visible = True

Case Else
Me!Company_ID.Visible = False

End Select
End Sub

 

[cranebill]

in Me!Company_ID go to Properties then Format then Visible then put it no.... this will set the default to not visible when form is loaded.

Bill

 

[Jazzitup]

Ok, that has fixed the initial problem, the CompanyID is invisible when the form is loaded, but is not becoming visible when Type of Contact is Client...
Should I have an AfterUpdate under the Type of Contact?

 

[cranebill]

Private Sub Combo0_AfterUpdate()
Select Case Me!Combo0

Case 1
Me!Combo2.Visible = True

Case Else
Me!Combo2.Visible = False

End Select

End Sub

I just tested this and it works fine....

i hit 1 then enter
second combo box appears

i hit any number but 1 then enter and it disappears

 

[cranebill]

Did you get it working?

 

[cranebill]

Oh yeah misread your post... this coding should go in After Update of the control which holds criteria.

 

[Jazzitup]

I did make the adjustments before I saw your post, but continue to receive an error
"The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or preceduring having the same name.
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure}
* There may have been an error evaluating the function, event or macro.
"

 

[cranebill]

Post your complete code for this item please.

Bill

 

[Jazzitup]

Thanks for all of your help

Private Sub Primary_Contact_Type_AfterUpdate()
Select Case Me!Primary_Contact_Type

Case Is = "Client"
Me!Company_ID.Visible = True

Case Else
Me!Company_ID.Visible = False

End Select
End Sub

 

[Jazzitup]

Thanks for all of your help

Private Sub Primary_Contact_Type_AfterUpdate()
Select Case Me!Primary_Contact_Type

Case Is = "Client"
Me!Company_ID.Visible = True

Case Else
Me!Company_ID.Visible = False

End Select
End Sub

 

[cranebill]

I made a form with your 2 combo boxes in your code, pasted your code directly into my after update event and it worked fine. Look through all your code for your form and make sure you dont have a duplicate subroutine for the after event of Primary_Contact_Type.

Sorry it took so long to reply... work got in the way lol

Bill

 

[Jazzitup]

Thanks, for the help, I must go through this more closely, because I made some deletions and now there are no errors, but the command just doesn't seem to be executing...Arrrrrrgh!

 

[cranebill]

Well let me know how it goes.....

 

[natatbh]

I guess that the data you want to display in the second combo box comes from a table.
If so how about using a combo box on a subform and linking the subform to the field in the first combo box. Like this the subform's comboBox would always show data related to the value in the original forms combo.

 

[Jazzitup]

Much work, to no avail...I've eliminated the errors, but the combo box of companies does not appear when "Client" is selected from the first combo box.

 

[cranebill]

When you select "client" do you hit enter or Tab to actually change it.... if not then your just dirtying the combobox

Just a thought

Bill