Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Posts
    23

    Question Unanswered: Display message on form when no record found..

    Hi,

    I have search this forum and did not found what I really needed. I have a form where user enter account # and it open a form. But when they enter an account that can't be found, it bring up a empty form. If the account # can't be found, I want to say on that form that "account# can't be found" and if the account# can be found, I want it to display on the form. Is there a way to do this. Thanks.

    -Mike

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    I suppose you could do this at least a few ways:

    1) Use the DLookUp function within a IF statement and display the account record only if it exists. If it does not exist, then display a message. For example:

    Code:
    IF Not IsNull(DLookUp("[RecordID]", "myAcntsTable", "[AcntNumFldInTable] = '" & Me.AcntEntryFldInForm & "'")) Then
       DoCmd.OpenForm "myViewRecordForm", , , "[AcntNumFldInTable] = '" & Me.AcntEntryFldInForm & "'"
    Else
       MsgBox "Account Number Not Found.@@" & "There are no Records that match your supplied Account Number.", vbExclamation 
    End If
    2) In the form where the account number is entered and you could open the form that displays the record as Hidden then check the record display form to see if a pertinent field within is Null (empty). If so, we display a message then close the form. If not, we display the form showing the account record. For example:

    Code:
    DoCmd.OpenForm "myViewRecordForm", , , "[AcntNumFldInTable] = '" & Me.AcntEntryFldInForm & "'", , acHidden
    If IsNull([Forms]![myViewRecordForm]![AcntNumField]) Then
       MsgBox "Account Number Not Found.@@" & "There are no Records that match your supplied Account Number.", vbExclamation 
       DoCmd.Close "myViewRecordForm"
       Exit Sub
    Else
       DoCmd.OpenForm "myViewRecordForm", , , "[AcntNumFldInTable] = '" & Me.AcntEntryFldInForm & "'", , acWindowNormal  
    End If
    3) From the OnCurrent event within the Form displaying the record, you could test to see if a pertinent field within is Null (empty). If so then display a message and close the form. For example:

    Code:
    Private Sub Form_Current()
      If IsNull(Me.AcntNumField) Then
          MsgBox "Account Number Not Found.@@" & "There are no Records that match your supplied Account Number.", vbExclamation 
          DoCmd.Close
          Exit Sub
      End If
    End Sub
    The first method is the easiest. There are lots of other ways as well. Hope this helps a little.

  3. #3
    Join Date
    Mar 2002
    Posts
    23

    Re: Display message on form when no record found..

    Originally posted by AccessNewbie
    Hi,

    I have search this forum and did not found what I really needed. I have a form where user enter account # and it open a form. But when they enter an account that can't be found, it bring up a empty form. If the account # can't be found, I want to say on that form that "account# can't be found" and if the account# can be found, I want it to display on the form. Is there a way to do this. Thanks.

    -Mike
    Sorry, but I am really a beginner at this. I actually have a command button which I click and ask for the criteria base on my query then it open the form. I want to display a message within that form if there is no record found otherwise display the record. I try to adjust your code to the best of my knowledge but none have work yet. What am I doing wrong. Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    I will need the code contained within the OnClick event for your command button to see what's up...can you post it?

  5. #5
    Join Date
    Mar 2002
    Posts
    23

    Re: Display message on form when no record found..

    Originally posted by AccessNewbie
    Sorry, but I am really a beginner at this. I actually have a command button which I click and ask for the criteria base on my query then it open the form. I want to display a message within that form if there is no record found otherwise display the record. I try to adjust your code to the best of my knowledge but none have work yet. What am I doing wrong. Thanks.
    Sorry about the late reply. Really busy. I finally got it to work. Thanks for all your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •