Subform Validation

[Guest]

I have a parent form named "newTeamMember." I have a subform on that form
named "newTM_Request". I have a button on the subform that checks to ensure
all of the required fields are not null, and if so it saves the record and
closes the form. If not, then I would like it to display a message box
advising the user they left one or more fields blank. However, when I click
the button, instead of my message box Access displays one that only says
"object required". Here is the code attached to the Save Record button.

'Button to save record and close form
Private Sub saveNewTMReq_Click()
On Error GoTo Err_saveNewTMReq_Click

If requestedBy Is Not Null And requestNumber > 29974 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "NewTeamMember"
Forms.Item("MainMenu").Visible = True
Else
MsgBox "You must enter the name of the person requesting access for
the new team member (must be their supervisor), and a project request number
(numbers only).", vbCritical, "Field Left Blank!"
End If

Exit_saveNewTMReq_Click:
Exit Sub

Err_saveNewTMReq_Click:
MsgBox Err.description
Resume Exit_saveNewTMReq_Click

End Sub

Thanks in advance for your help!

 

[RuralGuy]

I have a parent form named "newTeamMember." I have a subform on that form
named "newTM_Request". I have a button on the subform that checks to ensure
all of the required fields are not null, and if so it saves the record and
closes the form. If not, then I would like it to display a message box
advising the user they left one or more fields blank. However, when I click
the button, instead of my message box Access displays one that only says
"object required". Here is the code attached to the Save Record button.

'Button to save record and close form
Private Sub saveNewTMReq_Click()
On Error GoTo Err_saveNewTMReq_Click

If requestedBy Is Not Null And requestNumber > 29974 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "NewTeamMember"
Forms.Item("MainMenu").Visible = True
Else
MsgBox "You must enter the name of the person requesting access for
the new team member (must be their supervisor), and a project request number
(numbers only).", vbCritical, "Field Left Blank!"
End If

Exit_saveNewTMReq_Click:
Exit Sub

Err_saveNewTMReq_Click:
MsgBox Err.description
Resume Exit_saveNewTMReq_Click

End Sub

Thanks in advance for your help!

Instead of: If requestedBy Is Not Null

Try: If Not IsNull(requestedBy)
_______________________________________________
hth - RuralGuy (RG for short)
Please post to the NewsGroup so all may benefit.

 

[Marshall Barton]

I have a parent form named "newTeamMember." I have a subform on that form
named "newTM_Request". I have a button on the subform that checks to ensure
all of the required fields are not null, and if so it saves the record and
closes the form. If not, then I would like it to display a message box
advising the user they left one or more fields blank. However, when I click
the button, instead of my message box Access displays one that only says
"object required". Here is the code attached to the Save Record button.

'Button to save record and close form
Private Sub saveNewTMReq_Click()
On Error GoTo Err_saveNewTMReq_Click

If requestedBy Is Not Null And requestNumber > 29974 Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close acForm, "NewTeamMember"
Forms.Item("MainMenu").Visible = True
Else
MsgBox "You must enter the name of the person requesting access for
the new team member (must be their supervisor), and a project request number
(numbers only).", vbCritical, "Field Left Blank!"
End If

Exit_saveNewTMReq_Click:
Exit Sub

Err_saveNewTMReq_Click:
MsgBox Err.description
Resume Exit_saveNewTMReq_Click

End Sub

The message is probably coming from the IF statement. Is
Not Null is not a valid VBA operator. IS is a valid
operator, but is requires an object as its left operand.
Use the IsNull function instead.

If Not IsNull(requestedBy)l And requestNumber > 29974 Then

I am not a big fan of the obsolete DoMenuItem method. If
all you want to do is save the current record, then use:

If Me.Dirty Then Me.Dirty = False

 

[Guest]

Thank you! That did the trick.

The message is probably coming from the IF statement. Is
Not Null is not a valid VBA operator. IS is a valid
operator, but is requires an object as its left operand.
Use the IsNull function instead.

If Not IsNull(requestedBy)l And requestNumber > 29974 Then

I am not a big fan of the obsolete DoMenuItem method. If
all you want to do is save the current record, then use:

If Me.Dirty Then Me.Dirty = False