Global Variable Issue

[EnglisAP]

I am currently working on a way to automatically generate our clients account number. I have the process pretty much working the only issue i have is the global variable does not update. the form reads the variable and does the mathematical calculations necessary but even though i set the new number equal to the old the variable remains the same number each time i restart the form. I will include the coding for both my form and module below.
If anyone has an idea i would greatly appreciate the assitance it has been a very long time since i have coded in access.

Module Code:

Option Compare Database
Option Explicit

Global GenAct As Integer

Public Sub Init_Globals()
GenAct = 43
End Sub

Form Code:

Private Sub Form_Load()
Call Init_Globals

Dim act As Integer
Dim actbeg As Double

actbeg = 4230000
act = GenAct + 1
GenAct = act


starttb.Value = date
statustb.Value = "Pending"
accounttb.Value = actbeg & act

End Sub

Thank you again for your help in advance.

 

[ChipperT]

I am currently working on a way to automatically generate our clients account number. I have the process pretty much working the only issue i have is the global variable does not update. the form reads the variable and does the mathematical calculations necessary but even though i set the new number equal to the old the variable remains the same number each time i restart the form. I will include the coding for both my form and module below.
If anyone has an idea i would greatly appreciate the assitance it has been a very long time since i have coded in access.

Module Code:

Option Compare Database
Option Explicit
 
Global GenAct As Integer
 
Public Sub Init_Globals()
GenAct = 43
End Sub

Form Code:

Private Sub Form_Load()
Call Init_Globals
 
Dim act As Integer
Dim actbeg As Double
 
actbeg = 4230000
act = GenAct + 1
GenAct = act
 
 
starttb.Value = date
statustb.Value = "Pending"
accounttb.Value = actbeg & act
 
End Sub

Thank you again for your help in advance.

Each time you load the form you are calling Init_Globals, setting the value of GenAct to 43, which you then increment to 44. You need to call Init_Globals in an autoexec, if I understand what you are trying to do.

 

[vbaInet]

Why have it as a Global variable in the first place? How many forms use that variable?

 

[EnglisAP]

Chipper T: Sounds like a possible solution how would i go about in doing that?

vbaInet: I was taught that a global is the only way for it to store the variable and alter it each time the form loads. the number needs to increase by one for every client we add and if i had in on the form load it would not change the value. If you have a better solution please let me know.

 

[boblarson]

And another thing - GLOBAL is OLD, OLD, OLD code. You use

Public GenAct As Integer instead

But a couple of my observations.


1. As Chipper has said, you are resetting it each time anyway.

2. I would use a PROPERTY for this instead of just the variable:

'In the standard module
 
Public lngGenAcct As Long
 
Property Let GenAcct(lngInput As Long)
   lngGenAcct = lngInput
End Property
 
Property Get GenAcct() As Long
      GenAcct = lngGenAcct
End Property

Also, I would suggest using long instead of integer because I've been in the place where 7 years ago someone thought "oh we'll never get that high" and we did and then it broke.

You can then also initialize the property when the form opens up by getting the max number from the table. Then you don't reuse the same number(s).

 

[vbaInet]

vbaInet: I was taught that a global is the only way for it to store the variable and alter it each time the form loads. the number needs to increase by one for every client we add and if i had in on the form load it would not change the value. If you have a better solution please let me know.

I was just wondering what you were doing.

I would use this:

DMax("Mid([[COLOR=Red][B]Field[/B][/COLOR]], 8)", "[[COLOR=Red][B]TableName[/B][/COLOR]]") + 1

 

[EnglisAP]

boblarson: i will take this into consideration the reason i start at a certain number is that we are starting this database in the middle of business. Also the number consists of a value before hand the new clients will start with account numbers of 423000044 and up which is why the number needs to to be 4230000 + variable. so the only question i have is how to use your code and use a starting number of 43.

 

[EnglisAP]

well with a combination of all your efforts i have it up and running the only issue i have is i want to change the account number to start at 1 and go up that was easy is there anyway to format the text-box so it shows 4230000 then the account number, but does not save that as the value?

For example account number 34 would display in the text-box as 423000034 but it would drop the beginning when stored in the table and be only 34

 

[vbaInet]

If you wanted it to start from 43 all you would have done was this:

Nz(DMax("Mid([Field], 8)", "[TableName]"), 42) + 1

Look into the Format property of the textbox. You will find this under the first tab in the Property Sheet. I think you put just:
"4230000"0

 

[EnglisAP]

thank you all for your help i have completed the task i was aiming for. and thanks to your help with the code the database is working like a dream