Global Variable ? help 1

[Cloonalt]

I'm dimming some variables in the general declarations area of a form. On the after update of a text box I use the variables successfully to collect data in a SQL update string.

Then the user has to click on a button to update the data to the database. But at that point I don't have the variables any more.

Should this work? Do I need globable variables? Constants?

What's the best way to do this? Any help appreciated?

 

[Remou]

What is the code?

 

[Cloonalt]

This is the code I use the update the data. (I concatenate two strings). But at this point my variables are gone.

Private Sub cmdProcess_Click()
On Error GoTo ErrorHandler:

Dim sUserLoc As String
Dim sUserLocfullname As String
Dim sFolderLoc As String
Dim sFolderLocfullname As String
Dim sInsertLocFullname

cmdOK.Enabled = False

Dim strSQL As String
Dim rec As DBResults
Dim DB As EisDB
Dim lngRowIndex As Long
Dim strCell As String


intMsgIndex = 0

'Loop through the grid
For lngRowIndex = 1 To ssQueued.MaxRows

ssQueued.Row = lngRowIndex

If Not IsNull(ssQueued.CellValue(ssQueued.Col, ssQueued.Row)) Then
ssQueued.Col = 7
strCell = ssQueued.CellValue(ssQueued.Col, ssQueued.Row)
ssQueued.Col = 1

Select Case ssQueued.CellValue(ssQueued.Col, ssQueued.Row)

Case "F"
strconcatenated = strconcatenated & "UPDATE folder SET flocation = '" & sUserLoc & "'" & ", fcurrloc = " & " '" & "In: " & sUserLocfullname & "(" & sUserLoc & ")" & "'" & " where fbarcode = '" & strCell & "' "

Case "I"
strconcatenated = strconcatenated & "UPDATE incert set icurrloc = " & " '" & " In: " & sUserLocfullname & "(" & sUserLoc & ")" & " '" & " where ibarcode = '" & strCell & "' "
End Select


End If

Next lngRowIndex

Set DB = Nothing

sUserLoc = ""
sFolderLocfullname = ""
sFolderLoc = ""

cmdOK.Value = True

Exit Sub

ErrorHandler:

MsgBox "Error " & Err.Number & vbCr & _
"Location: " & "VBA.frmCheckIn.cmdOK" & vbCr & _
Err.Description, vbOKOnly + vbExclamation, _
"Records Check In

 

[Remou]

Can you pass the variables (?):

Private Sub cmdProcess_Click(sUserLoc, sUserLocfullname, etc ...)

 

[Cloonalt]

I don't have the variables when I click cmdProcess.

I get them on an after update of a text box, then do cmdProcess.

Thanks.

 

[Remou]

You will need set public variables in a module or store them in a hidden field.

 

[Cloonalt]

In a hidden field on the form?

Hate to ask a dumb question, but how do I declare public variables?

I tried that and it didn't work - I did below:

Public Sub Variables()

Dim sUserLoc As String
Dim sUserLocfullname As String
Dim sFolderLoc As String
Dim sFolderLocfullname As String
Dim sInsertLocFullname
Dim sBarcode As String
Dim strconcatenated As String

End Sub

Thanks!

 

[rjoubert]

Click on the modules tab in your Database, and create a new module. Then, just declare your global variables.

Global sUserLoc as String
...

 

[Remou]

You put them in the declaration section of a module, not the one attached to the form, so it would look like this:

[tt]Option Compare Database
Public gstrUserLoc[/tt]

Then set them at a suitable time, when the form opens or when the database opens:

[tt]Public Sub Variables()
sUserLoc = "Aarg"
End Sub[/tt]

 

[Remou]

A warning on global variables:
Global Variables get deleted after Execution error - remedy?
thread705-1035117

 

[Cloonalt]

Thank you.