Error sequence (1 Viewer)

[Jonny45wakey]

Hi

I have a form "frmDayshift" with a cmd button (Command28) and on the on click event it opens a popup form "frmDayshiftprintchecks" and looks for a matching record by date.

This works fine if there is a matching record but if there isnt, I'd like a message box to notify the user and then close the form but i cant seem to find the right vba.

Would it be better to validate the record on form before update?

Here is my code behind Cmd28

DoCmd.OpenForm "frmDayShiftPrintcheck", , , "[CheckDate]=#" & Me.ShiftDate & "#"



Any support greatly appreciated

Jonny

 

[XPS35]

Before you open the form, you can check whether there are any records that meet the condition. Something like

If DCount("*", "YourTable", "[CheckDate]=#" & Me.ShiftDate & "#) > 0 Then
   DoCmd.OpenForm "frmDayShiftPrintcheck", , , "[CheckDate]=#" & Me.ShiftDate & "#"
Else
   MsgBox "no records"
EndIf

 

[Gasman]

Also, start given your controls meanngful names. Command28 is not going to mean that much to anyone, including you, a few months down the line.
Something along the lines of cmdPrint or cmdPrintChk ?

 

[arnelgp]

also you may try this on your behind cmd8 button:

On Error Resume Next
DoCmd.OpenForm "frmDayShiftPrintcheck", , , "[CheckDate]=#" & Me.ShiftDate & "#"
If Err Then
    Msgbox "No shift for that day!"
End If

and on the Open event of frmDayShiftPrintcheck form:

Private Sub Form_Open(Cancel As Integer)
Cancel = (Me.Recordset.RecordCount < 1)
End Sub

 

[Jonny45wakey]

also you may try this on your behind cmd8 button:

On Error Resume Next
DoCmd.OpenForm "frmDayShiftPrintcheck", , , "[CheckDate]=#" & Me.ShiftDate & "#"
If Err Then
    Msgbox "No shift for that day!"
End If

and on the Open event of frmDayShiftPrintcheck form:

Private Sub Form_Open(Cancel As Integer)
Cancel = (Me.Recordset.RecordCount < 1)
End Sub

Hi Thanks for this, much appreciated.

The form "frmMain" with the CmdButton "cmdPrintCheck" remains open once the form "frmDayshiftPrintChecks" has opened, how would you close this form please?

Thanks
JOnny

 

[Gasman]

I would not, I would open the second form in Dialog mode, so that needs to be closed to get back to the mainform.

I as a user would not want to have to open the mainform again, to process for another date. I would want to go between forms, mainform - second form - mainform -second form etc..

 

[arnelgp]

also you can change the code of Open event of frmDayshiftPrintChecks form:

Private Sub Form_Open(Cancel As Integer)
Cancel = (Me.Recordset.RecordCount < 1)
If Not Cancel Then
    On Error Resume Next
    DoCmd.Close acForm, "frmMenu", acSaveNo
End If
End Sub

 

[nector]

I think you need a text box for Date select just below your onclick command, for example you may call it txtRequiredDate, then you code will be as follows:

Private Cmd28_Onclick()
Dim Cancel as integer
If DLookup("lookupDate", "YourTable", "[ShiftDate] <>#" & Me.txtRequiredDate & "#) Then
Beep
MsgBox "Please note that your date does not exist","The form will now close"
Cancel = True
Exit Sub
Dcmd.close "frmDayShiftPrintcheck"
ElseIf DLookup("lookupDate", "YourTable", "[ShiftDate] = #" & Me.txtRequiredDate & "#) Then

DoCmd.OpenForm "frmDayShiftPrintcheck"

End If

End Sub

 

[Minty]

@nector What does setting a local variable called Cancel achieve in the code above?
What are you checking for in the If ... Then expression?
And why do the lookup again, you already know that it exists if you reach that point in the code

I already know the answer, I just wondered if you did.