Form_Open subroutine Doesn't Execute in Correct Order?

[ItsRoland]

I have a main form that will always be opened first when someone is working in my db, called "fAssessment". Another form, called "fDefineDefaults", allows the user to edit any of the default values before they start working in fAssessment. I recently set it up so that when you open fAssessment for the first time it opens fDefineDefaults and waits for that to close before it continues with any code. Assuming it's the first time the user is opening fAssessment, this SHOULD open fDefineDefaults and wait for it to be closed before doing anything else. After fDefineDefaults is closed, fAssessment will prompt the user for some more information, and then the user can begin working in fAssessment.

However..

For some reason this is not the order in which these events take place. The code that opens fDefineDefaults was occurring where it should (first), until I added a simple 'If' statement to check if it needed to be opened or not. After doing this, it completely skipped over that if statement and starts prompting the user for the additional information I mentioned earlier.

I thought this was because I constructed my If statement wrong, but...

If the user doesn't enter valid input, or clicks cancel, the code moves back up to the initial prompt via a 'GoTo'. What's REALLY strange is that if this occurs, instead of going back to the spot defined by GoTo, it goes back up to the top and opens fDefineDefaults and continues where it should have after fDefineDefaults is closed.

This doesn't make any sense, because if the code that opens fDefineDefaults is ran, then that means the if statement was true and it should have occurred before anything else.

The Code:

If IsNull(DLookup("Size", "tRAMSize")) Or DLookup("Size", "tRAMSize") = 0 Then

    DoCmd.OpenForm "fDefineDefaults", , , , , acDialog      <------------- This is the line that opens fDefineDefaults and waits for it to be closed before continuing. 


End If


If DLookup("Size", "tRAMSize") > 0 Then


    Exit Sub


End If


Re_Prompt:


Rsize = InputBox("Please Enter RAM dimensions")         <----------- This is the line the code starts on, for some reason. 


'Ends process if user clicks 'cancel' or gives no input, and checks if input is a number
If Rsize = "" Then
    MsgBox "Dimension selection was canceled", vbOKOnly
  
    Exit Sub


ElseIf IsNumeric(Rsize) Then
    
    Rsize = CInt(Rsize)
    
    db.Execute "INSERT INTO tRAMSize(Size)" & "VALUES(" & Rzie & ")"
    
    
Else


    MsgBox "Please only enter a number for RAM dimensions"
    
    
    GoTo Re_Prompt 


End If

I've tried adding MsgBox's to see what was happening, and even the MsgBox I placed at the very top of the sub was completely skipped. This MsgBox is displayed when the 'GoTo' gets used. (Note that I removed these msgbox's before posting to clean up the code a bit)



It should also be mentioned that this code was working 100% as intended prior to me adding the line to open fDefineDefaults. Adding those lines is the only thing that has changed since it started acting strange.

Hopefully I explained that well enough. I'd be happy to provide any additional details if necessary.

Any help is greatly appreciated.

UPDATE:

I tried adding the line that opens fDefineDefaults to the if statement that executes if the user enters an appropriate input for the additional information, i.e. it should execute that before it updates tables with the user input. For some reason this still doesn't cause the form to open, almost as if it's just ignoring the line while its in an if statement. To test this I placed the line in an If that will always be true (If 1 = 1 Then) and it ran fine. Not sure whats causing it to be ignored everywhere except where I don't need it to be.

I also added more MsgBox's throughout the sub to try to understand what process it goes through. From what I discovered, it prompts for input before doing anything else and then immediately jumps back to the top. I stopped the code before it could continue after moving back to the top of the sub, and checked my table that the user input is stored in. Somehow, the input was stored in a table without me telling it to do so yet (unless it's ignoring my MsgBox's until it jumps to the top?). The messages that I set to appear before and after updating the table with user input never appeared, so I'm assuming it never executed the UPDATE command. That being said it doesn't make any sense that it would be saved to the table without being told to save it there..

I'm seriously stumped on this one..

[ranman256]

put a STOP on the first line. (red line)
then when it executes and stops, hover the cursor over each value to see if it gets the value you think its getting.

step thru with F8

[ItsRoland]

I think I may have figured out the problem while trying out your suggestion and testing a few more ideas I had.

I forgot to mention that this form has a few subforms on it, because I didn't realize that the subform's Form_Open event triggers prior to the parent form's. Before I tied all these together I must have set up this prompt in another forms open event.

Long story short, my new problem is that the subform that caused all this confusion needs the info in order to load everything correctly, but placing this new line there isn't ideal either. Is there a way to force the parent form's Form_Open event to trigger before it's subforms do?

I apologize for the lack of info, I'm typing this in a rush as I just realized I'm late for an appointment. I'll get back to you as soon as I'm able.

Thanks for the help.

[Micron]

Is there a way to force the parent form's Form_Open event to trigger before it's subforms do?

It is not possible (at least I would bet against it).
If you try to get a value in subform code from a main form you will generate an error for the reason you discovered. You would have to get that value in main form code and pass it to the subform control if you can't make the field part of the subform query. If your form(s) is based on a table, then you also now know why I seldom base a form or report on a table.

Also note that without criteria, DLookup will return the first value it finds in the specified field of the specified domain. That may or may not be the first visible record in that domain. So I'm not sure of the reliability of your lookup with no criteria.

Not sure what you mean by 'if the form needs to be opened'. Why not - because it already is open? In that case, you may want to test if it's open instead, but if so, then what? Do you need to requery it? Or close and reopen it? Or simply show it? I would also caution against GoTo for a reprompt. It looks like you could lock the user into a loop that they may want to get out of. I think it's generally considered poor practice to use goto's to control flow (beyond directing error handling). A message box with a cancel option might be better, and use the message return result to reopen the input or quit.

[Minty]

There is a way around this.
Set all of the the subforms containers Source objects to be empty (either "" or Null). This will make the subform container appear to be empty in design mode.
Save the form.

In code, after you have set up your defaults then via VBA reinstate the subform containers Edit:source objects to your subform names.
Something like this on your main form ;

Me.cntContainer1.SourceObject = "MySubForm1"
Me.cntContainer2.SourceObject = "MySubForm2" 

etc etc

This will force the sub forms to load After the main form.
If you have loads of subform controls you can get a bit cunning and use a loop around all the container controls and set the property using an appropriate tag value.
But that's probably another subject.

And do all this on the VBA in the form Load event not the Open event.

[ItsRoland]

Sorry for the long delay in response. I actually managed to get it to work by changing what it checks for to confirm whether or not it should open. Instead of checking for a table to be empty (which needed to not be empty for the subforms to open, which is why I was having difficulties), it's checking for a different, more specific value.

In response to Micron's post:

You asked what I meant by "if it needs to be opened". fDefineDefaults only ever needs to be opened the first time fAssessment is opened (speaking in terms of the open/load event of fAssessment) . Once someone has defined the defaults they want to work with, they won't need to define them again. This way if fAssessment is accidentally closed, the user won't have to deal with fDefineDefaults before continuing where they left off.

Also, I'll make the changes to how I handle my prompt so it doesn't use GoTo's anymore. Thanks for the tip.



In response to Minty's post:

What you posted is probably better than what I used to solve my problem, but I'm not entirely sure I follow what you mean. Are you saying to remove the control sources of my subforms and add them back in when the main form (fAssessment) is loaded?
Won't doing that cause an error when I open the main form, because it won't be able to find the data it needs when it tries to open the subforms?





Regardless, thanks for all the help. Turns out taking almost a week break was enough for me to realize how to solve the problem when I came back to it, even if its not the best solution.

[Minty]

Won't doing that cause an error when I open the main form, because it won't be able to find the data it needs when it tries to open the subforms?

In theory no, because the sub forms are linked to the main form, so as long as the main form is loaded the subforms fall in line once the source object is set.
It's quite a common technique used if you have multiple sub forms on different tab controls, you only load the sub forms objects on the visible tab to speed up the form performance. It can make a significant difference on complex forms.

It sound like you have achieved what you need by a applying a more logical approach to the issue, which is always a win .