Results 1 to 7 of 7
  1. #1
    nickmit11 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    9

    Question Searching for the next record based on a text box value in a form (VBA)

    I am trying to set up a search bar that takes the value from my textbox and jumps from one matching record, to the next record, and so on. The data that I am using repeats numbers, not a lot of them but enough to make it relevant. So far I have been able to build a code that when I enter my number in a textbox and then click a command button, it jumps to the first instance that this number is found in my data and displays it on the form. I have not been able to get it to then jump to the NEXT instance of this number. I am not a great coder and I have been playing around with it, but my code that is currently able to find the first record is listed below, any help would be greatly appreciated!!

    [code]
    Private Sub cmdSearch_Click()
    Dim strLocationRef As String
    Dim strSearch As String

    'Check txtcmdSearch for Null value or Nill Entry first.


    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
    MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
    Me![txtSearch].SetFocus
    Exit Sub
    End If
    '-------------------------------------------------------------------------------


    'Performs the search using value entered into cmdSearch
    'and evaluates this against values in Loc


    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Loc")
    DoCmd.FindRecord Me!txtSearch

    Loc.SetFocus
    strLocationRef = Loc.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text

    'If matching record found sets focus in Loc

    If strLocationRef = strSearch Then
    Loc.SetFocus

    'If value not found sets focus back to cmdSearch and shows msgbox
    Else
    MsgBox "Match Not Found For: " & strSearch & " -Please Try Again.", _
    , "Invalid Search Criterion!"
    txtSearch.SetFocus
    End If


    End Sub
    [code]

    Thanks Again!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your code will only repeat the same search unless the criteria changes. You've not said what would cause the second search (looking for the next record based on the same criteria) to run. The simplest solution might be to create a recordset based on the criteria, then cycle through this subset.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    nickmit11 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    9
    Do you have any idea how I would go about doing that? I made this code mainly by pulling from others on the interwebs.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Problem is you're not providing enough info, so all I can do at this time is point you in the general direction. Google ms access create recordset and start there. You can create these based on sql statements, queries, tables, form recordset clones - anything that provides a set of records, and I don't know which you have available. Nor do I know how you want to see these records; one at a time? Subform datasheet? Basically, after you've ensured necessary controls have values (as you've already done) you'd open a recordset, which is a set of records in memory. They're not actually visible, so you have to either pass their values to controls, or assign the recordset as the record source for your form (you can do other things with them, but they're beyond the scope of your needs at present). Use a DAO recordset, which requires a reference to the DAO object library in your project.
    Gotta run...
    Last edited by Micron; 07-26-2016 at 02:30 PM. Reason: clarification

  5. #5
    nickmit11 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2016
    Posts
    9
    I pull my data from a single table that holds all of my records, and then display this data on a form which displays each record individually (one at a time)

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not enough info and context to provide a focused response. However, here is a link to Allen Browne's search that covers most aspects of search.
    If you provide more detailed description and a sample of the data, a sample of what you do on the form and what your expected result would be, someone will provide more direct answer.

    You could watch this free video by Steve Bishop and see how he does a search (again it may not apply to your situation).

    Good luck with your project.

  7. #7
    apr pillai's Avatar
    apr pillai is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    May 2010
    Location
    Alappuzha, India
    Posts
    209
    No don't need any Code to do this kind of search.

    Right-Click on the Field where you want to do the search. Select the Find... option from the Shortcut menu displayed. A dialog-box will appear. You can enter the Search Text on the Text control displayed then Click on the FindNext... Command Button. The first record will be found. You can click on the Find Next command button to repeat search operation further with the same search text, each time you click on the command button.

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

Similar Threads

  1. Replies: 2
    Last Post: 09-24-2014, 05:19 PM
  2. Replies: 7
    Last Post: 06-06-2013, 10:32 AM
  3. Replies: 12
    Last Post: 04-07-2012, 12:09 PM
  4. Replies: 1
    Last Post: 12-22-2010, 01:28 AM
  5. Replies: 1
    Last Post: 11-07-2010, 11:04 AM

Tags for this Thread

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