Refresh Problem – after new ADO record sets written

[tchaplin]

I have an unbound form creating a new job this writes new data to four tables using ADO recordsets (sample below) one to many type relationships. After writing the data I close the form and refresh the main form with the new Job details – tabs with sub forms in (7 in total) and some sub forms within that.
To get the refresh to work I have to pause the code for two seconds (below) before that written records can be requeryed. Otherwise the database will not pick up the new records consistently. The database is front back end using ACCDB.
Write new records example

Call OpenDbConnection
Dim i As Integer
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
rst.Open "tblJob", dbcon, adOpenStatic, adLockOptimistic
With rst
.AddNew
If Left(Me!cboClientId.Column(1), 7) = "Default" Then
!ClientId = Nz(Me!txtClientId1)
Else
!ClientId = Nz(Me!cboClientId)
End If
!QuantitySurveyorId = Nz(Me!cboQuantitySurveyor)
!DescriptionOfWorks = Nz(Me!txtWorksDescription)
![JobStreetNo/Lot] = Nz(Me!txtStreetNoLot)
!JobStreet = Nz(Me!txtJobStreet)
!JobSuburb = Nz(Me!txtSuburb)
!JobCity = Nz(Me!txtCity)
!CSQNumber = Nz(Me!txtCSQ)
‘ You get the idea
.Update
intJobId = !JobId
txtJobId = !JobId
.Requery

End With
rst.Close
Set rst = Nothing


Refresh the new records written – necessary to pause the DB processing by 2 seconds.
Not an eloquent solution

dim strSql As String
strSql = "SELECT tblJob.JobID, tblJob.[JobStreetNo/Lot], tblJob.JobStreet, tblJob.JobSuburb, tblJob.JobCity, tblJob.ClientId, tblJob.JobNumber, tblJob.EstimateStatus, tblJob.EstimateAmount, tblJob.QuantitySurveyorId, tblJob.ResidentialWorks, tblClient.CompanyName, tblJob.CSQNumber, tblJob.Attachment, tblJob.DescriptionOfWorks, tblJob.Comments, tblJob.ReceivedDate, tblJob.ClientTemplate " _
& "FROM tblClient RIGHT JOIN tblJob ON tblClient.ClientId = tblJob.ClientId WHERE tblJob.JobID=" & intJobId

Forms!frmNavform.RecordSource = strSql
Forms!frmNavform.SetFocus
Forms!frmNavform.Form.Requery
'MsgBox "Job number : " & intJobId & " has been saved", vbInformation, "Record saved"
DoCmd.OpenForm "frmSaveJob", , , , , acDialog
DoCmd.Hourglass (True)
Sleep (2000)
DoCmd.Hourglass (False)
Forms!frmNavform.Form.Requery
DoCmd.Close acForm, "frmCreateJob"

Thanks for your time and consideration

 

[PeteJohnston]

I don't know if you've already solved this but I've done it before by creating a class module clsYourClass that raises an event

Public Event InvoiceChange(ByVal Invoice As String, Cancel As Boolean)

and declaring it as Public in the main form. Declare a class module in the unbound form

Public WithEvents cInvoice As clsInvoice

then in Form_load you set it to

Private Sub Form_Load()
   Set cInvoice = Form_MainMenu.cInvoice
End Sub

In the main form you define the event and call the refresh routine

Private Sub cInvoice_InvoiceChange(ByVal Invoice As String, Cancel As Boolean)
    cbfRequery
End Sub

Your module should have a property that raises the event

Public Property Let Changed(ByVal vNewValue As Boolean)
    RaiseEvent InvoiceChange(m_Invoice, bCancel)
End If
End Property

which you set cInvoice.Changed = True immediately after you have done the updates. It also means that you can have several open forms that rely on the data being updated that can all be listening for the event and can all refresh at the same time.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)