Form datasource as query problem.

[webcat]

Hi All,

another day another problem!

I have a SQL table shown on a form which i wish to filter by current logged in user -

I have a local table storing the currently logged in user and all records in the SQL table have a field called userEmail

so - i have forms that take their datasource from a query which then only shows the user his/her records.

SELECT *
FROM userDetails, dbo_TrackerMainData
WHERE (((dbo_TrackerMainData.userEmail)=userDetails.user email));

that works fine - after much torment, i figured out that you must use the query as the datasource and not the table as the datasource with a filter, because that just didn't work.

anyhoo.

my problem then is that when i use a form in this way - ie, datasource is a query, i can't then modify any data in the table!

I need to have a few checkboxes on the form that set values - for instance archiveFlag and TestFlag are fields and i need to be able to change their value.

If i set the form datasource to the table (dbo_TrackerMainData) it works ok - my check boxes tick and set -1 (but i see ALL records)

if i set the form datasource to the query i see only 'my' records, but when i try to click the checkboxes nothing happens..


any ideas?!

thanks

[Scott Price]

If you create the query to only include your linked table (not using any criteria at this point, just a simple SELECT * FROM dbo_***) Can you do any update through the query?

If not, what is the nature of your link?

If so, set the WHERE criteria to use this kind of statement: =Forms!frmLogin!user_email

Where user_email is the name of the control that contains the users email address, and frmLogin, is any form that contains that information (you can even create a hidden form to use as a test in order to populate the WHERE criteria)

Regards,
Scott