Variable Loses Scope in Recordset

[Newbie in ChiTown]

I created a subprocedure that includes a recordset as follows:

Private Sub Combo17_Click()

'Declaring object and string variables
Dim DB As Database
Dim rec As Recordset
Dim sDealName As String

'Assign Object variable to current database
Set DB = CurrentDb

'Assign object variable to recordset
Set rec = DB.OpenRecordset("qryName", dbOpenDynaset)

'Comparison of value from user to recordset
rec.FindFirst "[EmployeeID] =" & Nz(Me!Combo17.Value, 0)

'If no match is found, user receives error message _
'exit routine

If rec.NoMatch Then
MsgBox "Check your entry", vbCritical, "No Matches"
Me!Combo17.Value = ""
Exit Sub
Else
' If record is found, value entered by user is passed to variable sFirstName
sDealName = Nz(rec.Fields("Firstname"), "")

If sDealName = "" Then
MsgBox "Your sDealName does not exist"
Me!Combo17.Value = ""
Exit Sub
End If

End If

rec.Close

End Sub

The problem is that the variable sDealName loses scope when I close the recordset. I need to pass the value from this sDeal variable to another subprocedure.

Thank you.

[sashi]

Hi there,

Declare the variable in a module file as public variable, kindly refer to below sample, good luck & take care.

  'Some module file
  Public sVariableName as String
 

[willakawill]

Do yo want to store this value for use later or do you wish to use it immediately?

[Newbie in ChiTown]

Do yo want to store this value for use later or do you wish to use it immediately?

I want to store this value for use later.

[willakawill]

I want to store this value for use later.

Then you can either use a global variable as per Saashi above or, if the form you are using is not going to be closed, put the following code at the top of the form code module:

Private variable_name As variable_type

This will remain in scope for as long as the form is open and will not suffer from the problems of using global variables which you can get lost with if you have several forms and modules.

[Newbie in ChiTown]

Then you can either use a global variable as per Saashi above or, if the form you are using is not going to be closed, put the following code at the top of the form code module:

Private variable_name As variable_type

This will remain in scope for as long as the form is open and will not suffer from the problems of using global variables which you can get lost with if you have several forms and modules.

Thank you. I used the global variable and it works!