Results 1 to 12 of 12
  1. #1
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88

    Cant get Total Records Found

    Cant get total records found after search...I get all records total. thnx.

    '---------------------------------------------
    ' 07. Search button
    '---------------------------------------------
    Private Sub cmdSearch_Click()

    Dim lRecCount As String
    Dim strSearch As String
    Dim strFind As String
    'check if a keyword has been entered or not
    If IsNull(Me.txtSearch) Or Me.txtSearch = "" Then
    MsgBox "Please type in your search keyword.", vbInformation, "Keyword Needed!"
    Me.txtSearch.BackColor = vbYellow
    Me.txtSearch.SetFocus

    'if record not found then...
    Me.txtSearch.BackColor = vbRed
    DoCmd.CancelEvent
    MsgBox "Record not found!", vbExclamation, "Oh Nooooo!"
    'resets form
    Me.RecordSource = "SELECT * FROM tblQuestion"
    Me.txtSearch.BackColor = vbWhite
    Me.txtSearch = ""


    Me.txtSearch.SetFocus
    Else
    strSearch = Me.txtSearch.Value
    strFind = "SELECT * FROM tblQuestion WHERE ((Question Like ""*" & strSearch & "*"") OR (Possible1 Like ""*" & strSearch & "*"") OR (Possible2 Like ""*" & strSearch & "*"") OR (Possible3 Like ""*" & strSearch & "*"") OR (Possible4 Like ""*" & strSearch & "*""))"
    Me.RecordSource = strFind
    Me.txtSearch.BackColor = vbGreen

    lRecCount = DCount("SELECT * FROM tblQuestion WHERE (Question Like ""*" & strSearch & "*""))
    MsgBox "" & lRecCount & " records found!"
    End If
    End Sub

  2. #2
    Join Date
    Mar 2019
    Location
    Netherlands
    Posts
    9
    I'n not completely sure if that would solve your problem, but the way you use the DCount function would be the following:

    DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

    Where the first parameter would typically be the unique key, the second the table and the third the criteria.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I think
    IRecCount = me.recordsetclone.recordcount
    would work.
    Should show the current number of records available to the form.

  4. #4
    Join Date
    Mar 2019
    Location
    Netherlands
    Posts
    9
    It sometimes helps if you first jumb to the last record and then request the recordcount. Otherwise it might just return 1!

  5. #5
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88

    Arrow

    Quote Originally Posted by EZ Access Gideon View Post
    I'n not completely sure if that would solve your problem, but the way you use the DCount function would be the following:

    DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")

    Where the first parameter would typically be the unique key, the second the table and the third the criteria.

    yes i tried something like that...still cannot get correct count of records found...not sure what else to do.
    lRecCount = DCount("[ID]", "tblQuestion", "[Question] = 'Count(*)'")
    MsgBox "" & lRecCount & " records found!"

    Ive even tried this one to see if I get closer to a result.
    lRecCount = DCount("lRecCount")

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That expression is completely wrong. Don't use Count in the criteria filter for DCount.
    Not sure you need criteria.
    Try DCount("ID", "tblQuestion") or DCount("*","tblQuestion")
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88
    Quote Originally Posted by isladogs View Post
    That expression is completely wrong. Don't use Count in the criteria filter for DCount.
    Not sure you need criteria.
    Try DCount("ID", "tblQuestion") or DCount("*","tblQuestion")
    I have tried that one and I get a total of all not the one's searched. I've also tried and still the same:
    lRecCount = DCount("Question", "tblQuestion", txtSearch)

    lRecCount = DCount("[ID]", "tblQuestion", "[Question]='" & "'")

    lRecCount = Me.RecordsetClone.RecordCount

  8. #8
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88
    This one only gives be a record of 2 no matter how many records are found...
    lRecCount = Me.RecordsetClone.RecordCount

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    some of your code doesn't make sense. If control is null upon button click, set control color, then set focus to it, then change its color (again) then reset the form record source (automatically wiping out whatever the records are). But if it's not null or "" then set source to strFind where ""*" & strSearch & "*"" part looks suspect. Did you happen to output source string in immediate window to check? Does it result in a filter clause that you can verify the record count against?

    Would be interesting to follow the code execution and verify conditions - if you could post sample db.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your filter criteria are still being used incorrectly

    lRecCount = DCount("Question", "tblQuestion", txtSearch)
    You need to set the appropriate field equal to txtSearch for this to be useable.


    lRecCount = DCount("[ID]", "tblQuestion", "[Question]='" & "'")
    The above filters for an empty string in the question field

    Note that you onl need the [] where the field name contains a space or special character OR is a reserved word in Access.


    lRecCount = Me.RecordsetClone.RecordCount
    Impossible to say what this is counting with the info you've supplied but you've said the answer is wrong anyway
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88
    Quote Originally Posted by isladogs View Post
    Your filter criteria are still being used incorrectly

    lRecCount = DCount("Question", "tblQuestion", txtSearch)
    You need to set the appropriate field equal to txtSearch for this to be useable.


    lRecCount = DCount("[ID]", "tblQuestion", "[Question]='" & "'")
    The above filters for an empty string in the question field

    Note that you onl need the [] where the field name contains a space or special character OR is a reserved word in Access.


    lRecCount = Me.RecordsetClone.RecordCount
    Impossible to say what this is counting with the info you've supplied but you've said the answer is wrong anyway
    i just cant figure it out so im going to close this...thanks to all.

  12. #12
    ortizimo is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Jun 2017
    Location
    FL
    Posts
    88
    found a fix until i figure it out...
    on a textbox =Count(*) named RecFound for display

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

Similar Threads

  1. No records found error message
    By Niko in forum Programming
    Replies: 4
    Last Post: 10-16-2017, 10:40 AM
  2. Find records not found in INNER Join
    By ItsMe in forum Queries
    Replies: 4
    Last Post: 10-30-2013, 01:28 PM
  3. Need some help with a No Records Found query
    By kilosierra71 in forum Queries
    Replies: 1
    Last Post: 08-19-2011, 02:56 PM
  4. MsgBox for no records found
    By Lxmanager in forum Forms
    Replies: 4
    Last Post: 11-02-2010, 12:31 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