AfterUpdate Yes/No MsgBox with Loop

[steve21nj]

Hello,

I am working with an AfterUpdate check box.

What am I am attempting to do is just before the highlighted red text is ask the user, do you wish to make the changes. I have a main form that feeds into a subform. The upper part of the subform is a continuous form while the form footer is where I am looping the record.

If the user selects yes on the message box, proceed with the highlighted red text below and loop that subform record, without asking again for the record. (Example, if the subform had three records, it would change all three after selecting yes rather than asking each individually).

If no, do nothing.

I was using the blue text below but it kept highlighting rst.edit and wouldn't loop.

Private Sub Ctl216_AfterUpdate()
Dim dbs As Database, rst As DAO.Recordset
Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset("SELECT * FROM tblPropertyDetails WHERE DetentionID=" & Key, dbOpenDynaset)
  If Not rst.EOF Then
 
    If Me![216] = -1 Then
    Me![216_Number] = Forms![216]![216_Number]
    Me![Left] = Forms![216]![Transfer to]
    Me![Date Out] = date
    Me![Time Out] = time()
      Do
        If IsNull(rst![BagNum]) Then
          Exit Do
        Else
 
            [COLOR=red]rst.Edit[/COLOR]
[COLOR=red]           rst![PropertyStatus] = 3[/COLOR]
[COLOR=red]           rst![DateOut] = date[/COLOR]
[COLOR=red]           rst.Update[/COLOR]
                        
            End If
        rst.MoveNext
      Loop Until rst.EOF
 
    Else
 
    If Me![216] = 0 Then
    Me![216_Number] = ""
    Me![Left] = ""
    Me![Date Out] = ""
    Me![Time Out] = ""
      Do
        If IsNull(rst![BagNum]) Then
          Exit Do
        Else
          rst.Edit
          rst![PropertyStatus] = 2
          rst![DateOut] = Null
 
          rst.Update
        End If
        rst.MoveNext
      Loop Until rst.EOF
      End If
    End If
    Me.Refresh
    End If
 
End Sub

[COLOR=blue]LResponse = MsgBox("Do you wish to 216 property?", vbYesNo, "Property Information")[/COLOR]
[COLOR=blue]If LResponse = vbYes Then[/COLOR]
[COLOR=blue]  {...red text statements from above...}[/COLOR]
[COLOR=blue]Else[/COLOR]
[COLOR=blue]   {...do nothing...}[/COLOR]

 

[gemma-the-husky]

deleted

not correct advice for an unbound form

 

[steve21nj]

thank you for the attempt

 

[gemma-the-husky]

ok

instead of doing it with a recordset, just try a query

the easiest way is to design a stored update query, that selects records with retentionid equal to the value on your form, together with any other fields you need to check.

then just

docmd.openquery "Myupdatequery" (which will give you warnings)
or
currentdb.exectute "MyUpdatequery", which won't give you warnings

(it's a bit more subtle than that, but that will get you started)

everything else is just doing the same thing, in code.

test it as a select query, then as a real query. use a copy of your real data until you get the hang of it

 

[steve21nj]

Got this to work, hopefully i'll be error free for the rest of the day!

  [COLOR=darkred]Else[/COLOR]
[COLOR=darkred]           LResponse = MsgBox("Continue?", vbYesNo, "Property Information")[/COLOR]
[COLOR=darkred]           If LResponse = vbYes Then[/COLOR]
[COLOR=darkred]           rst.Edit[/COLOR]
[COLOR=darkred]           rst![PropertyStatus] = 3[/COLOR]
[COLOR=darkred]           rst![DateOut] = date[/COLOR]
[COLOR=darkred]           rst.Update[/COLOR]
[COLOR=darkred]             Else[/COLOR]
[COLOR=darkred]               Exit Do[/COLOR]
[COLOR=darkred]               End If[/COLOR]
[COLOR=darkred]           End If[/COLOR]
[COLOR=darkred]       rst.MoveNext[/COLOR]
[COLOR=darkred]     Loop Until rst.EOF[/COLOR]

 

[steve21nj]

With this database, it is shared on the network. The calculation when running the looop is pretty slow, maybe up to 5-10 seconds per click. Is there a faster way?

 

[gemma-the-husky]

as I say - use stored queries. 5 seconds seems ridiculous though, unless your table is very large, and detentionID is not indexed.

it also looks like the me.refresh is inside one of the loops. i would leave that till after the loops finish. that might be the issue.