A little help with If, Else and Exit sub

Z

zulu100

Member
Local time
Today, 21:31
Joined
Nov 3, 2021
Messages
54
I have a form where a production order can be stopped by the button "btn_StopOrdre". However certain conditions must bet met in order to do this.
First criteria - If no choice has been made in combo box "cbm_Medarbejder" a message box tells the operator to fill it out.
Second criteria - If the Query "qry_AfslutProduktionsordreMedarbejder" returns no rows another message box tells the operator that
the productions order is not started yet and therefore cant be stopped.
If both criteria is met, form "frm_ctn_TidStop" will open
I need a little help with the (if, else and exit sub) in the right sequence :-)
Here is what I have so far:

Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer
Dim inCnt As Integer

If IsNull(Forms!frm_ctn_List!cbm_Medarbejder) Then
    
    intanswer = MsgBox("Husk... Vælg medarbejder fra dropdown list", vbOKOnly)
    Forms!frm_ctn_List!cbm_Medarbejder.SetFocus
    Else
        
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False
    
    End If
    
    intCnt = DCount("[No_]", "[qry_AfslutProduktionsordreMedarbejder]")
    If intCnt = 0 Then
    MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"
    End If
    
End Sub
 
Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer
Dim inCnt As Integer

If IsNull(Forms!frm_ctn_List!cbm_Medarbejder) Then
  
    intanswer = MsgBox("Husk... Vælg medarbejder fra dropdown list", vbOKOnly)
    Forms!frm_ctn_List!cbm_Medarbejder.SetFocus
exit sub
    Else
      
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False
    exit sub
    End If
  
    intCnt = DCount("[No_]", "[qry_AfslutProduktionsordreMedarbejder]")
    If intCnt = 0 Then
    MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"
    End If
  
End Sub
 
SHANEMAC51 said:
Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer
Dim inCnt As Integer

If IsNull(Forms!frm_ctn_List!cbm_Medarbejder) Then
 
    intanswer = MsgBox("Husk... Vælg medarbejder fra dropdown list", vbOKOnly)
    Forms!frm_ctn_List!cbm_Medarbejder.SetFocus
exit sub
    Else
     
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False
    exit sub
    End If
 
    intCnt = DCount("[No_]", "[qry_AfslutProduktionsordreMedarbejder]")
    If intCnt = 0 Then
    MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"
    End If
 
End Sub
Click to expand...
Hi
Thanks for the quick reply :)
I must be doing something wrong. It looks like the first criteria is ignored as it goes to the DoCmd.OpenForm... where I get an error cause "cbm_Medarbejder" isn't filled out yet - this makes sense.
However maybe I have a syntax error i my opening criteria for the form.
 

Attachments

  • Error1.png
    Error1.png
    22.1 KB · Views: 319
  • Error.png
    Error.png
    4.8 KB · Views: 247
shouldn't it be:

"MedarbejderID = " & [Forms]![frm_ctn_list]![cbm_Medarbejder] & " And [Off] = 0"
 
arnelgp said:
shouldn't it be:

"MedarbejderID = " & [Forms]![frm_ctn_list]![cbm_Medarbejder] & " And [Off] = 0"
Click to expand...
Well "cbm_Medarbejder" is on the current form but the "Off" field is located in table "tbl_TidsTabel"
Am I missing a table reference here ?
 
You need to start indenting your code properly as well.?
How is the code below meant to execute?
Code: 
    intCnt = DCount("[No_]", "[qry_AfslutProduktionsordreMedarbejder]")
    If intCnt = 0 Then
    MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"
    End If
 
If "cbm_Medarbejder" empty then msgbox
Code: 
 If cbm_Medarbejder = "" Then
    intanswer = MsgBox("Husk... Vælg medarbejder fra dropdown listen", vbOKOnly)
    Forms!frm_ctn_list!cbm_Medarbejder.SetFocus
    Exit Sub
    Else
so far so good :)

Instead of checking a query's outcome it might be better to do it with VBA.

So if field "cbm_Medarbejder" (which is a number field in the current form) has a match in table "tbl_TidsTabel" in field "MedarbejderID" AND
field "No_" (which is a short text field in the current form) has a match in table "tbl_TidsTabel" in field "No_"
The two "No_" are different fields.
AND... The field "SlutTidspunkt" in table "tbl_TidsTabel" is empty

If above is being met then open form....
Code: 
DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
Forms![frm_ctn_TidStop].AllowAdditions = False

Otherwise msgbox
Code: 
MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"

Need some help with the middle part :-)
 
Are you sure the field is null and not a ZeroLengthString? I've modified the code to handle both. Also, it is important to be ruthless in your formatting. It is so much easier to find bugs if your alignment is helping you rather than hiding things.
Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer
Dim inCnt As Integer

If Me.cbm_Medarbejder & "" = "" Then   
    intanswer = MsgBox("Husk... Vælg medarbejder fra dropdown list", vbOKOnly)
    Me.cbm_Medarbejder.SetFocus
    Exit Sub
Else       
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & Me.cbm_Medarbejder & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False
End If
    
intCnt = DCount("[No_]", "[qry_AfslutProduktionsordreMedarbejder]")
If intCnt = 0 Then
    MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"
End If
    
End Sub
 
Pat Hartman said:
Are you sure the field is null and not a ZeroLengthString? I've modified the code to handle both. Also, it is important to be ruthless in your formatting. It is so much easier to find bugs if your alignment is helping you rather than hiding things.
Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer
Dim inCnt As Integer

If Me.cbm_Medarbejder & "" = "" Then  
    intanswer = MsgBox("Husk... Vælg medarbejder fra dropdown list", vbOKOnly)
    Me.cbm_Medarbejder.SetFocus
    Exit Sub
Else      
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & Me.cbm_Medarbejder & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False
End If
   
intCnt = DCount("[No_]", "[qry_AfslutProduktionsordreMedarbejder]")
If intCnt = 0 Then
    MsgBox "Du kan ikke afslutte en ordre du ikke har startet", 64, "Stop produktionsordre"
End If
   
End Sub
Click to expand...
Hi
This works
Thanks... :-)
However, the form opens even though both criteria aren't met.
So I ended up with this:

Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer

If cbm_Medarbejder = "" Then
    intanswer = MsgBox("Husk at vælge medarbejder fra dropdown listen", 64, "Vælg medarbejder")
    Forms!frm_ctn_list!cbm_Medarbejder.SetFocus
    Exit Sub
End If
        
If DCount("No_", "qry_AfslutProduktionsordreMedarbejder") > 0 Then
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False

Else
    MsgBox "Du har ikke startet nogen produktionsordre endnu !", 64, "HUSK - Start produktionsordre"
    Forms!frm_ctn_list![Produktionsordre Nummer].SetFocus
End If
    
End Sub

I am just a novice in Access VBA trying to learn, so bear with me for my poor formatting, indenting etc..
Thanks for helping :)
 
[КОД] Частная субмарина btn_StopOrdre_Click()

Dim intanswer As Integer

Если len(cbm_Medarbejder & "")=0 То ''''''''''''
intanswer = MsgBox("Husk at vælge medarbejder fra dropdown listen", 64, "Vælg medarbejder")
Формы!frm_ctn_list!cbm_Medarbejder.SetФокус
Выход из sub
Закончить, если

Если len(DCount("No_", "qry_AfslutProduktionsordreMedarbejder") и "") > 0 То
DoCmd.OpenForm "frm_ctn_TidStop", , ,"MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
Формы! [frm_ctn_TidStop]. РазрешитьДобавления = False

Еще
MsgBox "Du har ikke startet nogen produktionsordre endnu !", 64, "HUSK - Start produktionsordre"
Формы!frm_ctn_list! [Produktionsordre Nummer]. СетФокус
Закончить, если

Конец Sub[/CODE]
 
Code: 
Private Sub btn_StopOrdre_Click()

Dim intanswer As Integer

If len(cbm_Medarbejder & "")=0 Then
    intanswer = MsgBox("Husk at vælge medarbejder fra dropdown listen", 64, "Vælg medarbejder")
    Forms!frm_ctn_list!cbm_Medarbejder.SetFocus
    Exit Sub
End If
        
If len(DCount("No_", "qry_AfslutProduktionsordreMedarbejder")  & "")=0  Then
    DoCmd.OpenForm "frm_ctn_TidStop", , , "MedarbejderID=" & cbm_Medarbejder.Value & "and [off]= 0", acFormEdit, acWindowNormal
    Forms![frm_ctn_TidStop].AllowAdditions = False

Else
    MsgBox "Du har ikke startet nogen produktionsordre endnu !", 64, "HUSK - Start produktionsordre"
    Forms!frm_ctn_list![Produktionsordre Nummer].SetFocus
End If
    
End Sub
 
you do the Validation on the Form's BeforeUpdate event.
 
However, the form opens even though both criteria aren't met.
So I ended up with this:
Click to expand...
Yes, but what does it open to? Is there code in the form that is overriding you where clause?
 
Pat Hartman said:
Yes, but what does it open to? Is there code in the form that is overriding you where clause?
Click to expand...
No - it opens the form showing the records matching the employee ID where off is equal to 0.
Off=0 is set when Stop time field (Which is a date field) is empty
 
You must log in or register to reply here.

Similar threads

Z
If statement with two criteria
Replies
5
Views
810
zulu100
Z

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom