473,320 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Updating a table cell value from global variable & vice-versa using VBA

I've designed a database that stores some info about people, and the view available to the user depends on their access level. I've got most of the database completed, but am now having trouble with some global variables.

Long story short, the problem is that each "user" who accesses the database needs different levels of access to various forms, etc, and their name, etc needs to be stored in a way that it can be accessed by any form/report during their session.

I had this working well with global variables until recently, and hours of internet searching when this failed have revealed this is a bad way to do this! I have now created a table "tblVariables" to store this information temporarily, and I'm after the VBA code allowing me to access the cell contents, and load it into a variable for use and manipulation on the form, then update/store it back in the cell when closing the form.

I would really appreciate some help in finishing this off so that it works! For simplicity, there's a table of users (tblusers) with "username", "password", "accesslevel", "firstname", "surname", "fullname" (first name + last name); another table (tblVariables) stores the global variables for the session in case there's an error and they get reset. My current code looks something like below, but is not working! The immediate window on the login form shows the correct variables, but as soon as it gets to the main menu, all the variables are lost, and nothing is updated in the table!

Expand|Select|Wrap|Line Numbers
  1. Public Sub VariablesUpdate()
  2. on error goto ErrorHandler
  3. dim rstVariables as recordset
  4.  
  5. set rstvariables = currentdb.openrecordset("tblVariables")
  6.  
  7. with rstvariables
  8.    .movefirst
  9.    !CurrentUN = gstrUN
  10.    !CurrentUFN = gstrUFN
  11.    !CurrentAL = gstrAL
  12.    !CurrentSU = gstrSU
  13. end with
  14.  
  15. rstvariables.close
  16.  
  17. Errorhandler:
  18. End Sub
Jan 26 '16 #1
2 1010
Seth Schrock
2,965 Expert 2GB
Just a heads up, Access is not the way to go if you are truly needing security.

Now for your code. In this case, you are assigning the values in the variables and writing them to the table. If you are wanting to edit the first record in the table, then all you need to do is add .Edit right after the .MoveFirst and add .Update right before your End With.

Also, please make sure to always copy and paste your code and not retype it. Otherwise, typos in your code get subconsciously fixed and we would never see the error. Also, please use the [CODE/] button to added code tags around your code so that it can be formatted properly.
Jan 26 '16 #2
Thanks Seth! I am new to the forum, and my next question WAS going to be "how do you get that cool code snippet window everyone seems to have" but you've anticipated and answered that too! Surely it can't be as simple as that (and if it does, i'll feel like a numpty)! I'll give it a shot and let y'all know how it went!
Jan 26 '16 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: yukatan | last post by:
let's say this is a html table, how can i get the string value contained in the X cell ? --------------------- | | | | --------------------- | | | ...
2
by: Dennis | last post by:
Hi, I have declared a global variable in module using the following syntax (without Class) in my ASP.NET application: Public UserName As String There is no problem for me to share the value...
2
by: D. Alvarado | last post by:
Will Javascript allow me to extract the value 'NWEINETTE' (value from first td) from the following HTML? <tr id="lightrow" class="data"> <td class=data id='USER_NAME' nowrap><A class='data'...
13
by: Schultzy | last post by:
I am studying a book in VBA language for beginners and I can't see how I can get a value from a table field and store it in a variable or get a value from a variable and store it in a table field....
4
by: =?Utf-8?B?Qw==?= | last post by:
How do I get the text from my cell e..g. <td>Test Text</td> var elTableCells = elTableRow.getElementsByTagName("td"); alert(elTableCells.innerText);
1
by: =?Utf-8?B?Qw==?= | last post by:
How do I get the text from my cell e..g. <td>Test Text</td> var elTableCells = elTableRow.getElementsByTagName("td"); alert(elTableCells.innerText);
3
by: Sunny | last post by:
Hi, If I have an Html like that: <input id="testObjectId" type="text" value="1" /> <table> <tr> <td>25</td> <td>Sunday</td> <td>0</td> <td>518</td>
0
by: hydro123 | last post by:
I am using VC++2008 and am trying to read data enetered in unbound datagridview to implement in function. Under button_click event I entered the following: // initialize varaibles from...
5
by: dave816 | last post by:
Sorry for the Excel question in an Access forum...................I don't see an Excel forum and there's probably a reason for that but figured I'd give this a shot anyway. Again sorry, delete if...
3
by: uxcrh | last post by:
Hello All, I have a simple data-table being sourced from an Oracle DB. There is a column whereas each cell contains only one character - "R","Y", or "N". The logic is focused on the correct...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.