query won't work in a form

[webcat]

hi

i have a DB which tracks order numbers,
the main table is a SQL table linked in, and there are lots of local tables as well, one of which is called userDetails and contains a field useremail

i want to open a form and only show records from trackermaindata that contain my useremail -


SELECT dbo_TrackerMainData.userEmail
FROM dbo_TrackerMainData, userDetails
WHERE dbo_TrackerMainData.userEmail=userDetails.useremail;

when i double click this query in access it works and it shows me only my records, but when i try and run this query from a button (using a macro) in my form, it asks me to Enter Parameter Value userDetails.useremail


i've tried lots and lots of things so far and nothing works. i have lots of other queries which do work, but they don't reference another table - they either contain a static value or take their data from an entry box.

can anyone give any reason why this should not work? it seems straight forward enough!!

thanks for any help

 

[Guus2005]

First rewrite your query:

SELECT dbo_TrackerMainData.userEmail
FROM dbo_TrackerMainData INNER JOIN userDetails ON dbo_TrackerMainData.userEmail=userDetails.useremail;

Joins are a lot faster than cartesisch products with a where clause.
Second, i am not sure what you want your macro to do. Do you want to open the query? do you want to open a form based on that query?

HTH

 

[webcat]

thanks - i did try an inner join. the result was always the same.

I have now got it working - what i had to do in the end was set the forms record source to the name of the query, rather than the table.

I think this is a bug or glitch in Access 2003... there is nothing wrong with the syntax of my SQL query, the problem was Access!

 

[neileg]

Eh?

If you set the record source as the table, how does the existence of the query make any difference? This ain't a bug!

 

[Guus2005]

thanks - i did try an inner join. the result was always the same.

I have now got it working - what i had to do in the end was set the forms record source to the name of the query, rather than the table.

I think this is a bug or glitch in Access 2003... there is nothing wrong with the syntax of my SQL query, the problem was Access!

Yes, the result of the query is the same. The processing time is not!
No, it is not a bug in Access. You did something wrong. When you want the form react to your query, you should tell your form to do so. It does what you tell it to do, not what you want it to do.