Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    History search (97)

    Is it possible to have a command button on a form that when clicked will bring up previous records that match a particular field. I have a group number field and a transaction date field ( these 2 fields are the key) I would like to pull up previous records that match the group number field in descending order according to the transaction date. I know there is a search function but I would like this command button, when pressed, to bring up the previous occurance of that group number according to the transaction date. When pressed again it would bring up the next occurance and so forth until there are no more occurances Is this possible? Thanks for your help. I hope I explained this plainly enough.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: History search (97)

    There are several ways to accomplish this. Doing it exactly the way you described it involves using DAO (for Access97 at least) and opening a recordset that you use to populate a form. In other words, it involves some moderate level VBA to do it as you describe. On the other hand . . .

    Being entirely lazy and using VBA only if can't be done any other way, I would use a subform linked on the group number field, with a data source sorted in descending date order. I would make the subform a single record form rather than continuous, and have the user navigate with the standard navigation controls. You could turn those off and provide a forward and a backward button that do next record and previous record, but as I said, I'm lazy. There are a couple of other approaches if you are interested, but these two represent more or less the extremes. Hope it helps.
    Wendell

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

    Re: History search (97)

    Do you want these old records displayed in the current window? Are they included in recordsource of the current form? Is Group Number a number?

    If so you could try something like this

    In the declarations section of the module put this
    Dim bkmrk As String

    By setting a bookmark you can return to the original record at the end
    <pre>Dim strFilter as string
    Dim strSortOrder as string
    strFilter = "[Group Number] =" & me![group Number]
    strSortOrder = "transaction date DESC"

    if me.filteron = false then
    bkmrk = Me.Bookmark
    me.filter = strFilter
    me.filteron = true
    me.orderby = strSortOrder
    me.orderbyon = true
    else
    DoCmd.GoToRecord acActiveDataObject, , acNext
    end if
    </pre>


    if Group Number is a string then replace the strFilter line with:

    strFilter = "[Group Number] =' " & me![group Number] & " ' "
    Regards
    John



  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: History search (97)

    I put this code in the on click event of the command button - is this right? I tried it but if there isn't any history, it freezes. Maybe I am not doing something right. Yes, I would like these old records displayed in the current window. Yes, they are included in recordsource of the current form. GroupNumber is a text field.

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: History search (97)

    If Group Number is a text field, you should add text qualifiers to the following line:<pre>strFilter = "[Group Number] ='" & me![group Number] & "'"</pre>


    Hope this helps

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: History search (97)

    I would go with Wendell's suggestion - you don't need any code or buttons.

    Alternatively, you could use a single form, and order the data by group number and transaction date. Prevent the user from changing the sort order (don't display the standard menus, toolbars and context menus). Again, the user can find previous records with the built-in navigation buttons.

    John Hutchison's method will filter the form - the user will only see records for the current GroupNumber. (And I don't understand what he's doing with the undeclared bookmark variable).

    Yet another way is to order the data by transaction date only (and again preventing the user from changing this). You could have a command button with code like this:

    Private Sub cmdFindPrevious_Click()
    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.Bookmark = Me.Bookmark
    rst.FindPrevious "[GroupNumber] = " & Chr(34) & Me.[txtGroupNumber] & Chr(34)
    If rst.NoMatch Then
    Beep
    Else
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing
    End Sub

    Replace GroupNumber by the exact name of your field and txtGroupNumber by the exact name of the control on the form bound to the GroupNumber field. Chr(34) is the equivalent of a double quote; single or double quotes are needed if you construct criteria for a text field.

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

    Re: History search (97)

    >>(And I don't understand what he's doing with the undeclared bookmark variable).

    It was declared ( but I did not 'pre' that line of code). I suggested that you could set a bookmark, so that when you have finished with looking at the history you could easily return to the original record.

    To do this you would have another button that removes the filter and return to the original record.
    Regards
    John



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

    Re: History search (97)

    Yes put the code in the onclick event of a command button.

    Doesn't the current record always constitute one item in the history?

    I was wrong to try to use bookmarks to return to the current record. Applying a filter and clearing it removes the bookmark so it cannot be used.
    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
  •