update subform query from VBA

[timekeepr9]

Hi, I have a table w/ a bunch of generic text fields. I am trying to create a form with a number of comboboxes where you can change the WHERE in the query by changing what is in the combo boxes and hitting search. This seems like it should be really simply, but I can't get it to work. I have a subform which displays the fields from the table. When you hit search, I do the following:

Dim sql As String
sql = "SELECT * FROM [Incident Report Merged Table]"
sql = sql & "WHERE true = true"
If Me!id <> "*" Then
sql = sql & " AND [Foreign Key] = " & Me![id]
End If
If Me!combobeh <> "*" Then
sql = sql & " AND [Incident Report Information] = '" & Me!combobeh & "'"
End If
If Me!combocons <> "*" Then
sql = sql & " AND Consequences = '" & Me!combocons & "'"
End If
sql = sql & ";"

Forms![Incident Search]![incident lookup subform].SetFocus
Me.RecordSource = sql


What I think that should be doing is build up a sql statement that adds to the WHERE clause depending on if an actual entry is in the combobox, and then seetting the RecordSource of the subform equal to the sql call. However, it is not actually filtering the records at all. I tried requerying the subform after I changed the recordsource, but that didn't work either. I could get it partially working by just editing the Record Source in the table w/out using code at all, but it didn't work for cases where they didn't want to filter that field at all.

Can anyone show me what I am doing wrong, or direct me to some easier way of doing this?

Thanks!

 

[TomCologne]

Your code line

sql = sql & "WHERE true = true"

is missing a space (" WHERE..).

Have you tried stepping through the code?

TomCologne

 

[1003]

It might be easier to set the Filter property rather than the recourdsource:

Me.Filter = sql

You would ommit the line
sql = "SELECT * FROM [Incident Report Merged Table]"

To debug the code, try printing the SQL string in the immediate window:

debug.Print sql