Global Variable?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am somewhat of a beginner at Access and require
assistance regarding variables. Here's what I would like
to do:

set a variable called "salesordernumber" and reference
this from any query, macro or forms. Right now, I'm using
controls in forms to do this, but it's problematic (forms
aren't always open).

Any help is welcome, thanks.

Gary
 
In fact, most programmers prefer to use Control on a Form rather than the
Public Variable. The problem with Public Variables is that they are reset
to their default values if an untrapped error happens in your code.

Personally, I don't use the reference to the Control directly. I use a
Public Function (UDF) that checks whether the Form (usually the "LogIn" Form
in my databases) is open. If the Form is open, retrieve the value from the
Control. If the Form is closed, open the Form for the user to enter the
value and then retrieve the value. I always leave this Form open but hidden
when the users don't need to see it.
 
In a Standard Module, add this to the General Declarations section (the very
top):

Public gMyVariable As String

Now, set gMyVariable = salesordernumber anywhere you want to save it.

Be aware, however, that it is fairly easy for your global variable to lose
their value, especially if you don't have proper error handling in place.
Most advanced developers avoid global variables.
 
I am somewhat of a beginner at Access and require
assistance regarding variables. Here's what I would like
to do:

set a variable called "salesordernumber" and reference
this from any query, macro or forms. Right now, I'm using
controls in forms to do this, but it's problematic (forms
aren't always open).

Any help is welcome, thanks.

Gary
Click to expand...

In the General Declarations section I define giInvoiceNo.
Then when I need an invoice number I call the procedure
GetInvoiceNo and when it returns, I know the new invoice
number is in giInvoiceNo which I can use in a form, query,
or report.

The table "Control" is a 1 record table that contains a number
of variables the are used by the program. Invoice number
is just one of the fields in the control table.

This is just one way of doing what you want. Another way
is to do a DMax on the table and have it return the highest
Sales Order Number in the table, then add 1 to it. Then store
the new number in your Public variable.

Public giInvoiceNo As Long


Sub GetInvoiceNo()
'--Get a new Invoice no.
'--Get the number to be used, update it and put it back into the control record.

'--The Invoice no. in the control record is the next one to use.
'------------------------------------------------------------------------------
On Error GoTo GetInvoiceNo_Err
Set MYDB = CurrentDb
Set MYCRRS = MYDB.OpenRecordset("Control")
MYCRRS.MoveFirst
giInvoiceNo = MYCRRS!CT_InvoiceNo
MYCRRS.Edit
MYCRRS!CT_InvoiceNo = MYCRRS!CT_InvoiceNo + 1
MYCRRS.Update
GetInvoiceNo_Exit:
Exit Sub
GetInvoiceNo_Err:
MsgTitle = "Invoice Number Error"
MsgLine1 = "The error occured when updating the Invoice Number." & vbCrLf
MsgLine2 = "Error Code " & Err.Number & vbCrLf
MsgLine3 = Err.Description & vbCrLf
MsgLine4 = MsgLine1 & MsgLine2 & MsgLine3
MsgDialog = MB_ICONSTOP + MB_DEFBUTTON1
MsgAnswer = MsgBox(MsgLine4, MsgDialog, MsgTitle)
Resume GetInvoiceNo_Exit
End Sub


Ron
 
Back
Top