Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Unanswered: Orderby- How to keep track of selected record

    hi,

    I have a little quirk in my design which I need your expert advice

    I have created a sub form which lists the number of tests we performs. Within the list is the primary key, customer name, date and so forth.

    In the form header I have command buttons for each one of the fields in the detail section. So, I command button for Primary key, customer, date....etc.

    I have an event (on click) for each command button. This is the VBA code:

    Private Sub Command1_Click()
    If Me.OrderBy = "" Then
    Me.OrderBy = "UnquieID desc"
    Else
    Me.OrderBy = "UnquieID"
    End If
    Me.OrderByOn = True
    End sub

    Click once and it will sort descending. Click again it will sort ascending.

    Here is where i need your help. If i select a record, then press any of the command buttons i loose focus on the record i had selected. Focus changes to the highest or lowest record in my form.

    I would like to select a record and stay with that record when i sort using the command buttons.

    thank you for your time
    Beth

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try this:
    Code:
    Private Sub Command_Sort_Click()
    
        Dim rst As DAO.Recordset
        
        ' If the Identity column is of type Text use:
        ' Dim strRowID As String
        '
        Dim lngRowID As Long
    
    ' Store the Identity of the current record
    '    
        ' Here SysCounter is the Identity column (Primary Key, Autonumber)
        '
        lngRowID = Me!SysCounter
        
     ' Sort the form data set
     '
        Me.OrderBy = "Art_Description"
        Me.OrderByOn = True
        
     ' Return to the previous current record
     '
        Set rst = Me.RecordsetClone
    
        ' If the Identity column is of type Text use:
        ' rst.FindFirst "<Identity> = '" & StrRowID & "'"
        '
        rst.FindFirst "SysCounter = " & lngRowID
        If rst.NoMatch = False Then ' Should always be True
            Me.Bookmark = rst.Bookmark
        End If
        Set rst = Nothing
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    Excellent!! you clearly have a better grasp than me over VBA.
    The code worked wonderfully.

    One of the fields contains a date. The code didn't remember the record when it was sorted. Any advise?
    I tried in vain to change it but thought 'best leave it to the experts!!'.

    Best regards
    Beth

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by wotwot View Post
    One of the fields contains a date. The code didn't remember the record when it was sorted. Beth
    I'm not sure to understand. Do you mean that the Identity column of your form is of type Date/Time and that the method does not work in this case?
    Have a nice day!

  5. #5
    Join Date
    Nov 2010
    Posts
    9
    that's correct. One column is of type date. The remaining are text fields. It works fine with text fields, not with the date. The code below is for the date_created column which is causing trouble.

    Private Sub Command6_Click()
    Dim rst As DAO.Recordset

    ' If the Identity column is of type Text use:
    Dim strRowID As Date
    '
    'Dim lngRowID As Long

    ' Store the Identity of the current record
    '
    ' Here SysCounter is the Identity column (Primary Key, Autonumber)
    '
    strRowID = Forms![main]![sub1].Form![Date_Created]


    ' Sort the form data set

    If Me.OrderBy = "Date_Created" Then
    Me.OrderBy = "Date_Created desc"
    Else
    Me.OrderBy = "Date_Created"
    End If
    Me.OrderByOn = True

    ' Return to the previous current record
    '
    Set rst = Me.RecordsetClone

    ' If the Identity column is of type Text use:
    ' rst.FindFirst "<Identity> = '" & StrRowID & "'"
    '
    rst.FindFirst "Date_Created = " & strRowID
    If rst.NoMatch = False Then ' Should always be True
    Me.Bookmark = rst.Bookmark
    End If
    Set rst = Nothing
    End Sub

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure we talk about the same thing. Allow me first to recall that an Identity column is a column that uniquely identify a row in a rowset, i.e. for every row the contents of this column is different (unique). Such a column is often the Primary Key of the table but that's not mandatory. Personnally I tend to use a Surrogate Key:
    Wikipedia: "A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data
    i.e. An Autonumber column in Access, but that's not mandatory: the only condition required is that the column contains unique data.

    Though it's possible to use a Date/Time column as the Identity column of a table (sometimes referred to as "TimeStamp"), it's a solution I do not like using in Access. In Access the time is stored to the second and if you're dealing with a multi-user system nothing can guarantee that two records will never be created at the same time. Precision to the second is not accurate enough for this purpose (in system such as SQL Server Date/Time data can be stored with a precision several orders of magnitude greater than the second and this solution is sometimes used).

    Coming back to the solution I proposed, you do not change the column used to retrieve an Identity value when you change the sorted column. Let's take an example:
    Table: Table1
    Column 1: Name:[ID], Autonumber
    Column 2: Name:[Customer], Text(50)
    Column 3: Name:[Date_Of_Purchase] Date/Time
    Column 4: Name:[Amount] Currency
    ... etc.
    a) Sorting by Customer:
    Code:
    Private Sub Command_Sort_Click()
    
        Dim rst As DAO.Recordset
        Dim lngRowID As Long
        lngRowID = Me!ID    
        Me.OrderBy = "Customer"
        Me.OrderByOn = True
        Set rst = Me.RecordsetClone
        rst.FindFirst "ID=" & lngRowID
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        Set rst = Nothing
        
    End Sub
    b) Sorting by Date_Of_Purchase:
    Code:
    Private Sub Command_Sort_Click()
    
        Dim rst As DAO.Recordset
        Dim lngRowID As Long
        lngRowID = Me!ID    
        Me.OrderBy = "Date_Of_Purchase"
        Me.OrderByOn = True
        Set rst = Me.RecordsetClone
        rst.FindFirst "ID=" & lngRowID
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        Set rst = Nothing
        
    End Sub
    However, if the Date/Time column actually is the Identity column of the table, you can use the following code:
    Code:
    Private Sub Command_Sort_Click()
    
        Dim rst As DAO.Recordset
        Dim dteRowID As Double
        
        dteRowID = Me!Date_Of_Purchase
        Me.OrderBy = "Customer"
        Me.OrderByOn = True
        Set rst = Me.RecordsetClone
        rst.FindFirst "Cdbl(Date_Of_Purchase)=" & Replace(dteRowID, ",", ".")
        If rst.NoMatch = False Then Me.Bookmark = rst.Bookmark
        Set rst = Nothing
         
    End Sub
    Replace(dteRowID, ",", ".") is necessary if your Windows settings use the comma "," as the decimal separator, which is usual in several parts of the world, otherwise simply use: "Cdbl(Date_Of_Purchase)=" & dteRowID.
    Have a nice day!

Posting Permissions

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