By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,199 Members | 1,474 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,199 IT Pros & Developers. It's quick & easy.

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

P: 13
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 !
Jul 28 '07 #1
Share this Question
Share on Google+
3 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
Jul 28 '07 #2

maxamis4
Expert 100+
P: 295
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
Jul 28 '07 #3

P: 13
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.
Jul 31 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.