How to use a combo box to query for a report

Discussion in 'Microsoft Access Form Coding' started by Guest, Nov 22, 2005.

  1. Guest

    Guest Guest

    How do you capture a combo box selection to use in a query?

    Thanks!
    Opal
     
    Guest, Nov 22, 2005
    #1
    1. Advertisements

  2. Guest

    tina Guest

    set the criteria in the query's field to

    [Forms]![FormName]![ComboBoxName]

    substitute the correct form and control names, of course.

    hth


    "opal" <> wrote in message
    news:D...
    > How do you capture a combo box selection to use in a query?
    >
    > Thanks!
    > Opal
     
    tina, Nov 22, 2005
    #2
    1. Advertisements

  3. Guest

    RobFMS Guest

    To expand on Tina's solution, the combo box control has "Columns" you can
    access in the event the information that is shown is NOT what you want to
    send to the query.

    For example:

    You may have 2 columns in the query: PersonID, PersonFullName

    You want to SHOW the Person's full name but you want to send the PersonID
    value to the query results (b/c you need to Access it by the PersonID
    value).

    You expand on Tina's solution by:

    [Forms]![FormName]![ComboBoxName.Column(0)]

    Such as:

    strSQL = "SELECT* FROM tblPerson WHERE Person ID = " &
    [Forms]![FormName]![ComboBoxName.Column(0)]

    HTH

    Rob Mastrostefano

    --
    FMS Professional Solutions Group
    http://www.fmsinc.com/consulting

    Software Tools for .NET, SQL Server, Visual Basic & Access
    http://www.fmsinc.com


    "tina" <> wrote in message
    news:9Jugf.79236$...
    > set the criteria in the query's field to
    >
    > [Forms]![FormName]![ComboBoxName]
    >
    > substitute the correct form and control names, of course.
    >
    > hth
    >
    >
    > "opal" <> wrote in message
    > news:D...
    >> How do you capture a combo box selection to use in a query?
    >>
    >> Thanks!
    >> Opal

    >
    >
     
    RobFMS, Nov 22, 2005
    #3
  4. Guest

    tina Guest

    have you tested this, Rob? i thought i recalled that you can't reference a
    combobox's Column property in a query object - but i haven't tested it
    lately, so...


    "RobFMS" <Rob@FMS_FinancialModelingSpecialists.com> wrote in message
    news:...
    > To expand on Tina's solution, the combo box control has "Columns" you can
    > access in the event the information that is shown is NOT what you want to
    > send to the query.
    >
    > For example:
    >
    > You may have 2 columns in the query: PersonID, PersonFullName
    >
    > You want to SHOW the Person's full name but you want to send the PersonID
    > value to the query results (b/c you need to Access it by the PersonID
    > value).
    >
    > You expand on Tina's solution by:
    >
    > [Forms]![FormName]![ComboBoxName.Column(0)]
    >
    > Such as:
    >
    > strSQL = "SELECT* FROM tblPerson WHERE Person ID = " &
    > [Forms]![FormName]![ComboBoxName.Column(0)]
    >
    > HTH
    >
    > Rob Mastrostefano
    >
    > --
    > FMS Professional Solutions Group
    > http://www.fmsinc.com/consulting
    >
    > Software Tools for .NET, SQL Server, Visual Basic & Access
    > http://www.fmsinc.com
    >
    >
    > "tina" <> wrote in message
    > news:9Jugf.79236$...
    > > set the criteria in the query's field to
    > >
    > > [Forms]![FormName]![ComboBoxName]
    > >
    > > substitute the correct form and control names, of course.
    > >
    > > hth
    > >
    > >
    > > "opal" <> wrote in message
    > > news:D...
    > >> How do you capture a combo box selection to use in a query?
    > >>
    > >> Thanks!
    > >> Opal

    > >
    > >

    >
    >
     
    tina, Nov 22, 2005
    #4
  5. Guest

    RobFMS Guest

    Yes. This works.

    I have the following combo box control on the form:

    Combo box Control
    ----------------------------
    Row Source Type: Table/Query
    Row Source: SELECT tblPerson.ID, tblPerson.FirstName, tblPerson.LastName,
    tblPerson.FullName FROM tblPerson;
    Column Count: 4
    Column Widths: 0";0";0";1"


    I have the following command button control on the form:

    Private Sub Command2_Click()

    MsgBox Prompt:=Me.Combo0.Column(0), Title:="Column 0"

    End Sub

    When I run the form, I select an item from the comb box. What I see
    displayed is the FullName. What I want to use in the query is the ID field.
    The Me.Combo0.Column(0) will get me that value.

    Does this help any better?? (and yes, I did test this part) =)

    Rob Mastrostefano

    --
    FMS Professional Solutions Group
    http://www.fmsinc.com/consulting

    Software Tools for .NET, SQL Server, Visual Basic & Access
    http://www.fmsinc.com


    "tina" <> wrote in message
    news:dnygf.79818$...
    > have you tested this, Rob? i thought i recalled that you can't reference a
    > combobox's Column property in a query object - but i haven't tested it
    > lately, so...
    >
    >
    > "RobFMS" <Rob@FMS_FinancialModelingSpecialists.com> wrote in message
    > news:...
    >> To expand on Tina's solution, the combo box control has "Columns" you can
    >> access in the event the information that is shown is NOT what you want to
    >> send to the query.
    >>
    >> For example:
    >>
    >> You may have 2 columns in the query: PersonID, PersonFullName
    >>
    >> You want to SHOW the Person's full name but you want to send the PersonID
    >> value to the query results (b/c you need to Access it by the PersonID
    >> value).
    >>
    >> You expand on Tina's solution by:
    >>
    >> [Forms]![FormName]![ComboBoxName.Column(0)]
    >>
    >> Such as:
    >>
    >> strSQL = "SELECT* FROM tblPerson WHERE Person ID = " &
    >> [Forms]![FormName]![ComboBoxName.Column(0)]
    >>
    >> HTH
    >>
    >> Rob Mastrostefano
    >>
    >> --
    >> FMS Professional Solutions Group
    >> http://www.fmsinc.com/consulting
    >>
    >> Software Tools for .NET, SQL Server, Visual Basic & Access
    >> http://www.fmsinc.com
    >>
    >>
    >> "tina" <> wrote in message
    >> news:9Jugf.79236$...
    >> > set the criteria in the query's field to
    >> >
    >> > [Forms]![FormName]![ComboBoxName]
    >> >
    >> > substitute the correct form and control names, of course.
    >> >
    >> > hth
    >> >
    >> >
    >> > "opal" <> wrote in message
    >> > news:D...
    >> >> How do you capture a combo box selection to use in a query?
    >> >>
    >> >> Thanks!
    >> >> Opal
    >> >
    >> >

    >>
    >>

    >
    >
     
    RobFMS, Nov 22, 2005
    #5
  6. Guest

    tina Guest

    yes, i understood what you were doing, just had the stray "memory" of a
    problem with using a Column property reference in a query criteria. but
    since you tested and it works, i must be thinking of something else. :)


    "RobFMS" <Rob@FMS_FinancialModelingSpecialists.com> wrote in message
    news:...
    > Yes. This works.
    >
    > I have the following combo box control on the form:
    >
    > Combo box Control
    > ----------------------------
    > Row Source Type: Table/Query
    > Row Source: SELECT tblPerson.ID, tblPerson.FirstName, tblPerson.LastName,
    > tblPerson.FullName FROM tblPerson;
    > Column Count: 4
    > Column Widths: 0";0";0";1"
    >
    >
    > I have the following command button control on the form:
    >
    > Private Sub Command2_Click()
    >
    > MsgBox Prompt:=Me.Combo0.Column(0), Title:="Column 0"
    >
    > End Sub
    >
    > When I run the form, I select an item from the comb box. What I see
    > displayed is the FullName. What I want to use in the query is the ID

    field.
    > The Me.Combo0.Column(0) will get me that value.
    >
    > Does this help any better?? (and yes, I did test this part) =)
    >
    > Rob Mastrostefano
    >
    > --
    > FMS Professional Solutions Group
    > http://www.fmsinc.com/consulting
    >
    > Software Tools for .NET, SQL Server, Visual Basic & Access
    > http://www.fmsinc.com
    >
    >
    > "tina" <> wrote in message
    > news:dnygf.79818$...
    > > have you tested this, Rob? i thought i recalled that you can't reference

    a
    > > combobox's Column property in a query object - but i haven't tested it
    > > lately, so...
    > >
    > >
    > > "RobFMS" <Rob@FMS_FinancialModelingSpecialists.com> wrote in message
    > > news:...
    > >> To expand on Tina's solution, the combo box control has "Columns" you

    can
    > >> access in the event the information that is shown is NOT what you want

    to
    > >> send to the query.
    > >>
    > >> For example:
    > >>
    > >> You may have 2 columns in the query: PersonID, PersonFullName
    > >>
    > >> You want to SHOW the Person's full name but you want to send the

    PersonID
    > >> value to the query results (b/c you need to Access it by the PersonID
    > >> value).
    > >>
    > >> You expand on Tina's solution by:
    > >>
    > >> [Forms]![FormName]![ComboBoxName.Column(0)]
    > >>
    > >> Such as:
    > >>
    > >> strSQL = "SELECT* FROM tblPerson WHERE Person ID = " &
    > >> [Forms]![FormName]![ComboBoxName.Column(0)]
    > >>
    > >> HTH
    > >>
    > >> Rob Mastrostefano
    > >>
    > >> --
    > >> FMS Professional Solutions Group
    > >> http://www.fmsinc.com/consulting
    > >>
    > >> Software Tools for .NET, SQL Server, Visual Basic & Access
    > >> http://www.fmsinc.com
    > >>
    > >>
    > >> "tina" <> wrote in message
    > >> news:9Jugf.79236$...
    > >> > set the criteria in the query's field to
    > >> >
    > >> > [Forms]![FormName]![ComboBoxName]
    > >> >
    > >> > substitute the correct form and control names, of course.
    > >> >
    > >> > hth
    > >> >
    > >> >
    > >> > "opal" <> wrote in message
    > >> > news:D...
    > >> >> How do you capture a combo box selection to use in a query?
    > >> >>
    > >> >> Thanks!
    > >> >> Opal
    > >> >
    > >> >
    > >>
    > >>

    > >
    > >

    >
    >
     
    tina, Nov 22, 2005
    #6
  7. Guest

    RobFMS Guest

    Well, if it comes back to you, please share with us.

    And speaking of combo boxes, take a look at this tip I put together.

    Tip #16: Data in a combo box control on a continuous form/datasheet
    disappears
    http://www.fmsinc.com/free/tips.html#tip16foraccess

    Rob Mastrostefano

    --
    FMS Professional Solutions Group
    http://www.fmsinc.com/consulting

    Software Tools for .NET, SQL Server, Visual Basic & Access
    http://www.fmsinc.com


    "tina" <> wrote in message
    news:qlLgf.149185$...
    > yes, i understood what you were doing, just had the stray "memory" of a
    > problem with using a Column property reference in a query criteria. but
    > since you tested and it works, i must be thinking of something else. :)
    >
    >
    > "RobFMS" <Rob@FMS_FinancialModelingSpecialists.com> wrote in message
    > news:...
    >> Yes. This works.
    >>
    >> I have the following combo box control on the form:
    >>
    >> Combo box Control
    >> ----------------------------
    >> Row Source Type: Table/Query
    >> Row Source: SELECT tblPerson.ID, tblPerson.FirstName, tblPerson.LastName,
    >> tblPerson.FullName FROM tblPerson;
    >> Column Count: 4
    >> Column Widths: 0";0";0";1"
    >>
    >>
    >> I have the following command button control on the form:
    >>
    >> Private Sub Command2_Click()
    >>
    >> MsgBox Prompt:=Me.Combo0.Column(0), Title:="Column 0"
    >>
    >> End Sub
    >>
    >> When I run the form, I select an item from the comb box. What I see
    >> displayed is the FullName. What I want to use in the query is the ID

    > field.
    >> The Me.Combo0.Column(0) will get me that value.
    >>
    >> Does this help any better?? (and yes, I did test this part) =)
    >>
    >> Rob Mastrostefano
    >>
    >> --
    >> FMS Professional Solutions Group
    >> http://www.fmsinc.com/consulting
    >>
    >> Software Tools for .NET, SQL Server, Visual Basic & Access
    >> http://www.fmsinc.com
    >>
    >>
    >> "tina" <> wrote in message
    >> news:dnygf.79818$...
    >> > have you tested this, Rob? i thought i recalled that you can't
    >> > reference

    > a
    >> > combobox's Column property in a query object - but i haven't tested it
    >> > lately, so...
    >> >
    >> >
    >> > "RobFMS" <Rob@FMS_FinancialModelingSpecialists.com> wrote in message
    >> > news:...
    >> >> To expand on Tina's solution, the combo box control has "Columns" you

    > can
    >> >> access in the event the information that is shown is NOT what you want

    > to
    >> >> send to the query.
    >> >>
    >> >> For example:
    >> >>
    >> >> You may have 2 columns in the query: PersonID, PersonFullName
    >> >>
    >> >> You want to SHOW the Person's full name but you want to send the

    > PersonID
    >> >> value to the query results (b/c you need to Access it by the PersonID
    >> >> value).
    >> >>
    >> >> You expand on Tina's solution by:
    >> >>
    >> >> [Forms]![FormName]![ComboBoxName.Column(0)]
    >> >>
    >> >> Such as:
    >> >>
    >> >> strSQL = "SELECT* FROM tblPerson WHERE Person ID = " &
    >> >> [Forms]![FormName]![ComboBoxName.Column(0)]
    >> >>
    >> >> HTH
    >> >>
    >> >> Rob Mastrostefano
    >> >>
    >> >> --
    >> >> FMS Professional Solutions Group
    >> >> http://www.fmsinc.com/consulting
    >> >>
    >> >> Software Tools for .NET, SQL Server, Visual Basic & Access
    >> >> http://www.fmsinc.com
    >> >>
    >> >>
    >> >> "tina" <> wrote in message
    >> >> news:9Jugf.79236$...
    >> >> > set the criteria in the query's field to
    >> >> >
    >> >> > [Forms]![FormName]![ComboBoxName]
    >> >> >
    >> >> > substitute the correct form and control names, of course.
    >> >> >
    >> >> > hth
    >> >> >
    >> >> >
    >> >> > "opal" <> wrote in message
    >> >> > news:D...
    >> >> >> How do you capture a combo box selection to use in a query?
    >> >> >>
    >> >> >> Thanks!
    >> >> >> Opal
    >> >> >
    >> >> >
    >> >>
    >> >>
    >> >
    >> >

    >>
    >>

    >
    >
     
    RobFMS, Nov 22, 2005
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. XMan

    Combo Box Depending on Combo Box

    XMan, Oct 15, 2003, in forum: Microsoft Access Form Coding
    Replies:
    4
    Views:
    232
    Adrian Jansen
    Oct 17, 2003
  2. XMan

    Combo box depending on combo box in continuous subform

    XMan, Oct 21, 2003, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    282
    Newbie
    Oct 21, 2003
  3. Guest

    Combo box doesn't relate correctly to another combo box

    Guest, Dec 15, 2003, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    163
    Guest
    Dec 15, 2003
  4. zawanda
    Replies:
    3
    Views:
    293
  5. RCGUA
    Replies:
    2
    Views:
    726
    RCGUA
    Jul 23, 2009
Loading...

Share This Page