Multisearch textboxes on split form

[jaryszek]

Hi

i have multisearch textboxes on my split form:



My split form is build on query:

SELECT tbl_Slownik.Slownik_ID, tbl_Slownik.PESEL, tbl_Slownik.Person_ID, tbl_Slownik.Nazwisko_imie, tbl_Slownik.Department, tbl_Slownik.Company_NIP, tbl_Slownik.Company_name, tbl_Slownik.Company_shortcut, tbl_Slownik.System, tbl_Slownik.Aktualna
FROM tbl_Slownik
WHERE (((tbl_Slownik.PESEL) Like "*" & [Formularze]![tbl_Korekty]![Txb_pesel] & "*" Or (tbl_Slownik.PESEL) Is Null) AND ((tbl_Slownik.Person_ID) Like "*" & [Formularze]![tbl_Korekty]![Txb_person_id] & "*" Or (tbl_Slownik.Person_ID) Is Null) AND ((tbl_Slownik.Nazwisko_imie) Like "*" & [Formularze]![tbl_Korekty]![Txb_nazwisko_imie] & "*" Or (tbl_Slownik.Nazwisko_imie) Is Null) AND ((tbl_Slownik.Company_NIP) Like "*" & [Formularze]![tbl_Korekty]![Txb_company_NIP] & "*" Or (tbl_Slownik.Company_NIP) Is Null) AND ((tbl_Slownik.Company_name) Like "*" & [Formularze]![tbl_Korekty]![Txb_company_name] & "*" Or (tbl_Slownik.Company_name) Is Null) AND ((tbl_Slownik.Company_shortcut) Like "*" & [Formularze]![tbl_Korekty]![Txb_company_shortcut] & "*" Or (tbl_Slownik.Company_shortcut) Is Null)) OR ((("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_pesel] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_person_id] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_nazwisko_imie] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_company_NIP] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_company_name] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_company_shortcut] & "" And ("Or") Like "" & [Formularze]![tbl_Korekty]![Txb_pesel] & ""));

It is working fine but problem is when user inputs string which is not presented within where clause in my query, for example "dasdkhjasdklshjadk" into one of textbox.
Then my filtered split form looks like below:





any quick method to return to source query look?
To show whole query table without filters?

Thank you for helping me,
Best Wishes,
Jacek

[ranman256]

build the 'where' clause by cycling thru all the controls....
it executes after a find button CLICK event
if null, ignore.
if not, apply.

'----------------
sub btnFilter_click()
'----------------
dim sWhere as string 


sWhere = "1=1"
if not IsNUll(cboST) then sWhere = sWhere & " and [State]='" & cboST & "'"
if not IsNUll(cboCity) then sWhere = sWhere & " and [city]='" & cboCity & "'"
if not IsNUll(cboZip) then sWhere = sWhere & " and [ZipCode]='" & cboZip & "'"


If sWhere = "1=1" Then
  Me.FilterOn = False
Else
  Me.Filter = sWhere
  Me.FilterOn = True
End If
end sub

[jaryszek]

Hi ranman256,

thank you.

My button with magnifying glass code is :

 Me.Requery

so your code will not bring my query to life again.
But it is an alternative to my code.

You can see that in my quere there are expresiions like:

WHERE (((tbl_Slownik.PESEL) Like "*" & [Formularze]![tbl_Korekty]![Txb_pesel] & "*" Or (tbl_Slownik.PESEL) Is Null)
So thanks to it i can write first letter of name and all matching results will be shown.

These textboxes on form are used as where clause in query. And this is how it is working.

Best Wishes,
Jacek

[jaryszek]

Anyone?

Jacek

[jaryszek]

Hi,

anybodY?

Jacek

[aytee111]

Create a simple query that will fulfill your requirements and show all the records, then have a refresh button on the form and use this query as your record source.

[davegri]

My button with magnifying glass code is:

You need to have code to determine if no records are returned by your criteria:

If Me.RecordsetClone.RecordCount = 0 Then
        ' set your search textboxes to null here
        MsgBox "No records"
    End If
    me.requery

[jaryszek]

Thank you Guys,

So I found the solution.

First i am requerying split form and if recordcount = 0 then...
and once again reauery

Thank you !

Jacek