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

[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 !

[puppydogbuddy]

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 !

Hmmm...query works, but not with form....
1. Make sure form and controls are bound to the query via the record source and control source properties.
2. Verify that form's data entry property set to no, otherwise form will open in new record mode (blank).
3. Verify that allow edits,add, and deletes properties of the form are set to yes.

[maxamis4]

what you need to do is build the query. When you build it and reference another table you have inlcude the table name. for example you would have to use table1![Your table variable] The reason its prompting you is because it doesn't know where to reference your value.

let me know if you need any other help

[webcat]

thanks - but i have tried all these things and it doesn't work :(


when i run the query by double clicking it, it returns the records it should be returning, but when i run the query from a form, it asks for 'enter parameter value' with the name of the box as my query

userDetails.useremail

instead of taking that value from the table

why would this happen ???

thanks for any help.