"You must enter a value"

[Design by Sue]

I have a mainform and with a subform. The user makes a selection on the mainform of the pack they want to assign items to. Then the tab to the subform where they select items to add to the pack. When the user returns to the mainform, if the required fields in the subform are not completed, a message box pops up that states "You must enter a value..." because one of the required fields has been left blank. I have added the following code to the Before Update event of the subform and it seems to work correctly ... almost...If the user selects Yes, the subform is cleared and the user can select the new pack on the mainform. If the user selects No, he is returned to the subform to complete the record, but still gets the "You must enter a value" access message. Is there anyway to intercept that message so it doesn't appear, other than to make the fields not required in the table? (I believe I have coded the buttons on the form to avoid saving a record without entering all required fields, but I like the security of the required fields in case I have missed something in the coding)

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Not IsNull(Me.Description) Or Not IsNull(Me.Quantity) Then
If MsgBox("You have not completed your item entry." & vbCrLf & "Do you want to continue and delete this partial record?", vbQuestion + vbYesNo, "Continue Without Saving?") = vbYes Then

DoCmd.SetWarnings False

DoCmd.RunCommand acCmdUndo
DoCmd.SetWarnings True

End If
End If

End Sub

Sue

 

[Design by Sue]

Just found out I can't use the beforeupdate event because it breaks the buttons I have on the subform - so now I am back to needing help on bypassing the "you must enter a value" message completely.

Sue

 

[Design by Sue]

It took some work but by adding coding to different areas and completing saves I was able to make this work. If others have a similar problem, think of ways to avoid getting to the point where this message appears.

 

[Alansidman]

I do it slightly differently. I have a combo box to select queries to run. It is triggered by a command button. If the combo box is empty, then they get a message and the sub exits allowing the user to input a selection.

Private Sub Command3_Click()
    Dim ReportName As String
    ReportName = Me.Combo1.Column(1)
    
    If IsNull(Me.Combo1) Then
    MsgBox "Select a report to view and try again."
    Exit Sub
    Else
    If Me.Combo1.Value = "" Then
    MsgBox "Select a report to view and try again."
    Exit Sub
    End If
    End If
               
    DoCmd.OpenQuery ReportName, acViewNormal, acEdit
    
End Sub

Just another way to skin the cat.

Alan