Filtering a form works in 2.0 not in XP

[Jim Andersen]

Hi,

I have this form I want to open, and filter the records.

This is the forms recordsource:
SELECT A.CustID, A.Name
FROM TblCustomers AS A

this is a filter-query "qryFindItems":
SELECT A.*
FROM TblCustomers AS A INNER JOIN (jointable AS B INNER JOIN TblKort ON
B.KortID = TblKort.KortID) ON A.CustID = B.CustID
WHERE (((TblKort.StamNr) Like Cnull([forms]![frmhoved]![field4],'*')))

I then do:
DoCmd.OpenForm "frmResult", , "qryFindItems"

This works great in A2.0, but in AXP it stops and complains that is doesn't
know about TblKort.StamNr.

Any hints ?

/jim

[Allen Browne]

Hi Jim

There are indeed cases where later versions of Access do not use the Filter
property in the way that Access 2 did.

The easiest solution is probably one of these:
- Reassign the RecordSource of the form. That looks easy enough:
strSQL = "SELECT A.* FROM TblCustomers AS A INNER JOIN (jointable AS B
INNER JOIN TblKort ON B.KortID = TblKort.KortID) ON A.CustID = B.CustID
WHERE ...
Me.RecordSource = strSQL

Use the Filter property of the form (if you want the user to be able to
remove the filter easily). You can use a subquery in the form's Filter so it
limits the records.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Andersen" <jimVÆÆ[e-mail]> wrote in message
news:bu**********@sunsite.dk...

I have this form I want to open, and filter the records.

This is the forms recordsource:
SELECT A.CustID, A.Name
FROM TblCustomers AS A

this is a filter-query "qryFindItems":
SELECT A.*
FROM TblCustomers AS A INNER JOIN (jointable AS B INNER JOIN TblKort ON
B.KortID = TblKort.KortID) ON A.CustID = B.CustID
WHERE (((TblKort.StamNr) Like Cnull([forms]![frmhoved]![field4],'*')))

I then do:
DoCmd.OpenForm "frmResult", , "qryFindItems"

This works great in A2.0, but in AXP it stops and complains that is doesn't know about TblKort.StamNr.

Any hints ?