Retrieving data from database using VBA received Error # 424

  • Thread starter Thread starter Guest
  • Start date Start date
G

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,
 
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.
 
Try this:
Set rst = db.OpenRecordset(sql, dbOpenSnapshot)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
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]
 
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,
 
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!
 
Back
Top