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

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote