How to make recordsetclone data always accurate

[copleyuk]

I have a database with a number of users entering data into an SQL database from a form.

The form is set so that when entering data, if a reference number has already been used the form will display the matching information using recordsetclone.

code is as follows:

 Dim rs As DAO.Recordset
    Dim ref As String
    
    ref = Me.refrencenumber
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[refrencenumber] = '" & Me.refrencenumber & "'"

However the problem is that if the form is left open then the data in the clone does not seem to update itself.

So if more records are entered by others you cannot see this new info without closing the form and then reopening it.

Any ideas on why this is happening and how to fix it?

[Stewart Ross]

Hi. The form's recordsource will need to be requeried, as the recordset is only loaded once, at the time the form is opened. Requerying is straightforward to do:

Me.Requery
Set rs = Me.Recordset.Clone

This should make sure that the form is up to date before you use the recordset clone method to copy its current source data.

-Stewart

PS if you have any filtering applied to the form you may wish to use its RecordSetClone method, which will reflect the filtered recordset:

Me.Requery
Set rs = Me.RecordsetClone

[copleyuk]

I'm getting an error stating that:
The before update or validationrule property for this feild is preventing microsoft office access from saving the data in the field...

[Stewart Ross]

Ahh, so you have pending updates. Use the form's Dirty property to resolve this:

IF Me.Dirty Then Me.Dirty = False
Me.Requery 
Set rs = Me.RecordsetClone

The additional line tests the Dirty property and resets it to false, effectively saving the current record being edited.

By the way, if there is a validation error occurring as a result of bespoke code for a Before Update event you will need to resolve that before saving the record using Me.Dirty = False, as otherwise you will get into a position where you can't update the record because the Before Update event has not been completed.

-Stewart

[copleyuk]

Thanks Stewart, but I'm still getting the same error message.

There are no other events happening as before update and there is no validation rules set for this field.

Any further info would be great thanks.

[Stewart Ross]

Could you post the whole of the sub or function you are using? If there is no validation or update routine running it suggests that something is being changed by the routine itself, and it would help to see what code you are using.

You could also use trace statements such as

msgbox "Record edited = " & Me.Dirty

to give you an idea of what the status of the current record is at the time your routine is called.

-Stewart

[copleyuk]

Hi Stewart,

have tried that and get = True

The complete sub is

Private Sub RefrenceNumber_BeforeUpdate(cancel As Integer)
    On Error GoTo Err_RefrenceNumber_BeforeUpdate
    
    Dim rs As DAO.Recordset
    Dim ref As String
    ref = Me.refrencenumber
    
    
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[refrencenumber] = '" & Me.refrencenumber & "'"
    If rs.NoMatch And Me.DepartmentRegistrationNumber Like "*" Then Me.Undo
    If rs.NoMatch And Me.DepartmentRegistrationNumber Like "*" Then MsgBox "This action will result in a record being overwritten! A new request form is being opened to allow you to add the new record. Click ok to reset the form and continue."
    If rs.NoMatch And Me.DepartmentRegistrationNumber Like "*" Then DoCmd.GoToRecord , , acNewRec
    
    
  
    If rs.NoMatch = False Then
        Me.Undo
        Me.Bookmark = rs.Bookmark
        End If
    Set rs = Nothing
    
    
    
Exit_RefrenceNumber_BeforeUpdate:
Exit Sub

Err_RefrenceNumber_BeforeUpdate:
MsgBox Err.Description
Resume Exit_RefrenceNumber_BeforeUpdate
   

End Sub

Could any other events be causing the same issue other then just the before update?

[Stewart Ross]

Hi. Your code is running in the Before_Update event of your reference number control. This will itself cause a conflict, because (by definition) the control's update event has not been fired at this stage, and nor has the form's record update event.

I would move the sub to the AfterUpdate event of the same control, if this is the appropriate place to put it, as you will not be able to successfully requery the form or clear the Form Dirty property from the Before Update event of an individual control (because of the clash of events).

The form's dirty property being True when you echo it via the message box just confirms for me that the current record needs to be saved before the requery takes place. This cannot happen until the update event for the current control has completed, hence my suggestion to use its AfterUpdate event property and not its BeforeUpdate one.

-Stewart