Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Multi-field Index (Access 97-SR-2)

    I have a cemetery database where graves are designated by Section, Row and Lot. In the main table (called Lot) I have created a unique index based on these three fields. This flags data errors if someone tries to enter a new burial into an existing grave. This works fine, but Access produces a long-winded error message stating that the changes would "...create duplicate values in the index, primary key or relationship." Since the user doesn't see the msg until he tries to save the record he has no clue about which field is a duplicate.
    How can I replace this msg with a simpler, more precise one? In the query that drives the data entry form I have created a calculated field called SecRowLot, which is a concatenation of the fields Section, Row and Lot. How can I use this field, or some other technique, to create my error message?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multi-field Index (Access 97-SR-2)

    Use the dlookup function in the Before Update event code for your Section, Row, and Lot fields. Since you have three fields in your key your code might need to get a little fancy (e.g. you probably only want to check if the three fields are all non-null so you're only checking when the three have all been filled in.

    To give you a starting point, following is some code I use in the Before Update code of one of my forms, for the same reason as you have in mind.

    If Not IsNull(DLookup("[DocNumber]", "[Document Index]", "[Document Index].[DocNumber]='" & Me.DocNumber & "'")) Then
    MsgBox Me.DocNumber & " already exists in the database. Either choose a new number or use the Select Number dropdown to find " & Me.DocNumber, vbExclamation + vbOKOnly, "Number already exists"
    Me.Undo
    End If

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Multi-field Index (Access 97-SR-2)

    Since I have the calculated field SecRowLot in the query, wouldn't it be easier to add a control, based on the calculated field, to the form? Then I could use your Dlookup technique on this one control. I would be testing whether SecRowLot already exists in the database.

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

    Re: Multi-field Index (Access 97-SR-2)

    You can't use dlookup on a form, only on a "domain", which includes a table or query. You could, however, use a recordset clone and do a FindFirst on the criteria. If the FindFirst returns a NoMatch = False, you'll know the record already exists. You don't need a control on the form for this, as long as the field is in the underlying recordset.

    Dim rst As DAO.Recordset
    Dim strFind as String

    Set rst = Me.RecordsetClone
    strFind = "[SecRowLot] = ' " & strValue & " ' "

    rst.FindFirst strFind
    If Not rst.NoMatch Then
    MsgBox "The Section Row and Lot you have entered already exist in the database.", vbOkOnly, "Invalid Selection"
    End If

    This assumes that the SecRowLot value you've calculated is a string value and that strValue contains whatever the user has entered. Keep in mind though, that is you have a very large number of records, this will be slow due to the fact that SecRowLot is not indexed.

    BTW, how are you planning to handle multiple interments in the same grave? I know it's done because I was asked about it when my father died recently.
    Charlotte

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Multi-field Index (Access 97-SR-2)

    Thanks for the reply. I see that I now have two choices:
    Use Dlookup in the query or the recordset clone on the form. I may play around with both to see how they work. Any thoughts on which technique would be faster?

    As far as multiple internments, some lots are designated as two-deep. This means that the first burial in the grave is at the bottom, and a later burial will be on top of the first. I imagine this will become more common as cemeteries fill up.

    Howard

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

    Re: Multi-field Index (Access 97-SR-2)

    A Dlookup will usually be the slowest method. The recordsetclone method has a big advantage because the recordset doesn't have to be created again, you just have to search for a particular field value.
    Charlotte

Posting Permissions

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