Lost - Please help with a query

[deaconj999]

Hi,

I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!

Any kind soul out there that can give me an example of where to
start.......... ?

Many Thanks

Joe

[fredg]

On 6 Apr 2007 14:47:34 -0700, deaconj999 wrote:

Hi,

I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!

Any kind soul out there that can give me an example of where to
start.......... ?

Many Thanks

Joe

Is the query to be viewed by itself, or is it used as the recordsource
for a report.
Different methods.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Don Leverton]

Hi,

What you really meant to say was "use a combo-box to specify criteria"
instead of being prompted for it as per the usual (eg [Enter a nuber between
1 and 6] )parameter query, right?

My suggestion,( if this is what you want) is to design an unbound main form
to put your combo-box on, and a datasheet style subform that will be used to
display the results.

Use the combo-boxes AfterUpdate to build your SQL statement (query) in code,
and then use the resulting SQL as the RecordSource for the subform.
Here is a code sample:
=============== ========

Public Sub sRequerySubform ()
'What I want to do here is build the RecordSource for the subform
'ON-THE-FLY...to allow for a variable WHERE statement.
'IF the user wants to view:
' 1.) ALL records
' The combo-box also has an "All" option. Instructions at
http://www.mvps.org/access/forms/frm0043.htm

' 2.) filtered by Line code (a 3-character brand abbreviation)

Dim MySQL As String
Dim RptSQL As String
Dim whr As String

Dim MyLine
MyLine = Me![cboLine]

'Build the SELECT portion of the SQL statement
MySQL = ""
MySQL = MySQL & "SELECT tblPartsInvento ry.* FROM tblPartsInvento ry "

'Build the WHERE portion
whr = "" 'initialize variable

If Not IsNull(MyLine) And MyLine <"(All)" Then
whr = whr & "(tblPartsInven tory.Line = '"
whr = whr & MyLine
whr = whr & "' )"
MySQL = MySQL & "WHERE ("
MySQL = MySQL & whr
MySQL = MySQL & ") "
End If

'The report will use the same SQL except it needs this line added.
RptSQL = MySQL & "And (tblPartsInvent ory.Selected = True)" & ";"

MySQL = MySQL & "ORDER BY tblPartsInvento ry.Line,
tblPartsInvento ry.PartNumber "
MySQL = MySQL & ";"

'Debug.Print "MySQL: " & MySQL
'Debug.Print "-------------------------" & CR
'Debug.Print "RptSQL: " & RptSQL
Me![sbfBinLabelSele ction].Form.RecordSou rce = MySQL

End Sub

=============== ===

--
--
HTH,
Don
=============== ==============
E-Mail (if you must) My*****@Telus.n et

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code samples are also Access97- based
unless otherwise noted.

=============== =============== =============== =============== ==============
"deaconj999 " <de********@bti nternet.comwrot e in message
news:11******** **************@ y66g2000hsf.goo glegroups.com.. .

Hi,

I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!

Any kind soul out there that can give me an example of where to
start.......... ?

Many Thanks

Joe

[scartnal]

On Apr 6, 5:47 pm, "deaconj999 " <deaconj...@bti nternet.comwrot e:

Hi,

I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!

Any kind soul out there that can give me an example of where to
start.......... ?

Many Thanks

Joe

Joe,

1. Create a query to "feed" the combo box, including only the fields
you want to appear in the combo box.

2. Create an unbound form or use an existing form, depending on from
where you want the user to conduct the search.

3. Put a combo box on your form, using your query from step 1 as the
row source. (I like to use the wizard for combo boxes. Click the
wizard button on the tool bar before you place the combo box on your
form, and then just answer the questions to set up the combo box.)
Give the combo box a name or remember the one Access gave it.

4. Decide how you want the user to initiate the search. I would
suggest a couple of buttons on the form; one thats says "Search" and
one that says "Cancel".

5. Create another query for your search results. In the criteria blank
for the field in this query that matches the combo box type: Like
"[Forms]![Name of Your Form]![Name of your combo box]". (If the field
is numeric or date/time, as opposed to text, use = instead of Like.)

6. Create macrosnfor the buttons. If you choose "Macro Names" from the
View menu, you can place both of the macros is the same macro group.
For the "Search" macro, merely use an OpenQuery command to open the
search result query. For the "Cancel" macro, use a Close command on
the search form.

7. If you would rather the user view the search results in a form or
report instead of a query, create one, using the query as the control
source.

That's the simplest way I know of to do it and it doesn't involve any
knowledge of VBA.

Good luck.

Steve

[deaconj999]

On Apr 7, 4:53 am, scart...@alltel .net wrote:

On Apr 6, 5:47 pm, "deaconj999 " <deaconj...@bti nternet.comwrot e:

Hi,

I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!

Any kind soul out there that can give me an example of where to
start.......... ?

Many Thanks

Joe

Joe,

1. Create a query to "feed" the combo box, including only the fields
you want to appear in the combo box.

2. Create an unbound form or use an existing form, depending on from
where you want the user to conduct the search.

3. Put a combo box on your form, using your query from step 1 as the
row source. (I like to use the wizard for combo boxes. Click the
wizard button on the tool bar before you place the combo box on your
form, and then just answer the questions to set up the combo box.)
Give the combo box a name or remember the one Access gave it.

4. Decide how you want the user to initiate the search. I would
suggest a couple of buttons on the form; one thats says "Search" and
one that says "Cancel".

5. Create another query for your search results. In the criteria blank
for the field in this query that matches the combo box type: Like
"[Forms]![Name of Your Form]![Name of your combo box]". (If the field
is numeric or date/time, as opposed to text, use = instead of Like.)

6. Create macrosnfor the buttons. If you choose "Macro Names" from the
View menu, you can place both of the macros is the same macro group.
For the "Search" macro, merely use an OpenQuery command to open the
search result query. For the "Cancel" macro, use a Close command on
the search form.

7. If you would rather the user view the search results in a form or
report instead of a query, create one, using the query as the control
source.

That's the simplest way I know of to do it and it doesn't involve any
knowledge of VBA.

Good luck.

Steve

Many thanks

[engles]

On Apr 7, 1:47 am, "deaconj999 " <deaconj...@bti nternet.comwrot e:

On Apr 7, 4:53 am, scart...@alltel .net wrote:

On Apr 6, 5:47 pm, "deaconj999 " <deaconj...@bti nternet.comwrot e:

Hi,

I have nearly finished my database and I would like to add a query
that uses a combo box to get the results, not the usual paramater
style input. I suppose it would need a form and a query and a combo
box, but where to start !!!!!!!!

Any kind soul out there that can give me an example of where to
start.......... ?

Many Thanks

Joe

Joe,

1. Create a query to "feed" the combo box, including only the fields
you want to appear in the combo box.

2. Create an unbound form or use an existing form, depending on from
where you want the user to conduct the search.

3. Put a combo box on your form, using your query from step 1 as the
row source. (I like to use the wizard for combo boxes. Click the
wizard button on the tool bar before you place the combo box on your
form, and then just answer the questions to set up the combo box.)
Give the combo box a name or remember the one Access gave it.

4. Decide how you want the user to initiate the search. I would
suggest a couple of buttons on the form; one thats says "Search" and
one that says "Cancel".

5. Create another query for your search results. In the criteria blank
for the field in this query that matches the combo box type: Like
"[Forms]![Name of Your Form]![Name of your combo box]". (If the field
is numeric or date/time, as opposed to text, use = instead of Like.)

6. Create macrosnfor the buttons. If you choose "Macro Names" from the
View menu, you can place both of the macros is the same macro group.
For the "Search" macro, merely use an OpenQuery command to open the
search result query. For the "Cancel" macro, use a Close command on
the search form.

7. If you would rather the user view the search results in a form or
report instead of a query, create one, using the query as the control
source.

That's the simplest way I know of to do it and it doesn't involve any
knowledge of VBA.

Good luck.

Steve

Many thanks- Hide quoted text -

- Show quoted text -

I'll toss in one more approach. I'm assuming that all you need is a
different filter on the report (the usual). If so, then use the combo
box to get what it is to be reported on. You can then use the filter
on the report open code to have the report show what is desired. For
example

docmd.openrepor t "MyReport", ,,, "Key = " & me.comboBox

I don't remember off-hand how many commas are needed here, but VBA
will show you the arguments once you start typing.

-- Larry Engles