Results 1 to 11 of 11
  1. #1
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70

    Help needed with code for message box when no records found


    Here is what I'm trying to do:


    The form "frm_RepairInfo-Asset" is based on a query (RepairInfo-SearchByAsset) that asks the user to enter an asset number. If it's never been sent in for repair, the query will have no results. If it HAS been sent in for repair, the results of this query will be 1 or more.


    I created a second query (RepairInfo-SearchByAsset2) that has one field (TestData), a count of the records in RepairInfo-SearchByAsset query.


    What I need it to do is if TestData is NOT 0, open "frm_RepairInfo-Asset" with the info from the RepairInfo-SearchByAsset query. (The ID key for this is "TicketNum".)


    If TestData IS 0, I need it to go back to the RepairMenu on the Search tab and display a message box that says "No records found." Clicking "Ok" would close the message box and they would be a the screen where they can select another search.


    I have tried two different ways, with no luck. In both cases the "RepairInfo-SearchByAsset2" query returns the correct number of results. Also in both cases, no form ever opens up, and I never get a message box when there are no matching records. AND, after I click the button to run the code once, I am never asked to enter an asset number ... the screen just flickers, but doesn't move to another screen or display the message box.
    _____________________________________


    CODE #1:


    Private Sub Command40_Click()
    DoCmd.Close acForm, "frm_RepairMenu"
    On Error GoTo fErr
    DoCmd.OpenQuery "RepairInfo-SearchByAsset2", acViewNormal, acEdit
    Dim SearchVal As String
    SearchVal = DLookup("TestData", "RepairInfo-SearchByAsset2")
    If SearchVal = "0" Then
    DoCmd.Close acQuery, "RepairInfo-SearchByAsset2"
    DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
    MsgBox "Your search criteria found no matching records."
    Else
    DoCmd.OpenForm "frm_RepairInfo-Asset"
    End If
    Exit Sub
    fErr:
    DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
    End Sub
    _____________________________________


    CODE #2:


    DoCmd.Close acForm, "frm_RepairMenu"
    On Error GoTo fErr
    DoCmd.OpenQuery "RepairInfo-SearchByAsset2", acViewNormal, acEdit


    Dim SearchVal As String
    If SearchVal = 0 Then
    DoCmd.Close acQuery, "RepairInfo-SearchByAsset2"
    DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
    MsgBox "No records found."
    Else
    DoCmd.OpenForm "frm_RepairInfo-Asset"
    End If
    Exit Sub
    fErr:
    DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
    End Sub
    _____________________________________


    Someone who knows VBA can probably spot the problem in seconds, but I'm stymied! Any help you can give will be appreciated.
    Jackie

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    RepairInfo-SearchByAsset2
    It's a bad idea to use the dash is object names. Access could interpret this as RepairInfo minus SearchByAset2

  3. #3
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    Note taken ... but does it see it as a minus when the name is enclosed in quotes?

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    if the recordsource of frm_RepairInfo-Asset is an indicator of a result, I would use DAO and count the records in that form's recordsource. So something like the following in the forms onload

    Dim rs as dao.recordset
    set rs = me.recordsetclone

    if rs.recordcount < 1 then


    msgbox "No records found"

    else
    rs.close
    end if

    set rs = nothing

  5. #5
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Wouldn't it be simpler to use a subform to display the info instead of all the logic required to open a second form? That way they can see for themselves whether there is data or not.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Or - link the main form's record source (or is it a combo/listbox?) to the second query - that way they won't be able to enter assets with no information.

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I usually hide the subform control until after deciding it is useful to display it.

  8. #8
    JackieEVSC is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    70
    To aytee111 ... If I were better at coding, a LOT of things would be easier!

    To: It'sMe ... I'm so happy I could dance!!

    I tweaked your code slightly and it works PERFECTLY!!!! Thank you SOOOOO much!

    Dim rs As dao.Recordset
    Set rs = Me.RecordsetClone
    If rs.RecordCount < 1 Then
    DoCmd.OpenForm "frm_RepairMenu", View:=acNormal, OpenArgs:="SearchRep"
    MsgBox "No records found"
    DoCmd.Close acForm, "frm_RepairInfo-Asset"


    Else
    txtDamageDesc = Replace(Nz(txtDamageDesc, ""), "'", "")
    DoCmd.OpenForm "frm_RepairInfo-Asset"
    rs.Close
    End If


    Set rs = Nothing

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    As long as you understand what this line is cloning and the second line is counting ...
    Set rs = Me.RecordsetClone
    If rs.RecordCount < 1 Then

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by JackieEVSC View Post
    Note taken ... but does it see it as a minus when the name is enclosed in quotes?
    No, but why invite problems??

    Having and using a naming convention helps reduce errors. One convention is to not use spaces, punctuation or special characters (exception is the underscore).

    And if you have fields/forms/queries with a dash in the name and at some point you want to convert your dB to SQL Server/SQL Express/MySQL//Oracle/etc, you will have problems. SQL Server/ SQL Express/MySQL/Oracle/etc don't allow spaces, punctuation or special characters (exception is the underscore) in object names.



    My $0.02......

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    +1 on naming conventions.

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

Similar Threads

  1. VBA File Not Found Message
    By dgutsche in forum Programming
    Replies: 2
    Last Post: 08-25-2014, 10:18 AM
  2. Replies: 2
    Last Post: 01-23-2014, 12:40 PM
  3. Replies: 2
    Last Post: 11-12-2013, 07:06 PM
  4. macrohelp needed - error message if statement
    By dcorleto in forum Queries
    Replies: 8
    Last Post: 07-22-2013, 12:44 PM
  5. Error Message -- Path not found.
    By alansidman in forum Programming
    Replies: 5
    Last Post: 05-28-2013, 01:58 PM

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