Access - Showing a message when there is no data available 2

[philthepowerhouse]

Hi there,

I have a form with a button on it. This button takes the information form a combo box also on the form and retrieves the relevant journal details. The user is then presented with the appropriate details of the journal through a form not a report.

In the combo box's list of Journal titles there are some titles that do not have data for them yet probably because they haven't arrived yet. At the moment these journals in question bring up a blank form because there is no data for them as yet.

I would like a message box to appear telling the user this situation. Below is the code behind the button which is linked to the combo box.
...................................
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.Combo2) Then
MsgBox "You Must Select a Journal Title before proceding", vbCritical, "No ItemSelected"
Me.Combo2.SetFocus
Exit Sub
Else

stDocName = "Central Sources Journal Entry Form 2004 2"

stLinkCriteria = "[Title]=" & "'" & Me![Combo2] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
.........................................
Let me know if you need anymore info!

Thanks in advance,

Powerhouse.

 

[ZmrAbdulla]

See
Keep form from opening if no data?
thread702-1093610


________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[philthepowerhouse]

ZmrAbdulla,

Thank you for your reply, I have looked at this thread and have inserted the code but I'm not sure its in the right place. Access is also telling me that my form is not based on a table/query so the recordsetclone function is not valid.

I have included the code of the button where I inserted your code below:
................................
Private Sub Command7_Click()
On Error GoTo Err_Command7_Click

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There is no data available for this journal at this moment in time"
DoCmd.Close
End If

Dim stDocName As String
Dim stLinkCriteria As String

If IsNull(Me.Combo2) Then
MsgBox "You Must Select a Journal Title before proceding", vbCritical, "No ItemSelected"
Me.Combo2.SetFocus
Exit Sub
Else

stDocName = "Central Sources Journal Entry Form 2004 2"

stLinkCriteria = "[Title]=" & "'" & Me![Combo2] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_Command7_Click:
Exit Sub

Err_Command7_Click:
MsgBox Err.Description
Resume Exit_Command7_Click


End Sub
.....................................

Many thanks,

Kindest Regards,

Powerhouse.

 

[ZmrAbdulla]

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "There is no data available for this journal at this moment in time"
DoCmd.Close
End If

Should go in OnOpen/Load Event of the second form that you are trying to open. See that thread again

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[philthepowerhouse]

ZmrAbdulla,

Sorry to keep bothering you, I have inserted the code in the right place now! It works fine but it doesn't seem to close the form, the message box appears just fine though - thanks.

Any ideas?

Many Thanks,

Powerhouse

 

[ZmrAbdulla]

What is the RecordSource(Table/QueryName) of the Second form?
Also you can try the "DCount" method posted at the end of the other thread.

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[philthepowerhouse]

Record Source of second form: Central Sources 2004.

I didnt understand the DCOUNT method sorry!

Many Thanks

Powerhouse

 

[ZmrAbdulla]

Code for the Command Button

Private Sub Command7_Click()
    On Error GoTo Err_Command7_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    If IsNull(Me.Combo2) Then
        MsgBox "You Must Select a Journal Title before proceding", vbCritical, "No ItemSelected"
        Me.Combo2.SetFocus
        Exit Sub
    Else
        stDocName = "Central Sources Journal Entry Form 2004 2"

        stLinkCriteria = "[Title]=" & "'" & Me![Combo2] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
Exit_Command7_Click:
    Exit Sub

Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

End Sub

Code for the Second Form (Central Sources Journal Entry Form 2004 2)

Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "No Record to Show, Cancelled Operation"
        Cancel = True
    End If
End Sub

For more about Dcount have a search in the VBA Help file.

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[OnTheFly]

Powerhouse,

If your second form is set so that you can not add records the easiest way to accomplish this is to put this in the Form Load of your second form "Central Sources Journal Entry Form 2004 2":

If Me.CurrentRecord=0 Then
MsgBox "There are no details for this item at this time.",vbInformation, "No Details Available"
Docmd.Close acForm, Me.Name, acSaveNo
End If



Hope this helps.

OnTheFly

 

[philthepowerhouse]

Thank you to you both this has been most helpful,

Zameer Abdulla,

I have inserted your code and it works but is there anyway to get rid of the resulting message box which appears afterwards telling me that I have cancelled and OpenForm action?

Thank you so much for your help though!

 

[ZmrAbdulla]

Private Sub Command7_Click()
    On Error GoTo Err_Command7_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    If IsNull(Me.Combo2) Then
        MsgBox "You Must Select a Journal Title before proceding", vbCritical, "No ItemSelected"
        Me.Combo2.SetFocus
        Exit Sub
    Else
        stDocName = "Central Sources Journal Entry Form 2004 2"

        stLinkCriteria = "[Title]=" & "'" & Me![Combo2] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
Exit_Command7_Click:
    Exit Sub

Err_Command7_Click:
[b]    If Err = 2501 Then Err.Clear
[/b]    Resume Exit_Command7_Click

End Sub

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[philthepowerhouse]

Thank you to you both my problems are sorted and its all thanks to you!

Many thanks,

Kindest Regards,

Powerhouse

 

[ZmrAbdulla]

Glad it helped..

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.