Query created with code not working as intended

[Guest]

Hello all,

Here is the code that I am trying to use that will create a query depending
on which form fields are populated.

Dim C As Access.Control
Dim sC As Variant
Dim Qdef As DAO.QueryDef
Dim Db As DAO.Database

sC = Null
Set Db = Access.CurrentDb()
Set Qdef = Db.QueryDefs("QryBlank")
Qdef.SQL = "SELECT * FROM TblClientData A"

For Each C In Me.Controls
If TypeOf C Is Access.TextBox Or TypeOf C Is Access.ComboBox Then
If VBA.InStr(C.Tag, "Criteria") > 0 And Not VBA.IsNull(C.Value)
Then
sC = (sC + " AND ") & "[" & C.Name & "] Like '*Forms![" & Me.Name & "]! _
[" & C.Name & "]*'"
End If
End If

Next
If Not VBA.IsNull(sC) Then Qdef.SQL = Qdef.SQL & " WHERE " & sC

End Sub


I am not sure what I am doing wrong but when I run this code and then go
look at the query in sql view all that is there is this:

SELECT *
FROM TblClientData AS A;

The query shows everything on the table whether I populate the text boxes on
the form or not.

Couple of things that I am not sure if they make a difference. I do not have
a control source for my form and for the c.tag property I assume that is the
same thing as smart tag when I look at the controls property under data.
Thanks for any insight you can offer.

James O

 

[Sandra Daigle]

What is TypeOf C? I'm not familiar with this so while it may be valid I
would suggest trying the following:

If c.ControlType = acTextbox or _
c.controlType= acComboBox then

Also, the Tag property is not the same as the SmartTags property. You will
find the Tag property under the Other tab.