I have an unbound form with 9 fields - 1 xext, 1xYes/No, 7xCombo boxes. After extensive trawling though EE I decided to use the solution explained in
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21582815.html by Harfang which I have amended (as far as I'm able) with my own specifics.
When the user has entered/selected data (or not) and clicks the OK button I would like it to open the form frmPolicy which is based on the query forFrmPolicy.
The button to Clear the form before entering data works fine.
However, on clicking OK I just get a message like: ContactID=7 AND ProviderID=30 AND PolicyPrint=-1
Presumably indicating that it's got the selected data form the form but not doing anything with it. Right?
The solution feels so close I can almost smell it! So what have I missed?
Here's my version of the code:
Option Compare Database
Option Explicit
Private Sub cmdClear_Click()
ContactID = Null
ProviderID = Null
ProductID = Null
PolicyStatusID = Null
Kfdreference = Null
PolicyFundFormatID = Null
AdviserID = Null
ParaplannerID = Null
PolicyPrint = Yes
ContactID.SetFocus
End Sub
Private Sub cmdQuery_Click()
Dim varCriteria As Variant
varCriteria = Null
If Not IsNull(ContactID) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("ContactID",
dbLong, ContactID)
If Not IsNull(ProviderID) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("ProviderID"
, dbLong, ProviderID)
If Not IsNull(ProductID) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("ProductID",
dbLong, ProductID)
If Not IsNull(PolicyStatusID) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("PolicyStatu
sID", dbLong, PolicyStatusID)
If Not IsNull(Kfdreference) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("Kfdreferenc
e", dbText, Kfdreference)
If Not IsNull(PolicyFundFormatID)
Then _
varCriteria = varCriteria + " AND " & BuildCriteria("PolicyFundF
ormatID", dbLong, PolicyFundFormatID)
If Not IsNull(AdviserID) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("AdviserID",
dbLong, AdviserID)
If Not IsNull(ParaplannerID) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("Paraplanner
ID", dbLong, ParaplannerID)
If Not IsNull(PolicyPrint) Then _
varCriteria = varCriteria + " AND " & BuildCriteria("PolicyPrint
", dbBoolean, PolicyPrint)
MsgBox Nz(varCriteria, "No Criteria selected - All Records will be shown")
'Exit Sub
' store into a query definition for later use:
CurrentDb.QueryDefs("qselT
emp").SQL = "SELECT * FROM forFrmPolicy" & " WHERE " + varCriteria & ";"
' open a report:
' DoCmd.OpenReport "rptPolicies", acViewPreview, WhereCondition:=Nz(varCrit
eria)
' filter an open form:
Forms!frmPolicy.Filter = Nz(varCriteria)
Forms!frmPolicy.FilterOn = Not IsNull(varCriteria)
End Sub