Using Loops While Refreshing Subforms

[dannymack]

I ran into an issue a few years back that I found a quick, but ugly fix for. Now that fix is causing problems and I want to see if there is a cleaner way to do it.

Problem:
I have two subforms. 1 is a form that shows "Available Staff" based on certain criteria (Date, Time of Day). The 2nd form shows which staff members have already been "Assigned". When a staff member is assigned they are removed from the "Available Staff" form and their assignment appears in the "Assigned" form. The problem I am having is with the subforms registering the change. I know that the change is happening based upon the tables, but there seems to be a disconnect with the subforms. The only way I have gotten the subforms to register the change is by requerying within a loop until the record count changes. It's not clean, can take forever and has recently started to crash the database. Is there something that I am missing here?

Private Sub ctlAssign_Click()
Dim RecCount As Integer
TempVars.Add ("DateVar"), Me.Text43.Value
Me.RecordsetClone.MoveLast
RecCount = Me.RecordsetClone.RecordCount
AssignStaff Me.StaffID.Value, TempVars.Item("CenterVar")
Do Until Me.RecordsetClone.RecordCount = RecCount - 1
    Forms!f_Navigation!f_FieldStaff.Requery
Loop
Forms!f_Navigation!f_CenterFieldStaff.Requery
Forms!f_Navigation!f_Center.Requery
Forms!f_Navigation!f_Center.Form.cmbLead.Requery
Me.cmdFilter.SetFocus
End Sub

Public Function AssignStaff(strID As String, CenID As String)
 
    Dim strSQL As String, MyDB As New ADODB.Connection, rstProjTable As 
    
    rstProjTable.Open "select * from T_SRM_Schedule", MyDB, adOpenKeyset, adLockPessimistic
    
    rstProjTable.AddNew
    rstProjTable("StaffID").Value = strID
    rstProjTable("SchoolID").Value = CenID
    rstProjTable("AssignedDate").Value = TempVars.Item("DateVar")
    rstProjTable("AMPM").Value = TempVars.Item("TimeVar")
    rstProjTable("AddedBy").Value = TempVars.Item("SRMVar")
    rstProjTable("AddedDate").Value = Now

    rstProjTable.Update
    rstProjTable.Close
    DoEvents
    
    Set rstProjTable = Nothing
    
    MyDB.Close
    Set MyDB = Nothing

End Function

 

[llkhoutx]

You go to a lot of trouble to attempt to move move staff from one table to another, which could be done with simple SQL statement executions and a requery of each subform, e.g.

4 lines of code would to it in DAO or ADO:
Update record in Assigned table
Delete/Modify/Tag record in Available table
Requery Available subform
Requery Assigned subform

2 tables containing essentially the same data is poor design in my opinion. Another way would be to have on 1 table with a junction table indicating the Assigned particulars related to some particular event.

 

[pbaldy]

A couple of thoughts. First, opening the recordset on the entire table can really slow things down if the table gets large. Since you're only opening to append, you can do:

rstProjTable.Open "select * from T_SRM_Schedule WHERE 1=0", MyDB, adOpenKeyset, adLockPessimistic

Which won't return any records but will allow the append.

To your question, I've heard of something called a "lazy write" related to ADO recordsets that concerned data not updating immediately. It's a tickle of a memory so don't quote me on it. I don't normally use ADO and I just do my append/update and then requery, so you might try switching to a DAO recordset or executing SQL. Simplest is probably the former, since most of the code wouldn't change.

 

[Galaxiom]

Is there a good reason to use an ADO Recordset for the job? With DAO you could open the recordset with dbAppendOnly.

If there is a reason for using an ADO recordset then rather than the WHERE 1=0 you could use the ADO equivalent of dbAppendOnly before opening it.

rstProjectTable.Properties("Append-Only Rowset") = True

However since you are just adding one record, executing an ADO Command would probably make more sense.

I've heard of something called a "lazy write" related to ADO recordsets that concerned data not updating immediately.

You would be referring to the BatchUpdate. Use the adLockBatchOptimisitic Option parameter when opening the recordset. Changes to the recordset are held locally until the UpdateBatch Method is called.

 

[llkhoutx]

I use DAO recordsets exclusively with Access.