CODEWITHSUNDEEP
vbams-access
Cher
Cher

Reputation: 2937

How to use OpenRecordSet?

I'm trying to use OpenRecordSet.

The parameter is the query:

Select * FROM Batiments WHERE Type = "2";

which works perfectly if I write it as a query.

However with OpenRecordSet I get this error:

Object variable or With block variable not set

I don't understand what is wrong and from all tutorial of similar case I see, my syntax should be correct.

Here is the full code:

temp = "Select * FROM Batiments WHERE Type = " & Chr(34) & txtNouveauBatiment.Value & Chr(34) & ";"
        rstBatiment.OpenRecordset tmp

        If (rstBatiment.EOF) Then
            Set dbs = CurrentDb
            Set rstBatiment = dbs.OpenRecordset("Batiments")

            rstBatiment.AddNew
            rstBatiment!Type = txtNouveauBatiment.Value
            rstBatiment.Update

            Refresh
        End If

Upvotes: 1

Views: 517

Answers (1)

Erik A
Erik A

Reputation: 32642

The .OpenRecordset method on the recordset object is not intended to open a new recordset, but rather to open a filtered recordset (using the .Filter property) based on an open recordset.

If you want to open a new recordset, you need to use CurrentDb.OpenRecordset:

temp = "Select * FROM Batiments WHERE [Type] = " & Chr(34) & txtNouveauBatiment.Value & Chr(34) & ";"
Set dbs = CurrentDb
Set rstBatiment = dbs.OpenRecordset(temp)

If (rstBatiment.EOF) Then
    Set rstBatiment = dbs.OpenRecordset("Batiments")
    rstBatiment.AddNew
    rstBatiment!Type = txtNouveauBatiment.Value
    rstBatiment.Update
    Refresh
End If

Note that I don't really get the Refresh line. If you're refreshing a form, it's usual to specify you're executing a method on the current form object, e.g. Me.Refresh.

Also note that Type is one of the reserved words in Access SQL, thus needs to be bracketed

Upvotes: 1

Related Questions