Update records error

[smh]

In my project, when I press the update inquiry button, it is updating the current record, and also updating the first record in the database. Please help!

Private Sub cmdUpdate_Click()

Dim temp As String

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=H:\Issues_files\issues.mdb;"


rst.Open "Select * from tblLog", cnn, adOpenKeyset, adLockOptimistic


rst.Update

rst!ReportedBy = txtReportedBy
rst!EnteredBy = txtEnteredBy
rst!Category = cboCategory.Text
rst!Issue = txtIssue
rst!datereported = txtDateReported
rst!DateOccured = txtDateOccured
rst!TimeOccured = txtTimeOccured
rst!Priority = cboPriority.Text
rst!DownTime = txtDownTime.Text
rst!Location = txtLocation.Text
rst!CubeNumber = txtCube
rst!Tech = cboTechAssigned.Text
rst!Status = cboStatus.Text
rst!DateClosed = txtDateClosed
rst!ClosedBy = cboClosedBy.Text
rst!DownTimeHours = txtDownTimeHours
rst!Solution = txtSolution


rst.Update

temp = MsgBox("Record successfully updated.", vbOKOnly, "Update Record")

rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

End Sub

[Tadej]

After you open a recordset the first (if any)
record becomes current. So you should move to the
record you want to update before doing rst.Update.

You can also filter out the desirable record in your
SQL statement's WHERE clause.

rst.Open "Select * from tblLog WHERE something"

Hope this helps.

Tadej

[smh]

I have a button to navigate through the records, so I am not on the first record anymore.

Private Sub cmdNext_Click()

Dim temp As String


With datRecords.Recordset
.MoveNext
If .EOF Then
.MoveLast
temp = MsgBox("This is the last record.", vbOKOnly, "Last Record")
End If
End With

End Sub

Also, I do not know what field in what record they may update, so I don't see how I can use an SQL statement. They may adjust one of 20 fields, or 20 of 20 fields. Any more suggestions?

[Tadej]

How about:

Private Sub cmdUpdate_Click() 
   With datRecords.Recordset
   .Edit
   .Fields("ReportedBy") = txtReportedBy 
   .Fields("...")
   .Fields("...")
        .
        .
        .
   .Update
   End With
end sub

[smh]

Thanks, that example code worked. I wonder what the big difference was?

[Tadej]

Well, the difference is that in your example
you opened an entire new recordset with all records.
When you did that, as I said, the first record became
current. And so you edited the first one.

In my example you don't open the recordset again,
because datRecords.Recordset is alredy open.
And the current record is the one that the user
sees and edits.