Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: Combo Box Selection to open Form

    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

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    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
    Attached Files Attached Files

  3. #3
    Join Date
    Jan 2012
    Posts
    6

    :-/

    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

  4. #4
    Join Date
    Jan 2012
    Posts
    6

    nevermind

    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!!!!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2012
    Posts
    97
    Your welcome,

    Dave

  7. #7
    Join Date
    Mar 2012
    Posts
    5

    please help

    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
    Last edited by abrown_801; 03-27-12 at 09:39.

  8. #8
    Join Date
    Jan 2012
    Posts
    97
    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
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  9. #9
    Join Date
    Mar 2012
    Posts
    5
    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

  10. #10
    Join Date
    Jan 2012
    Posts
    97
    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
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  11. #11
    Join Date
    Mar 2012
    Posts
    5
    ok but this wont work with the combo box in place on the form will it

  12. #12
    Join Date
    Jan 2012
    Posts
    97
    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
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  13. #13
    Join Date
    Mar 2012
    Posts
    5
    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
    Attached Files Attached Files

  14. #14
    Join Date
    Jan 2012
    Posts
    97
    Andy,

    Unfortunately I'm running Access 2000, if you could save the DB as an MDB file then upload it again I'd be happy to have a look.

    Apologies
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  15. #15
    Join Date
    Mar 2012
    Posts
    5
    sorry dave here it is
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •