Results 1 to 14 of 14
  1. #1
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11

    How to search for subsequent dates in form

    I'm using the code below to search for the RefDate (e.g. order date) using the unbound box FindCCDate
    It works, but to find the next record with the date, I have to reenter (from date selection icon) the date in the unbound box.


    How to I have it continue to search for the RefDate by just using an Enter key without having to reenter the date and then clear the date in FindCCDate after all the records have been searched?

    Code:
    Private Sub FindCCDate_AfterUpdate()
    If (FindCCDate & vbNullString) = vbNullString Then Exit Sub
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    rs.FindNext "[RefDate]= #" & Format(FindCCDate, "mm-dd-yyyy") & "#"
    If rs.NoMatch Then
    MsgBox "Sorry, '" & FindCCDate & "' was not found.", vbOKOnly + vbInformation
    
    
    Else
    Me.Recordset.Bookmark = rs.Bookmark
    End If
    rs.Close
    FindCCDate = Null
    End Sub

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    don't use find, just filter ALL the records. (in a continuous form)
    in the text box afterupdate event:

    Code:
    if isnull(txtFind) then
        me.filterOn = false
    else
        me.filter="[RefDate]='" & txtFind & "'"
        me.filterOn = true
    endif

  3. #3
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    I'm not that good at programming, so where would I put the code you did? Would it just replace the FindFirst line?

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    yes, just replace your code.

  5. #5
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    I replaced all my code and it finds the first instance of the date but does not find the next one.
    and it leaves the date in the box.
    Am I missing a setting somewhere?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    is your form a single or continuous form?

  7. #7
    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 Beeblebrox View Post
    ....... it finds the first instance of the date but does not find the next one..........
    So what do you mean by "Next date"? The next sequential date? ie first time enter 4/4/2016, the next date would be 4/5/2016?
    Or do you mean the next record with the same date? enter 4/4/2016, and looking for another record with 4/4/2016?

  8. #8
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    I'm using a single form
    I want to find another record with the same search date.

    What I want is to be able to enter a search date and then find the first record with that date (e.g. an Order date), then find the next ones until I get to the end of all the records. then delete the date from the search box.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if your form was continuous then with Ranmans suggestion you would see all the records in one scrollable form. As your form is single you need to show the navigation buttons (see form properties) which you can use to go to the next record, last record etc. Or have a button with code which does the same thing

  10. #10
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    I'm getting confused.
    What I want is to be able to enter a search date and then find the first record with that date (e.g. an Order date), then find the next ones, one-at-a-time, until I get to the end of all the records. then have the date in the search box deleted.
    Can you give me all the code I need and which types of controls I need?

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have all the code you need in Ranmans code post#2

    Then if you have a single form, show (and use) the navigation buttons per my post #9

    the only other point is that your control FindCCDate should be unbound (i.e. the control source should be blank)

  12. #12
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    Almost got it. All I need now is for the search field to be cleared after the last record searched.
    What happens now is that the navigation button goes thru all the records with the date in in search box and then goes to a blank (New) form.
    But I also have a navigation button for sequentially going thru the records. Since the date in the search box is still there, it continues to find only the records with the search date in it.
    My original code would clear the search box after completing the search.
    Also I'd like it to tell me that there are no more records with the date I'm searching rather than going to a blank form.

  13. #13
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    seems like an awful lot of effort to duplicate standard access functionality, but I presume you have your reasons.

    to not see the blank record, modify the form allow additions property to false
    to manipulate the date field and provide a message, google 'access vba currentrecord' and 'access form recordcount' - a simple comparison between the two will tell you when you are on the last record. Put the code in your navigation button

  14. #14
    Beeblebrox is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2016
    Posts
    11
    Looks like I was trying to use conflicting commands on the same form, so the original solution in #2 above does it.

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

Similar Threads

  1. Query Search Form - Searching Between Two Dates
    By ryanmce92 in forum Queries
    Replies: 7
    Last Post: 06-05-2015, 01:46 PM
  2. Search form not working corerctly with dates
    By mesba713 in forum Access
    Replies: 2
    Last Post: 03-27-2015, 06:25 AM
  3. Replies: 18
    Last Post: 01-31-2013, 01:18 PM
  4. Replies: 7
    Last Post: 05-02-2012, 08:04 AM
  5. Finding subsequent codes
    By Rixxe in forum Queries
    Replies: 8
    Last Post: 09-15-2010, 02:44 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