Variables

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

Guest

Good afternoon,
I'm working on a small project that requires a 'global' type variable based
on a single input. Unfortunately, I cannot figure out how to get Access to
stop asking me for a new value for my variable. I'm not yet in development
for a solution as I'd like to learn more about how to 'save' variables until
they are changed either by user input or programmatically.

Thank you in advance!

Derek
 
if your global variable is in code that has stopped
running or a form that has be closed, your variable is
gone which is why access keeps asking for it.
You might try sticking your variable in a form for future
reference and overwrite it when needed. or you could stick
in into a hidden text box or lable is you still have the
form open.
Things that reside in memory go away sooner or later. i
have not seen your code or your process so i am guessing
that your are try to use a variable that access deleted
when it no longer needed it. you may still need it but
when the code stopped or the form closed, access didn't
need it anymore.
 
Here's what I was able to come up with for my first shot at code. Please
note that I'm not developing any output yet. I realize that I could msgbox
all the variables - I've done this and it gave me the expected results. I
found the STATIC statement long after my original posting. The database
application's purpose is to compare the weekly sales (units) of all the item
numbers (generally 1 to 1 with slot numbers) and put the higher sales in the
C level and the lower items in B and D and the lowest in A and E. There are
more slots than item numbers, and any item over X height requires E level,
even if it's got the highest sales.

At this point, I'm not concerned with assigning new slots, just levels. And
seeing what % of Current Levels vs. New Levels change or stay the same.

Thanks for your help!

Option Compare Database
Public Sub Command0_Click()
Static Pickbox As String

Dim eUsed As Integer
Dim dUsed As Integer
Dim cUsed As Integer
Dim bUsed As Integer
Dim aUsed As Integer
Dim eRem As Integer
Dim dRem As Integer
Dim cRem As Integer
Dim bRem As Integer
Dim aRem As Integer

Pickbox = InputBox("Please enter the PickBox ID:", "Entry Required")

eUsed = eUse(Pickbox)
dUsed = dUse(Pickbox)
cUsed = cUse(Pickbox)
bUsed = bUse(Pickbox)
aUsed = aUse(Pickbox)

End Sub

Functions:

Public Function eUse(ePickbox As String) As String
eUse = DCount("[NSlot]", "tbl" & ePickbox, "NSlot = 'E'")
End Function

Public Function dUse(dPickbox As String) As String
dUse = DCount("[NSlot]", "tbl" & dPickbox, "NSlot = 'D'")
End Function

Public Function cUse(cPickbox As String) As String
cUse = DCount("[NSlot]", "tbl" & cPickbox, "NSlot = 'C'")
End Function

Public Function bUse(bPickbox As String) As String
bUse = DCount("[NSlot]", "tbl" & bPickbox, "NSlot = 'B'")
End Function

Public Function aUse(aPickbox As String) As String
aUse = DCount("[NSlot]", "tbl" & aPickbox, "NSlot = 'A'")
End Function

Derek
 
I've revamped my code, and while it works, it does ask me twice the same
question. Can someone please help me out?

Thank you!

Public Sub Command0_Click()
Static Pickbox As String

Dim eUsed As Integer
Dim dUsed As Integer
Dim cUsed As Integer
Dim bUsed As Integer
Dim aUsed As Integer
Dim eRem As Integer
Dim dRem As Integer
Dim cRem As Integer
Dim bRem As Integer
Dim aRem As Integer
Dim ecurrent As Integer
Dim dcurrent As Integer
Dim ccurrent As Integer
Dim bcurrent As Integer
Dim acurrent As Integer
Dim aSQL As String
Dim bSQL As String
Dim cSQL As String
Dim dSQL As String
Dim eSQL As String
Dim tempsql As String

Pickbox = PBox()
DoCmd.OpenQuery "qryMakeTempPickbox", acViewNormal

DoCmd.Rename "tbltemp" & Pickbox, acTable, "tblTempPickbox"
eUsed = eUse(Pickbox)
dUsed = dUse(Pickbox)
cUsed = cUse(Pickbox)
bUsed = bUse(Pickbox)
aUsed = aUse(Pickbox)


ecurrent = DMax("[ESlots]", "tbltemp" & Pickbox, "")
dcurrent = DMax("[DSlots]", "tbltemp" & Pickbox, "")
ccurrent = DMax("[CSlots]", "tbltemp" & Pickbox, "")
bcurrent = DMax("[BSlots]", "tbltemp" & Pickbox, "")
acurrent = DMax("[ASlots]", "tbltemp" & Pickbox, "")

eRem = ecurrent - eUsed
dRem = dcurrent - dUsed
cRem = ccurrent - cUsed
bRem = bcurrent - bUsed
aRem = acurrent - aUsed

eSQL = "UPDATE " & "tbltemp" & Pickbox & " SET [ESlots] = " & eRem
dSQL = "UPDATE " & "tbltemp" & Pickbox & " SET [DSlots] = " & dRem
cSQL = "UPDATE " & "tbltemp" & Pickbox & " SET [CSlots] = " & cRem
bSQL = "UPDATE " & "tbltemp" & Pickbox & " SET [BSlots] = " & bRem
aSQL = "UPDATE " & "tbltemp" & Pickbox & " SET [ASlots] = " & aRem

DoCmd.RunSQL eSQL
DoCmd.RunSQL dSQL
DoCmd.RunSQL cSQL
DoCmd.RunSQL bSQL
DoCmd.RunSQL aSQL


End Sub



Public Function eUse(ePickbox As String) As String
eUse = DCount("[NSlot]", "tbltemp" & ePickbox, "NSlot = 'E'")
End Function

Public Function dUse(dPickbox As String) As String
dUse = DCount("[NSlot]", "tbltemp" & dPickbox, "NSlot = 'D'")
End Function

Public Function cUse(cPickbox As String) As String
cUse = DCount("[NSlot]", "tbltemp" & cPickbox, "NSlot = 'C'")
End Function

Public Function bUse(bPickbox As String) As String
bUse = DCount("[NSlot]", "tbltemp" & bPickbox, "NSlot = 'B'")
End Function

Public Function aUse(aPickbox As String) As String
aUse = DCount("[NSlot]", "tbltemp" & aPickbox, "NSlot = 'A'")
End Function

Static Function PBox()
PBox = InputBox("Please enter the PickBox ID:", "Entry Required")
End Function
 
Back
Top