i need to update new records to the database. i created a button and typed the below mentioned code
But an error saying,"Current recordset does not support updating. this may be a limitation of the provider, or of the selected loc type" appears.

Could you please help

Private Sub Command3_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM SEAT WHERE LOCID='" & Combo4.Text & "'", con, adOpenKeyset, adLockReadOnly
If rs.EOF Then
Else: rs.Update
End If
rs!locid = Combo4.Text
rs!floorid = Combo5.Text
rs!seatno = Combo6.Text
rs!seatid = Text7.Text
rs!seattype = Combo7.Text
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error GoTo Form_QueryUnload_Error

If Not con Is Nothing Then
If con.State Then con.Close
Set con = Nothing
End If

Exit Sub

MsgBox "An Error has occured in Procedure Form_QueryUnload." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description
Resume Form_QueryUnload_Done

End Sub

Private Sub Form_Load()

On Error GoTo Form_Load_Error

Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & App.Path & "\Office Utilization.mdb"
Call FillLocation
Call Filllocation2

Exit Sub

MsgBox "An Error has occured in Procedure Form_Load." & vbCrLf & vbCrLf & Err.Number & " : " & Err.Description
Resume Form_Load_Done

End Sub

Recommended Answers

All 5 Replies

open the recordset as adOpenDynamic, adLockOptimistic

adOpenKeyset, adLockReadOnly opens the recordset in readonly mode and prevents any changes to the data.

Mark the solved threads as solved if they are solved.

Shaik Akthar

when u want only to read the data use in readonly mode. if u want to manipulate the data then it should be opened in dynamic mode.

try to get some documentation on ADODB usage of Connection, Command, Recordset and Parameter

Shaik Akthar

will do. sorry i didn't know that we've tro mark it as solved

thanks a lot for the help


Another suggestion is when putting the controls on the form, change their name as per their functionality. like the command buttons can be named cmdSave, cmdCancel, cmdClose, cmdReport... and not Command1, Command2 ...
txtCode, txtName instead of Text1, Text2...
cmbFloor, cmbLocation instead of Combo1, Combo2...

It is not mandatory but only a convention which will help you to arrange the code more systematically.

Use sufficient Tab spacing when writing iteration/conditional statements i.e For...Next, While...Wend, If...Else...End If

Also make a habit of adding error handling code to every procedure to handle any errors that may rose during runtime.

U can follow like the one i have given in the sample project.

I am sorry if i have replied anything out of ur way.

Shaik Akthar

The reason for marking the threads as solved is that other users having a similar problem can search for the solution first in the solved threads instead of keeping on posting similar threads which are actually already solved.

It is not for just adding a Solved Thread to my Credit.

Shaik Akthar

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.