dear readers. plz help me out from the following problem in coding

i have made a form with images when i click on that image then data behind the image comes out on the other form if data is in single row then it can show but if more than one record then it goes using DataGrid. where i then select the required record. but i m facing problem in the following coding while going on the form in the load event. right after 2nd attempt on any image having more than one records. then i get an error

Operation is not allowed when the object is open

here is coding.

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Public VarPlot As String
Public VarSVY As String

Private Sub Form_Load()
With db 'database connectivity with access

.Provider = "microsoft.jet.oledb.4.0" (The yellow mark comes over here
' .CursorLocation = adUseClient
         .Open App.Path & "\cantonment.mdb"
    End With

db.CursorLocation = adUseClient
If db.State = 0 Then
db.Open
End If

Set rs = db.Execute("select PROP_NO, MANAGED_BY,Class, LandLord,
HOLDER_OF_OCCUPANCY_RIGHTS, PLOT_NO from CANTT where SVY_NO='" & VarSVY & "' and Plot_No='" & VarPlot & "'")
Set DataGrid1.DataSource = rs
DataGrid1.Columns(0).Width = 0
End Sub

plz help me and thnx in advance

It's because your creating a new database object each time you load the form. The database is opened when you click the first image, when you try to click another image it tries to open the database again but it's already open.

Also, you're opening the database in your with statement, you don't need to check the state of the database outside and open if it's closed, since it's already been opened in your with statement. If you remove that if statement and instead add an if statement before your with block to check if it's open, and close it if it is, then it works.

because db is declared at module level, it is not closed/destroyed when the form unload. you can follow DanRoches suggestion, but that will leave the db open even when the form is unloaded. To avoid this, you can explicitely close the db in the Form Unload event

That is true. I would check the db state in unload event of form and close it if it's opened. Nice catch

been there, done that :)