Whole Form Populate at Once

[aw23]

I have a large form with several subforms. When a user moves to a record (based on a selection from a compbobox)lots of code is run and all the forms are populated. It runs very quickly but the data is populated from one form to the next very quickly. Is there anyway to load all the data at once? So that all the data can be set but not visible and then at once it'll all populate?

Thanks

 

[VBslammer]

You can turn off screen repainting - but you have to make sure it gets turned back on or Access will appear to have frozen. Once it's turned off, the form won't appear to change until you turn it back on. I've had good results with this in most situations, but there are some events/dialogs that will still force a screen repaint.

Sub LongProcess()
  Screen.MousePointer = 11
  DoCmd.Echo False

  [green]'lengthy data load process...[/green]

  DoCmd.Echo True
  Screen.MousePointer = 0
End Sub

If you add error handling, you can ensure that repainting is turned back on (provided you don't stop the code using the debugger):

Sub LongProcess()
On Error GoTo ErrHandler

  Screen.MousePointer = 11
  DoCmd.Echo False

  [green]'lengthy data load process...[/green]

  Exit Sub

ErrHandler:
  DoCmd.Echo True
  Screen.MousePointer = 0
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[aw23]

Thanks but that didn't work. The mouse turned to an hourglass but the form continued to load while the hourglass was there before turning it back on.

 

[VBslammer]

If your process takes a long time you could create separate recordset objects for each subform and then attach them after the processing is finished.

I would think that it would be easier to re-work the form so less data is presented at any given time. When I build SQL Server projects I load almost every form with no data in it at all (unbound), then let the user select a precise amount of data to work with, limiting returns to 100 records or less using [tt]"SELECT TOP 100 ..."[/tt] Web apps are especially sensitive to the loading of unnecessarily large resultsets, and I always incorporate recordset paging so the user can load a page at a time instead of 60,000 records at once.

Anyway, to create the illusion that all subforms load at once, try creating their recordsets behind the scenes. If you have 3 subforms, you'd have to create 1 recordset for each and possibly another one for the main form's recordsource (unless the combobox data is all they see of the master record).
Once they select a value in the combo, create the 3 recordsets for the subforms, then after they're all populated, assign them using [tt]"Set"[/tt]

Private Sub Form_Load()
  ClearSubforms
End Sub

[green]' to clear the subforms of records, set their recordsource
' properties to a query that is guaranteed to return no results.
' they can still be linked to the main form using Master Link / Child Link 
' properties, but won't contain any data.[/green]
Private Sub ClearSubforms()
  [green]' use empty recordsets to clear the data[/green]
  Me![sbfrm1].Form.RecordSource = "SELECT * FROM Contacts WHERE CustomerID Is Null"
  Me![sbfrm2].Form.RecordSource = "SELECT * FROM Orders WHERE OrderID Is Null"
  Me![sbfrm3].Form.RecordSource = "SELECT * FROM Payments WHERE PaymentID Is Null"
End Sub

[green]' when the user changes the combo value, create recordsets
' for each subform, then set them after processing[/green]
Private Sub cboChoose_AfterUpdate()
  Dim lngID As Long
  Dim rst1 As DAO.Recordset
  Dim rst2 As DAO.Recordset
  Set rst3 As DAO.Recordset
  Dim db As DAO.Database

  Screen.MousePointer = 11
  SysCmd acSysCmdSetStatus, "Loading data, please wait..."
  
  lngID = Me!cboChoose.Value
  Set db = CurrentDb()

  [green]'load recordsets and perform lengthy calculations, etc.[/green]

  Set rst1 = db.OpenRecordset("SELECT * FROM Contacts WHERE CompanyID = " & lngID)
  
  Set rst2 = db.OpenRecordset("SELECT * FROM Orders WHERE CompanyID = " & lngID & " AND OrderDate > #" & Format(DateAdd("m", -1, Date), "mm-dd-yyyy") & "#")

  Set rst3 = db.OpenRecordset("SELECT * FROM Payments WHERE CompanyID = " & lngID & " AND PaymentDate > #" & Format(DateAdd("m", -1, Date), "mm-dd-yyyy") & "#")

  With rst1
    While Not .EOF
      [green]'do stuff...[/green]
      .MoveNext
    Wend
    .MoveFirst
  End With

  With rst2
    While Not .EOF
      [green]'do stuff...[/green]
      .MoveNext
    Wend
    .MoveFirst
  End With

  With rst3
    While Not .EOF
      [green]'do stuff...[/green]
      .MoveNext
    Wend
    .MoveFirst
  End With

  [green]' processing done, now assign the preloaded 
  ' recordsets to each subform using Set statement[/green]
  Set Me![sbfrm1].Form.Recordset = rst1
  Set Me![sbfrm2].Form.Recordset = rst2
  Set Me![sbfrm3].Form.Recordset = rst3

  Screen.MousePointer = 0
  SysCmd acSysCmdClearStatus
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob