SQL in VBA

[Rob M.]

Beneath you will find a piece of VBA-coding from an Access97 database, which
has always been working correctly. When I make a copy of this piece of coding
into a new database (Access97), the code doesn't work anymore (no records
found). Apparantly, MySet isn't recognized anymore ("MySet = nothing"). Can
anybody help me ?

Sub Convocatie()

On Error Resume Next
Dim I As Integer
Dim MySQL As String
Dim MyDB As Database
Dim MySet As Recordset

Set MyDB = DBEngine.Workspaces(0).Databases(0)

MySQL = "SELECT DISTINCT Soort FROM Dossiers WHERE Rnummer = '7654'"

Set MySet = MyDB.OpenRecordset(MySQL)

MySet.MoveLast
MySet.MoveFirst

I = MySet.RecordCount

MsgBox I

End Sub

 

[Douglas J. Steele]

There's nothing obviously wrong with that code.

Does it compile correctly, or are you having a problem there as well? You
might change the On Error Resume Next to actually trap any error that may be
occurring, to help figure this one out.

 

[Brendan Reynolds]

What's the data type of the field 'Rnummer'?