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 tblPartsInventory.* FROM tblPartsInventory "
'Build the WHERE portion
whr = "" 'initialize variable
If Not IsNull(MyLine) And MyLine <"(All)" Then
whr = whr & "(tblPartsInventory.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 (tblPartsInventory.Selected = True)" & ";"
MySQL = MySQL & "ORDER BY tblPartsInventory.Line,
tblPartsInventory.PartNumber "
MySQL = MySQL & ";"
'Debug.Print "MySQL: " & MySQL
'Debug.Print "-------------------------" & CR
'Debug.Print "RptSQL: " & RptSQL
Me![sbfBinLabelSelection].Form.RecordSource = MySQL
End Sub
==================
--
--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net
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********@btinternet.comwrote in message
news:11**********************@y66g2000hsf.googlegr oups.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