Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sequentially labeling a set of records (Access 97)

    I have a set of records which state the required measurements of different parts of various garments.
    I have a (continuous) form which shows a subset of these records that relate to just one garment.
    Each record appears in the order it was entered (using ID autonumber as index)

    eg
    Garment PointOfMeasurment REF
    JKT2056 Sleeve Length
    JKT2056 Sleeve Openning
    JKT2056 C/Front Length
    JKT2056 Hem Circumference
    JKT2056 Button Spacing
    etc etc

    I want to be able to fill in a field call REF with A,B,C,D,E,F in the order they appear down the list of PointsOfMeasurement

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sequentially labeling a set of records (Access 97)

    What exactly do you need help with, adding the field, populating it, or what? Do you have a set order you want them to come in (i.e., Sleeve Length before Sleeve Opening, etc.) or is this something the user gets to decide? If you have a table of standard points of measurement, so that the user can select a point from a dropdown list, you could easily add the REF field to that table and use the Ref value to sort the records in the form, but you'll need to provide more details on the desired solution.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sequentially labeling a set of records (Access 97)

    Thank you Charlotte for your quick reply

    I need help in populating the field called REF.
    I have a table of standard points of measurement that the user selects and adds to the list. This standard table has about 40 different points of measurement that can be applied to the garment they are working on, but they will only choose about 8-15 of these which are relevant to that garment. The order that they choose the points is the order that they appear on the form.
    The records are actually ordered on the form by the REF field and then by the ID field. As the REF is not filled in when the records are first added they are ordered by the ID field (which is an autonumber)

    They end up with a form which lists all the points of measurement which a blank entry in the REF field.
    The user currently goes through and manually enters A in the first row B in the second C etc
    If they need to swap the order around they just have to change the letters around but generally they want them to be lettered in the same order that they have entered them

    I want to have a button they can click and the letters are added to the REF field automatically for them.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: sequentially labeling a set of records (Access 97)

    I'm still a bit confused. Do they need to see these REF values in the form for some reason or is that just so they can enter/change them? You *could* do it with a button, but there's no reason why the REF field should be left null when they make a selection from the list. Why not do it automatically when the selection is made? Then if they needed to, they could still change them (assuming you keep the REF field visible), but they wouldn't have to if the order was acceptable to them. The logic for populating it automatically would be something like this:

    <UL><LI>Look up previous maximum REF value for this garment
    <LI>Determine what the next value after that should be
    <LI>Insert that new value into the REF field for the current/new record[/list]
    So, assuming that you want to use letters instead of numbers (which would be easier), if the maximum REF value for the garmet up to this point is B, take the ASCII value of B and add one to it, then convert that back to a character, like this:

    Me!REF = Chr(Asc(strPreviousMax) + 1)

    where strPreviousMax is the value looked up in the first step (i.e. [img]/forums/images/smilies/cool.gif[/img]. The ASCII value of "B" is 66 (lower case "b" has an ASCII value of 98) so Asc("B") + 1= 67 and Chr(Asc("B") + 1) will yield "C".
    Charlotte

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sequentially labeling a set of records (Access 97)

    The letters in the REF field refer to a sketch of the garment which has arrows indicating where the measurement is taken, each arrow has a corresponding letter on it. So they need to be seen so the correct letter can be added to the sketch in the correct spot.

    I was trying to use a loop statement together with the Chr(Asc(*)+1) method you have suggested.

    Your idea of adding the letters as the point of measurement is added seams much easier plus it means there will be nothing extra for the user to do (like click another button).

    I will try using the DMax function to determine the maximum REF for this garment.

    Thank you very much for your help. I will give it a go over the weekend and let you know how I went.

  6. #6
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: sequentially labeling a set of records (Access 97)

    If you want to sort items by letter rather than number, another option is to use a lookup table. Small sample database (ACC 97 format) is attached that shows how to do this. This db contains 4 tables: stripped-down versions of Orders, Order Details, & Products from Northwind.mdb, and cleverly-named Alphabet table. The latter has 2 fields, Number (Integer) and Letter (text). A is 1, B is 2, etc thru Z is 26. Number is primary key, related to foreign key ItemNo in Order Details table. There is a main Orders form & Order Details subform that demonstrate this technique. The ItemNo field displays a letter ("looked up" from the Alphabet table) but the actual value is a number, so you don't have to convert to a text character. When entering records in subform, you can select letter from ItemNo combobox, or leave blank to have letter entered "automatically". You cannot enter same letter twice for same order because OrderID & ItemNo are composite primary key for Order Details table. ReorderForm sub (see below) loops thru records & updates the ItemNo field in each record (if necessary) so items are "numbered" (lettered) in sequence (A, B, etc); if ItemNo left blank, sub determines next available unused letter, updates record, then after record updated, resequences items:

    Private Sub ReorderForm(intUpdateType As Integer)
    On Error GoTo Err_Handler

    ' intUpdateType 1 = Before Update (only if ItemNo left blank)
    ' intUpdateType 2 = After Insert/Update/Delete record

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim lngOrderID As Long
    Dim intCount As Integer
    Dim n As Integer
    Dim strMsg As String

    Set db = CurrentDb
    lngOrderID = Me.OrderID
    strSQL = "SELECT <!t>[ItemNo]<!/t> FROM <!t>[Order Details]<!/t> " & _
    "WHERE <!t>[OrderID]<!/t> = " & lngOrderID & " " & _
    "ORDER BY <!t>[ItemNo]<!/t>;"
    Set rst = db.OpenRecordset(strSQL)

    Select Case intUpdateType
    Case 1 ' Before Update
    With rst
    If .EOF Then
    Me.ItemNo = 1
    Else
    .MoveLast
    Me.ItemNo = !ItemNo + 1
    End If
    End With
    Case 2 ' After Update
    With rst
    If Not .EOF Then
    .MoveLast
    intCount = .RecordCount
    .MoveFirst
    For n = 1 To intCount
    If !ItemNo > n Then
    .Edit
    !ItemNo = n
    .Update
    End If
    .MoveNext
    Next n
    Else
    ' No records - do nothing
    End If
    End With
    Me.Requery
    DoCmd.GoToRecord , , acNewRec
    End Select

    Exit_Sub:
    If Not rst Is Nothing Then rst.Close
    Set db = Nothing
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub

    End Sub

    It may be easier to see how this works by taking a look at the attached db and try entering, deleting, & updating records. You may or may not be able to adapt this technique for your project.

    HTH
    Attached Files Attached Files

  7. #7
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sequentially labeling a set of records (Access 97)

    Thank you Charlotte and Mark for your help,

    I was able to get it working very well and learnt some things along the way.
    My friends here at work also send their thanks.

    Regards

Posting Permissions

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