Retrieving data from database using VBA received Error # 424

[Guest]

Can someone help in retrieving data from the Access 2000 using VBA.

I have the following code where on OpenRecordset I receive:
Error # 424: Object required

Option Compare Database
Dim valYear As String
Dim valMonth As String
Dim sMonth As Double

Sub MonthYear()
On Error GoTo MonthYear_Err:

Dim db As Database
Dim rst As Recordset
Dim sql As String

sql = "Select tblMonth.fldPoints " _
& "From tblMonth Where tblMonth.fldMonth = '" & valMonth & "'"
MsgBox sql

Set db = CurrentDb()
Set rst = qry.OpenRecordset(sql, dbOpenSnapshot)

If Not rst.EOF Then
sMonth = rst!Month
End If

sMonth = rst!fldMonth

MonthYear_Exit:
Set db = Nothing
Set rst = Nothing
Exit Sub

MonthYear_Err:
MsgBox "Error # " & Err.Number & ": " & Err.Description
Resume MonthYear_Exit
End Sub

Thanks in advance,

 

[Guest]

This line is incorrect:
Set rst = qry.OpenRecordset(sql, dbOpenSnapshot)
Should be:
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

I also suspect this may be a problem. Not the one you are reporting, but
when you get it correct, this may be an issue:
sql = "Select tblMonth.fldPoints " _
& "From tblMonth Where tblMonth.fldMonth = '" & valMonth & "'"

The possible problem I see is that you are including valMonth in your sql.
I would suspect by the name it is a numeric value. and that tblMonth.fldMonth
is a numeric field. If this is the case, then you need to remove the single
quotes around valMonth. If fldMonth is a text value, then (assuming both
fldMonth and valMonth are formatted the same, it is okay.

 

[Graham R Seach]

Try this:
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Douglas J Steele]

In addition to the advice you got from Klatuu and Graham, you're trying to
refer to a field named Month in your recordset, yet the only field that
you're selecting in your SQL statement is fldPoints

FWIW, Month is not a good choice for a field name: it's a reserved word. If
you cannot change the name of the field, at least put square brackets around
it when you're referring to it:

sql = "Select tblMonth.fldPoints, [Month] " _
& "From tblMonth Where tblMonth.fldMonth = '" & valMonth & "'"

and

sMonth = rst![Month]

 

[George Nicholson]

1) Add "Option Explicit" to the top of your module
2) Add "Dim rst as DAO.Recordset" to your procedure
3) Make the changes suggested in other responses.

HTH,

 

[Van T. Dinh]

In addition, your partial code:

...
If Not rst.EOF Then
sMonth = rst!Month
End If

sMonth = rst!fldMonth
...

refers to 2 different Fields: [Month] and [fldMonth]. It looks like some
typing error here, too.

Logically, the construct is incorrect and will give you run-time error if
the Recordset is empty since the statement

sMonth = rst!fldMonth

will be executed regardless of whether the Recordset is empty or not!