Static and Global Variables

[BruceM]

I have recently started wondering about the extent to which I should be
concerned about variables. Knowledgeable people have expressed a dislike
for global variables because they can be invisibly reset, but I am having
some trouble understanding just how a global variable differs from another
kind.

I often declare public variables in a standard module. For instance, I
usually use custom record navigation buttons and record counters. I declare
the variables for RecordCount, etc. once in a standard module, then use them
as needed in forms. I don't know if this is a bad idea or not.

Another example is that if the total for a purchase order (i.e. the sum of
Quantity*UnitPrice for all line item records) is above a certain amount, an
extra level of approval is needed. When the total goes above that amount
the After Update code for the line items subform checks a Boolean. If it is
False the user is advised that the extra approval is needed, and the Boolean
is set to True. Another entry on the same Purchase Order record (another
line item) will mean the total is still above the critical amount, but there
is no need to tell the user again (it could get very annoying). The Boolean
is True, so the message does not appear. Is this a global variable, and am
I asking for trouble if it is? If yes to both questions, what is the
simplest way to solve the problem?

Any insight would be appreciated, either by way of explanation or a link to
some comprehensible discussion of variables and their lifetimes, and how to
avoid being bitten. I found some things in VBA help, but they seem somewhat
sketchy.

 

[Klatuu]

There actually is no longer a specific designation of a Global variable in
Access VBA. Any varialbe declared in a Standard Module before any subs or
functions as Public is a Global variable.

A Static varialbe is a different beast, but I will cover that after I
descrive varialbe scoping.

Local Variables
A variable Dimmed in a procedure (sub or function) is a Local variable and
is visible only to the procedure in which it is declared.

Module Variables
A variable Dimmed at the beginning of a form or report module or Dimmed at
the top of a standard module using Dim instead of Public is visible only
within the module it is declared. Any procedure within the module can see a
module variable.

Global Variable
A variable declared Public in a standard module is known as a Global
variable and can be seen anywhere within the application.

Now, there is an order of precedence for variables with the same name. If
you have a module level variable named strBofo and also create a variable
named strBof in a procedure in the module, that procedure will only see the
local variable. If you have a Global variable named lngTimeGone and declare
a module variabnle named lngTimeGone, all procedures in the tne module will
see only the module level variable.

Now, the major problem with Global variables is that any unhandled error
resets all Global variable values.

Good programming practices dictate you should keep your scope as local as
possible. You should also use a naming convention for variables that
includes the scope, so that no two variables with the same name exists within
the current scope. That is, it is okay to use the same name for variables in
different procedures. I do it all the time because I know by the variable
name what I commonly use it for. For example, when I see a variable named
strWhere, I know I am using it to filter something. But, I would not use a
strWhere at the module level and at the local level.

If you code is well formed, you will very seldom use module level variables
and almost never use global variables. Some of the examples of how you use
them you should reconsider. Keeping a record count value in a global
variable is dangerous. It can change without your knowing it or you can
forget to code in updating the value. It is always advisable to recalculate
the values when you need them.

I, personally, never use global variables. The way I structure my
applications does not require it. But, if there is the odd occassion where
it is necessary, a better technique is to use a Static function. Aha! there
is that workd static. A static variable is one that retains is values
between calls to a procedure. Normally when you call a function and assign a
value to a variable, the value is destroyed when the procedure is exited.
You can retain the value by using the word Static instead of Dim. That way,
any value you assign to the variable will be presistent until you either
change the value or exit the application.

Did I say Static Function? Yes, it is like a startic variable except that
all variables Dimmed in the function retain their values under the same
rules. So, If I need to use a value at various locations in the application,
here is an example of how I do it using a Static Function in a Standard
Module:

Static Function UserName(Optional NewValue As Variant) As Variant
Dim varOldValue As Variant

If Not IsMissing(NewValue) Then
varOldValue = NewValue
End If
UserName = varOldValue
End Function

Now, if you call it without passing a value, it returns the previously
assigned value. If you pass it a value, it returns the value you passed. So
to set a value:

UserName("Freddy")

Then to use it:

If UserName = "Freddy" Then
Do Something
End If

This can be very handy for queries. Jet SQL cannot recognize a variable,
but it can recognize a function. so you can assign the function a value,
then use the function in the query for filtering.

Hope that gives you some ideas.

 

[Albert D. Kallal]

I have recently started wondering about the extent to which I should be
concerned about variables. Knowledgeable people have expressed a dislike
for global variables because they can be invisibly reset

I think you've been misinformed, or you misunderstand what is being told to
you.

Any local variable, any variable declared it static, any module variable
(declared as public, and therefore a global variable) gets reset if you have
a un-handeled error. END OF STORY!!

In other words, certain kinds of variables don't get reset "invisibly", or
easier or worse than any other kind of variables.

Again:

Any un-handled error blows out all variables. End of discussion in this
regards.

, but I am having

some trouble understanding just how a global variable differs from another
kind.

In terms of their ability to be reset, they don't differ in any way shape or
form. How globals differ is in the question of how you design and your build
your applications. We are talking about the design methodology you choose as
a developer to make your application more maintainable.

I often declare public variables in a standard module. For instance, I
usually use custom record navigation buttons and record counters. I
declare the variables for RecordCount, etc. once in a standard module,
then use them as needed in forms. I don't know if this is a bad idea or
not.

In this case, the suggestion to stay away from global variables is good one
here, because then how can you have more than one form open at the same time
and use the same code base? In the above, I would declare the variables in
the forms code module, not a standard one, because you need those vars FOR
THAT particular form, no all forms.

The main thrust here is that if you can design software that avoids the use
of global variables, then you should often make the extra effort to do so.
The reason is then that you can freely move the code between applications
(or the form). You will also have more confidence when you attempt to modify
the code for that to take over form (because if you have a lot of code
sharing the same set of variables, and you don't know which ones changing
what, then changing code in one spot can damage other code in the
application that uses the same variables).

the term or using here is what is called scope. furthermore let's assume
that you have a few counters or have a few loops that go:

for i = 1 to 10
bla bla bla

In the above a case of you could define the interger variable in the form's
module, or you could define the variable in the local sub/function routine.
That means the scope of the variable would be local only to the routine
you're using (sub/functon). if you define the var in the forms module
declaration section, then the var is local to that form. if you have some
variables like you're testing flag in the above example, it's perfectly
normal and advisable to declare that variable as local to the whole forms
module.

The idea here is to encapsulate or reduce the dependencies of each piece of
code you write so that when you modify or change that code, you are less
likely to damage or affect other routines in your system. So you wanna make
an effort to use the correct scope for your variables that applied to the
set are retained your working with, not for at least until piece of code and
the whole application at the same time.

Another example is that if the total for a purchase order (i.e. the sum of
Quantity*UnitPrice for all line item records) is above a certain amount,
an extra level of approval is needed. When the total goes above that
amount the After Update code for the line items subform checks a Boolean.
If it is False the user is advised that the extra approval is needed, and
the Boolean is set to True. Another entry on the same Purchase Order
record (another line item) will mean the total is still above the critical
amount, but there is no need to tell the user again (it could get very
annoying). The Boolean is True, so the message does not appear.

The above is a good use, but you'd certainly would not want that variable to
be global to all your forms, because then one form running this code and
setting the variable to true would mean that any other form opened would now
not function correctly. Your application would be limited to only having one
form using this feature if you declare the variables as global.

Is this a global variable,

I hope it is not, without looking at your code and how you define it, we
can't know. It sounds to me in this case that you've defined the variable as
local to the forms code module (thr correct term here is what we call scope,
and that variable will only exist and stay alive while that module in that
form is opened - so, no this is not a global variable, it's certainly global
to that current forms module. So, we would say that the "scope" of the
varaible is to that form, or locak to that form, but not hte whole
applcaiton. You'll likely declare the variable as such in the form's code
module:

Option Compare Database
Option Explicit

Dim bolTotalCheck as boolean


If you define your variable in the form's code module, then that variable is
only local and scoped to while the form/module is opened (so, no, this would
not be considered a glboal var).

A true global variable is one define in a standard code module (not a
forms/reprot module), and is defined as public

eg:

public strUserLogOnName as string


In the above, we've declared a global variable called user log on name. In
the cases that the variable is truly global to the scope of the WHOLE
application. In this case, the above variable it is able to be used by any
code, forms module or any place in the whole application.

So, the suggestion here is not to never use a global var, but use one only
when all code and all modules need to use the same value.

If you have access 2003, while in the code editor, go help, and then type in

scope

Tthere is quite a good aritcle in the help that expliens this issue.

So at the end of the day don't confuse the issue of scope and local/global
variables with that of an error in code that resets all variables. They're
completely separate issues.

The Hierarchy is as follows

standard code modules
forms/reports moudules
sub/functions


So, the "lowest" scope is when you delcare a var in a sub-funciton, and we
would say the scope is local to the sub/function only.

note that if you declare a variable with the same name in a lower level
scope (say stardard code module), and the again declear the var in a
sub/fuction, the var delceared in the sub/function will be used here...and
when the sub/function ends, then we back to using the next lower scope.

It's very easy to understand this if you think of it as a stack of cards,
and for each level you go up you are throwing a card (variable) on top of
the deck. As you exit the sub/function and go down a level you are pulling
the cards back off the top of the deck...

 

[BruceM]

Thanks to both for the replies. This posting is in response to both.

I have a much clearer understanding now of the scope of variables (thanks
for pointing out how to find the Help section, as information is not always
where it can be readily found by somebody who does not know the correct
terminology). As far as variables being reset "invisibly", that is my term.
I meant that variables may be reset without the user knowing, so that a
variable could suddenly have an unexpected value.

If I understand correctly, any variable (public, private, or static) will be
reset if there is an unhandled error. In light of this it seems a static
variable offers no advantages over any other kind of variable in this
regard. Does the same apply to a static function?

I would like to be clearer about unhandled errors. I generally use error
handling along the lines of:
On Err GoTo ProcErr
with a message box containing specific information about the error number
and description, and in which event it occurred. In that sense all errors
and handled, but is that is what you mean? Or is a handled error in this
context one that is specifically trapped by number? In other words, if the
error handling routine generates an error message, is that an unhandled
error?

Here is a better example of how I have used global variables:
Public ctl as Control

Now when I need to loop through controls I can just do:

For Each ctl in Me.Controls ...

I see one disadvantage is that anybody maintaining the code will need to
hunt down the variable declaration if it is some place other than in the
procedure, or in the form's code module. The "advantage" is that the
declaration only has to happen once, but I am starting to realize it is
probably offset by the potential confusion.

I think can see, too, that an often-used variable such as strWhere for
filtering would be best declared as private each time it is used, so that
its value cannot be applied in the wrong place.

Here is a situation in which I do not know how best to proceed, even in
light of the new information I have learned. I have a Purchase Order form
with a LineItems subform, based on related tables in a standard set-up for
this situation. The LineItems subform includes a combo box that gets its
Row Source from the Products table. If a product is not in the Products
table, the user can type its description into the combo box. The combo box
Not InList event causes an unbound pop-up form to open (in dialog mode), on
which the user enters the rest of the Product information (VendorCode,
etc.). The NotInList event has a NewData argument, which I convert to a
string in order that the pop-up form includes the Description text box
already filled in.
After filling in the rest of the data, the user clicks an Add button, which
converts the text box values into strings and closes the pop-up form. The
NotInList event opens a recordset and adds the string values. This is an
abbreviated version of the code:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Select Case MsgBox("New Product?", vbQuestion + vbYesNo)
Case vbYes
strNewDescr = NewData
DoCmd.OpenForm "frmAdd", , , , , acDialog
Set rs = db.OpenRecordset("tblProduct", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SupplierID = lngSuppID
rs!ProdCode = strNewCode
rs!ProdDescr = strNewDescr ' This could be NewData
rs!ProdUnit = strNewUnit
rs!UnitPrice = varUnitPrice
If Err Then
MsgBox Err.Number etc.
rs.Close
Exit Sub
End If
rs.Update
rs.Close

The variables in the five lines of code after rs.AddNew were passed from the
pop-up form (frmAdd). Where do I declare the variables? If I declare them
as Public in frmAdd, from what I understand they will not be available to
the NotInList event after frmAdd is closed. As I recall I can hide a form
that is open in Dialog mode, and will then be able to copntinue with the
next line of code (Set rs etc. in this case). If so I could keep frmAdd
open, and use the NotInList event to take the values directly from the
frmAdd text boxes. Or I could declare the variables as Public in the
LineItems form (or the Purchase Order form), but then am I getting into the
global variable house of cards? The business of passing variables from one
object to another seems to come up from time to time, so I would like to
adopt a viable and stable strategy for dealing with this.
If it matters, the next part of the NotInList event is to write the newly
created product record to the LineItems subform source table. The whole
point is to make the process as seamless and simple as possible for the
users, most of whom have responsibilities that don't involve a lot of
computer work.
Also, as I recall I had frmAdd be unbound because otherwise the NotInList
event generated a standard message about the item not being in the list.
The NotInList code is adapted from Dev Ashish's code at the mvps web site,
but that code was to add a single item (the NewData item). That code
worked, so I adapted the approach to the situation where I needed to add a
multi-field record.

 

[Albert D. Kallal]

Thanks to both for the replies. This posting is in response to both.

If I understand correctly, any variable (public, private, or static) will
be reset if there is an unhandled error. In light of this it seems a
static variable offers no advantages over any other kind of variable in
this regard. Does the same apply to a static function?

Your above assumptions are correct. The only advantage of using a "static"
declaration for variables in a sub/function is that if you have to call the
function/sub over and over and need some variables to "retain" their values
from call to call. This feature simply means that you can limit the scope of
the variables to JUST that routine, but they keep their values.

In programming languages before we had a static type declaration ability,
you would have to declare your variable at a lower scope level (say perhaps
at a module level) for the ONE routine to keep some of the values intact
when the routine EXITS. If you don't use the static declaration, when you
exit the subroutine/function, then ALL of the variables declared in that
fucntion/sub instantly lose their values. So, the exception to this rule is
of course when you use the static key word to declare the variable(s).

Once again however, the the issue of the variables being reset due to an
handeled error is not related to this issue of a static varible in a sub.

In that sense all errors and handled, but is that is what you mean?

Yes, if you have a error handling code in every single function and sub that
you write in your application, then it's going to be impossible for you to
have an un-handled error. And, if you have some subs and routines that don't
have error code handling in them, then you have the possibility of a un
handeled error.

Or is a handled error in this context one that is specifically trapped by
number?

no...is just a case of where you have an routine where no error code traps
the error (so it has nothing to do with a error number. It's just the issue
that if an error occurs and you don't have an error handling routine, all
your variables are simply lost..).

As mentioned, I never had this problem since I've always for years and years
have religiously distributed a mde to my users, and no matter if you have an
error handling or not, a mde does not lose its variables of under any
circumstances (even when your application has zero error handling code in
it).

Here is a better example of how I have used global variables:
....
I see one disadvantage is that anybody maintaining the code will need to
hunt down the variable declaration if it is some place other than in the
procedure, or in the form's code module. The "advantage" is that the
declaration only has to happen once, but I am starting to realize it is
probably offset by the potential confusion.

I agree with your above conclusion 100%

more comments follow:

Here is a situation in which I do not know how best to proceed, even in
light of the new information I have learned. I have a Purchase Order form
with a LineItems subform, based on related tables in a standard set-up for
this situation. The LineItems subform includes a combo box that gets its

DoCmd.OpenForm "frmAdd", , , , , acDialog
Set rs = db.OpenRecordset("tblProduct", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!SupplierID = lngSuppID
rs!ProdCode = strNewCode
rs!ProdDescr = strNewDescr ' This could be NewData
rs!ProdUnit = strNewUnit
rs!UnitPrice = varUnitPrice
If Err Then
MsgBox Err.Number etc.

The variables in the five lines of code after rs.AddNew were passed from
the pop-up form (frmAdd). Where do I declare the variables? If I declare
them as Public in frmAdd, from what I understand they will not be
available to the NotInList event after frmAdd is closed.

That is correct, but why don't you make the form based on tblProduct, and
open the from in add mode? That way, you not have to write all that extra rs
code? However a lot of people in the newsgroups actually suggest to make
some global variables to return the values back from the form after it's
closed, I find that a poor programming practice, and you'll have to declare
the variables somewhere.

In the following article of mine I explain in far better details as to how
you can have a form return all the values, and you don't need to declare
variables or even use globals:

http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html

And the above also shows that in place of using variables, you simply can
reference the text controls on the form directly.

As I recall I can hide a form that is open in Dialog mode, and will then be
able to copntinue with the next line of code (Set rs etc. in this case).
If so I could keep frmAdd open, and use the NotInList event to take the
values directly from the frmAdd text boxes.

yes, that is exactly what my article suggesting to do.

Assuming you don't wanna examine the text controls on that form, as my above
article shows, let's pretend for some strange reason we do want use
variables (I see no reason why). you would simply declare the variables as
public in the form frmAdd. You can then go:


someValue = forms!frmAdd.nameOfPublicVarible

So keep in mind that if you do delare module level variables in a form's
module level as **public**, then any routine in any other place your
application can reference the variables in that form (as long as the form is
open) as I've shown above.

Or I could declare the variables as Public in the LineItems form (or the
Purchase Order form), but then am I getting into the global variable house
of cards?

Again a good assumption and thinking on your part, I agree you're getting
into the global variable house of cards, and those other forms would not be
re-usable in other applications or by other code if you start declaring
variables that actually belong in frmAdd. The design philosophy and approach
here is to encapsulate as much into each individual object or form as
possible. (so in this case, since those variables belong to frmAdd, that is
where we should try and limit or keep those variables).

Also, as I recall I had frmAdd be unbound because otherwise the NotInList
event generated a standard message about the item not being in the list.

The above is incorrect and I'm going to cut and paste a post of how to solve
this problem correctly ----------------------------------

The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words, quite
a bit of stuff happens to ensue that the combo box is re-loaded, and re-set.
(so, don't forget to set the response as above...it is the key here).

However, there
is one thing we should do, and that is that then the frmAddClient loads, we
should put in the NewData value into the correct field so the user does not
have to re-type it. (and it helps the user "see" things a lot better". So,in
our forms on-load event, we will take the value of NewData, and set the
correct field. The code for this is:

if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

That is all you need. However, to make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

Further, turn off the forms ability to "add new" records. Yes, you read this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record at
a time, then put the follwing in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.

 

[Klatuu]

Excellent disertation on the use of the Not In List event, Albert.

 

[BruceM]

Thank you again for the time and attention you have put into this response.
Questions and comments inline.

Your above assumptions are correct. The only advantage of using a "static"
declaration for variables in a sub/function is that if you have to call
the function/sub over and over and need some variables to "retain" their
values from call to call. This feature simply means that you can limit the
scope of the variables to JUST that routine, but they keep their values.

OK, this will probably be good to know.

In programming languages before we had a static type declaration ability,
you would have to declare your variable at a lower scope level (say
perhaps at a module level) for the ONE routine to keep some of the values
intact when the routine EXITS. If you don't use the static declaration,
when you exit the subroutine/function, then ALL of the variables declared
in that fucntion/sub instantly lose their values. So, the exception to
this rule is of course when you use the static key word to declare the
variable(s).

Once again however, the the issue of the variables being reset due to an
handeled error is not related to this issue of a static varible in a sub.


Yes, if you have a error handling code in every single function and sub
that you write in your application, then it's going to be impossible for
you to have an un-handled error. And, if you have some subs and routines
that don't have error code handling in them, then you have the possibility
of a un handeled error.


no...is just a case of where you have an routine where no error code traps
the error (so it has nothing to do with a error number. It's just the
issue that if an error occurs and you don't have an error handling
routine, all your variables are simply lost..).

As mentioned, I never had this problem since I've always for years and
years have religiously distributed a mde to my users, and no matter if you
have an error handling or not, a mde does not lose its variables of under
any circumstances (even when your application has zero error handling code
in it).


I agree with your above conclusion 100%

more comments follow:



That is correct, but why don't you make the form based on tblProduct, and
open the from in add mode? That way, you not have to write all that extra
rs code? However a lot of people in the newsgroups actually suggest to
make some global variables to return the values back from the form after
it's closed, I find that a poor programming practice, and you'll have to
declare the variables somewhere.

I have spent the best part of the day attempting to base the form on
tblProduct. I finally stripped the NotInList code down to:

If MsgBox("New Product?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmAdd", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Undo
End If

However, I still get the notification that the item is not in the list. In
stepping through the code I find that the message appears after the code has
run. When the highlighted line of code is End Sub and I press F8 I get the
message that the item isn't in the list. I think I have done everything you
suggested, but I have run into a wall here. I don't see anything left to
try.

However, I did get away from the global variables by hiding frmAdd and
leaving it open. In the NotInList event I declared private variables, then
assigned their values from text boxes on frmAdd. At the end of the
NotInList event I close frmAdd.

Even though I have been frustrated by the attempt to use a bound form for
NotInList, I have taken a lot of valuable information away from this
exchange. For one thing is the added information about the use of
variables, but there was lots of other good stuff like about the use of form
in Dialog mode, and about setting the Cycle property to Current Record.
That last one is simple, but very useful.

In the following article of mine I explain in far better details as to how
you can have a form return all the values, and you don't need to declare
variables or even use globals:

http://www.members.shaw.ca/AlbertKallal/Dialog/Index.html

And the above also shows that in place of using variables, you simply can
reference the text controls on the form directly.


yes, that is exactly what my article suggesting to do.

Assuming you don't wanna examine the text controls on that form, as my
above article shows, let's pretend for some strange reason we do want use
variables (I see no reason why). you would simply declare the variables as
public in the form frmAdd. You can then go:


someValue = forms!frmAdd.nameOfPublicVarible

So keep in mind that if you do delare module level variables in a form's
module level as **public**, then any routine in any other place your
application can reference the variables in that form (as long as the form
is open) as I've shown above.


Again a good assumption and thinking on your part, I agree you're getting
into the global variable house of cards, and those other forms would not
be re-usable in other applications or by other code if you start declaring
variables that actually belong in frmAdd. The design philosophy and
approach here is to encapsulate as much into each individual object or
form as possible. (so in this case, since those variables belong to
frmAdd, that is where we should try and limit or keep those variables).



The above is incorrect and I'm going to cut and paste a post of how to
solve this problem correctly ----------------------------------

The easy way to do this is have ms-access do ALL of the work for you. So,
given that new data is the actually text you typed into the combo, then
you
can do the following:

Private Sub Distributee_NotInList(NewData As String, Response As Integer)

if MsgBox("Do you want to add this value to the list?", _
vbYesNo) then
DoCmd.OpenForm "frmAddClient", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If

End Sub

The above is ALL YOU need. You can see it is not much code.

Note that by setting response = acDataErrAdded, then ms-access does a
re-load, and a re-query of the comb box list for you. In other words,
quite
a bit of stuff happens to ensue that the combo box is re-loaded, and
re-set.
(so, don't forget to set the response as above...it is the key here).

However, there
is one thing we should do, and that is that then the frmAddClient loads,
we
should put in the NewData value into the correct field so the user does
not
have to re-type it. (and it helps the user "see" things a lot better".
So,in
our forms on-load event, we will take the value of NewData, and set the
correct field. The code for this is:

if isnull(me.OpenArgs) = false then

me.CompanyName = me.Openargs

endif

I did this part. I know about OpenArgs, but for some reason I got it into
my head to pass a string variable. I changed it to add OpenArgs to the
OpenForm command, as you suggested.

That is all you need. However, to make life a bit easier to your users,
there are a good number of things you should set in frmAddClent to make
life
better.

They are:

Set the forms "cycle" property to current. This means the cursor when on
the
bottom of the screen at the last field does not jump to another record. It
is horrible when a user bumps the tab key, and access goes to another
record, so, set the forms cycle property (other tab) to current. In fact,
this is a good setting for most forms, and NOT just this add example.

Since this is just a add form, then turn off the record navigation at the
bottom, again this serves only to confuse the user (since you have a
frmAddClient, I bet you already did this too). Why confuse the user!

I almost always use substitute command buttons. Most often lately I have
been using Lebans use of a command button subform that allows the AutoRepeat
property of the command button work for record navigation. I keep the
subform in a storage database, and import it into new projects.

Further, turn off the forms ability to "add new" records. Yes, you read
this
100% correct. You want turn off the forms allow additions property. The
reason for this is that the above "open" form will OVER RIDE this setting.
This is ideal, since once again, hitting page/down key, or even the mouse
wheel will not cause the form to jump to another record, and confuse the
heck out of the user (this is along the same lines as the tab key setting
above as per "cycle" setting).

Note if you need this form to do "double duty" and want to use it in other
places in the application to allow the user will add MORE THEN ONE record
at
a time, then put the follwing in the forms on open

if isnull(me.OpenArgs) = false then

me.Company = me.Openargs
me.AllowAdditions = false
Me.NavigationButtons = False

endif

That way, this form can be used else where in the program, but for our
combo
box, the navigation buttons, and accidental adding of MORE THEN one record
will not occur.

All in all, you don't have to do any of the additional things I mention
above, but can *just* use the short sample code, but all of the above
together makes a real slick app.

I wish I knew why it isn't working, but thanks again for all of the
attention you put into these responses.

 

[Klatuu]

Hopefully, Albert will chime in on this, because I find it a puzzlement, too,
but I have found that even though he implies it is not necessary, I foun
found that if you undo the combo box at the beginning of the event, you don't
get the message. What is happening is the event is actually firing twice:

Me.MyCombName.Undo

If MsgBox("New Product?", vbQuestion + vbYesNo) = vbYes Then
DoCmd.OpenForm "frmAdd", , , , acFormAdd, acDialog, NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
Me.Undo
End If

 

[BruceM]

Thanks the the observation. I'm about through for today, but I will give it
a try tomorrow. In some of my experiments I thought I had observed the
event firing twice (at times, anyhow) as I stepped through the code, but I
didn't manage to put the pieces together and figure out why it was running
again.

 

[BruceM]

No, that didn't do it either. The event that was firing twice was After
Update in frmAdd (the pop-up form in which I enter the new product
information for a single record). I'm stumped, but since opening, adding
to, updating, and closing the recordset works, I will stick with that. I
had wondered if the problem is the code doesn't immediately "see" the
updated recordset when I add a new product record by way of a bound form.
When I use the recordset method there is a line of code that specifically
updates the recordset, so I wondered if there is a way to have the code
update the recordset (the Products table) to which the new product has been
added, but if there is I cannot find it.