Storing Variables for later use.

[KenHigg]

Marnieg, Here's the alternative solution I was referring to, hope you get it to work either way

You can use:

g_user_id
g_user_name
g_user_type
g_user_password

In most parts of your application or:

fncUserID()
fncUserName()
fncUserType()

In queries.

The user name is 'John Doe' and the password is 'Password'. I think it all works, I put it together rather quickly...

(Sorry my critique of your suggestion has upset you Bob. Seems you have an unethical Moderator in your pocket )

 

[boblarson]

The other thing about using variables as compared to a form reference is that the wrapper function is called for every single record. We had an app that needed to know the security level of the person and the original code used a wrapper to pass the value to the stored procedure. It had to run 2600 times in order to go through a single query, which meant we were hitting the stored proc 2600 times where using the hidden form it only had to do it ONCE in the initial setup.

So, again, I will say -

You can use a public variable, but

1. they are volatile - you may find your program erroring because it can't get the value because it has been reset.
2. It has to be called from a wrapper function (you can't reference it directly)

3. If used in certain places it may be called once for every record and that can add extra overhead to your processing. For a simple app, not so much a problem. For a very complex app with lots of records (10's of thousands or more) it can be a big problem and can slow things down.

I used to use the public variables. I was shown by Pat Hartman (who is highly respected by most of the users on this forum) that the hidden form method was superior in most instances to the global variable method. So, that is what it is...

 

[marnieg]

Sorry to have caused such a ruckus here.

I chose to use the hidden form and have most of it working. But when I open one of my forms that is using the loaded value it gives me nothing. I run the query and the data is retrieved, but the form displays nothing. I do have another filter going into the form, but I don't think that is causing the problem. Any suggestions. My form has a INNER JOIN on another table, but when I run the query the data is joined correctly.

 

[boblarson]

Sorry to have caused such a ruckus here.

I chose to use the hidden form and have most of it working. But when I open one of my forms that is using the loaded value it gives me nothing. I run the query and the data is retrieved, but the form displays nothing. I do have another filter going into the form, but I don't think that is causing the problem. Any suggestions. My form has a INNER JOIN on another table, but when I run the query the data is joined correctly.

Can you post the database (minus any sensitive data, of course)?

 

[ByteMyzer]

Sorry marnieg, Bob isn't big on people skills and I have better things to do than fuss with him. PM me if you want the sample db

Actually, Ken, Bob has more patience than most people I've observed in this forum, and he is to be commended for the excellent service and advice that he provides in this forum.

 

[marnieg]

Sorry I couldn't upload the database yesterday, my internet connection was acting funny. I actually have two databases. The one that has the problem uses linked tables to the other.

When you open the one called soe_poll you need to login as nonadmin, test, which will load the variables and take you to a switchboard. Then click on "Ballots"

The form opens but with no data. If you go into design view of the form and execute the query the data is displayed correctly.

I had to zip the databases because they exceeded the limit of the forum.

 

[boblarson]

Your problem is that the Ballot Usage form has its DATA ENTRY property set to YES when it should be NO.

 

[marnieg]

Thank you! Thank You! I don't know when I would have changed this property.

You have been an awesome resource and I love this forum!

Thank you for your patience and helpful directions.

Marnie

 

[KenHigg]

Glad you have it working - Sorry I caused so much of a distraction

 

[marnieg]

Ooops! I thought by changing the property this would resolve it. BUUUUUUUUUUUT, I do want the user to be able to update the information on this form. When I use the DATA Entry Property to NO the existing data show up, but I can't update the records. If I change it to YES then I can't enter the numbers.

Sorry for the excitement earlier. Any other suggestions

 

[marnieg]

I noticed in my other database too the same problem occurs. I have the same form, just without the filtered variable. I know this was working because I have entered data on this form before. Now I don't know what is going on.

 

[boblarson]

Ooops! I thought by changing the property this would resolve it. BUUUUUUUUUUUT, I do want the user to be able to update the information on this form. When I use the DATA Entry Property to NO the existing data show up, but I can't update the records. If I change it to YES then I can't enter the numbers.

Sorry for the excitement earlier. Any other suggestions

The problem appears to be that you have Ballot Percent Alerts linked as part of the form recordsource. That is rendering the BallotUsage part as non-updateable.

 

[ByteMyzer]

marnieg,

In soe_poll.mdb, try opening the Query titled BallotUsage Sort Query. If Form Defaultloc is not loaded, it will popup a prompt asking you for a value for Forms!Defaultloc!txtloc. Type it in and continue.

Now, when the Query displays data on the screen, are you able to edit it? If no, then the INNER JOIN between BallotUsage and Ballot Percent Alerts is preventing edits. In this case, you may wishto change your query's Recordset Type property to Dynaset (Inconsistent Updates). Save your changes to the query and see if this fixes the problem.

If you can edit the data in the Query, but not in the Form, you may need to check the setting on the Form's AllowEdits property.

 

[marnieg]

Yes I was able to enter data in the query after I changed the RecordSet as instructed, but I also had to set it in the Form Property as well. I will look more into why my INNER JOIN is causing a problem. Not sure why the INNER JOIN would be failing because the data is there.

Once again, thank you!

 

[Endre]

This is more interesting than "Thread disappeared - no notice". Maybe we should move this closer to the WaterCooler.

 

[ByteMyzer]

No, we shouldn't.

 

[Rich]

No, we shouldn't.

We shouldn't allow personal insults on these board either

 

[dan-cat]

Then whenever you need to know the value you just refer to the correct text box (like criteria in a query):

[Forms]![YourHiddenFormNameHere]![txtSecLevel]

That is basically it.

...and thus making changeable data static which is bad practice.

I'll explain why. Let's say that your user is only allowed to make a booking if [txtSecLevel] is set to '5'. Your user logs in and your hiddenform.txtSecLevel is set to 5. Supervisor determines that user must not do anymore bookings for today, so drops the SecLevel to 4 in the database in an attempt to prevent further bookings by that user.

What does the supervisor have to do? Go and tell the user to re-login into the app so txtSecLevel updates to 4. Not pretty if he/she has to look after 100 peeps.


Instead of telling your user to re-login each time this value is updated just use a calculation at the point of query, that way the value of 'SecLevel' will always be current because it is not pulling from disconnected data.

Only use the hidden form method if you are 100% sure that the value you are storing is a constant. For instance the primary key of the user. But nothing more.

 

[boblarson]

Only use the hidden form method if you are 100% sure that the value you are storing is a constant. For instance the primary key of the user. But nothing more.

And for me it has been so 100% of the time. I've yet to experience an instance where the user level would be changing. But if it was, then yes this method would not be appropriate. However, I do believe that it is the RARE instance that this would be necessary.

 

[dan-cat]

And for me it has been so 100% of the time. I've yet to experience an instance where the user level would be changing. But if it was, then yes this method would not be appropriate. However, I do believe that it is the RARE instance that this would be necessary.

You only need to store the primary key as static, in this case the UserName, to gain a unique reference to the current logged user.

Everything else should be calculated at database level so all referenced data is current at point of query, not current at point of log-in.

It's quite reasonable for a supervisor to expect an update of user security level to be reflected immediately rather than at next log-in.