Run time error 3421

[Phil Hood]

I have set up the following event procedure but keep
getting an error message as follows:

Run-time error 3421
Data type conversion error

I suspect it might be something to do with:

rstRecords!HighHeatNo

as it is shown as <Object variable or With block variable
not set>

Any help greatly appreciated (as always)


-------------------------------
Dim dbsDatabase As Database
Dim rstRecords As Recordset
Set dbsDatabase = CurrentDb

Set rstRecords = dbsDatabase.OpenRecordset("SELECT Max
(HeatNo)" & "As HighHeatNo FROM Heat", "WHERE (([Heat]!
[MatchID]= " & _
[Forms]![Heat]![MatchID] & ") AND ([Heat]![HeatID]= "
& [Forms]![Heat]![HeatID] & "));")

Me.HeatNo = rstRecords!HighHeatNo + 1
----------------------------------

 

[Douglas J. Steele]

I believe the problem is that your OpenRecordset statement is incorrect. The
Where clause isn't a separate parameter.

Set rstRecords = dbsDatabase.OpenRecordset( _
"SELECT Max (HeatNo)" & "As HighHeatNo " & _
"FROM Heat WHERE (([Heat]! [MatchID]= " & _
[Forms]![Heat]![MatchID] & ") AND " & _
"([Heat]![HeatID]= " & [Forms]![Heat]![HeatID] & "))")

The syntax for the OpenRecordset method is:

Set recordset = object.OpenRecordset (source, type, options, lockedits)

Where:
recordset is an object variable that represents the Recordset object you
want to open.
object is an object variable that represents an existing object from which
you want to create the new Recordset.
source is a String specifying the source of the records for the new
Recordset. The source can be a table name, a query name, or an SQL statement
that returns records. For table-type Recordset objects in Microsoft Jet
databases, the source can only be a table name.
type (optional) is a constant that indicates the type of Recordset to open
options (optional) is a combination of constants that specify
characteristics of the new Recordset
lockedits (optional) is a constant that determines the locking for the
Recordset

Check
http://msdn.microsoft.com/library/en-us/office97/html/output/F1/D2/S5A2D0.asp
for details.

Also, while it's not strictly necessary, you may find it safer to declare
the recordset object as

Dim rstRecords As DAO.Recordset

just to ensure you remember what type of recordset object you're using (the
ADO model also has a recordset object)