screen.activeform.name as query parameter

Hi,

I have a table named Perms that contains 3 fields: TheUser, TheForm and ThePermission.  I have written the following query

SELECT Perms.TheUser, Perms.TheForm, Perms.ThePermission
FROM Perms
WHERE (((Perms.TheUser)=Environ("username")) AND ((Perms.TheForm)=[screen].[activeform].[name]));

One record in the table is:   robert.smith / testform / Yes

When I test it by using the wizard to create a form based on this query and name it testform, it shows the record, but if I close that form and re-open it, it does not.  I can tell it's not picking up the screen.activeform.name the second time.  What could be causing this?

The goal I'm after is to have a function that will run whenever a form is opened, check the username and formname against the Perms table and conditionally do stuff based on what's in ThePermission.

Can anyone out there in smart guy land help me?

Bob
bastille2Asked:
Who is Participating?
 
Gustav BrockCIOCommented:
Use the Forms.Count. The last opened form has the highest index:

WHERE (((Perms.TheUser)=Environ("username")) AND ((Perms.TheForm)=Forms(Forms.Count-1).[name]));

/gustav
0
 
bastille2Author Commented:
Tried it.  It's very odd, when I use your where clause with forms.count, the query won't run at all.  It doesn't return bad results, it just won't run.  Switching to design view, the criteria for TheForm is blank.

Do you have any idea why screen.activeform.name won't get the job done?
0
 
bastille2Author Commented:
If I place the sql into my function and attach it to a form event, can I use Me.Name to pass the form name to the sql?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gustav BrockCIOCommented:
Yes.

/gustav
0
 
bastille2Author Commented:
Do you have any idea why screen.activeform.name works when a form is created but fails when the form is re-opened?
0
 
Gustav BrockCIOCommented:
Not really, except that one has to be careful when using ActiveForm and ActiveControl as there may happen not to be an active such object.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.