How would I open different forms based on a combobox selection (1 Viewer)

CuriousGeo · Jan 9, 2020

On an After Update Event of a combobox, how would I be able to open a specific form depending on the combobox selection.

The Row source of my combobox is

SELECT tblClinic.ClinicID, tblClinic.Clinic, tblClinic.FKDataModeID FROM tblClinic ORDER BY tblClinic.Clinic;

Depending on the value of the FKDataModeID I want either Form A or Form B to open.

My DataMode refers to a data entry method that would be used (in this case 1=Barcode scanning entry OR 2=Manual data entry). So if the clinic is specified as a Barcode type I want the Barcode Form to open; If the clinic is specified as Manual, I want the Manual form to open.

Thank you for any help.

isladogs · Jan 9, 2020

Use a select case statement. Something like

Code:

Private Sub ComboName_AfterUpdate()

Select Case Me.ComboName

Case 1
DoCmd.openForm "FormA"

Case 2
DoCmd.openForm "FormB"

End Select

End Sub

theDBguy · Jan 9, 2020

Hi. If you can add the name of the form to open in the dropdown (it can be a hidden column), you can simplify the code to something like:

Code:

DoCmd.OpenForm Me.ComboboxName.Column(x)

CuriousGeo · Jan 10, 2020

isladogs said:
Use a select case statement. Something like

Code:

Private Sub ComboName_AfterUpdate() Select Case Me.ComboName Case 1 DoCmd.openForm "FormA" Case 2 DoCmd.openForm "FormB" End Select End Sub

This sounds like a workable idea, one question though. How/Where do I specify the criteria to select the appropriate Case

If the combo selection is 1 = Form A (Case 1)
If combo selection is 2 = Form A (Case 1)
If combo selection 3 = Form B (Case 2) and so on....

CuriousGeo · Jan 10, 2020

theDBguy said:
Hi. If you can add the name of the form to open in the dropdown (it can be a hidden column), you can simplify the code to something like:

Code:

DoCmd.OpenForm Me.ComboboxName.Column(x)

This sounds simple enough, but could you explain how I would get the Form Name, just create a field in my table with the form name in there? Then add it to the row source of the combobox? Or are you talking about using the Access (hidden) system tables that hold properties of all the database objects?

theDBguy · Jan 10, 2020

CuriousGeo said:
This sounds simple enough, but could you explain how I would get the Form Name, just create a field in my table with the form name in there? Then add it to the row source of the combobox? Or are you talking about using the Access (hidden) system tables that hold properties of all the database objects?

Hi. Sure. Earlier, you said this:

CuriousGeo said:
Depending on the value of the FKDataModeID I want either Form A or Form B to open.

So, depending on "how" you have determined which form to open based on FKDataModeID, that's what you'll need to add to the dropdown. For example, you could create another table to list the form to open based on the value of FKDataModeID. Then, you can join that table into the row source query of your dropdown. The bottom line is to add the name of the form to open as a new column in the dropdown. Hope that makes sense...

isladogs · Jan 10, 2020

CuriousGeo said:
This sounds like a workable idea, one question though. How/Where do I specify the criteria to select the appropriate Case

I suggested that based on what you said your combo box values were in post #1.
It sounded like you had two columns with the first column bound but hidden and values 1, 2.
The second visible column would show the data entry mode : Barcode or Manual.

Have I understood you correctly?

CuriousGeo · Jan 11, 2020

isladogs said:
I suggested that based on what you said your combo box values were in post #1.
It sounded like you had two columns with the first column bound but hidden and values 1, 2.
The second visible column would show the data entry mode : Barcode or Manual.

Have I understood you correctly?

Thanks for responding for clarification. The combo does not have Barcode or Manual as the visible elements. The combo is actually 3 columns
1st column is bound and hidden (ID for a clinic)
2nd column is visible, shows the clinic name
3rd column is hidden and is the value for Barcode-1 or Manual-2

Code:

ClinicID	Clinic	FKDataModeID
1		Abbey		1
2		Barts		1
3		Crossfield	1
4		Diego		2
5		Echowood	1
6		Fairwald	1
7		Goldmill	1
8		Highmere	1
9		Morton		2
10		Roseville	1
11		Strongmoor	1
12		Waterbridge	1

isladogs · Jan 11, 2020

In that case use

Code:

Select Case Me.ComboName.Column(2) 
Case 1
…
Case 2
...
End Select

CuriousGeo · Jan 11, 2020

theDBguy said:
Hi. Sure. Earlier, you said this:

So, depending on "how" you have determined which form to open based on FKDataModeID, that's what you'll need to add to the dropdown. For example, you could create another table to list the form to open based on the value of FKDataModeID. Then, you can join that table into the row source query of your dropdown. The bottom line is to add the name of the form to open as a new column in the dropdown. Hope that makes sense...

Thank you dbGuy! Reading your latest reply finally clicked in my head. I was kind of thinking that's what you meant earlier. I just didn't think it would be that simple!

I made a table containing the form names and added it to my combobox row source, joining the DataModeID and the FormID. The form name was the 3rd column (hidden)

Code:

Private Sub cboClinic_AfterUpdate()
    
    DoCmd.OpenForm Me.cboClinic.Column(2)
    
End Sub

CuriousGeo · Jan 11, 2020

isladogs said:
In that case use

Code:

Select Case Me.ComboName.Column(2) Case 1 … Case 2 ... End Select

Thank you isladogs! I tried your suggestion and it worked as well! Your response came just minutes after I had tried dbGuy's suggestion.

isladogs · Jan 11, 2020

Re: How would I open different forms basedO on a combobox selection

Whichever solution you decide to use, we were happy to help.
Good luck with the rest of your project.

theDBguy · Jan 11, 2020

Ditto! Glad to hear you got it sorted out. Good luck with your project.

How would I open different forms based on a combobox selection (1 Viewer)

CuriousGeo

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

CuriousGeo

Registered User.

CuriousGeo

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

CuriousGeo

Registered User.

isladogs

MVP / VIP

CuriousGeo

Registered User.

CuriousGeo

Registered User.

isladogs

MVP / VIP

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread