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

    Access 97 calculated field

    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?

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

    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.

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

    Re: Access 97 calculated field

    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?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

    Here it is!

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

    Re: Access 97 calculated field

    I'm confused. Here what is?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Tennessee
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

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

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

    Re: Access 97 calculated field

    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.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

    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.

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

    Re: Access 97 calculated field

    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.

  10. #10
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

    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.

  11. #11
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

    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.

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

    Re: Access 97 calculated field

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

  13. #13
    Star Lounger
    Join Date
    Dec 2000
    Location
    Hellertown, Pennsylvania, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 97 calculated field

    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

Posting Permissions

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