Combo Box Selection to open Form

Lab_Access_DB · #1 (**permalink**) 01-09-12, 13:55

Hi All,

I am trying to make it so that my combo list selection opens a form. There will be twelve different selections to choose from. I tried using several different VBA codes found online and none of them have worked.

Below is the most recent one I attempted. Any help would be greatly appreciated!!!

Sub Number_of_Components_click()
If [Number_of_Components] = "1" Then
DoCmd.OpenForm "Chemical 1"
ElseIf ("Number_of_components=2") Then
DoCmd.OpenForm "2 Chemicals"
ElseIf ("Number_of_Components=3") Then
DoCmd.OpenForm "3 Chemicals"
ElseIf ("Number_of_Components=4") Then
DoCmd.OpenForm "4 Chemicals"
ElseIf ("Number_of_Components=5") Then
DoCmd.OpenForm "5 Chemicals"
ElseIf ("Number_of_Components=6") Then
DoCmd.OpenForm "6 Chemicals"
ElseIf ("Number_of_Components=7") Then
DoCmd.OpenForm "7 Chemicals"
ElseIf ("Number_of_Components=8") Then
DoCmd.OpenForm "8 Chemicals"
ElseIf ("Number_of_Components=9") Then
DoCmd.OpenForm "9 Chemicals"
ElseIf ("Number_of_Components=10") Then
DoCmd.OpenForm "10 Chemicals"
ElseIf ("Number_of_Components=11") Then
DoCmd.OpenForm "11 Chemicals"
ElseIf ("Number_of_Components=12") Then
DoCmd.OpenForm "12 Chemicals"
End If
End Sub

dave0810471 · #2 (**permalink**) 01-09-12, 14:22

Hi,

One solution to achieve achieve this is by using a Select Case statement, see the attached example.

If you open 'FormSelections' you can then open form1, form2 or form3 by selecting those numbers from the list. The code runs on the AfterUpdate event of the combo box.

Hope this helps,
Dave

Lab_Access_DB · #3 (**permalink**) 01-10-12, 12:12

Below is what I have for VBA, but it doesn't seem to be working...

Private Sub ComboBoxNumber_AfterUpdate()
Select Case Me.Number_of_Components.Value
Case 1
DoCmd.OpenForm "Chemical 1", acNormal, , , acFormEdit
Case 2
DoCmd.OpenForm "2 Chemicals", acNormal, , , acFormEdit
Case 3
DoCmd.OpenForm "3 Chemicals", acNormal, , , acFormEdit
Case 4
DoCmd.OpenForm "4 Chemicals", acNormal, , , acFormEdit
Case 5
DoCmd.OpenForm "5 Chemicals", acNormal, , , acFormEdit
Case 6
DoCmd.OpenForm "6 Chemicals", acNormal, , , acFormEdit
Case 7
DoCmd.OpenForm "7 Chemicals", acNormal, , , acFormEdit
Case 8
DoCmd.OpenForm "8 Chemicals", acNormal, , , acFormEdit
Case 9
DoCmd.OpenForm "9 Chemicals", acNormal, , , acFormEdit
Case 10
DoCmd.OpenForm "10 Chemicals", acNormal, , , acFormEdit
Case 11
DoCmd.OpenForm "11 Chemicals", acNormal, , , acFormEdit
Case 12
DoCmd.OpenForm "12 Chemicals", acNormal, , , acFormEdit
Case Else
MsgBox ("Form not available for this selection, please choose another number.")
End Select
End Sub

Private Sub Number_of_Components_AfterUpdate()

End Sub

Private Sub Number_of_Components_Click()

End Sub

Lab_Access_DB · #4 (**permalink**) 01-10-12, 12:23

It works, I realized after posting the last reply that I needed to make references match my database, duh! Thank you sooooooooo much! I have been stuck on this for months!!!!

healdem · #5 (**permalink**) 01-10-12, 12:23

another solution would be to stuff the form to be opened into the rowsource of the combo box
then when the item is selected int he combo box, retrieve the correct form name. you then have a common single call to the open report macro, supplying the name of the the form from the combo

mind you even seeing forms named
1 chemicals, 2 chemicals....12 chemicals sounds wrong to me.
I'd guess that could be simplified into a single form, with say a sub form identifying those specified number of chemicals.. thats 2 forms to replace your current 12, and best of all if someone decides to monitor chemicals with, say 14 components, then a simple change in the data means a change in data, not a reprogramming issue.

if someone decides to migrate a previous 4 chemical compound to say 6 chemical compound.. let 'em no complex programming.. just redefine the underlying data

dave0810471 · #6 (**permalink**) 01-10-12, 12:36

Your welcome,

Dave

abrown_801 · #7 (**permalink**) 03-27-12, 06:45

hi dave i have created a combo box like the one you show in the post it worked fine i then tried to change the numbers to words but its not recognising them i just get the message box pop up tht was put it is there a way i can get this to work heres what i changed it to

Private Sub invoice_PAYE_benefits_AfterUpdate()
Select Case Me.invoice_PAYE_benefits.Text
Case invoice
DoCmd.OpenForm "invoices", acNormal, , , acFormEdit
Case PAYE
DoCmd.OpenForm "payslips", acNormal, , , acFormEdit
Case benefits
DoCmd.OpenForm "benefits", acNormal, , , acFormEdit
Case Else
MsgBox ("Form not available for this selection, please choose another number.")
End Select
End Sub

also i wanted to transfer 1 field of data to a field on the opening form

cheers

andy

dave0810471 · #8 (**permalink**) 03-27-12, 15:37

Initially I would suggest that you need to enclose your text in quotation marks i.e. Case "invoice"
A simple way to check what value is being tested is to display this value in a message box at the start of the code, once you know your code is retrieving the correct value you can remove this line, your code might read...

Code:

Private Sub invoice_PAYE_benefits_AfterUpdate()
Select Case Me.invoice_PAYE_benefits.Text
Msgbox (Me.invoice_PAYE_benefits.text)'remove this line once checked
Case "invoice"
DoCmd.OpenForm "invoices", acNormal, , , acFormEdit
Case "PAYE"
DoCmd.OpenForm "payslips", acNormal, , , acFormEdit
Case "benefits"
DoCmd.OpenForm "benefits", acNormal, , , acFormEdit
Case Else
MsgBox ("Form not available for this selection, please choose another number.")
End Select
End Sub

abrown_801 · #9 (**permalink**) 03-27-12, 17:38

thanks dave

it worked how to the earlier comments how would i transfer the data from a field on the first form to the field on the form tht opens

cheers

dave0810471 · #10 (**permalink**) 03-28-12, 14:23

One solution to achieve this would be to create a command button on your first form and then place the code in the 'OnClick' event of the command button. The code below would declare a variable that you would set the value of to the value of the field on your first form, the first form would then be closed, the second form opened and the control set to the value of the variable. It may look something like this...

Code:

'Declare a variable to pass the value
Dim YourVariableName As String
'Set the variables value
YourVariableName = Me.yourfirstcontrolname.Value
'Open your second form
DoCmd.OpenForm "yourformname", acNormal
'Close your first form saving changes
DoCmd.Close acForm, "yourformname", acSaveYes
'Set control on second form to variable
Me.yoursecondcontrolname.Value = YourVariableName

abrown_801 · #11 (**permalink**) 03-28-12, 14:40

ok but this wont work with the combo box in place on the form will it

dave0810471 · #12 (**permalink**) 03-28-12, 16:31

You could also run this from the OnClick event of the combo box, its just personal preference as to whether you want to run it when a user makes a selection from the combo box or when they click a command button

abrown_801 · #13 (**permalink**) 03-29-12, 02:48

hi dave sorry i cant figure it out how to link it to the abouve stuff with the combo because its opening 3 seperate forms and i wanted the data to go on which ever form opened aswell the first box tht has the data in is a combo box too ill attach my database its the form called company 1

sorry im new to databases

cheers

andy