Open specific form from combo box

[KarenS]

I have a form with a subform which has a lookup table (combo box) as a field. I want to be able, when I select a certain item in the lookup to have it automatically open a form. e.g. A field called insurance class - I select motor, I want the motor details form to open. If I select Fire, I want it to open the fire form and so on. I have already created the forms. Please help!

 

[RuralGuy]

If the underlying field of that control on your subform is a LookUp field then do you want the control to change the data in the field *and* open some other form based on your selection?

 

[KarenS]

I have a Subform with Insurer being a field with lookup values e.g. fire, motor etc. When I select fire from the lookup, I want the form "fire" to open to allow data entry. Same too for any selection I make in the lookup - the pertaining form must open.

 

[RuralGuy]

It would be done in the AfterUpdate event of the ComboBox control. A Select Case statement would probably be the best programming structure to use.

 

[KarenS]

can u explain...sorry to b such a dummy

 

[RuralGuy]

Hi Karen,
Not a problem. When you look at the event tab of the property sheet for the ComboBox, do any of the events have [Event Procedure] in them? Is there one in the AfterUpdate event slot? If so, when you press the builder button "..." what code is in that event?

 

[KarenS]

No there is nothing in that field.

 

[RuralGuy]

I don't know what field your cbo is bound to but your code will end up looking something like:

Private Sub [b]cboInsurance[/b]_AfterUpdate()

Select Case Me.[b]cboInsurance[/b]

Case "Fire"
   DoCmd.OpenForm [b]"frmFire"[/b]
Case "Motor"
   DoCmd.OpenForm [b]"frmMotor"[/b]
Case "Home"
   DoCmd.OpenForm [b]"frmHome"[/b]
Case Else
   '-- Do nothing
End Select

End Sub

You will need to replace the Bold names with the names of your control and forms. Have you written any code before? Do you know how to get this in the AfterUpdate event of your ComboBox? We might still have to pass some data to the next form in the WhereCondition or OpenArgs of the OpenForm command.

 

[KarenS]

What is cbo? I guess you know the answer to your first question! I've never written any code, but I'll try to follow your example and play around in it. Thanks!

 

[KarenS]

nope..still not getting it...any further help please?

 

[RuralGuy]

In design mode get to the Event tab of the properties sheet for the ComboBox (cbo) and press the builder button"..." next to the AfterUpdate event. Copy the code I gave you and paste it in that event. Make the name changes and goto Tools>Compile. See if there are any errors. The 1st 2 lines in your code section should be
Option Compare Database
Option Explicit
If they are not there then put them there. They will be above your Private Sub line.

 

[KarenS]

Thanks for your responses so far, they have been really helpful. I have decided to change a few things. I created all the forms from the combo box as subforms in the same subform with the combo box. I want to have them all hidden, unless that specific option is chosen - then I want that form visible.

Reason for this is that when I used your code - the form did open - but it was not connected to the subform (clientID) - the clientID had to be manually put in. I wanted it to come up automatically just like the subform's did. So how can I do this.

 

[RuralGuy]

Like I had mentioned earlier we need to use the WhereCondition argument of the OpenForm command.

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmOneProduct"
stLinkCriteria = "[clientID]=" & Me![txtclientID] '-- If ID is Numeric
'stLinkCriteria = "[clientID]= '" & Me![txtclientID] & "'" '-- If ID is text
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[t3nchi]

I don't know what field your cbo is bound to but your code will end up looking something like:

Private Sub [b]cboInsurance[/b]_AfterUpdate()

Select Case Me.[b]cboInsurance[/b]

Case "Fire"
   DoCmd.OpenForm [b]"frmFire"[/b]
Case "Motor"
   DoCmd.OpenForm [b]"frmMotor"[/b]
Case "Home"
   DoCmd.OpenForm [b]"frmHome"[/b]
Case Else
   '-- Do nothing
End Select

End Sub

You will need to replace the Bold names with the names of your control and forms. Have you written any code before? Do you know how to get this in the AfterUpdate event of your ComboBox? We might still have to pass some data to the next form in the WhereCondition or OpenArgs of the OpenForm command.

rural, any chance this code can be used to open multiple forms based on several cases?

I am not opening a form based on a specific combo selection. This is mine:

I select a combo, and this is linked to another field (Y/N selection) that is not shown on the form. If this 'other field' is "Y", then it opens a certain form. If it's "N", it doesn't open anything.

The reason I ask is because there are several of these Y/N fields that are linked to the ONE combo box selection. I want to open multiple forms based on how many "Y" there are.

1) Select cbo1 (OtherField1=Y,OtherField2=N,OtherField3=Y)

2) Open OtherField1Form, open OtherField3Form

Someone help me out. Thanks. I appreciate it.

 

[RuralGuy]

Hi t3nchi,
I'm pretty sure the answer to your question is yes but I don't quite understand the situation. There is hardley anything you can't do inside of the Case statement including another Select Case!

Select Case Me.cboInsurance

Case "Motor"
   If CheckBox1 = True then
      DoCmd.OpenForm "frmMotor"
   Else
      DoCmd.OpenForm "frmFire"
   End If
Case "Home"
   DoCmd.OpenForm "frmHome"
Case Else
   '-- Do nothing
End Select

With some additional description of what you are wanting, we can come up with more precise code.

 

[t3nchi]

Hi t3nchi,
I'm pretty sure the answer to your question is yes but I don't quite understand the situation. There is hardley anything you can't do inside of the Case statement including another Select Case!

Select Case Me.cboInsurance

Case "Motor"
   If CheckBox1 = True then
      DoCmd.OpenForm "frmMotor"
   Else
      DoCmd.OpenForm "frmFire"
   End If
Case "Home"
   DoCmd.OpenForm "frmHome"
Case Else
   '-- Do nothing
End Select

With some additional description of what you are wanting, we can come up with more precise code.

rural>>Thanks but I have additional info maybe you can help me with.

Instead of pre-set cases ie. "Motor","Home" etc. The combo box I have grows when I add records elsewhere. So it's not pre-set.

Let's say there's cbo1 and fieldLinkedTocbo1.

What I want is to select from that combo, and have them check to see if fieldLinkedTocbo1 is either "True" or "false" and open a form *if it's true*. Else,don't do anything.

p/s: There is going to be more than just one field linked to the selection of cbo1. There are 2 others actually. All are "Y/N" checkboxes. So I guess 3 "if statements" might be what we're looking at.

I'm thinking......... (help me figure out the syntax)

Case me.cbo1{

if field1LinkedTocbo1==True {open frm_Field1LinkedTocbo1}
if field2LinkedTocbo1==True {open frm_Field2LinkedTocbo1}
if field3LinkedTocbo1==True {open frm_Field3LinkedTocbo1}

else { DO NOTHING }

 

[RuralGuy]

Is it the same "linkedFields" regardless of the cbo selection? It looks like your background might be C or C+. What is the truth table for opening the form(s)?

 

[t3nchi]

Is it the same "linkedFields" regardless of the cbo selection? It looks like your background might be C or C+. What is the truth table for opening the form(s)?

Yea it's the same linkedFields (meaning the name of the fields. Each record has all these 3 fields)

Yes, my background is C++. I just need VBA translation

 

[RuralGuy]

Are these "linkedFields" columns in your cbo, controls on your form, or fields in the current record of the table bound to your form? Give me psudo code in C++ on the logic for opening (or not opening) the various forms.

 

[ScottBeatty]

I have a similar issue currently and was wondering if it was just the coding but when I try to save the code builder, there is an error message "The 'OpenForm' macro action has an invalid value for the 'Where Condition' argument."