Select Syntax problem in a Query  
Author Message
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

Hi - i have a query setup that is held on a subform that displays all activities that have been logged by users.  there is a field called 'leadofficer' that displays the name of the user who logged the activity. Based on prior help from forum users I thought it would be easy enough to set criteria whereby the form, when opened would only show activity where the values in the 'leadofficer' field would equal the value of the user who has logged in - where the value of the user who has logged in is held in a global variable called 'loginname'  - but i was wrong! - ive been wrestling with the SQL syntax in the query but can only get it to work by having the query to prompt me for the name, which is no use because it allows an user to enter a different user name to their own.

here is the syntax ive been wrestling with - can anyone tell me how to put it right

SELECT tblactivity.activityID, tblactivity.activitydate, tblactivity.companyname, tblactivity.contactname, tblactivity.project, tblactivity.activity, tblactivity.activityhours, tblactivity.activitynotes, tblactivity.leadofficer
FROM tblactivity

WHERE tblactivity.leadofficer = loginname;

i have also tried

WHERE ("tblactivity.leadofficer = '" & [loginname])

WHERE ("tblactivity.leadofficer = '" & [loginname] & "'");

these also prompt for loginname but unlike the first WHERE clause above they dont actually return the results that match the name entered, they just bring all of the activities up.

Thanks again,

Rhys



Microsoft ISV Community Center Forums3  
 
 
ADG





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

Hi Rhys

Is the loginame the same as the users windows username If so try using Environ("username") instead of [loginname]


 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

Hi ADG - unfortunately it is not - the loginname and password are in a table in access and on login the loginname is stored in a global variable so that it can be accessed by other functions within the system.

Thanks for trying anyway, ill keep on trying!

Rhys.


 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

have managed to resolve it by placing the loginname into a text box on a form and then setting up a command button that opens the query and the 'leadofficer' field then has a criteria that looks up the value in the text box on the form - dont know if that makes sense or helps anyone but thought id add it in.

Rhys.


 
 
Uwe_Neufeld





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

Hint:_use_a_global_function,_instead_of_a_global_variable

 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

Rhys,

The problem could be the value of your global variable isn't getting persisted during development. For example if you run a piece of code during development without going through the login screen, or if you open the form straight from the macro editor then the gobal variable won't have a value and your query won't work. You always need to go through the login screens to set the value of loginname.

If you debug your code and check the value of login name just before your query runs and you find it has no value then the value isn't getting set and you need to go through the login screen.

Hope thats helpful.



 
 
RhysDavies





PostPosted: Visual Basic for Applications (VBA), Select Syntax problem in a Query Top

Cheers derek - ive been running all code whilst logged in although i can see what youre saying. Ive got it to work and its transparent to the user so im happy to go with it 'as is'.

Thanks,

Rhys.