How to use a combo box to query for a report

T

tina

set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth
 
R

RobFMS

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 said:
set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth


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

Thanks!
Opal
 
T

tina

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 said:
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 said:
set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth


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

Thanks!
Opal
 
R

RobFMS

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 said:
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 said:
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 said:
set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth


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

Thanks!
Opal
 
T

tina

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 said:
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 said:
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 said:
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


set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth


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

Thanks!
Opal
 
R

RobFMS

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 said:
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 said:
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 said:
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...


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


set the criteria in the query's field to

[Forms]![FormName]![ComboBoxName]

substitute the correct form and control names, of course.

hth


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

Thanks!
Opal
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top