Solved Run code from a form before data has changed, but can't be on Form open

[Dag Solder]

Hi everyone,

I have 2 forms: Frm_A & Frm_B. Frm_A is a small pop up which contains a combo box to allow the user to select a record to be passed to Frm_B and a command button ‘EDIT’

Frm_A and Frm_B are opened by another form at the same time with Frm_B being hidden until a command button is pressed to transfer data from A to B, at this stage Frm_A is hidden and Frm_B has the record ID passed to it. This all seems to work ok. Code on command button on Frm_A:

Private Sub EDIT_Click()
    DoCmd.OpenForm "Frm_B", acNormal, "", "", , acWindowNormal
            DoCmd.OpenForm "Frm_A", acNormal, "", "", , acHidden
            DoCmd.OpenForm " Frm_B", , , "[ID] = " & Me.txtZiff ' Value of txt box txtZiff=
End Sub

Once this code has run, Frm_B is available to the user with the selected record displayed - great!!

I now have another set of code to run within Frm_B, ordinarily I would have run it with the ‘On Load’ Property of the Form, but in this instance I can’t as the form opens before the data has been passed from Frm_A

As a temporary fix I am running the subject code from a command button on Frm_B, it works, but this is not ideal for many reasons, not least, the button must be pressed and if it isn't the project fails.

These are the requirements:

The code must be run, it must only be run once (another reason why a button is not ideal although it could be deactivated once pressed), it must be run before any data on the form is changed (This is a big problem with the button on the form) and it cannot be run until the data from Frm_A is passed to Frm_B.

I have experimented with various methods including using some of the form properties, but nothing seems to work as intended.

Any ideas??

I am new to VBA so please be gentle

Cheers Dag.

 

[theDBguy]

Hi Dag. Did you show us how you're passing the data from form A to form B?

 

[Dag Solder]

Hi DBguy,

I used the Control wizard for a command button to open another form initially and then changed the Macro it wrote to VBA using the Access function.... DoCmd.OpenForm " Frm_B", , , "[ID] = " & Me.txtZiff.

 

[theDBguy]

Hi DBguy,

I used the Control wizard for a command button to open another form initially and then changed the Macro it wrote to VBA using the Access function.... DoCmd.OpenForm " Frm_B", , , "[ID] = " & Me.txtZiff.

Ok, thanks. I guess I'll have to do some testing to see how opening a form with a filter doesn't affect the form's Load event. If that's the case, then perhaps you could try using a one-time Timer event to execute your code after the form opens.

 

[Gasman]

If formA is open but hidden, why can you not just refer to it when you need to?

Uncle Gizmo recently posted a video about using custom properties for a form as well?

Why not open FormA first?

I *thought* using OpenForm a second time, just activated the form if it was already open?
Someone was on here a while back complaining why Form Open or Form Load did not work the second time of opening?

 

[Dag Solder]

@Gasman,
The problem isn't with Form A, this performs it's intended function, the problem is that Form B needs to be open for the information to be passed from Form A and only then can the additional code be run.

@theDBguy,
I am not sure if a timer would work, at least, not from the point when Frm_A or Frm_B is opened. Say the user answered the phone or something else, the timer could rundown without the command button being pressed

 

[Gasman]

@Gasman,
The problem isn't with Form A, this performs it's intended function, the problem is that Form B needs to be open for the information to be passed from Form A and only then can the additional code be run.

@theDBguy,
I am not sure if a timer would work, at least, not from the point when Frm_A or Frm_B is opened. Say the user answered the phone or something else, the timer could rundown without the command button being pressed

If FormA is open, then FormB can reference the properties/controls from FormA? as far I know?

 

[MajP]

Also if you want to run code in formB from formA and the procedure in form B is called "SomeProcedure" then from formA
'pass data from A to B then
forms("formB").SomeProcedure

 

[Dag Solder]

@Gasman, yes this is correct

 

[theDBguy]

@theDBguy,
I am not sure if a timer would work, at least, not from the point when Frm_A or Frm_B is opened. Say the user answered the phone or something else, the timer could rundown without the command button being pressed

Hi. Maybe I misunderstood what was happening, but I thought you said manually clicking the button to run the code works because running the same code in the Load event doesn't work. So, I was just thinking to use a Timer event to let the form open and load first, then the timer event will auto-click the button or execute the same code for you. I usually use a 1 millisecond timer for something like that, so it happens immediately after the form loads/opens.

 

[Dag Solder]

Hi. Maybe I misunderstood what was happening, but I thought you said manually clicking the button to run the code works because running the same code in the Load event doesn't work. So, I was just thinking to use a Timer event to let the form open and load first, then the timer event will auto-click the button or execute the same code for you. I usually use a 1 millisecond timer for something like that, so it happens immediately after the form loads/opens.

Sorry, I probably didn't explain myself correctly. Form B is already open, so I can't use the On Load event in Form B to fire my procedure.
That is why I put a button on the form, to check if the code would run and function correctly when the data had been passed from Form A

 

[Dag Solder]

Also if you want to run code in formB from formA and the procedure in form B is called "SomeProcedure" then from formA
'pass data from A to B then
forms("formB").SomeProcedure

Interesting MajP.....

 

[arnelgp]

Make the Sub/Function in formB Public so it can be called from outside:

Forms!FormB.theSubname

 

[Dag Solder]

Thank you MajP and arnelgp perfect, this is exactly what I need - works like a charm