OpenRecordset not recognising .FindFirst

[David_Williams_PG ()]

I am trying to copy some values from the table Product_data_new using a combo
box [Code as below] to pick out the ID value from the table and then the code
should pick out the other values from the same line and copy these into a
form - but the code is not recognising the .FindFirst .NoMatch statements
within my " With Rst clause ".

Am using Access 2002 with references :-

Visual Basic for Applications
Microsoft Access 10.0 Object Libary
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Libary
Visual Basic for ApplicationsExtensibility 5.3


Any help much appreciated,
dw

----------------------------------------------------------------
Option Compare Database
Option Explicit
-------------------------------------------------------------------
Public Function getfieldname(commentnum As Double)

Dim db As Database
Dim rst As Recordset
Dim commstr As String
Dim strCriteria As String

' Define search criteria.
strCriteria = "[ID] = " & commentnum
Set db = CurrentDb()
Set rst = db.OpenRecordset("Product_data_new", dbOpenDynaset)

With rst
If .RecordCount > 0 Then
.FindFirst strCriteria
If .NoMatch Then

getfieldname = "not found"
Else

getfieldname = ![MAT_CODE]
Me![MAT_CODE] = ![MAT_CODE]
Me![EAN] = ![EAN]
Me![PACK_LEV] = ![PACK_LEV]
Me![SHORT_DESC] = ![SHORT_DESC]
Me![BUS_CAT] = ![BUS_CAT]
End If
End If
.Close
End With
End Functio

 

[Douglas J. Steele]

Recordset is an object in both the ADO (Microsoft ActiveX Data Objects 2.1
Library) and DAO (Microsoft DAO 3.6 Object Libary) object models. However,
only the DAO recordset supports the FindFirst method.

Because you have references to both libraries, you must disambiguate your
declarations to ensure that you get the DAO object:

Dim rst As DAO.Recordset

(if you didn't want to rely on Access picking the first reference it found
with the object to be sure you were getting an ADO recordset, you'd use Dim
rst As ADODB.Recordset)

While not strictly necessary (since the Database object doesn't exist in any
of the other libraries you're referencing), you might also use Dim db As
DAO.Database

If you're not using ADO in your application, you might want to uncheck the
reference to it.

 

[David_Williams_PG ()]

Excellent - thank you very much

Recordset is an object in both the ADO (Microsoft ActiveX Data Objects 2.1
Library) and DAO (Microsoft DAO 3.6 Object Libary) object models. However,
only the DAO recordset supports the FindFirst method.

Because you have references to both libraries, you must disambiguate your
declarations to ensure that you get the DAO object:

Dim rst As DAO.Recordset

(if you didn't want to rely on Access picking the first reference it found
with the object to be sure you were getting an ADO recordset, you'd use Dim
rst As ADODB.Recordset)

While not strictly necessary (since the Database object doesn't exist in any
of the other libraries you're referencing), you might also use Dim db As
DAO.Database

If you're not using ADO in your application, you might want to uncheck the
reference to it.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am trying to copy some values from the table Product_data_new using a
combo
box [Code as below] to pick out the ID value from the table and then the
code
should pick out the other values from the same line and copy these into a
form - but the code is not recognising the .FindFirst .NoMatch statements
within my " With Rst clause ".

Am using Access 2002 with references :-

Visual Basic for Applications
Microsoft Access 10.0 Object Libary
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library
Microsoft DAO 3.6 Object Libary
Visual Basic for ApplicationsExtensibility 5.3


Any help much appreciated,
dw

----------------------------------------------------------------
Option Compare Database
Option Explicit
-------------------------------------------------------------------
Public Function getfieldname(commentnum As Double)

Dim db As Database
Dim rst As Recordset
Dim commstr As String
Dim strCriteria As String

' Define search criteria.
strCriteria = "[ID] = " & commentnum
Set db = CurrentDb()
Set rst = db.OpenRecordset("Product_data_new", dbOpenDynaset)

With rst
If .RecordCount > 0 Then
.FindFirst strCriteria
If .NoMatch Then

getfieldname = "not found"
Else

getfieldname = ![MAT_CODE]
Me![MAT_CODE] = ![MAT_CODE]
Me![EAN] = ![EAN]
Me![PACK_LEV] = ![PACK_LEV]
Me![SHORT_DESC] = ![SHORT_DESC]
Me![BUS_CAT] = ![BUS_CAT]
End If
End If
.Close
End With
End Function
---------------------------------------------------------------------------------------------
'Combo box finds ID
Private Sub EAN6_Click()
getfieldname ([ID])
End Sub