reference in recordset.findfirst

S

SandyR

I am trying to find a record in a recordset where the value of the field
empno is equal to the value selected in an unbound combo box. I am getting
the error message: Error 3072 (unknow function name) on the findfirst line.
Here is my code:

Private Sub cboEmpno_Click()
Dim Rst As DAO.Recordset
Dim dbs As Database

Dim stsql As String
Dim STWHERE As String
On Error GoTo List2_Click_Error

stsql = "SELECT tbldayemps.empno, tbldayemps.crewno, tbldayemps.logday "
stsql = stsql & "FROM tbldayemps WHERE ((tbldayemps.crewno)=0) "
stsql = stsql & " AND (( tbldayemps.logday )= #" &
[Forms]![DAILYLOG].[Form]![cboTdate] & "# )"
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(stsql)

STWHERE = "RST.FIELDS('empno') = " & Me![cboEmpno]
Rst.FindFirst STWHERE
' this statement should never be true - I have it in here for debugging
purposes
If Rst.NoMatch Then
MsgBox "no match"
Else
Me.Bookmark = Rst.Bookmark
End If
Me.crewno = Me.Parent!cboCrewid

Me.Dirty = False
Me.Requery
Me.cboEmpno.Requery


List2_Click_exit:
On Error GoTo 0
Exit Sub

List2_Click_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure
List2_Click of VBA Document Form_members subform"
Resume List2_Clic
 
G

George Nicholson

STWHERE = "RST.FIELDS('empno') =

RST.Fields() is causing the error.
rst.FindFirst expects a FieldName, nothing more.

If Empno is a numeric field:
STWHERE = "[empno] = " & Me![cboEmpno]
If a text field:
STWHERE = "[empno] = '" & Me![cboEmpno] & "'"

HTH,
 
M

Marshall Barton

SandyR said:
I am trying to find a record in a recordset where the value of the field
empno is equal to the value selected in an unbound combo box. I am getting
the error message: Error 3072 (unknow function name) on the findfirst line.
Here is my code:

Private Sub cboEmpno_Click()
Dim Rst As DAO.Recordset
Dim dbs As Database

Dim stsql As String
Dim STWHERE As String
On Error GoTo List2_Click_Error

stsql = "SELECT tbldayemps.empno, tbldayemps.crewno, tbldayemps.logday "
stsql = stsql & "FROM tbldayemps WHERE ((tbldayemps.crewno)=0) "
stsql = stsql & " AND (( tbldayemps.logday )= #" &
[Forms]![DAILYLOG].[Form]![cboTdate] & "# )"
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(stsql)

STWHERE = "RST.FIELDS('empno') = " & Me![cboEmpno]
Rst.FindFirst STWHERE
[snip]

The FindFirst needs to refer to the field in the table.

Assuming empno is a numeric type field in tbldayemps:

STWHERE = "empno = " & Me!cboEmpno
 
S

SandyR

Thank-you both for replying. I am having an awful time figuring out under
what circumstances you have to qualify the name of something, and when you
don't.

Marshall Barton said:
SandyR said:
I am trying to find a record in a recordset where the value of the field
empno is equal to the value selected in an unbound combo box. I am getting
the error message: Error 3072 (unknow function name) on the findfirst line.
Here is my code:

Private Sub cboEmpno_Click()
Dim Rst As DAO.Recordset
Dim dbs As Database

Dim stsql As String
Dim STWHERE As String
On Error GoTo List2_Click_Error

stsql = "SELECT tbldayemps.empno, tbldayemps.crewno, tbldayemps.logday "
stsql = stsql & "FROM tbldayemps WHERE ((tbldayemps.crewno)=0) "
stsql = stsql & " AND (( tbldayemps.logday )= #" &
[Forms]![DAILYLOG].[Form]![cboTdate] & "# )"
Set dbs = CurrentDb
Set Rst = dbs.OpenRecordset(stsql)

STWHERE = "RST.FIELDS('empno') = " & Me![cboEmpno]
Rst.FindFirst STWHERE
[snip]

The FindFirst needs to refer to the field in the table.

Assuming empno is a numeric type field in tbldayemps:

STWHERE = "empno = " & Me!cboEmpno
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top