Help with If statement on close button

[maxxev]

I've made up the code below because I don't actually know how to code this button, can you let me know how the Ifs should work please. At the moment nothing happens at all!

Private Sub Command27_Click()
If [sfrm_Company_Division_Register].[Company_classification].IsNull Then
MsgBox "Company must contain at least one division entry"
Resume ExitPoint
ElseIf CurrentProject.AllForms("Company_Selection_Form").IsLoaded Then
Forms![Company_Selection_Form].Refresh
DoCmd.Close acForm, Me.Name
Else: DoCmd.Close acForm, Me.Name
End If

ExitPoint:
    On Error GoTo 0
    Exit Sub
        
ErrTrap:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitPoint

End Sub

Thank you.

 

[kjv1611]

First, you'll find your issues for many problems if you step through the code, by pressing <F8> instead of <F5>.

Here are some ideas/changes you can try:

1. First, I'd change the button to something meaningful in the name, so it'll make more sense when looking at it. Change the name in the control properties to cmdClose - or whatever you want. The cmd part is using a naming standard, so you might want to get used to something along those lines, such as Reddick.

2. Also, your spacing - makes it more difficult to follow. In any coding, spacing/tabbing is your friend. Especially if your code gets to be any size worth mentioning. For instance, within a procedure/function, I always tab over MOST of the lines at least one tab... So, I'll put 2 spaces where I'd put a tab in the VB editor in the below change suggestions.

3. What is the current form? If the current form is the first one mentioned in the code, then I'd just use the name of the control. If you're coding within a form, you do not have to reference that form.

4. If you need to reference multiple forms, you may be better off just creating variables for those forms, rather than typing the long name out each time - it's up to you, really, but variables can make for easier to follow code at times, and definitely less typing required.

5. IsNul is used like I changed it to below - IsNull(value)
--Another note here, if IsNull doesn't work correctly, then you can also do like this:
If Company_classification = vbNullString Then

Private Sub cmdClose_Click()
  Dim frmCompSel As Form
  Set frmCompSel = Forms!Company_Selection_Form
  If IsNull(Company_classification) Then
    MsgBox "Company must contain at least one division entry", vbCritical, "Missing Data!"
    Company_classification.SetFocus
    GoTo ExitPoint
  ElseIf frmCompSel.IsLoaded Then
    frmCompSel.Refresh
    DoCmd.Close acForm, Form.Name
  Else
    DoCmd.Close acForm, Form.Name
  End If

ExitPoint:
  On Error GoTo 0
  Exit Sub
        
ErrTrap:
  MsgBox Err.Number & " - " & Err.Description
  Resume ExitPoint

End Sub

Anyway, give it a shot. I hope it helps, but I didn't test everything.

Post back with your results..

--

"If to err is human, then I must be some kind of human!" -Me

 

[maxxev]

Hi, thank you for getting back to me, I actually managed to get the code to work, then realised I haden't acounted for someone opening the form and wanting to get out without entering anything....

So I did a little more research and came up with this working code.

Private Sub Command27_Click()
If [sfrm_Company_Division_Register].Form.RecordsetClone.RecordCount = 0 Then
     DoCmd.Hourglass True
         Select Case MsgBox("Company must contain at least one division entry, do you wish to exit without saving", vbYesNo, "Warning")
         Case vbYes
             Me.Undo
             Me.Dirty = False
             DoCmd.Close acForm, Me.Name
         Case vbNo
         End Select
     DoCmd.Hourglass False
    ElseIf CurrentProject.AllForms("Company_Selection_Form").IsLoaded Then
          Forms![Company_Selection_Form].Refresh
          DoCmd.Close acForm, Me.Name
    Else: DoCmd.Close acForm, Me.Name
End If

ExitPoint:
    On Error GoTo 0
    Exit Sub
        
ErrTrap:
    MsgBox Err.Number & " - " & Err.Description
    Resume ExitPoint

End Sub