0

So I have a very complicated database I've been asked to create forms for and it looks like i'll need the use of some global variables, plus it's just good practice since i'm rather new at VBA. I have read to declare global variables as such

Option Compare Database
Option Explicit

Public YourGlobalVariable As String

but i'm having trouble figuring out how to say take a user input from a textbox in a form and assign it to YourGlobalVariable, and then use YourGlobalVariable in other forms and enter it into a table.

Thanks so much for your time, as I mentioned i'm new to Access and VBA, so any and all help is much appreciated!

EDIT: Example...

So say in one form they input 10 activities they do. In another form they have to then rate how much they like each 10 activities. I need a way to log those 10 activities in 1) a table in a database 2) in a way I can spit them back out in a future form the user will be filling out. This is why I thought global variables would be perfect. Each activity string have it's own global variable I can use wherever needed.

Bob
  • 1,344
  • 3
  • 29
  • 63
  • You can retrieve global variables from a Module that is available to all forms, but, why would a user need to change it? Not a good recipe. Perhaps if you describe why you think you need it to be global you'll get advice. – Andy G Mar 26 '19 at 16:17
  • 1
    Check https://stackoverflow.com/a/3815797/9199828. About *how to say take a user input from a textbox in a form and assign it to YourGlobalVariable* would be something like `YourGlobalVariable=Textbox.Value`. (to be honest, in this case would not be a Global one because Global can only be used in standard modules, whereas Public can be used in all contexts (modules, classes, controls, forms etc.) – Foxfire And Burns And Burns Mar 26 '19 at 16:22
  • @AndyG Edited my initial question. Good point – Bob Mar 26 '19 at 16:22
  • I cannot see why they would be global. It reads as though activities are connected to a user. Either way, the data is stored, and retrieved from, database tables. Why global? – Andy G Mar 26 '19 at 16:25
  • @FoxfireAndBurnsAndBurns thanks for the response. I have spent way more time with VB.Net so I'm kind of spoiled here, but the Textbox.Value makes sense. I guess my next question would be how to throw the value captured there in a table, and then another form? It seems like "Public" is where I want to be opposed to "global" – Bob Mar 26 '19 at 16:25
  • @AndyG for easier manipulation on my part. This way I can have all the data at my fingertips regardless of changes that are made to the database. It will make it easier for me to fulfill some of the ridiculous form / table requirements I have – Bob Mar 26 '19 at 16:29
  • @Bob About data manipulation that's a big answer I'm afraid. To insert data into a table, you can do it with SQL statements or VBA (check object [Recordset](https://support.office.com/en-us/article/recordset-property-753d8ef0-6ad9-4731-b116-1788e3276600?ui=en-US&rs=en-US&ad=US). Same for retrieving data from a table and show it in a form. Indeed, for now is too broad to answer all. I suggest you to google a bit, and when you face more specific problems, then come back and SO will help for sure. – Foxfire And Burns And Burns Mar 26 '19 at 16:32
  • @FoxfireAndBurnsAndBurns that amazes me, I'm just thinking back to VB.Net and creating forms and inserting into Excel SpreadSheets with global variables and it was so easy. I even used Recordsets as well. So there's no easy way to simply set a Global variable, and then two forms later, make a textbox equal to that variable? – Bob Mar 26 '19 at 16:40
  • Yes, it's easy. The real trick is figuring out what event to put code into. Keep in mind Global and Public variables lose value with runtime error if no adequate error handler code. Can make debugging frustrating. TempVars, however, do not lose value. – June7 Mar 26 '19 at 18:42
  • @Bob Yes it is, but as a Public variable. Actually, VB.Net and VBA are similar-looking syntax, so if you know VB.NET, it should not be a problem for you. – Foxfire And Burns And Burns Mar 27 '19 at 09:41

1 Answers1

1

You don’t need nor want to use global vars to pass around a few values. This is a bad idea in .net, c++, FoxPro or ANY system you use to deal with data.

And how you use global vars in vb.net, and VBA is the SAME.

The first and most important question is that form used to enter those activities. It not clear if you have a normalized database, and they wind up entering 10 rows of data (or 5, or 15), and this form allows entry of such activates.

Access will do all the dirty work for you, because such forms are data bound, and no code to read/write/edit the data is required.

At this point, the user has then entered the 10 activates (but as noted, your design should allow 3, 5 or 50 activates).

Once they entered this data then it not clear as to why they did not enter the rating.

However, I would assume they now launch some form that allows the rating to be entered. It not clear why the original form simple does not have that extra text box bound to the column rating.

So, the user would launch this form when they do have the rating information, and simply find the correct record, and then simple type in the rating or perhaps select it from a nice combo box you built.

So far, we not had to write any code, and we simply created a form bound to the table. However, as noted, since there are going to be 3, or 30 activities, then I assume your design is not hard coded to only 10 activities.

And I would assume like some invoice form, you have information about the “event” in place of an invoice information (customer etc.), and then a sub form that allows you to enter one or many lines of invoice details, or in this case all the possible events and activities for this one event.

So far, based on your explain, then zero code is required here.

There is no need to pass the 10 events to some other form, but simply launch the same form used to enter the event, and the 10 activities, and now simple edit (enter) the result or status of the activities.

Nothing in your explain so are suggests that global vars are required, and in fact so far what you explain can be done with Access forms and no code at all.

What you first need to do is build a correct set of tables to work with this data before you build any forms, or write one line of code. With the correct table design, the rest of your requirements become trivial and as noted so far looks to not require any code if you use the great features of Access to build such forms.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51