Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: Combo box value in VBA

    Hi,

    I have a form with a combo box to choose an year. I would like to use the year selected in my VBA program.

    1. What would be the code to do this in VBA?

    2. What do I do to make sure that the value selected in the combo box is retained, even when the user closes the form, for use in my VBA code?

    Rgds

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    To use the value in the ComboBox Try
    Forms!yourFormName!yourComboBox.column(0, Forms!yourFormName!yourComboBox.ListIndex + 1)

  3. #3
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Ok,

    First the form, I have a combo box called year_combo and a button called command2.

    When you click the button, first it checks to make sure a value has been selected (saves that null error - very irratating). Then it assigns the selection to the variable The_Year.

    To make a value stay until the Database is closed can be achieved a couple of ways, write to a table, public...And so on..However, I create a module and call it 'globals' then I have a complete list of what I have set.

    Under this module I have put Global The_Year as String. String may not be the appropriate variable, maybe interger or something else.

    Then any other forms that need this value, you just ask for The_Year value..



    In the Form

    Private Sub Command2_Click()
    If IsNull(Me.Year_Combo) = True Then MsgBox "Please select a year!", vbOKOnly + vbInformation, "No selection": Exit Sub
    The_Year = Me.Year_Combo
    End Sub

    In the modules section

    Global The_Year as String


    Hope it helps

    Ken.

  4. #4
    Join Date
    Feb 2004
    Posts
    47

    Red face

    Thanks for your responses.

    Hammbakka, your code works, but only when the form is active.

    Ken, I tried what you said, but still have an issue. I want the combo box to default to the value selected by the user, when he closes the form.

    I guess the way out would be to have a combo box (year_combo) in the form having ControlSource as the Global declaration (The_Year As String) and with the RowSource getting from a table of all possible years.

    My problem is:

    How can I have the combo box ControlSource pointing to a Global declaration and store its literal value?

    Your valuable help is appreciated.

    Rgds

  5. #5
    Join Date
    Feb 2004
    Location
    Dorset UK
    Posts
    147
    Attached a database, it has 2 forms, and a module

    The Main form has a combo (main_combo) the other form has a combo (year_combo). There is also a module called globals.

    If you open the Main form and alter the year, it will allocate The_Year to your selection. Then when you press the command button to open the Other form, it changes the value of that combo on the new form to The_Year

    If you alter the date of this combo on the new form and close it to return to the main form, the combo box on the main will select The_Year when opened.

    Does this help ?



    Ken
    Attached Files Attached Files

Posting Permissions

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