trying to assign a variable to the field part of recordset!field

[Imran J Khan]

In Access 97, I am trying to assign a recordset to DAO.RecordSet type
variable as follows:

Public Sub GetEmail (strSQL as String, strFieldEmpId as String)
Dim rsSale as DAO.RecordSet
Dim rsEmp as DAO.RecordSet

rsSale = strSQL
rsEmp = "SELECT fldEmail FROM tblEmployee WHERE empId = " & rsSale!empId

I want to replace rsSale!empId with some thing like:

rsSale!strFieldEmpId

so that I can pass to this routine the EmpID field name which would be vary.
I have tried the following:
strField = "rsSale!" & strFieldEmpID, but this does not get the value of the
empID and simply passes is as a string "rsSale!strFieldEmpID" to the next
statement.
Imran

 

[Jack Leach]

You need to "Set" the object variable using the Database.OpenRecordset method:


Dim rs As DAO.Recorset

Set rs = CurrentDB.OpenRecordset("SELECT * FROM....")



Then, reference the field in the recordset to assign the value as part of
your string:

Dim rsSale As DAO.Recordset
Dim rsEmp As DAO.Recordset

Set rsSale = DoCmd.Openrecordset(strSQL)

'you need to move rsSale to a record here...
If rsSale.Recordcount <> 0 Then
rsSale.MoveFirst
Else
Exit Sub
End If

Set rsEmp = DoCmd.OpenRecordset( _
"SELECT fldEmail FROM tblEmployees " & _
"WHERE empID = " & rsSale("empID"))



You will need to move the record of the rsSale correctly as noted above...
otherwise the recordset will be at the beginning of the file (not a record).
Then you might want to make sure that there's a valid record in the field you
are trying to use. You may be much better off using DLookup or the like...
or even better, depending on your end goal this may be much more easily and
efficiently accomplished with SQL rather than recordsets.

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Imran J Khan]

Thank you Jack.
What I was not doing right was "WHERE empID = " & rsSale("strFieldEmpID").
I was doing it "WHERE empID = " & rsSale!strFieldEmpID . Error was object
not in collection.
It works great now.
Imran