Subform to requery from 1st subform (1 Viewer)

JMarie · Aug 30, 2018

I have 2 subforms on a page. The first subform holds data from other sources-claims that have been submitted and paid. The 2nd subform shows all the claims that are currently in her database.
The user needs to move these finished claims to her database. She can look over the data, confirm it is a new/valid claim and put it in her database by clicking ADD

The ADD button - saves the current record then calls a macro. The macro calls a query that appends the record to her claims. It then requeries the first subform and removes it from consideration (it no longer shows in the 1st subform).

The one issue I have is how to requery the 2nd subform. This new claim does not show up on her finished claims form until she goes to a different record then back to the record.
Should I do this whole procedure from vb and not call a macro? Either way how do I make this 2nd form show this new claim.
Thanks.

.

arnelgp · Aug 30, 2018

you cannot. sunce the new claim record is not save yet.

arnelgp · Aug 30, 2018

on second thought, you can forcefuly saved the new
record of subForm1 and then Requery subForm2.

you need VBA for this.
the events you will be touchings on
subForm1 are:

1. Exit Event of the subForm container.
2. Timer Event of subForm1
3. BeforeInsert Event of subForm1
4. Current Event of subForm1.

on design view of you form, leave the TimerInterval
of subForm1 as is (0).

click on subForm1 on design view until
you see it is surrounded by a yellow or orange box).
go to its Property->Event->Exit:

Code:

Private Sub subForm1_Exit(Cancel As Integer)
	Me.TimerInterval = 0
End Sub

click again the upper left corner of the subForm1 until
you see a small black square.
then add code to the following Events:

Code:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.TimerInterval = 2000
End Sub

Private Sub Form_Current()
    Me.TimerInterval = 0
End Sub

Private Sub Form_Timer()
    ' NOTE 
    ' ID (autonumber) is the PrimaryKey(PK) i am using.
    ' its best to always add an Autonumber field.
    ' it may not be obvious but it will save you later
    ' for what this field can offer you.
    If Nz(Me.ID, 0) <> 0 Then
        Me.Dirty = False
        '
        ' replace subForm2 with the correct subform name
        ' 
        Me.Parent!subForm2.Requery
    End If
End Sub

you will noticed that when we add the timer,
sometimes the form will flicker. this will only
be through for the New record.

JMarie · Sep 9, 2018

Thanks for your help. For whatever reason I could not get the timer to work. However, I did find a solution on another forum, and it was quite simple.

On the OnDataChange event of the first subform I put in the following code:
If Me!TabControlName = 1 Then Forms!MainformName!SubformName.Form.Requery

Here is the link to the solution: https://www.tek-tips.com/viewthread.cfm?qid=1442715

And it worked like a charm.
Thank you for your help. I like the idea of a timer for another project I will be doing soon.

Subform to requery from 1st subform (1 Viewer)

JMarie

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

JMarie

Registered User.

Similar threads

Users who are viewing this thread