If no records, form opens on blank screen. How to display msgbox instead?

[MultiplePOV]

My database has a switchboard page with a combo box that provides link criteria to several buttons used to open different forms. The queries behind some of the forms have record sets more restricted than the list in the combo box, i.e., for some choices in the combo box, users are expected to fill out other forms first. If the user picks a choice from the combo box without a corresponding record in the form opened by a particular button, the form opens but without any fields. Is there a way to cancel opening the form and display MsgBox instructions instead?

Private Sub btnEnterDetails_Click()
Dim stDocName As String
Dim stLinkCriteria As String


If IsNull(Me.txtItemToFind) Then
Strmessage = MsgBox("Please enter item to find.", vbCritical, "Item to find needed.")
Me.txtItemToFind.SetFocus

Else
stDocName = "frmEnterDetails"

stLinkCriteria = "[fldItemID]=" & Me![txtItemToFind]
DoCmd.OpenForm stDocName, , , stLinkCriteria


End If


Exit_btnEnterDetails_Click:
Exit Sub


Err_btnEnterDetails_Click:
MsgBox Err.Description
Resume Exit_btnEnterDetails_Click




Just want to add that even if nobody knows a way to do this, just asking this question has been helpful. I hadn't identified the potential disparity between the combo box record set and the form's query record set as being the issue before. If I can't just add a MsgBox explanation to users to go back and complete the "previous" form, I might be able to add a second step that limits the combo box record set when accessing the forms that have pre-requisites.

[Micron]

Seems to me the answer would be to close the second form if it has no records and present a message saying whatever you want about that. How depends on what you do or don't want to see. To not see the form, open it invisible, count its records and if count = 0, close the form otherwise make visible. Or just let the form open, count, message if 0 then close the form after user dismisses the message box. The count would be Me.Recordset.RecordCount.

EDIT - please post indented code within code tags (# on forum toolbar) to make it easier to read. I suspect your modules don't have Option Explicit at the top - you did not declare strMessage (that's the clue)

[Missinglinq]

I think rather than cancel opening the form you need to simply not open it at all if no corresponding record is present. You can use the DCount() Function against the RecordSource of the Form to be opened to do this:

Private Sub btnEnterDetails_Click() 
Dim stDocName As String
Dim stLinkCriteria As String


If IsNull(Me.txtItemToFind) Then
  Strmessage = MsgBox("Please enter item to find.", vbCritical, "Item to find needed.")
  Me.txtItemToFind.SetFocus

Else
  stDocName = "frmEnterDetails"

  If DCount ("*", "RecordSource for frmEnterDetails goes here", "[fldItemID]=" & Me![txtItemToFind] > 0 Then 
    stLinkCriteria = "[fldItemID]=" & Me![txtItemToFind]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  Else
    'Msgbox goes here
  End If 
End If


Exit_btnEnterDetails_Click:
Exit Sub


Err_btnEnterDetails_Click:
MsgBox Err.Description
Resume Exit_btnEnterDetails_Click

End Sub

This assumes that [fldItemID] is numeric, as your code would suggest.

It's early in the morning and my meds haven't kicked in, yet, but I think the above will do what you want.

Linq ;0)>

[MultiplePOV]

Seems to me the answer would be to close the second form if it has no records and present a message saying whatever you want about that. How depends on what you do or don't want to see. To not see the form, open it invisible, count its records and if count = 0, close the form otherwise make visible. Or just let the form open, count, message if 0 then close the form after user dismisses the message box. The count would be Me.Recordset.RecordCount.

EDIT - please post indented code within code tags (# on forum toolbar) to make it easier to read. I suspect your modules don't have Option Explicit at the top - you did not declare strMessage (that's the clue)

Hah. Cool solution. In typing out my question, I realized my problem was the mismatch between dropdown and records. I solved by adding a second dropdown with the same limits as the form.

But I used your visible, not visible solution for a second problem--a subform that showed as blank if it had no corresponding records. Now if the field that links the subform to the main form is null, then instructions placed on top of the subform are visible as well as a button to run a query that creates a record for the subform and makes the instructions invisible again.

Private Sub Form_Open(Cancel As Integer)


 If IsNull(Me.fldThreadType) Then
 
    Me.lblwarning.Visible = True
    Me.btnCreateThread.SetFocus


End If
End Sub

And thanks for pointing out the code formatting feature on this forum. I'll remember.

[MultiplePOV]

I think rather than cancel opening the form you need to simply not open it at all if no corresponding record is present. You can use the DCount() Function against the RecordSource of the Form to be opened to do this:

Private Sub btnEnterDetails_Click() 
Dim stDocName As String
Dim stLinkCriteria As String


If IsNull(Me.txtItemToFind) Then
  Strmessage = MsgBox("Please enter item to find.", vbCritical, "Item to find needed.")
  Me.txtItemToFind.SetFocus

Else
  stDocName = "frmEnterDetails"

  If DCount ("*", "RecordSource for frmEnterDetails goes here", "[fldItemID]=" & Me![txtItemToFind] > 0 Then 
    stLinkCriteria = "[fldItemID]=" & Me![txtItemToFind]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
  Else
    'Msgbox goes here
  End If 
End If


Exit_btnEnterDetails_Click:
Exit Sub


Err_btnEnterDetails_Click:
MsgBox Err.Description
Resume Exit_btnEnterDetails_Click

End Sub

This assumes that [fldItemID] is numeric, as your code would suggest.

It's early in the morning and my meds haven't kicked in, yet, but I think the above will do what you want.

Linq ;0)>

Oh, super cool. I didn't see this solution when I posted. I'll try it. Two dropdowns works but one would be more elegant. I'll probably need a day to get back to this.

[Micron]

FWIW, I like the idea of checking the record count then deciding whether or not to open the form.
If you do happen to post code and forget the tags, you can always edit your post and add them. I don't know what the time limit is for editing a post though.