Use Of Public Variables - Good or Bad

[DevelopV]

I use public variables a lot. In another thread I have been advised to stay away form them as much as possible.
Does the use of public variables have any effect on the performance of a database? Especially a database consisting of a front and back end running over a network?

I make use of public variables because I don't know to pass data. One example is the following:
I have a form for invoicing. Rather than have a combo box on the form to select an invoice (I have over 30,000) I open a pop up form showing a list of invoices. (I can filter the records returned in the list by date). When the user selects an invoice from the list I write the invoice id to a public variable. When the pop up form closes I use the public variable to open the correct record on the form.
On frmInvoice

Private Sub cmdSelectInvoice_Click()
intInvoiceId = 0
DoCmd.OpenForm "frmSelectInvoiceToOpen", acNormal, , , , acDialog
If intInvoiceId <> 0 Then
    Me.RecordSource = "SELECT * From tblInvoice WHERE InvoiceId = " & intInvoiceId
End If
End Sub

On frmSeelctInvoiceToOpen

Private Sub lstInvoice_AfterUpdate()
intInvoiceId = Me.lstInvoice
DoCmd.Close
End Sub

How would I do this without using a public variable?

Looking forward to a good debate on this!
Thanks in advance

 

[dhookom]

Don't close the dialog form, just make it invisible.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Just like sugar, carbs, and alcohol public variables are not bad, but should be used sparingly and with some thought. Performance is not an issue and there is no difference between a split and unsplit database as it relates to public variables. The issue has to do with debugging code, being able to control and see the control of your variables.

The rule for all variables is always:
1. Limit the scope to the smallest scope possible
2. Limit the lifetime to the shortest lifetime possible

Public variables obviously have the largest scope and longest lifetime, so you should have a good reason to use them. If it simplifies the code and understanding of the code that is a good reason. This is one case that is OK for a public variable, but as suggested you can do this without it.

1) on you dialog forms have an OK and cancel button
2) behind the OK the code is something like
Me.visible = false
this hides the form, but as stated gives control back to the calling routine
3) the cancel or close button is a simple "close form"

To use it

Private Sub cmdSelectInvoice_Click()
 dim strFrm as string
 strFrm = "frmSelectInvoiceToOpen"
 DoCmd.OpenForm strFrm, acNormal, , , , acDialog
 'control stops here until the frm is closed or hidden
 'If it is hidden then you can read it 
 if currentproject.allforms(strFrm).isloaded then
   intInvoiceID = forms(strFrm).invoiceID 
    Me.RecordSource = "SELECT * From tblInvoice WHERE InvoiceId = " &     intInvoiceId
   docmd.close acform, strFrm
 End If
End Sub

On my dialog I would ensure that an invoice ID is selected when they hit "OK", if not they have to close or cancel.

 

[vbajock]

I find them useful when I need to pass data from within a single procedure to a Access objects used in that procedure. For example, say I have an ADO loop that loops thru a list of building numbers, and produces a report for each building. Within the loop, I'll have a line that is something like:

Call SetUnitNo(rs!UnitNo)

In a module, I have this code

Private sUnitNo as string

Public Function SetUnitNo(ByVal sStr As String)

sUnitNo = sStr

End Function

Public Function GetUnitNo() As String

GetUnitNo=sUnitNo

End Function

on a subsequent line, when I produce the report, the query that the report is based on has this in the UnitNo criteria field:

GetUnitNo()

allowing me to communicate the parameter dynamically to the called object from the code loop. But they are really much more useful when your proc results in the display of a form. If I call a form from a single procedure, I might use the public variable in the form, for example, I could put into a form field UnitNumber a default value of =GetUnitNo(). Or I can use it to set a label's caption property. They are pretty useful for on the fly stuff that you need to code quickly, but in the end they are really just a cheesy substitute for class objects.

As MajP states, it is not really a good idea to go much beyond the limited scope of a single procedure, you should pass formal parameters instead to any sub you call from the procedure, instead of using the public variable in the called sub or anywhere else in your code, the reason being that if you re-use the called sub somewhere else you might miss the need to set the public variable in the called sub, which means the GetUnitNo() would come up with the empty string or an erratic value, and not produce a readily debuggable error and put you into needle-in-a-haystack land, so use them sparingly in ad hoc reporting and other fast-turnaround non-critical stuff, but avoid them in any formal applications you put together and use class objects instead, for which you will find a pretty detailed explanation here:

http://www.di-mgt.com.au/classes.html

 

[strongm]

>it is not really a good idea to go much beyond the limited scope of a single procedure
That's not quite what MajP said

 

[MajP]

because I don't know to pass data

These may help

http://msdn.microsoft.com/en-us/library/aa263527(v=vs.60).aspx

http://www.cpearson.com/excel/scope.aspx

http://www.functionx.com/vbaexcel/functions/Lesson3.htm

Returning info from a dialog form is however a unique issue.