filter a table based on a field in another table...

Originally Posted by webcat

this is crazy, why can't i get this to work?!!

i have a complex DB with many tables, but the ones that count here are:

a linked SQL table with many records and a field called userEmail

locally I have a table called userDetails, with a field called useremail

so i built this query which limits my view to only my records:

SELECT *
FROM dbo_TrackerMainData, userDetails
WHERE (((dbo_TrackerMainData.userEmail)=[userDetails]![useremail]));

when i run this query manually it returns only 'my' records. which is perfect

but when i try to use this query on a form, it doesn't work at all - instead of filtering based on the fields contents, its prompting me to type in data for

[userDetails]![useremail]


WHY!!


so, i tried something else:

a function to do the same work -

Public Function userFilter()
Dim useremail As String

useremail = DLookup("[useremail]", "userdetails", Not Null)

Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim strSQL As String

sql1 = "SELECT * "
sql2 = "FROM dbo_TrackerMainData "
sql3 = "WHERE userEmail='" & useremail & "'"
strSQL = sql1 & sql2 & sql3
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

End Function


that doesn't work either for lots of odd reasons.....


any ideas?????

I'm going mad here with this - its surely a simple thing to filter a table with data from another?!

thanks for any help !