IS IT POSSIBLE TO LINK A FORM TO A COMBO BOX 2

[zachsiue]

Hi,

I have created a form that is built off of several different tables and subforms. In order to "hide" some of the complexity of the form, I was wondering if it were possible to have a form/subform start up after a selection has been made from a combo box. Is this possible? If so, how could this be done? I don't have much experience with access, so as much detail as you can give would be greatly appreciated.

Thank You in Advance,
Zach

 

[WildHare]

Very easy. Just depends on how you want to do it. For example, you can place a subform on your main form, and make it invisible to start. Then, after the selection is made in the combo box, initiate your process that populates the subform, and make it visible:

Public Sub MyCombo_AfterUpdate()
{ run query or whatever }
Me!subform-control-name!FORM.visible = true
etc etc etc
End Sub

Just substitute your main-form's SUBFORM CONTROL name in where appropriate, and you should be good to go.

It may be just as easy to use a separate form, too. It depends on your situation, and what exactly you are trying to do.

Investigate the Visible property of a control, or the HIDDEN property of a loaded form.

Forms can be HIDDEN=TRUE or HIDDEN=FALSE, while controls on a form can be VISIBLE = TRUE or VISIBLE = FALSE


Jim Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[zachsiue]

Jim,

I will first apologize for my ignorance when it comes to this stuff, but where should I put the snippet of code? What do I put in the brackets that you have {run query or whatever} Is it the SQL code that you can see if you look in the SQL view mode of the subform?

Thanks again,
Zach

 

[WildHare]

oK - let's back up a bit. Please tell us something about your main form, and what you want the combo-box selection event to actually DO?

If you check out the Northwind sample database that comes with Access, you'll find several examples of combo boxes that drive subforms - perhaps one or more of them will also give you ideas...

Jim


Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[zachsiue]

The form utilizes three combo boxes that contain different types of names. Each type of name is associated with its own table that has unique parameters for that type of name. Each combo box has a seperate subform. The selection uses a subform to get all of the characteristics for the name selcted.
Right now I have all of the information on one form and I'm utilizing tab in order to get some sort of seperation. When a user makes a selection, he has to then click on the appropriate tab in order to see the information. This can be somewhat confusing to a new user and involves a degree of user error, therefore, I wanted to make the appropriate subform pop up for each selection. Is this clear enough? If not, let me know and I will try to describe it better.

Thanks once again,
Zach

 

[WildHare]

Depending on which combo box is used, open, or make visible, the appropriate subform with the information relating to that combo box's contents.

Every combo box has an "after Update" event, which you can get to from right-clicking the combo box and selecting "Build Event" .

The After Update event fires AFTER you have made a choice from the drop-down list. So what you want to do is make the appropriate sub-form visible.

Let's assume you have three combo boxes - cboA,cboB, and cboC

And three related subforms - sformA, sformB, and sformC

Each is in a control you have named SubA, SubB, and SubC

Here's what your combo box after update event would look like, for combo box cboA:

Public Sub cboA_AfterUpdate()

me!subA!FORM.visible = true
me!subB!FORM.visible = false
me!subC!FORM.visible = false

End Sub

THis is actually kinda ugly and kludgy, but I am trying to illustrate how it's done, and demonstrate the syntax.

It's a bit confusing if you've never done it before.

Did you check out how Northwind does it?

Jim










Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[zachsiue]

Thanks alot!! I'm gonna try to implement this now. I have been toying with it all day. I may have come up with a to do it by creating a seperate form and using the openform command. It is tough because I have zero visual basic experience!! Do you know of a statement that I could write for a combo box that would open a form depending on which item within the combo box was chosen? I was thinking of something like an if-else statement or case statement for each possible option that would open a seperate form depending on which was selected. Oh well, you have been a tremendous help, I really appreciate the time and effort!!

Thanks again,
Zach

 

[WildHare]

There are a couple of ways of implementing your last question.

Perhaps the most simple, at this stage, is to use a "Select Case" statement to open a specific form based on the selection in the combo box.

For example, lets say you have three possible choices in your combo box - DoThis, DoThat, and DoSomethingElse. Each choice has a form associated with it - frmThis, frmThat, and frmElse.

Build a routine something like this, to fire on the combo box's AFTER UPDATE event:


Private Sub MyCombo_AfterUpdate()

Select case MyCombo
Case "DoThis"
DoCmd.OpenForm "frmThis"
Case "DoThat"
DoCmd.OpenForm "frmThat"
Case "DoSomethingElse"
DoCmd.OpenForm "frmElse"
End Select


As noted, this may not be the most elegant or powerful way to do what you want, but it's the simplest for now, especially given your admitted new-ness. Let us/me know if you'd like to see other techniques.

Jim











Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm