Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Jun 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have created an Access database that uses a number of parameter queries. When a parameter query runs and returns no results (ie, someone types in a parameter for which there is no data), I would like a messagebox to pop up containing the words, "Sorry! No data was found." I have created a macro that should do this, but I have repeatedly received error messages when I try to use it. I think that the SQL statement is not formatted correctly, but I don't know how to fix it. Any suggestions? See code below.

    Dim x as String, y as Integer
    x = "((([Agency Contracts Table].[Agency Name]) Like '*' & [Agency Name:] & '*'))"
    y = DCount("[Agency Name]","Agency Contracts Table", x)
    If y = 0 then
    Msgbox "Sorry! No data was found."
    End if

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Elizabeth,

    Try this: x="(([Agency Name] Like '*') and ([Agency Name:] Like '*'))"

    I assume the the second one with the colon is a different field?
    Also you don't need to repeat the table name in the where clause when it is the same table in the previous argument.

    What I don't see is where the parameter substitution is taking place. Could you elaborate?

    Maybe what you want is: x="(([Agency Name] Like " & parameter & ") and ([Agency Name:] Like " & parameter & "))"
    Where parameter is a VBA variable containing the Agency Name you are searching for.
    You might also want to replace the AND with an OR if you want a match in either field vs both.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Elizabeth19 View Post
    Dim x as String, y as Integer
    x = "((([Agency Contracts Table].[Agency Name]) Like '*' & [Agency Name:] & '*'))"
    y = DCount("[Agency Name]","Agency Contracts Table", x)
    If y = 0 then
    Msgbox "Sorry! No data was found."
    End if
    So Agency Name: is a parameter, and you are using the same parameter for the query as for the Dcount?
    The problem with your SQL is the parameters need to be outside the string, so the value of the parameter is used.
    something like, the following. Notice the extra two "".
    x = "((([Agency Contracts Table].[Agency Name]) Like '*' " & [Agency Name:] & " '*'))"

    But I am not sure if this will recognise the parameter as the same as the query one?

    If the name of your query is qrysomequery, you could use

    Code:
    if Dcount("*","qrysomequery") > 0 then
    docmd.openquery "qrysomequery"
    else
    Msgbox "Sorry! No data was found."
    End if
    By counting records in the query, you avoid having to use the parameter again.
    Regards
    John



  4. #4
    New Lounger
    Join Date
    Jun 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    Thanks for your responses! However, I am still receiving error messages. Basically, I would like to refer to the number of results that the query produces based upon the parameter that is entered. Is there a way to use VBA to count query results? That might be easier.

    Thanks,

    Elizabeth

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Elizabeth,

    Attached is a small database with working VBA for your problem.
    I've used a simple where clause but you should be able to extrapolate.

    To run, unzip database, use F11 to get into the VBE then place cursor in the routine and hit F5. You probably know this but better safe than sorry.
    USE: NSA as the agency as it is not in the table. You can use DOD to test the case where there is a match.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    New Lounger
    Join Date
    Jun 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for your help! However, I notice that the code you sent shows that there is no data that matches "D" as a parameter entry, even though "DOD" exists in the Contracts table. Does that mean that the wildcard characters are not working or am I doing something wrong?

    Thanks again!!!

    Elizabeth

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Elizabeth,

    Sorry, I didn't include the wildcard in the code.
    Here's the line you need to fix: zWhere = "[Agency] Like " & Chr(34) & zParm & "*" & Chr(34)

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    New Lounger
    Join Date
    Jun 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much!!!

    I just have one more question: I am using the code below to attach this macro to a button and to run the query if iCnt > 0. However, because it is a parameter query that requires a parameter value, I find that I have to enter the parameter entry again (once in the first input box and again when the query is run). Is there any way I can avoid this?

    Thanks again!

    Elizabeth

    Private Sub Command_Agency_Contracts_by_Agency_Click()

    Dim zParm As String
    Dim zWhere As String
    Dim iCnt As Integer

    zParm = InputBox("Agency Name:", "Query Parameter Entry")
    zWhere = "[Agency Name] Like " & Chr(34) & "*" & zParm & "*" & Chr(34)
    iCnt = DCount("[Agency Name]", "Agency Contracts Table", zWhere)

    If iCnt = 0 Then
    MsgBox "Sorry! No data was found.", vbOKOnly + vbCritical, _
    "Query Results"
    End If
    If iCnt > 0 Then
    DoCmd.OpenQuery "Agency Contracts by Agency"
    End If

    End Sub

    Here is the SQL statement used for the query called "Agency Contracts by Agency":

    SELECT [Agency Contracts Table].[Agency Name], [Agency Contracts Table].Region, [Agency Contracts Table].Percentage, [Agency Contracts Table].[Date Signed], [Agency Contracts Table].Link
    FROM [Agency Contracts Table]
    WHERE ((([Agency Contracts Table].[Agency Name]) Like "*" & [Agency Name:] & "*"))
    ORDER BY [Agency Contracts Table].[Agency Name];

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I would suggest putting the [Agency Name:] Parameter onto the same form as the button.
    Then both queries can look at the same parameter

    So stick an Unbound Control onto the form with the button.
    You can name it [Enter Agency Name]
    Personally I would not include : in the name of anything.

    Then Suppose the Form is called frmAgencyContacts

    Your Button Code will become

    Code:
    Private Sub Command_Agency_Contracts_by_Agency_Click()
    
    Dim zParm As String
    Dim zWhere As String
    Dim iCnt As Integer
    
    zParm = Me![Enter Agency Name] & ""
    
    zWhere = "[Agency Name] Like " & Chr(34) & "*" & zParm & "*" & Chr(34)
    iCnt = DCount("[Agency Name]", "Agency Contracts Table", zWhere)
    
    If iCnt = 0 Then
          MsgBox "Sorry! No data was found for Agency Name Like " & zParm, vbCritical, "Query Results"
    Else
         DoCmd.OpenQuery "Agency Contracts by Agency"
    End If
    
    End Sub
    And the SQL for the Agency Contacts By Agency Query Become

    Code:
    SELECT [Agency Name], [Region], [Percentage], [Date Signed],[Link]  
    FROM [Agency Contracts Table]  
    WHERE [Agency Name] Like "*" & Forms![frmAgencyContacts]![Enter Agency Name] & "*"   
    ORDER BY [Agency Name];
    So you fill the Parameter box in once on the Form
    Then hit the button!
    Andrew

  10. #10
    New Lounger
    Join Date
    Jun 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    When I tried this, I got the message: Runtime Error 2465 - Can't find the field "Enter Agency Name" referred to in your expression. Am I doing something wrong?

    Elizabeth

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Have you got an unbound control called [Enter Agency Name] on the same form as the button?

    Might also be worth putting the Word Forms in []

    [Forms]![frmAgencyContacts]![Enter Agency Name]

    Can you post a sanitized copy of the database with the form on it?
    Andrew

  12. #12
    New Lounger
    Join Date
    Jun 2010
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    Thanks for your responses! I am not sure about putting an unbound control on the form with the button because there are other buttons on the same form. Will that affect the other buttons as well?

    Thanks again,

    Elizabeth

  13. #13
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    It shouldn't effect them at all. Just the one that is referenced from its Button and query.
    Andrew

  14. #14
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    Belfast, Ireland
    Posts
    339
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I tried using the VB Code supplied in the DB above, and it works insofar as I can get the Dialog Box to appear if there are no results. If it has results, instead of opening the form (which is based on a the query qryContracts and showing the results, it opens the form and shows every record. Can anyone point out what I'm doing wrong, please?

    Code:
    Dim zParm  As String
      Dim zWhere As String
      Dim iCnt   As Integer
      Dim stDocName As String
    
        stDocName = "frmContracts"
    
      zParm = InputBox("Enter Agency Name", "Search Dialog")
      zWhere = "[Agency] Like " & Chr(34) & zParm & "*" & Chr(34)
      iCnt = DCount("[Agency]", "qryContracts", zWhere)
      If iCnt = 0 Then
        MsgBox "Sorry! No data was found.", vbOKOnly + vbCritical, _
               "Query Results"
               Else: DoCmd.OpenForm stDocName
      End If
    Sorry, not very often I work with VBA and I then forget it all when I have to go back in.

    Many thanks

    Brian

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Try replacing

    DoCmd.OpenForm stDocName whcih just opens the form with no reference to the Where condition with

    DoCmd.OpenForm stDocName, , , zWhere
    Regards
    John



Posting Permissions

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