PC Review


Reply
Thread Tools Rate Thread

How to use a combo box to query for a report

 
 
=?Utf-8?B?b3BhbA==?=
Guest
Posts: n/a
 
      22nd Nov 2005
How do you capture a combo box selection to use in a query?

Thanks!
Opal
 
Reply With Quote
 
 
 
 
tina
Guest
Posts: n/a
 
      22nd Nov 2005
set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth


"opal" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> How do you capture a combo box selection to use in a query?
>
> Thanks!
> Opal



 
Reply With Quote
 
 
 
 
RobFMS
Guest
Posts: n/a
 
      22nd Nov 2005
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" <(E-Mail Removed)> wrote in message
news:9Jugf.79236$(E-Mail Removed)...
> set the criteria in the query's field to
>
> [Forms]![FormName]![ComboBoxName]
>
> substitute the correct form and control names, of course.
>
> hth
>
>
> "opal" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
>> How do you capture a combo box selection to use in a query?
>>
>> Thanks!
>> Opal

>
>



 
Reply With Quote
 
tina
Guest
Posts: n/a
 
      22nd Nov 2005
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:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:9Jugf.79236$(E-Mail Removed)...
> > set the criteria in the query's field to
> >
> > [Forms]![FormName]![ComboBoxName]
> >
> > substitute the correct form and control names, of course.
> >
> > hth
> >
> >
> > "opal" <(E-Mail Removed)> wrote in message
> > news(E-Mail Removed)...
> >> How do you capture a combo box selection to use in a query?
> >>
> >> Thanks!
> >> Opal

> >
> >

>
>



 
Reply With Quote
 
RobFMS
Guest
Posts: n/a
 
      22nd Nov 2005
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" <(E-Mail Removed)> wrote in message
news:dnygf.79818$(E-Mail Removed)...
> 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:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:9Jugf.79236$(E-Mail Removed)...
>> > set the criteria in the query's field to
>> >
>> > [Forms]![FormName]![ComboBoxName]
>> >
>> > substitute the correct form and control names, of course.
>> >
>> > hth
>> >
>> >
>> > "opal" <(E-Mail Removed)> wrote in message
>> > news(E-Mail Removed)...
>> >> How do you capture a combo box selection to use in a query?
>> >>
>> >> Thanks!
>> >> Opal
>> >
>> >

>>
>>

>
>



 
Reply With Quote
 
tina
Guest
Posts: n/a
 
      22nd Nov 2005
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:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:dnygf.79818$(E-Mail Removed)...
> > 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:(E-Mail Removed)...
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:9Jugf.79236$(E-Mail Removed)...
> >> > set the criteria in the query's field to
> >> >
> >> > [Forms]![FormName]![ComboBoxName]
> >> >
> >> > substitute the correct form and control names, of course.
> >> >
> >> > hth
> >> >
> >> >
> >> > "opal" <(E-Mail Removed)> wrote in message
> >> > news(E-Mail Removed)...
> >> >> How do you capture a combo box selection to use in a query?
> >> >>
> >> >> Thanks!
> >> >> Opal
> >> >
> >> >
> >>
> >>

> >
> >

>
>



 
Reply With Quote
 
RobFMS
Guest
Posts: n/a
 
      22nd Nov 2005
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" <(E-Mail Removed)> wrote in message
news:qlLgf.149185$(E-Mail Removed)...
> 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:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:dnygf.79818$(E-Mail Removed)...
>> > 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:(E-Mail Removed)...
>> >> 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" <(E-Mail Removed)> wrote in message
>> >> news:9Jugf.79236$(E-Mail Removed)...
>> >> > set the criteria in the query's field to
>> >> >
>> >> > [Forms]![FormName]![ComboBoxName]
>> >> >
>> >> > substitute the correct form and control names, of course.
>> >> >
>> >> > hth
>> >> >
>> >> >
>> >> > "opal" <(E-Mail Removed)> wrote in message
>> >> > news(E-Mail Removed)...
>> >> >> How do you capture a combo box selection to use in a query?
>> >> >>
>> >> >> Thanks!
>> >> >> Opal
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do i use one combo box to determine the content of a second combo box? zawanda Microsoft Access Form Coding 3 4th Sep 2007 12:35 AM
how to use a combo box result in a sub form combo box / query =?Utf-8?B?UmFiYmllIFdpbGxpYW1z?= Microsoft Access 3 10th Jul 2006 07:32 PM
Data Entry forms: use 1st combo box to filter next combo box? =?Utf-8?B?V2lsZEdvdXJk?= Microsoft Access Forms 1 23rd Jun 2006 07:43 PM
Combo Box - Hide Combo Box w/Check Box =?Utf-8?B?UGF1bA==?= Microsoft Excel Programming 5 3rd Dec 2004 11:58 PM
Re: Use variable from one combo box to call info to another combo box Nikos Yannacopoulos Microsoft Access 0 23rd Jun 2004 09:41 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:38 AM.