Object variable or With block variable not set 2

[ckeener]

This Code is giving me an error message (Error #91 - Object variable or With block variable not set). I can't find where I have not defined a variable. Can anyone else see offhand what is causing my problem? All the fields that I draw information from are not null but I don't know what is causing my problem.


Dim rst As DAO.Recordset
Dim db as DAO.Database
Dim StCode As String
Dim Medium As String
Dim AlbumNum As Long
Dim Sig As String
Medium = Me.cboMedium
StCode = Me.cboType & " "
Select Case Medium
Case Is = "Cassette"
Medium = "C"
Case Is = "Compact Disc"
Medium = "D"
Case Is = "Album"
Medium = "A"
End Select
AlbumNum = 0
strSQL = "SELECT * FROM tblArtists WHERE ArtistRef = " & Me.cboArtist.Column(0)
Set rst = db.OpenRecordset(strSQL) ' - (Error #91 - Object variable or With block variable not set)
Sig = rst!Signature
StCode = Me.cboType & " " & Sig & "." & AlbumNum & Medium
strSQL = "SELECT * FROM tblAlbums WHERE NewStationCode = '" & StCode & "'"
Set rst = db.OpenRecordset(strSQL)
Do While rst.RecordCount > 0
AlbumNum = AlbumNum + 1
StCode = Me.cboType & " " & Sig & "." & AlbumNum & Medium
strSQL = "SELECT * FROM tblAlbums WHERE NewStationCode = '" & StCode & "'"
Set rst = db.OpenRecordset(strSQL)
Loop
Me.txtCodeNew = UCase(StCode)

Thanks, Caleb

 

[LarryDeLaruelle]

Caleb:

The only problem I see is that you are missing the closing semicolon at the end of your sql strings.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[jbehrne]

Try this:

strSQL = "SELECT * FROM tblArtists WHERE tblArtists.ArtistRef = '" & Me.cboArtist.Column(0) & "'"

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[ckeener]

Thanks Larry and jbehrne,

I tried both of your suggestions but to no avail. It is still giving me the same error. Thanks for your quick response though, any other suggestions???

Caleb

 

[CajunCenturion]

You are not opening db. The db object needs reference a database, and that is the reference that is not being set. You must set it before you can open a Recordset against it.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[jbehrne]

OK,

Try running the code again, when the error pops up go to the code (by hitting edit or debug) and post line of code causing the error. I'll be able to help you out further.

If at first you don't succeed, call in an airstrike. - Murphy's Laws of Combat Operations

 

[ckeener]

Thanks CajunCenturion,

That should have been obvious to me. Thanks for pointing it out. Thanks also to all those who posted as well. Have a great day.

Caleb