[AccessD] Help with subform/query

Paul

Try using the txtSearch_onchange event to place this code
(Me.frmFindPersonnel_Sub.Requery).

Richard

-----Original Message-----
From: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net] 
Sent: 01 July 2004 09:20
To: accessd
Subject: [AccessD] Help with subform/query

To all,
 
I have a form which contains just a single text box and a subform
(Continuous) which contains personnel details.  The subform has the
following query:
 
SELECT tblPersonnel.PayrollNo, [Forename] & " " & [Surname] AS Name,
tblPersonnel.Address1, tblPersonnel.Postcode, tblPersonnel.NatInsNo,
tblPersonnel.Telhome, tblPersonnel.TelMobile, tblPersonnel.FinishDate,
tblPersonnel.Surname, tblPersonnel.Forename
FROM tblPersonnel
WHERE (((tblPersonnel.PayrollNo) Like
[Forms]![frmFindPersonnel]![txtSearch] & '*') AND
((tblPersonnel.FinishDate) Is Null)) OR (((tblPersonnel.FinishDate) Is
Null) AND ((tblPersonnel.Forename) Like
[Forms]![frmFindPersonnel]![txtSearch] & '*')) OR
(((tblPersonnel.FinishDate) Is Null) AND ((tblPersonnel.Surname) Like
[Forms]![frmFindPersonnel]![txtSearch] & '*')) OR
(((tblPersonnel.Postcode) Like [Forms]![frmFindPersonnel]![txtSearch] &
'*') AND ((tblPersonnel.FinishDate) Is Null)) OR
(((tblPersonnel.NatInsNo) Like [Forms]![frmFindPersonnel]![txtSearch] &
'*') AND ((tblPersonnel.FinishDate) Is Null)) OR
(((tblPersonnel.Telhome) Like [Forms]![frmFindPersonnel]![txtSearch] &
'*') AND ((tblPersonnel.FinishDate) Is Null)) OR
(((tblPersonnel.TelMobile) Like [Forms]![frmFindPersonnel]![txtSearch] &
'*') AND ((tblPersonnel.FinishDate) Is Null))
ORDER BY tblPersonnel.Surname, tblPersonnel.Forename;
 
(Sorry for any wrap around.)
 
on the text box of the main form (keypress event) I have:
 
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
    ' **** Filter personnel data based on criteria data ****
    Me.frmFindPersonnel_Sub.Requery
End Sub
 
But the subform still returns all the records and not just the ones
which contain the txtSearch text.  What am I missing or am I going about
this the wrong way.
 
Thanks in advance for all your help.
Paul Hartland

-- 

Whatever you Wanadoo:
http://www.wanadoo.co.uk/time/

This email has been checked for most known viruses - find out more at:
http://www.wanadoo.co.uk/help/id/7098.htm
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com