I would start of with creating the "Oracle" DUAL table
ie a table with 1 Field & 1 record
CREATE TABLE DUAL (DUMMY TEXT(1));
INSERT INTO DUAL VALUES('X');
Then I Would Build The ComboBox Values for instance
SELECT DISTINCT CUSTOMERTYPE FROM CUSTOMER UNION ALL SELECT '<ALL>' FROM
DUAL ORDER BY 1
etc
If You Name your Combo's the same as their corresponding Field Names in The
Table you could Have a button
With Code similar to this
Sub CmdDoit_Click()
Dim strSQL as String
Dim strWhere AS Variant
Dim C AS Access.Control
Dim Rs AS DAO.Recordset
'etc
StrWhere = Null
For Each C In Me.Controls
If TypeOf C is combobox Then ' And C.Tag="Selection" (if you have other
combo's not involved in the action)
If C.Value <> "<ALL>" Then
StrWhere = (StrWhere + " AND " ) & C.Name " = '" & C.Value & "'"
' Assume text fields only - more complex with say dates too - but possible
End If
End If
Next
strWhere = "WHERE " + strWhere ' By Using + it will be empty with no
selection
strSQL = "SELECT .... FROM TheTable " & strWhere
Set Rs = CurrentDb.OpenRecordset(strSQL,DbOpenDynaset,DBSeeChanges) '
Always use DbSeeChanges if you ever move to SQLServer
' ...
Rs.Close : Set Rs = Nothing
End Sub
annette said:
I would like users to be able to create a select query using drop down box
selection criteria from a form - eg: a certain type of customer in a
particular state, then mass update the recordset with a couple of values -
ie: they all received a marketing campaign (ID) on a date. I'm using Access
97. DOes anyone have any suggestions on how to do this?
