Access 97 calculated field

Topic

New Reply

I am designing a db to automate an old cemetery’s records. A grave’s location is designated by its Section, Row and Lot number, which are separate text fields in the Lot Table. I have created a query with a calculated field, SecRowLot, that concatenates the three text fields. I used an Append Query to populate the SecRowLot field in the Lot Table. I have a LotUpdate form for entering new lot purchases. The form has a SecRowLot field, based on the Lot Query. This field calculates correctly on the form (courtesy of the query) but does not store the value in the Lot Table. I want to store it while checking that this value is not duplicated in the table. (The current occupants of the lot might not appreciate a newcomer.) How do I do this?

Reply | Quote

Replies

I’m not sure I follow you entirely but I wouldn’t recommend storing a calculated value in a table unless you have a very good reason – it can create havoc when deleteing and updating values that the calculation is based. Besides, you can recreate it in a query as you have. I would create a unique index of the 3 fields – that would prevent the addition of a duplicate value. Of course this would happen at the end of an update so you may want to provide a way to check for duplicates as soon as the new lot is entered. There are several ways to do this but probably the easiest is with the recordsetclone property. Take a look at the online ehpl for this property – there should be some decent examples that can point you in the right direction.

Reply | Quote

Paul, thanks for the info. I researched RecordsetClone and think I know how to proceed. Should I use the FindFirst method to test for existing value? Consider the following complications:
I tried to create a unique index on the 3 fields but was stymied by data format. Some entries are blank because the cemetery layout varies all over the lot (pun intended.) For example, section F has no rows, only lots, so a location might be “F blank 23”, while section H might be “H 7 31”. That’s why I created my calculated value SecRowLot. Can I use the Recordset clone to test for duplicate SecRowLot values that exist in other records?

Reply | Quote

Here it is!

Reply | Quote

I’m confused. Here what is?

Reply | Quote

if you are storing the calculated value in a table
set the field properties to no duplicates
KTK

Reply | Quote

Thanks for the response, but…
Now we’re back to square one. PaulK said in his post it’s not a good idea to store a calculated field in the table. If I really want to, should I use an Append Query to add the calculated field?

I really have two problems here:
1. Find any duplicates in existing data. I’ve solved that with a query on the SecRowLot calculated field, which found about 40 duplicates.

2. Once those are cleaned up, how do I test new data for duplicates? Store the calculated field in the table? (how?) Or, use the RecordsetClone method referred to by PaulK. I posted a question about that, with no reply.

Reply | Quote

Don’t store calculated values unless you have a good reason…and in this case you may have a good enough reason. Another thing to consider is how likely is it that the components of the calculated value will change. Only you can decide that but it seems to me that the occupants of the grave sites will not be changing or moving around that much.

Another alternative to consider is to enter a standard value in the component that doesn’t exist like BLANK or NA then remove it using IIF() when you recreate it in the query. This should allow you to create a unique three field index and prevent future dups.

Let me know which way you want to proceed and if you need further help. I’d be happy to help if needed.

Reply | Quote

Paul, many thanks for the suggestions. After much thought and discussion with the cemetery folks we’ve decided to clean up the data, then pad the blank Rows with a “phantom” entry like 99. Then all sections will have consistent data for Section, Row and Lot. Then I can create a three-field index, no duplicates.

I tried that on a small subset of clean data and it works fine. When I enter duplicate data and save the record, Access gives me a long-winded error msg saying I’ve entered duplicate data or committed some other infraction. That will work fine for now, but I plan to put some code in the Form to make the msg shorter for the users. I’l probably use the After Update event in the Row field to trigger code that uses Recordset and FindFirst to check if this value already exists. I’ll let you know how that works out.

Reply | Quote

Howard, glad to hear you found a mutually satisfactory solution. Using the after update event sounds good. Interested to hear how it works out for you.

Reply | Quote

Howard,

I followed this post. If you want to trap the error that occurs when you have duplicate data entered, it is error number 3022. If you want, I’ll send you a code snippet that explains how to do this. You can have a much more friendly message for your user.

Reply | Quote

Allan,
Thanks for the reply. Yes, I would appreciate that code snippet.
Howard

Reply | Quote

The code is listed below. Please check it first, as I am on my way out and have not tested it as written. If it gives your problems, let me know..

Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo On Error GoTo HandleErr

Dim strMsg As String

strMsg = “Data has changed.”
strMsg = strMsg & “Do you wish to save the changes?”
strMsg = strMsg & “Click Yes to Save or No to Discard changes.”
If MsgBox(strMsg, vbQuestion + vbYesNo, “Save Record?”) = vbYes Then

Else
DoCmd.RunCommand acCmdUndo

End If

ExitHere:
Exit Sub

HandleErr:
Select Case Err.Number
Case 2501
Resume Next
Case 3022
If MsgBox(“Record already exists” & vbCrLf & _
vbTab & “Cancel Entry?”, vbYesNo + vbCritical, “Wait”) = vbYes Then
MsgBox “The entry has been cancelled”, , “Cancelled”
DoCmd.RunCommand acCmdUndo
End If
Case Else
MsgBox Err.Description & ” ” & Err.Number
Resume Exit_cmdSave_Click
End Select

Reply | Quote