Creating a Filtered Form and Searching Values

[JHNielson]

I have a table that I would like to be able for the users to search/filter.
I have multiple fields to search:
[DATA_OWNR]
[REC_ID]
[SERL_CD]
[DATE]

I built a form, and I would like them to be able to choose form a set of drop-downs which criteria to filter on. and then it show in the sub-form the records that match that criteria.

I initially created a select query on the table I'm searching. With Dlookups of the drop-down values for each of the relevant fields.

I can do it fine with all things being populated, but I Don't know how to make an unselected field return all values (i.e if they don't select a DATA_OWNER, it should not get filtered by that value).

any ideas on how to do this? Do I need ot do it a different way?

[Rabbit]

Have the field return an asterisk * if it's blank.

This does not work on NULL fields.

To get around that you'll have to change the query def.

[JHNielson]

Have the field return an asterisk * if it's blank.

This does not work on NULL fields.

To get around that you'll have to change the query def.

Thanks for your help. I just have a couple of questions?

1) What do you mean by changing the query definition? To what?
2) To have it send an asterisk, do you mean, to simply change the default value of the drop-down to an asterisk?

[Rabbit]

Thanks for your help. I just have a couple of questions?

1) What do you mean by changing the query definition? To what?
2) To have it send an asterisk, do you mean, to simply change the default value of the drop-down to an asterisk?

1)Every query has a query definition. Using code you can change the query definition's .SQL property.

2) That's one way of doing it.

[JHNielson]

I set the Criteria to [Forms]![SEARCH]![ID]..... etc. for all the fields.
I set the default value to "*"
and when I run it, I get no records

When I type in a "*" in the fields, I still get no records.

As well, when I look at the query definition, there is nothing in it. What do I need to change this to? Or can you point me to some information on this?

Thanks for your help....

[Rabbit]

The criteria has to be Like [Forms]![Form Name]![Control Name]

[JHNielson]

The criteria has to be Like [Forms]![Form Name]![Control Name]

I really appreciate all your help, but I changed my criteria to what you said. Now it looks like this:

SELECT MAST_REC_LOG.REC_ID, MAST_REC_LOG.DATA_OWNR
FROM MAST_REC_LOG
WHERE (((MAST_REC_LOG.DATA_OWNR) Like [Forms]![SEARCH_TEST]![DATA]));

Now it is not filtering any records. Did I do something else wrong.


Thanks again

[Rabbit]

Well, if DATA is = * then it won't filter anything. * returns all records.

[JHNielson]

Well - I got the * issue to work fine. Thanks so much for your help.

However, I have discovered what you meant about the Null values issue- if there is any NULL values in the entire column, it stops the filter and I get no records. So how would I fix this?

You mentioned that I would need t change the definition of the query. Currently, there is nothing in the definition - can you tell me how to fix this? O r can you point me to something that can show me how?

Thanks again for all your great help!!

[JHNielson]

Can anyone help with this?

[Rabbit]

Changing the query definition requires code.

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
qdf.SQL = "New SQL Statement"
Set qdf = Nothing
 

[JHNielson]

Changing the query definition requires code.

Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("QueryName")
qdf.SQL = "New SQL Statement"
Set qdf = Nothing
 

Great. I had got to that point, but where I'm struggling is what the new SQL statements should look like to avoid the Null Problem.

Thanks again. You have been very generous in trying to help.

[Rabbit]

Great. I had got to that point, but where I'm struggling is what the new SQL statements should look like to avoid the Null Problem.

Thanks again. You have been very generous in trying to help.

You don't have to do anything to avoid the Nulls. If the user wants to return all records, meaning he leaves the textbox/combobox blank, then the SQL query will select the field but won't put any criteria on it.

[Rabbit]

One thing I forgot to mention is before assigning the new SQL you have to build the statement using code.

[JHNielson]

One thing I forgot to mention is before assigning the new SQL you have to build the statement using code.

Okay great - I think I get what I need to do. I just am not sure where I put the code. Do i put it in the Query Definition of the actual query? or do I put it in the form somewhere?

[Rabbit]

Okay great - I think I get what I need to do. I just am not sure where I put the code. Do i put it in the Query Definition of the actual query? or do I put it in the form somewhere?

What you do is build the SQL statement as a string and then store it in the existing query definition's .SQL property.

[JHNielson]

GREAT! You have been a life saver! So now I have the code down below. I just don't know how to call on it. Do I put it as an AfterUpdate... on the drop down boxes? Do I need to put a EXECUTE button? Or does it go somewhere else in the DB?


THANKS AGAIN

Function CREATE_SQL()
Dim CURR_SQL As String
Dim NEW_SQL As String
Dim qdf As QueryDef
 
    Set qdf = CurrentDb.QueryDefs("SRCH - Master Records")
 
    CURR_SQL = "SELECT MAST_REC_LOG.* FROM MAST_REC_LOG WHERE (((MAST_REC_LOG.EVNT_DT)>[FORMS]![SEARCH - MASTER RECORDS]![EVNT_DT_MIN] And (MAST_REC_LOG.EVNT_DT)<[FORMS]![SEARCH - MASTER RECORDS]![EVNT_DT_MAX])"
 
        If [Forms]![SEARCH - MASTER RECORDS]![DATA_OWNR] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.DATA_OWNR) =" & """&[Forms]![SEARCH - MASTER RECORDS]![DATA_OWNR] & ""))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![REC_ID] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.REC_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![REC_ID] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![SRL_CD] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.SRL_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![SRL_CD] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_TYPE_ID] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_TYPE_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_TYPE_ID] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![CREA_BY] <> "*" Then
            NEW_SQL = CURR_SQL & "AND((MAST_CUST.CREA_BY) =" & [Forms]![SEARCH - MASTER RECORDS]![CREA_BY] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![CUST_ID] <> "*" Then
            NEW_SQL = CURR_SQL & "AND((MAST_CUST.CUST_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![CUST_ID] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![EVNT_ID] <> "*" Then
            NEW_SQL = CURR_SQL & "AND((MAST_CUST.EVNT_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![EVNT_ID] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![TXN_ID] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.TXN_ID) =" & [Forms]![SEARCH - MASTER RECORDS]![TXN_ID] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![SPND_ATVY_CD] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.SPND_ATVY_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![SPND_ATVY_CD] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_1_DS] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.BRD_NM_1_DS) =" & [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_1_DS] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_2_DS] <> "*" Then
            NEW_SQL = CURR_SQL & "AND((MAST_CUST.BRD_NM_2_DS) =" & [Forms]![SEARCH - MASTER RECORDS]![BRD_NM_2_DS] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_FIRST_NM] <> "*" Then
            NEW_SQL = CURR_SQL & "AND((MAST_CUST.RCIPT_FIRST_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_FIRST_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_LAST_NM] <> "*" Then
            NEW_SQL = CURR_SQL & "AND((MAST_CUST.RCIPT_LAST_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_LAST_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ADDR_LN_1_TXT] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_ADDR_LN_1_TXT) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ADDR_LN_1_TXT] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_CITY_NM] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_CITY_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_CITY_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ST_NM] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.RCIPT_ST_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![RCIPT_ST_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![INSN_NM] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![INSN_SUB_TYPE] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_SUB_TYPE) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_SUB_TYPE] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![INSN_ADDR_LN_1_TXT] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_ADDR_LN_1_TXT) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_ADDR_LN_1_TXT] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![INSN_CITY_NM] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_CITY_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_CITY_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![INSN_ST_CD] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_ST_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_ST_CD] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![INSN_PSTZ_CD] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.INSN_PSTZ_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![INSN_PSTZ_CD] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_NM] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_SUB_TYPE] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_SUB_TYPE) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_SUB_TYPE] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ADDR_LN_1_TXT] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_ADDR_LN_1_TXT) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ADDR_LN_1_TXT] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_CITY_NM] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_CITY_NM) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_CITY_NM] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ST_CD] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_ST_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_ST_CD] & "))"
            CURR_SQL = NEW_SQL
        End If
 
        If [Forms]![SEARCH - MASTER RECORDS]![ORGZN_PSTZ_CD] <> "*" Then
            NEW_SQL = CURR_SQL & " AND((MAST_CUST.ORGZN_PSTZ_CD) =" & [Forms]![SEARCH - MASTER RECORDS]![ORGZN_PSTZ_CD] & "))"
            CURR_SQL = NEW_SQL
        End If
 
    qdf.SQL = CURR_SQL
    Set qdf = Nothing
    MsgBox NEW_SQL
 
End Function

[JHNielson]

Okay - so I think I got there on my own. I made a search button that runs the query. When I open the query that it changes, it looks correct.

But the subform I have that shows the filtered values, on the form where they choose the search criteria, doesn't update.

I added Me.requery, but that still doesn't get me updated records. I don't know if I need to get the requery to point ot the subtable or to the underlying query...or what.

I can't find the exact syntax for that, I've tred a few things but just keep getting errors.

Is this what I need to do?
If so, what is the syntax to direct what to requery.
If not, what should I do?


Thanks again

[Rabbit]

Okay - so I think I got there on my own. I made a search button that runs the query. When I open the query that it changes, it looks correct.

But the subform I have that shows the filtered values, on the form where they choose the search criteria, doesn't update.

I added Me.requery, but that still doesn't get me updated records. I don't know if I need to get the requery to point ot the subtable or to the underlying query...or what.

I can't find the exact syntax for that, I've tred a few things but just keep getting errors.

Is this what I need to do?
If so, what is the syntax to direct what to requery.
If not, what should I do?


Thanks again

I can't remember exactly but I believe it's either:

Me.SubformControl.Requery
or
Me.SubformControl.Form.Requery

If neither works, you might have to set the record source of the subform to the query again.

[JHNielson]

Thanks for everyone's help.

I have tried adding all the following to the on_click event that updates my search criteria.

Me.SRCH___Master_Records_subform.Requery
'Forms![Search - Master Records].Form.Requery
'Me!SRCH___Master_Records_subform.Form.Requery
'Me!SRCH___Master_Records_subform.Requery

Currently it looks like:

Call CREATE_SQL
Me.SRCH___Master_Records_subform.Requery

The odd thing is that when I open the query, that the subform's record source is assigned to, the search is right.

Also, If I close the form, and then reopen it, then try to search based on a criteria, the records it returns are the ones from the previous search

example: If I choose for DATA_OWNER as TEST and click the searhc button, I won't get any records, or I will get wrong records. Then if i maually open the query - It will be only the TEST records.

Then If i close the form and reopen the form, then choose DATA_OWNER as GENIE, I get the TEST records from the previous search. I don't know why. It seems like the refresh isn't actually happening.

I don't know if I also have to requery the actual source query, or if there is a form property I need to fix, or my code is in the wrong order, or what. I'm out of ideas.

Please help. My deadline is tomorrow by noon! EST! I thought I had it working but it seems to have stopped.

Thanks for anyones help!!

[JHNielson]

I hate to bump this so soon, but I'm on a tight deadline and really hoping someone can help.....

[Rabbit]

What's the row source of the subform?