A little help with If, Else and Exit sub (1 Viewer)

zulu100

Member
Local time
Today, 23:53
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
 

SHANEMAC51

Active member
Local time
Tomorrow, 00:53
Joined
Jan 28, 2022
Messages
294
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
 

zulu100

Member
Local time
Today, 23:53
Joined
Nov 3, 2021
Messages
54
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
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: 202
  • Error.png
    Error.png
    4.8 KB · Views: 138

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:53
Joined
May 7, 2009
Messages
16,867
shouldn't it be:

"MedarbejderID = " & [Forms]![frm_ctn_list]![cbm_Medarbejder] & " And [Off] = 0"
 

zulu100

Member
Local time
Today, 23:53
Joined
Nov 3, 2021
Messages
54
shouldn't it be:

"MedarbejderID = " & [Forms]![frm_ctn_list]![cbm_Medarbejder] & " And [Off] = 0"
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 ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:53
Joined
Sep 21, 2011
Messages
10,863
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
 

zulu100

Member
Local time
Today, 23:53
Joined
Nov 3, 2021
Messages
54
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 :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:53
Joined
Feb 19, 2002
Messages
36,939
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
 

zulu100

Member
Local time
Today, 23:53
Joined
Nov 3, 2021
Messages
54
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
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 :)
 

SHANEMAC51

Active member
Local time
Tomorrow, 00:53
Joined
Jan 28, 2022
Messages
294
[КОД] Частная субмарина 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]
 

SHANEMAC51

Active member
Local time
Tomorrow, 00:53
Joined
Jan 28, 2022
Messages
294
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
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:53
Joined
May 7, 2009
Messages
16,867
you do the Validation on the Form's BeforeUpdate event.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:53
Joined
Feb 19, 2002
Messages
36,939
However, the form opens even though both criteria aren't met.
So I ended up with this:
Yes, but what does it open to? Is there code in the form that is overriding you where clause?
 

zulu100

Member
Local time
Today, 23:53
Joined
Nov 3, 2021
Messages
54
Yes, but what does it open to? Is there code in the form that is overriding you where clause?
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
 

Users who are viewing this thread

Top Bottom