[AccessD] Help with subform/query

paul.hartland at fsmail.net paul.hartland at fsmail.net
Thu Jul 1 03:42:56 CDT 2004


magic send button done the trick........I needed to add .[Text] to 
[Forms]![frmFindPersonnel]![txtSearch]

Thanks anyway sir.
Paul



Message date : Jul 01 2004, 09:39 AM
>From : "Griffiths, Richard" 
To : "Access Developers discussion and problem solving" 
Copy to : 
Subject : RE: [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


-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 

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


More information about the AccessD mailing list