Change values on details of continuous form 1

[lorirobn]

Hi,

I have a subform that is a continuous form.
I have a command button set up to delete a detail record. When a record is deleted, I would like to bump down the value of a field on the records displayed after the deleted record.

Can I do this by accessing the details on the form in any way, and updating them? Or must I create an SQL processing loop to do so? I coded the SQL for this, in the subform's cmdDelete event, but am not quite sure how to do the recordset, how to execute the SQL (and can that initiate a loop?), how to reference the field I want to change, and set up a loop for my select statement. So I got to wondering whether I can reference the detail lines themselves.

If I am on the right track, the code I have generated so far is: (for simplicity, leaving out some of the dim statements).

   Dim db As DAO.Database
   Dim rst As DAO.Recordset
   Set db = CurrentDb   

   For X = MinSeqNum To MaxSeqNum
        
      strSQL = "SELECT tblRoomItems.SeqNo From tblRoomItems " _
       & "WHERE (((tblRoomItems.ItemID)= " & [Forms]![fsubRoomItemDetails].[ItemID] & ")" _
       & "AND ((tblRoomItems.SeqNo)= " & X _
       & "));"
 
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
   [purple] rst.SeqNo = X - 1  [/purple]
    rst.Update

    Next X

I get an error on the purple line, for rst.seqno, saying "Compile Error: method or data member not found".

Any suggestions on the best way to go about doing this?

Thanks,
Lori

 

[PHV]

Only syntax correction:
rst.Edit
rst!SeqNo = X - 1
rst.Update

Another way:
CurrentDB.Execute "UPDATE tblRoomItems SET SeqNo=SeqNo-1 WHERE SeqNo>=" & MinSeqNum & " AND ItemID=" & Me!ItemID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

I imagine you need a loop,
because you want to edit all records which came
after the deleted record. Not just one record?

Try the DAO.recordsetclone property.

set rst = Me.sfrmForm.Form.RecordsetClone
rst.MoveFirst
Do Until rst.EOF
x = x + 1
As PHV said, using rst.Edit before the modification,
rst!SeqNo = x
and rst.Update after.
rst.MoveNext
Loop

Careful, This will re-enumerate, all the records.

rst.Close: set rst = nothing

 

[lorirobn]

Thank you so much, I am going to try it out right now.

One question, though, as I'm not clear on the recordset portion.

If I use what you suggest, Zion7:
set rst = Me.sfrmForm.Form.RecordsetClone

will that give me ALL records, leaving me to sift through all until I get to the ones I want?

Can I use my strSQL to get the record set that I want (a subset of what's on the subform):
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

??

 

[lorirobn]

Addendum: got it working fine! I ended up using
set rst = currentdb.openrecordset(strSQL, dbOpenDynaset).

Not the first time either of you has helped me, so many thanks, once again, for helping me out of a tough spot...

For anyone who's interested, here's my complete code.

        Dim MinSeqNum
        MinSeqNum = DelSeqNum + 1
        
        Dim strSQL As String
        Dim rst As DAO.Recordset

        strSQL = "SELECT tblRoomItems.SeqNo From tblRoomItems " _
               & "WHERE ((tblRoomItems.RoomID = '" & RoomID & "')" _
              & " AND (tblRoomItems.ItemID = " & ItemID & ")" _
              & " AND (tblRoomItems.SeqNo >= " & MinSeqNum _
              & " AND tblRoomItems.SeqNo <= " & MaxSeqNum _
              & "));"
            
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
       
        rst.MoveFirst
        Do Until rst.EOF
           rst.Edit
           rst!SeqNo = rst!SeqNo - 1
           rst.Update
           rst.MoveNext
        Loop
        rst.Close: Set rst = Nothing

 

[PHV]

Why not simply this ?
CurrentDB.Execute "UPDATE tblRoomItems SET SeqNo=SeqNo-1" _
& " WHERE RoomID='" & RoomID & "' AND ItemID=" & ItemID _
& " AND SeqNo Between " & MinSeqNum & " And " & MaxSeqNum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zion7]

Actually lorirobn, I would agree with PHV.
I wasn't sure if you needed to increment the modified value,
for each record. I see now, you don't.
In lieu of this, ...too much overhead opening a recordset.

PHV's suggestion, is a better route.

good luck, either way!

 

[lorirobn]

Ok, I have modified the code to PHV's suggestion (which he suggested earlier but I did not know it was less overhead). Works great, and was sooo much easier to code, too! I have learned a lot today. Thanks to the two of you - you are practically doing my job for me. ;-)