Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: Using combo box selection

    Hi,
    I have a combo box on a form and I want to use the value that the user selects to run an Export to Excel when they click on another button.

    I know how to do the export bit but how do I pass the combo selection to the export code?
    I take it that I use the combo seclection to become a variable but I don;t know how to do this

    Regards
    John

  2. #2
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Refer to it as;
    Forms![frmName]![cboName]

    Of if you are calling it from the form where the combobox is, then use;
    me.cboName

    Does your combobox have more than one colunm?
    Perhaps a hidden number field and a visible text fields? If you need to use the hidden number (i.e. first column), the code should work.

    If not and you need to use the text (or what have you) in another column use
    me.cboname.column(1)
    You need to use 1 for the second column and a 0 for the first column

    Hope this helps! If I didn't understand your question properly or didn't explain it enough, post back and I'll try to help!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    Thanks for the reply,

    The combo box and the command button are on the same field.
    The combo box has 2 columns, one of which is hidden. It is this hidden column that I want to use as it has the same name as the query that I want to export.

    Therefore what I want to do is put this hidden column selection in the following code

    Private Sub Export_1_Click()
    On Error GoTo Err_Export_1_Click
    'Export all accruals for all policies

    DoCmd.OutputTo acOutputQuery, "All_Policy_accruals_export", acFormatXLS, , True

    Exit_Export_1_Click:
    Exit Sub

    Err_Export_1_Click:
    MsgBox Err.Description
    Resume Exit_Export_1_Click

    End Sub

    The bit in bold and underlined is the bit that I want to take the combo box selection....................if that makes sense

    Regards
    John

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    Quote Originally Posted by Sticker
    The combo box has 2 columns, one of which is hidden. It is this hidden column that I want to use as it has the same name as the query that I want to export.
    So you have a combobox with two columns where column 1 have your query names and column 2 is a description to help you identify what the query is, correct? Did you enter this as a data source for the combobox or are you puling from a table?

    If it is a table, does it have an autonumber primary key? If so, make sure that that wasn't included in the combobox as well.
    Quote Originally Posted by Sticker
    DoCmd.OutputTo acOutputQuery, "All_Policy_accruals_export", acFormatXLS, , True

    The bit in bold and underlined is the bit that I want to take the combo box selection....................if that makes sense
    Change it to
    DoCmd.OutputTo acOutputQuery, me.cboName, acFormatXLS, , True
    where cboName is the name of your combobox

    I would suggest temporarily adding these lines of code to make sure you have the correct data from the combobox

    msgbox me.cboName, vbInformation, "Column 1"
    msgbox me.cboName.column(1), vbInformation, "Column 2"

    That way you know what is in each column.

    I hope that is what you are looking for!
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi Cosmos,
    Many thanks for your help

    Quote Originally Posted by Cosmos75
    So you have a combobox with two columns where column 1 have your query names and column 2 is a description to help you identify what the query is, correct? Did you enter this as a data source for the combobox or are you puling from a table?

    If it is a table, does it have an autonumber primary key? If so, make sure that that wasn't included in the combobox as well.
    Change it to
    DoCmd.OutputTo acOutputQuery, me.cboName, acFormatXLS, , True
    where cboName is the name of your combobox

    I would suggest temporarily adding these lines of code to make sure you have the correct data from the combobox

    msgbox me.cboName, vbInformation, "Column 1"
    msgbox me.cboName.column(1), vbInformation, "Column 2"

    That way you know what is in each column.

    The data source for the combo is from a table and there is no auto number.

    I've done as you suggested and the temporary message box give me the correct column but I then get an error message of "Object required"

    Regards
    John

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    are you changing cboName to the name of your combo box, or did you flat out cut and paste?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Quote Originally Posted by Teddy
    are you changing cboName to the name of your combo box, or did you flat out cut and paste?
    Hi Teddy,
    I changed the name as below



    Private Sub Export_Click()
    On Error GoTo Err_Export_Click
    'Export all accruals for all policies


    DoCmd.OutputTo acOutputQuery, cboSelect_export.Column(1), acFormatXLS, , True

    Exit_Export_Click:
    Exit Sub

    Err_Export_Click:
    MsgBox Err.Description
    Resume Exit_Export_Click
    End Sub


    Regards
    John

  8. #8
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    OK solved it


    The line
    DoCmd.OutputTo acOutputQuery, cboSelect_export.Column(1), acFormatXLS, , True

    should be

    DoCmd.OutputTo acOutputQuery, Me.Select_export.Column(2), acFormatXLS, , True


    Thanks for everyones help

    John

Posting Permissions

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