Results 1 to 5 of 5
  1. #1
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24

    MsgBox for no records found

    Hi guys,

    I have a form which can be opened vior a button on a switchboard, when opened it is read only so all the fields are not enabled. I have just created a button on the form to find and show a record, the code is as follows:-

    Private Sub cmdGotoRecord_Click()

    Dim strReport As String
    strReport = InputBox("Enter Report No")

    Me.ReportNo.Enabled = True
    Me.ReportNo.SetFocus
    DoCmd.FindRecord strReport
    Me.ReportNo.Enabled = False

    End Sub

    The search is on the ReportNo field. The code enables the field, sets it as the focus then carries out the search, then sets the enable to false again.

    The code works welland finds the records and displays them, the problem is, it is ok for records that exist, but if I put in a value for a record that does not exist nothing happens.

    What code do I need to add to the existing or replace it with to show a MsgBox with the words like "No Record Found, Please Try Again".



    Please could someone help.

    I am teaching myself VBA, about half way through the VBA for Dummys, but not quite there yet.

    Could you also recoment some more training books or information.

    Thanks

    Andy

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hi Andy,

    Could you also recoment some more training books or information
    There are many excelent places for info. Check out Crystal's web page:

    http://www.accessmvp.com/Strive4Peace/



    Try this:
    Code:
     
    Private Sub cmdGotoRecord_Click()
       Dim strReport As String
       Dim tmp As String
     
       strReport = InputBox("Enter Report No")
     
       'get current ReportNo
       tmp = Me.ReportNo
     
       Me.ReportNo.Enabled = True
       Me.ReportNo.SetFocus
       DoCmd.FindRecord strReport
     
       ' move to some other control to be able to be able to set
       ' ReportNo Enabled property to false. Change to whatever
       ' control you want
       Me.cmdGotoRecord.SetFocus
     
       Me.ReportNo.Enabled = False
     
       'check if the report no. was found 
       If (tmp = Me.ReportNo) And (Me.ReportNo <> strReport) Then
          MsgBox "Report number " & strReport & " Not found, Please Try Again"
       End If
    End Sub

  3. #3
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    Thanks Steve,

    The code as you have written it works great. im not sure of one thing, the part of the code where you have put

    ' move to some other control to be able to be able to set
    ' ReportNo Enabled property to false. Change to whatever
    ' control you want
    Me.cmdGotoRecord.SetFocus

    Do I need to enter something myself?

    Also the ReportNo field is a number field, can I set an error check to see if the operator enters letters or anything other than numbers and return another measage box.

    Can I also get the initial ImputBox to re-appear after the error.

    I know you will say this is basic code, but im still learning. The problem I have is that I am learning individual parts of code but have trouble knowing how to piece it together?? Does it get easier???

    Thanks Again

    Andy

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Andy,

    ' move to some other control to be able to be able to set
    ' ReportNo Enabled property to false. Change to whatever
    ' control you want
    Me.cmdGotoRecord.SetFocus

    Do I need to enter something myself?
    You cannot set the Enabled property of a control to FALSE if it has the focus. So I set the focus to the button - you can set the focus to any control except disabled controls or the control you are trying to disable.



    Do you *have* to use an Input box? Have you thought about using a combo box? How many unique report numbers are there?

    In a *copy* of your database, open the form that has the button "cmdGotoRecord". Next to it, add a combo box.
    Name it "cboSelectRptNo" (no quotes).

    Set the Row Source property to:

    SELECT DISTINCT YourTableName.ReportNo FROM YourTableName ORDER BY YourTableName.ReportNo;


    Set the "Limit To List" and :Auto Expand" properties to YES.

    Next, change the button code to:

    Code:
    Private Sub cmdGotoRecord_Click()
       On Error GoTo Err_cmdGotoRecord_Click
     
       Dim strReport As Integer
       Dim tmp As Integer
     
       strReport = Me.cboSelectRptNo
     
       'abort if number not selected
       If Len(Trim(strReport)) = 0 Then
          MsgBox "Report number Not Selected"
       End If
     
       'get current ReportNo
       tmp = Me.ReportNo
       Me.ReportNo.Enabled = True
       Me.ReportNo.SetFocus
       DoCmd.FindRecord strReport
       ' move to some other control to
       ' be able to be able to set
       ' ReportNo Enabled to false
       Me.cmdGotoRecord.SetFocus
       Me.ReportNo.Enabled = False
       'check if the report no. was found and
       If (tmp = Me.ReportNo) And (Me.ReportNo <> strReport) Then
          MsgBox "Report number " & strReport & " Not found"
       End If
     
    Exit_cmdGotoRecord_Click:
       Exit Sub
    Err_cmdGotoRecord_Click:
       MsgBox Err.Description
       Resume Exit_cmdGotoRecord_Click
    End Sub


    The combo box automatically validates the entry.

    Will this work for you?

  5. #5
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    Steve,

    Tried your latest idea and the code, it works great. Thanks for all your help,, I will hopefully get there aventually. Could do with attending a training course I think.

    Cheers again,

    Andy

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Custom MsgBox
    By roccoIT in forum Programming
    Replies: 3
    Last Post: 07-06-2010, 10:43 AM
  2. Replies: 1
    Last Post: 04-29-2010, 05:22 PM
  3. Yes No Cancel MsgBox
    By Rick West in forum Forms
    Replies: 5
    Last Post: 04-14-2010, 08:57 AM
  4. Number of Records Found by a Query?
    By Xiaoding in forum Queries
    Replies: 3
    Last Post: 03-05-2010, 03:34 PM
  5. Return 0 if no records found
    By skwilliamson in forum Programming
    Replies: 1
    Last Post: 12-01-2009, 10:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums