find record in dynaset

[kronar]

Am trying to find a record in a ynaset table "EM_EmpNumber" where EMEmpNo is equal to The text box value 'EnterEmpNo'

I don't understand why these don't work? Got DoCmd from help function Access 97 and the rec.FindFirst from book 'Beging Access 97 VBA Programmong by Smith and Sussman.
Any ideas? The .MoveNext works but is ineligant/Inafficient.



Private Sub EnterEmpNo_LostFocus()
'Look up EmpNo in Emp table - If found display Emp Name in txtEmpName

Dim strEmpNo As String
Stop


'This dosen't work!
'DoCmd.FindFirst EnterEmpNo
'If NoMatch Then
'MsgBox "no emp found"
'Else
'MsgBox "employee found!!!"
'End If




'This didn't work
'strEmpNo = EnterEmpNo
'EMrec.FindFirst "EM_EmpNumber = " & strEmpNo 'get error 3251 Operation not supported for this type of object

'This didn't work
'EMrec.FindFirst "EM_EmpNumber = " & strEmpNo 'get error 3251 Operation not supported for this type of object

'If EnterEmpNo = EMrec(0) Then
'txtEmpName = EMrec(2) & " " & EMrec(1)
'Else
'MsgBox "This is not a valid Employee Number"
'End If




'This works!!!
'EMrec.MoveFirst

'LoopEM:
'If EMrec.EOF Then
'txtEmpName = Null
'EnterEmpNo = Null
'MsgBox "This is not a valid Employee Number"
'ElseIf EnterEmpNo = EMrec(0) Then
'txtEmpName = EMrec(2) & " " & EMrec(1)
'Else
'EMrec.MoveNext
'GoTo LoopEM
'End If
End Sub

 

[PHV]

Seems EMrec is an ADODB.Recordset, so you may try this:
EMrec.MoveFirst
EMrec.Find "EM_EmpNumber = " & strEmpNo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kronar]

I tried this

EMrec.MoveFirst
strEmpNo = EnterEmpNo
EMrec.FindFirst "EM_EmpNumber = " & EnterEmpNo
get error msg 3251 Operation not supported for this type of object

tried this
EMrec.MoveFirst
strEmpNo = EnterEmpNo
EMrec.FindFirst "EM_EmpNumber = " & strEmpNo

get same error msg 3251

EnterEmpNo is text box on a form of type integer
EM_EmpNumber is integer
EM_Emp table is dataset type table indexed on EM_EmpNumber

 

[PHV]

An ADODB.Recordset has NO FindFirst method.
Please reread carefully my previous suggestion( .Find vs .FindFirst).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kronar]

When I run this:

strEmpNo = EnterEmpNo
EMrec.MoveFirst
EMrec.Find "EM_EmpNumber = " & strEmpNO

I get error "Method or Data Member not found" with the .Find highlighted.

 

[PHV]

sorry, forgot to ask:
what is EMrec ? where and how is it defined ? where and how is it instantiated ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kronar]

In General Declarations section
Dim EMrec As Recordset

Public Sub GetRecsEnterTimes()
'Stop

Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)
Set temprec = db.OpenRecordset("EnterTimes")
Set EMrec = db.OpenRecordset("EM_Emp")

 

[PHV]

If EM_Emp is a table (instead of a query) and if EM_EmpNumber is indexed then you have to use the Index property and the Seek method of EMrec.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886