If IsNull / If Not IsNull / Reference to MainForm and Subform

S

steve21nj

Registered User.
Local time
Yesterday, 23:48
Joined
Sep 11, 2012
Messages
260
No error but it is either to much code or at least mixing code together and not getting the desired results.

I want to click on my command and:

if Me.To IsNnull
[have a message saying you must enter .....]
[another message asking to exit without saving]
If no, continue on main form
If yes, exit

When clicking command again (assuming I entered a value above)
[run above], if true then
If subform value [FileNo] IsNull
MsgBox, click ok, focus back to main form

When clicking command again (assuming I entered both values above)
[run above], if true then
If Not IsNull [above]
save and close

Right now it meshes everything together with all three messages boxes one after another. I enter values, click the command, and nothing happens.

Code: 
Private Sub Command8_Click()
 
    Dim strWhere As String
    Dim response As Integer
    'If IsNull (Me.Field) Then
    If IsNull(Me.To) Then
        MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
        response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
        If response = vbYes Then
        DoCmd.Close
        Exit Sub
    End If
 
    'If IsNull (Forms!MainFormName!SubFormName.Form.Field) Then
    If IsNull(Forms!I216!I216Details.Form.FileNo) Then
        MsgBox "You must enter an A or T File Number!", vbOKOnly, "Input Required"
 
    End If
    Else
        If Not IsNull(Me.To) And (Forms!I216!I216Details.Form.FileNo) Then
            DoCmd.Save
            DoCmd.Close
    End If
    End If
 
End Sub

Suggestions?
 
steve21nj said:
...

When clicking command again (assuming I entered a value above)
[run above], if true then
If subform value [FileNo] IsNull
MsgBox, click ok, focus back to main form

...
Suggestions?
Click to expand...

As it stands I don't see where your code bails and returns to the form for that portion of the code :confused:


Also your code seems to have an orphan Else clause and a superfluous End If.

steve21nj said:
Code: 
Private Sub Command8_Click()
 
    Dim strWhere As String
    Dim response As Integer
    'If IsNull (Me.Field) Then
    If IsNull(Me.To) Then
        MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
        response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
        If response = vbYes Then
        DoCmd.Close
        Exit Sub
    End If
 
    'If IsNull (Forms!MainFormName!SubFormName.Form.Field) Then
    If IsNull(Forms!I216!I216Details.Form.FileNo) Then
        MsgBox "You must enter an A or T File Number!", vbOKOnly, "Input Required"
 
    End If
    [COLOR="Red"]Else[/COLOR]
        If Not IsNull(Me.To) And (Forms!I216!I216Details.Form.FileNo) Then
            DoCmd.Save
            DoCmd.Close
    End If
    [COLOR="Red"]End If[/COLOR]
 
End Sub
Click to expand...

Also if you are bailing out of code to allow the user to correct an error it's always helpful to set focus to the control that requires correcting.
 
Thank you for the reply, I had the setfocus prior but it would produce the same results, so i pulled it all together.

Does the code structure look correct overall? I must be missing something.

I will remove the Else and End If in the morning to test again.

Code: 
If IsNull(Me.To) Then
        MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
        response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
        Me.To.SetFocus
        If response = vbYes Then
        DoCmd.Close
        Exit Sub
    End If
 
That looks Ok, and I've just notice where, what I thought was a superfluous End If should go, and I think you might need an additional Exit Sub

Code: 
If IsNull(Me.To) Then
        MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
        response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
             If response = vbYes Then
                DoCmd.Close
                Exit Sub
             [COLOR="Red"]End If[/COLOR]
        Me.To.SetFocus
        [COLOR="Magenta"]Exit Sub[/COLOR]
    End If
 
I added the SetFocus to the subform in the second [If IsNull] but encounter a runtime 13 error on the [If Not IsNull] line. It saves the material entered, half the battle. Is it because I have the And statement?

Code: 
Private Sub Command8_Click()
    
    Dim strWhere As String
    Dim response As Integer
    'If IsNull (Me.Field) Then
    If IsNull(Me.To) Then
            MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
            response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
                If response = vbYes Then
                    DoCmd.Close
                     Exit Sub
                End If
            Me.To.SetFocus
            Exit Sub
    End If
    'If IsNull (Forms!MainFormName!SubFormName.Form.Field) Then
    If IsNull(Forms!I216!I216Details.Form.FileNo) Then
            MsgBox "You must enter an A or T File Number!", vbOKOnly, "Input Required"
            Forms!I216!I216Details.SetFocus
            Forms!I216!I216Details.Form.FileNo.SetFocus
    End If
        
    If Not IsNull(Me.To) And (Forms!I216!I216Details.Form.FileNo) Then
            DoCmd.Save
            DoCmd.Close
    End If
        
End Sub
 

Attachments

  • RT13.PNG
    RT13.PNG
    20.5 KB · Views: 271
  • RT13Line.PNG
    RT13Line.PNG
    4.7 KB · Views: 262
The error is Type mismatch, Try..
Code: 
IsNull(Forms!I216!I216Details.Form.FileNo)
Instead of
Code: 
(Forms!I216!I216Details.Form.FileNo)
 
Thank you. I ended up with the below because I didn't want it to save if Null.

Code: 
If Not IsNull(Me.To) And Not IsNull(Forms!I216!I216Details.Form.FileNo) Then

I have another question. If I entered a value on the (Me.To) and Left the second null, it would give the MsgBox "you must enter an A...." So when the focus is back on the main form, if I were to delete the value in (Me.To) and proceed to fill out everything else, it saves.....Why? If the code states If Not IsNull, why does it save? Why doesn't is cycle the code from the top again?
 
Might be because it is setting a zero length string in the Me.To field?? Just my hypothesis.. To escape that ZLS you might want to try Len() instead of IsNull.. try this and see what happens..
Code: 
If Len(Me.To & vbNullString)>0 And Len(Forms!I216!I216Details.Form.FileNo & vbNullString)>0 Then
 
Here are the steps and what I encounter:

Open Form
Click Save
MsgBox1
MsgBox2
Enter Value in [To]
Click Save
MsgBox
Enter Value in [FileNo]
Delete Value in [To]
Click Save
[Form sits with no action]
Enter Value in [To]
Click Save
Saves

How do i revert back to MsgBox1 after I delete the Value in [To]?
 
I do not get it., did you use the code in post#8 ? Also after SetFocus use Exit Sub.
 
Sorry, yes I entered the code and it works except if you were to follow the steps in post 9. And entered the Exit Sub.

Basically if I enter a value in [Me.To], then click save, it will display a MsgBox and setfocus to [Forms!I216!I216Details.Form.FileNo]

If I then enter a value for [Forms!I216!I216Details.Form.FileNo], and then delete the value in [Me.To], click save, the form doesn't do anything. It just sits without feedback. If I enter a value into [Me.To], it will then save and exit.

For me, i'd be fine with it, but other people are going to use it...This is one of those "what if" scenarios.
 
That is really strange.. okay try the Len(Me.To & vbNullString) for Me.To and see what happens..
 
By putting Len in the upper portion, it bypasses the If statement, msgboxes, and goes straight to the second If statement. So i put Len in the second statement, clicked save, and the form was at a standstill. This is represented in the Updated Code.

Updated code:
Code: 
Private Sub Command8_Click()
 
    Dim strWhere As String
    Dim response As Integer
    'If IsNull (Me.Field) Then
    If Len(Me.To & vbNullString) Then
            MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
            response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
                If response = vbYes Then
                    DoCmd.Close
                     Exit Sub
                End If
            Me.To.SetFocus
            Exit Sub
    End If
    'If IsNull (Forms!MainFormName!SubFormName.Form.Field) Then
    If Len(Forms!I216!I216Details.Form.FileNo & vbNullString) Then
            MsgBox "You must enter an A or T File Number!", vbOKOnly, "Input Required"
            Forms!I216!I216Details.SetFocus
            Forms!I216!I216Details.Form.FileNo.SetFocus
            Exit Sub
    End If
 
   If Len(Me.To & vbNullString) > 0 And Len(Forms!I216!I216Details.Form.FileNo & vbNullString) > 0 Then
            DoCmd.Save
            DoCmd.Close
    End If
 
End Sub



My old code:
Code: 
Private Sub Command8_Click()
 
    Dim strWhere As String
    Dim response As Integer
    'If IsNull (Me.Field) Then
    If IsNull(Me.To) Then
            MsgBox "You must select a Transfer To Destination!", vbOKOnly, "Input Required"
            response = MsgBox(prompt:="Do you want to Exit without saving?", buttons:=vbYesNo)
                If response = vbYes Then
                    DoCmd.Close
                     Exit Sub
                End If
            Me.To.SetFocus
            Exit Sub
    End If
    'If IsNull (Forms!MainFormName!SubFormName.Form.Field) Then
    If IsNull(Forms!I216!I216Details.Form.FileNo) Then
            MsgBox "You must enter an A or T File Number!", vbOKOnly, "Input Required"
            Forms!I216!I216Details.SetFocus
            Forms!I216!I216Details.Form.FileNo.SetFocus
            Exit Sub
    End If
 
   If Len(Me.To & vbNullString) > 0 And Len(Forms!I216!I216Details.Form.FileNo & vbNullString) > 0 Then
            DoCmd.Save
            DoCmd.Close
    End If
 
End Sub
 
Sorry if I was not clear.. you have to use =0 after the Len()..
So..
Code: 
If Len(Me.To & vbNullString) = 0 Then
:
:
If Len(Forms!I216!I216Details.Form.FileNo & vbNullString) = 0 Then
 
Awesome. My fault, I shouldn't known after >0 in the If Len lower line.
Thank you!
 
You must log in or register to reply here.

Similar threads

Mike Krailo
Solved Need help with before update code logic?
Replies
12
Views
363
Mike Krailo
Mike Krailo
C
Checking if records exist
Replies
12
Views
545
cheekybuddha
cheekybuddha
A
Filter Records
Replies
1
Views
232
June7
June7
essaytee
Why messagebox appears then closes on successful run of DoCmd.OutputTo....pdf
Replies
5
Views
209
Mark_
M
D
Solved Change password using tempvar not passing to following form
Replies
4
Views
324
Gasman
Gasman

Users who are viewing this thread

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