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
rs.AddNew
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
rs.Update
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

Form_QueryUnload_Done:
Exit Sub

Form_QueryUnload_Error:
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

Form_Load_Done:
Exit Sub

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

End Sub

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.


Regards
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

Regards
Shaik Akthar

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

thanks a lot for the help

regards
ruchini

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.


Regards
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.

Regards
Shaik Akthar

This question has already been answered. Start a new discussion instead.