Trying to do a simple open form but code is getting stuck

[ScubaBart]

Still working on bee hives..... Still converting to VBA.......



Stuck on something that should be simple and yet, I can't get it to work.

Using code that is virtually identical to code used with another set of forms that works fine. Obviously form and control names are adjusted.

With this form however, the code keeps bugging out. I have verified form name, control names, the controls being on the forms, etc. multiple times.

The stop is on the DoCmd.OpenForm line and it happens regardless of whether it's the Then condition or the Else condition.

Here is the code:

What am I missing ?? I uploaded the file again. The offending event is when the F_Log_Hive_Main form is open and attempting to open another form using the De-Activate Hive button.

Private Sub Command_Status_Change_Click()

    If Forms!F_Log_Hive_Main!Status = -1 Then   'Opens the Status Change form to De-Activate Hives - No other options
    
        DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = -1 And If Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1) > 1 Then Log_Apiary_ID = Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1)", acFormReadOnly, acDialog
        Forms!F_Log_Hive_Status_Change!Status_Change_Title.Caption = "De-Activate Hive"  'Change form Title
        Forms!F_Log_Hive_Status_Change!Command_Return_to_Log.Caption = "Return to" & vbCrLf & "In-Active Log"  'Changes Button Text
        Forms!F_Log_Hive_Status_Change!Command_Change_Status.Caption = "De-Activate"  'Changes Row Button Text
        Forms!F_Log_Hive_Status_Change!Note_Reactivate.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Deactivate.Visible = -1
        Forms!F_Log_Hive_Status_Change!Note_Move.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Delete.Visible = 0
        Forms!F_Log_Hive_Status_Change!Action_Mode = -1
        Forms!F_Log_Hive_Status_Change!Move_Mode = 0
        Forms!F_Log_Hive_Status_Change!Delete_Mode = 0
        
    Else   'Opens the Status Change form to Re-Activate Hives - No other options
    
        DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = 0", "acFormReadOnly", acDialog
        Forms!F_Log_Hive_Status_Change!Status_Change_Title.Caption = "Re-Activate Hive"  'Change form Title
        Forms!F_Log_Hive_Status_Change!Command_Return_to_Log.Caption = "Return to" & vbCrLf & "Active Log"  'Changes Button Text
        Forms!F_Log_Hive_Status_Change!Command_Change_Status.Caption = "Re-Activate"  'Changes Row Button Text
        Forms!F_Log_Hive_Status_Change!Note_Reactivate.Visible = -1
        Forms!F_Log_Hive_Status_Change!Note_Deactivate.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Move.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Delete.Visible = 0
        Forms!F_Log_Hive_Status_Change!Action_Mode = 0
        Forms!F_Log_Hive_Status_Change!Move_Mode = 0
        Forms!F_Log_Hive_Status_Change!Delete_Mode = 0
        
    End If


End Sub

[Welshgasman]

Ypu cannot do this?

DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = -1 And If Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1) > 1 Then Log_Apiary_ID = Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1)", acFormReadOnly, acDialog

[ScubaBart]

Ypu cannot do this?

DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = -1 And If Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1) > 1 Then Log_Apiary_ID = Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1)", acFormReadOnly, acDialog

Which part can I not do ?

If I try to run the Else condition that has the more simple Where clause, it also stops the code but that line of code is exactly the same format as one that is working correctly.

Not working:

DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = 0", "", acDialog

Working fine:

DoCmd.OpenForm "F_Log_Apiary_Status_Change", acNormal, "", "Apiary_Active = -1 And Log_Apiary_ID > 1", acFormReadOnly

I can use the And function in the Where clause. Can I not us the If / Then function ?

This is why I can't make sense of it.

[Welshgasman]

No, the And is for a simple expression.
So test that If with the And before trying to open the form.
I cannot see it all, on my phone, but convert that If to some simple expression even if it is blnFrmOpen and then test for that in the openform.

[moke123]

The acDialog pauses your code so everything after it does not run until the form is closed.

I believe you can put it at the end of your code as

        DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = -1 And If Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1) > 1 Then Log_Apiary_ID = Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1)", acFormReadOnly     Forms!F_Log_Hive_Status_Change!Status_Change_Title.Caption = "De-Activate Hive"  'Change form Title
        Forms!F_Log_Hive_Status_Change!Command_Return_to_Log.Caption = "Return to" & vbCrLf & "In-Active Log"  'Changes Button Text
        Forms!F_Log_Hive_Status_Change!Command_Change_Status.Caption = "De-Activate"  'Changes Row Button Text
        Forms!F_Log_Hive_Status_Change!Note_Reactivate.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Deactivate.Visible = -1
        Forms!F_Log_Hive_Status_Change!Note_Move.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Delete.Visible = 0
        Forms!F_Log_Hive_Status_Change!Action_Mode = -1
        Forms!F_Log_Hive_Status_Change!Move_Mode = 0
        Forms!F_Log_Hive_Status_Change!Delete_Mode = 0

Forms!F_Log_Hive_Status_Change.modal = true  '<<<<<

[Welshgasman]

Also, whenever you are duplicating code, there is a better way?

Take this code out of your If logic.

        Forms!F_Log_Hive_Status_Change!Note_Move.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Delete.Visible = 0
        Forms!F_Log_Hive_Status_Change!Move_Mode = 0
        Forms!F_Log_Hive_Status_Change!Delete_Mode = 0

[moke123]

The above I think got a little screwed up due to the site issues but you can see the last line

[ano]

And If Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1) > 1

can you put the if before all into a variable so you just test variable instead?

[ScubaBart]

OK,

Taking the input from you guys, I took a slightly different approach and came up with something that seems to work. The syntax for the DoCmd.ApplyFilter function throws me off a little bit with the early closing parenth and the & tossed in there but finally got a something.

May be a bit unconventional but hey, it works.

Private Sub Command_Status_Change_Click()


    If Forms!F_Log_Hive_Main!Status = -1 Then   'Opens the Status Change form to De-Activate Hives - No other options
    
        DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = -1"  'Opens form
        Forms!F_Log_Hive_Status_Change!Status_Change_Title.Caption = "De-Activate Hive"  'Change form Title
        Forms!F_Log_Hive_Status_Change!Command_Return_to_Log.Caption = "Return to" & vbCrLf & "In-Active Log"  'Changes Button Text
        Forms!F_Log_Hive_Status_Change!Command_Change_Status.Caption = "De-Activate"  'Changes Row Button Text
        Forms!F_Log_Hive_Status_Change!Note_Reactivate.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Deactivate.Visible = -1
        Forms!F_Log_Hive_Status_Change!Note_Move.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Delete.Visible = 0
        Forms!F_Log_Hive_Status_Change!Action_Mode = -1
        Forms!F_Log_Hive_Status_Change!Move_Mode = 0
        Forms!F_Log_Hive_Status_Change!Delete_Mode = 0
        
        If Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1) > 1 Then
    
            DoCmd.ApplyFilter , "Log_Apiary_ID =" & Forms!F_Log_Hive_Main!Combo_Filter_by_Apiary.Column(1)
        
        End If
        
    Else   'Opens the Status Change form to Re-Activate Hives - No other options
    
        DoCmd.OpenForm "F_Log_Hive_Status_Change", acNormal, "", "Apiary_Active = 0"
        Forms!F_Log_Hive_Status_Change!Status_Change_Title.Caption = "Re-Activate Hive"  'Change form Title
        Forms!F_Log_Hive_Status_Change!Command_Return_to_Log.Caption = "Return to" & vbCrLf & "Active Log"  'Changes Button Text
        Forms!F_Log_Hive_Status_Change!Command_Change_Status.Caption = "Re-Activate"  'Changes Row Button Text
        Forms!F_Log_Hive_Status_Change!Note_Reactivate.Visible = -1
        Forms!F_Log_Hive_Status_Change!Note_Deactivate.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Move.Visible = 0
        Forms!F_Log_Hive_Status_Change!Note_Delete.Visible = 0
        Forms!F_Log_Hive_Status_Change!Action_Mode = 0
        Forms!F_Log_Hive_Status_Change!Move_Mode = 0
        Forms!F_Log_Hive_Status_Change!Delete_Mode = 0
        
    End If


End Sub

To Welshgasman, The code you highlighted as repetitive is a unique combination for several different conditions that the form can open under to perform completely different functions. They are unbound checkboxes that are set based on what the desired type of action is to be, ie. Move, De-Activate, Re-Activate, Delete, etc. By setting these, the button commands on that form perform the intended action(s). Yes, there probably is a simpler way to do it but if it works smoothly for me, I'm gonna let it be.



So for today, problem is solved.

Thanks all for the assistance. More to come I'm sure.

[Welshgasman]

The way I look at it, is that you have the same code in both sides of the Else?

Therefore, it can go once outside the IF logic, as it is executed no matter which path the code takes at the moment.

[ScubaBart]

The way I look at it, is that you have the same code in both sides of the Else?

Therefore, it can go once outside the IF logic, as it is executed no matter which path the code takes at the moment.

In the form that is called to open, there are currently four options that can be presented depending on how it is opened. Instead of setting the Caption text of a single fixed note in the code, I made four different text block notes, one specific for each potential action. They are not bound and the previous visibility cannot be confirmed due to previous actions that may have been taken using that form.

Likewise, the variables are unbound and therefore the condition on opening not guaranteed so they must be set when the form is opened so that when any commands are executed, the values dictate what the command button actually does.

If you look, they should be slightly different between conditions. The same form is called from other screen options as well and the variables are different for each of those options.

I get what you are saying as some values are common but it's easier for me to keep the same layout to keep consistency in working with the code.