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

Dag Solder

New member
Local time
Today, 15:28
Joined
May 8, 2020
Messages
19
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:

Code:
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

I’m here to help
Local time
Today, 07:28
Joined
Oct 29, 2018
Messages
10,720
Hi Dag. Did you show us how you're passing the data from form A to form B?
 

Dag Solder

New member
Local time
Today, 15:28
Joined
May 8, 2020
Messages
19
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

I’m here to help
Local time
Today, 07:28
Joined
Oct 29, 2018
Messages
10,720
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

Enthusiastic Amateur
Local time
Today, 15:28
Joined
Sep 21, 2011
Messages
5,963
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? :unsure:
 

Dag Solder

New member
Local time
Today, 15:28
Joined
May 8, 2020
Messages
19
@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

Enthusiastic Amateur
Local time
Today, 15:28
Joined
Sep 21, 2011
Messages
5,963
@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

You've got your good things, and you've got mine.
Local time
Today, 10:28
Joined
May 21, 2018
Messages
3,183
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
 

theDBguy

I’m here to help
Local time
Today, 07:28
Joined
Oct 29, 2018
Messages
10,720
@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

New member
Local time
Today, 15:28
Joined
May 8, 2020
Messages
19
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
 
Last edited:

Dag Solder

New member
Local time
Today, 15:28
Joined
May 8, 2020
Messages
19
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

error reading drive A:
Local time
Today, 22:28
Joined
May 7, 2009
Messages
9,593
Make the Sub/Function in formB Public so it can be called from outside:

Forms!FormB.theSubname
 

Dag Solder

New member
Local time
Today, 15:28
Joined
May 8, 2020
Messages
19
Thank you MajP and arnelgp perfect, this is exactly what I need - works like a charm
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom