Acc2000: Recordset not updating

[noodnutt]

G'day all,

I originally did this DB in Acc97, and the following code works fine, I then
converted it to Acc2000.

Now all it does is updates the first recordID (no. 1) in the table instead
of the recordID (eg 212) that is visible when I execute the code.

Private Sub Titlecbo_AfterUpdate()
Dim dbMyDB As Database, rsMyRS As Recordset, intresponse As Integer
Set dbMyDB = CurrentDb
Set rsMyRS = dbMyDB.OpenRecordset("tblDVDList", dbOpenDynaset)**
rsMyRS.Edit
rsMyRS("Location") = 1
intresponse = MsgBox("You are about to loan this DVD Title out! - Are
You Sure???", vbYesNo)
If intresponse = vbYes Then
rsMyRS.Update
Me.Titlecbo.Requery
Else
rsMyRS.CancelUpdate
rsMyRS.Close
dbMyDB.Close
End If
End Sub

** I even changed this section to the following, but it didn't work either:

Set rsMyRS = dbMyDB.OpenRecordset("tblDVDList", "DVDTitleID =
Me.DVDTitleID")

Any & all suggestions greatfully recieved.

TIA

Mark.

 

[RoyVidar]

G'day all,

I originally did this DB in Acc97, and the following code works fine, I then
converted it to Acc2000.

Now all it does is updates the first recordID (no. 1) in the table instead
of the recordID (eg 212) that is visible when I execute the code.

Private Sub Titlecbo_AfterUpdate()
Dim dbMyDB As Database, rsMyRS As Recordset, intresponse As Integer
Set dbMyDB = CurrentDb
Set rsMyRS = dbMyDB.OpenRecordset("tblDVDList", dbOpenDynaset)**
rsMyRS.Edit
rsMyRS("Location") = 1
intresponse = MsgBox("You are about to loan this DVD Title out! - Are
You Sure???", vbYesNo)
If intresponse = vbYes Then
rsMyRS.Update
Me.Titlecbo.Requery
Else
rsMyRS.CancelUpdate
rsMyRS.Close
dbMyDB.Close
End If
End Sub

** I even changed this section to the following, but it didn't work either:

Set rsMyRS = dbMyDB.OpenRecordset("tblDVDList", "DVDTitleID =
Me.DVDTitleID")

Any & all suggestions greatfully recieved.

TIA

Mark.

Hi!

You are simply opening the table, without specifying any criterion, or
sort order, which will give you "the first record", according to the
sort order of the table (based on primary key or something else).

To edit title you're after, try using a where clause

dim strSql as string
strSql = "select * from tblDVDList where DVDTitleID=" & Me!DVDTitleID
Set rsMyRS = dbMyDB.OpenRecordset(strSql)

assuming numeric, else
....where DVDTitleID='" & Me!DVDTitleID & "'"