'Requery', but only by re-loading a form ?

[liamfitz]

I have a 'Me.Requery' in an After_Update event proc. for sub form. The reason being, as the field concerned with the 'After_Update' event, is written to my table, I use VBA to add another field to the same record based on this value. However, it does not 'refresh' and show both fields' new values, until I close, and re-load the main form. I thought requery took the SQL string used ( recordsource ), and emptied - then requeried the table(s), thus showing the new data ? ( I suspect it's about timing and order of events - any help much appreciated ) Thanks.

 

[vbaInet]

What are you requerying, the main form or the subform?

 

[liamfitz]

Just the subform, ( but it wouldn't hurt if I had to, requery both, as the data is/should be, related ).

 

[vbaInet]

Ok. Do you have the Requery code in the main form or the subform?

 

[vbaInet]

elomelo10: Please don't post your questions on someone else's thread. Create your own thread and post your question there.

 

[liamfitz]

Thanks vbaInet - the code is in my subform. here it is ...

Private Sub Referral_Date_AfterUpdate()
Dim refid As String
Dim dt As Date
dt = Me.Referral_Date
Me.Parent!txtForename.SetFocus
refid = Left(Me.Parent!txtForename.Text, 1)
Me.Parent!txtSurname.SetFocus
refid = refid & Left(Me.Parent!txtSurname.Text, 1)
refid = refid & Format(dt, "ddmmyy")
Me.Parent!txtClient_ID.SetFocus
cl = Val(Me.Parent!txtClient_ID.Text)
With Me.Recordset
    .Edit
    !Referral_ID = refid
    !Client_ID = cl
    .Update
End With
refSQL = ""
cl = pcl 'Recordset.Fields("Client_ID")
sid = Forms!frmLog_In.pSid
refSQL = "SELECT tblReferrals.Referral_ID, tblReferrals.Referral_Date, tblReferrals.Assignment_Date, tblReferrals.Assessment_Date, tblReferrals.Status, tblReferrals.Staff_ID From tblReferrals WHERE (((tblReferrals.Client_ID)=" & cl & "));"
Me.RecordSource = refSQL
Me.Parent.Requery
Me.Requery
End Sub

The code in question, is after where I set the string value of the variable 'refSQL' i.e. refSQL = "SELECT tblReferrals.' etc. This would be EXACTLY the same as the existing recordsource ( before After_Update ), but when Me.Requeried, showing a value in the Referral_ID field, for the currently being updated record.

 

[vbaInet]

Amended code:

Private Sub Referral_Date_AfterUpdate()
    Dim refid As String
    Dim dt As Date
    
    dt = Me.Referral_Date
    
    With Me.Recordset
        .Edit
        !Referral_ID = Left(!txtForename.Value, 1) & Left(!txtSurname.Value, 1) & Format(dt, "ddmmyy")
        !Client_ID = Val(!txtClient_ID.Value)
        .Update
    End With
    
    cl = pcl 'Recordset.Fields("Client_ID")
    sid = Forms!frmLog_In.pSid
    
    refSQL = "SELECT Referral_ID, " & _
                    "Referral_Date, " & _
                    "Assignment_Date, " & _
                    "Assessment_Date, " & _
                    "Status, " & _
                    "Staff_ID " & _
             "FROM tblReferrals " & _
             "WHERE (((Client_ID)=" & cl & "));"
    
    With Me
        .RecordSource = refSQL
        .Requery
        .Parent.Requery
    End With
End Sub

What is pcl? It's not assigned any value within your code block.

 

[liamfitz]

It's a Public variable used for a field 'Client_ID' accross the project - but as I noticed when sending you, it's a 'vestige' of some experimenting I was doing with the code, and unused here. I'll clear it out when I tidy up the code - post 'functioning properly' ! Many thanks to you.

 

[vbaInet]

Is it working now?

 

[liamfitz]

No. In fact it's no longer writing the new fields. It's stopping during the update i.e '!Referral_ID =' etc. ( Item not found in collection ) when I hover over this line of code, and this field, it shows the value for this field as the existing value for the first record in the recordset ( in this case 1 of 4 - or 5 with the 'New' record ) In my original code, it did at least write the correct value to the correct field. The only thing you appear to have changed in the logic, is you do not concatenate the 3 elements, into one variable, before assigning that to the field. I will compare the changes and see what I can do. P.S. you also changed txtWhatever.Text to txtWhatever.Value ( I'm not sure why, but I know there is a difference )