0

I have a ‘Multiple Items’ form in Access which shows the user the set of 1 to n existing allegation records related to a selected case. The form is based on a query showing linked information from three underlying tables, with one record for each allegation in the “Cases” table (which should have been two tables, one for cases, one for allegations, but it was developed years ago and I can’t change that). Currently, I can then select any record and bring up a form with that record for editing Case information. Once that is done another routine updates all n allegations with the appropriate info (so all allegations have the same case level data). All this works.

I want to be able to add additional allegations or delete extra ones, ideally from this same form to keep it simple for users.

To add allegations, I would like to copy the first allegation record and create a new record. I’m assuming the underlying query complicates this since it has info from multiple linked tables. Thus ideally I’d copy the unique ID for the first record, and use that to copy the record (consisting of approximately 50 fields) in the underlying table (Cases) with a new unique ID and a new allegation number. I need the unique key to automatically increment and the new allegation numbers will be built from Case# and user supplied letter (TempVars!tV_newAllegation below). Cases are numbered (19001, 19002…) and allegations are lettered, thus the full allegation number is 19001A, 19001B, 19001C… The user will supply the letter they want to append (via a text box), e.g., “E”, and it will be combined with the case# to create a new allegation# for the record.

I used a slight modification of the [code here](MSAccess - Requery a Subform After Insert?> for the onClick() of a button added to the footer of my Multiple Items form. When I run it I get "Run-time error '3027': Cannot update. Database or object is read-only." The debug screen shows the ".AddNew" call highlighted. I cannot figure out what is creating the read-only lock.

My code:

Private Sub cmdInsert_Click()

Dim rstSource As DAO.Recordset
Dim rstInsert As DAO.Recordset
Dim fld       As DAO.Field
Dim tV_newAllegation As TempVars

    'Create new allegation number.
TempVars!tV_newAllegation = [TempVars]![tV_selectIncidNum] & [Forms]![1frmAllegationChooseList]![txtAllegLetter]
    ' Begin copy and update of record.

If Me.NewRecord = True Then Exit Sub

Set rstInsert = Me.RecordsetClone
Set rstSource = rstInsert.Clone
With rstSource
    If .RecordCount > 0 Then
    ' Go to the current record.
    .Bookmark = Me.Bookmark
    With rstInsert
        .AddNew
            For Each fld In rstSource.Fields
              With fld
                If .Attributes And dbAutoIncrField Then
                    ' Skip Autonumber or GUID field.
                ElseIf .Name = "AltIntCaseNumber" Then
                    rstInsert.Fields(.Name).Value = TempVars!tV_newAllegation
                    ' Add other ElseIf statements for any other field needing adjustment.
                Else
                    ' Copy field content.
                    rstInsert.Fields(.Name).Value = .Value
                End If
              End With
          Next
        .Update
        'Go to the new record and sync form.
        .MoveLast
        Me.Bookmark = .Bookmark
        .Close
      End With
    End If
    .Close
End With

Set rstInsert = Nothing
Set rstSource = Nothing

End Sub
braX
  • 11,506
  • 5
  • 20
  • 33
A.Birdman
  • 161
  • 1
  • 2
  • 12
  • Really don't need to save "19001A". Just save letter identifier then in query concatenate with case number to display as allegation identifier. What happens when you hit 26 allegations? Your presumption about 0 ID and auto-increment is incorrect. On the first time it will insert record with 0 value and subsequent inserts will not happen. – June7 Sep 20 '19 at 22:43
  • Can't have multiple SQL actions in one statement. There are 5 actions and each must be executed independently. Instead of repeatedly creating and dropping table, leave table permanent and insert/delete records. Is this a split, multi-user database? Temp tables need to be in frontend to prevent multi-user conflicts, assuming multiple users can run this process. – June7 Sep 20 '19 at 22:49
  • >26 allegations has never happened. Highly unlikely in this system but would probably be split cases. – A.Birdman Sep 20 '19 at 23:29
  • I didn't realize the SQL couldn't be read as a single statement. That's how I found the foundation for it online. This IS a split, MU database and setting up permanent tables is difficult at best. The goal is that yes, multiple front-end users need to be able to access and edit the data. Our users are not very tech savvy so I'm trying to set up series of forms and under-the-hood routines to assist them by reducing workload and decreasing errors. This is an old database that is to be archived, but not for a couple years. I just started and have been charged with "clean-up". – A.Birdman Sep 20 '19 at 23:33
  • Other db like SQLServer and Oracle can run multiple actions in a single statement - it is called a 'stored procedure' but I don't use other db platforms, only Access. – June7 Sep 21 '19 at 02:10
  • Exactly what data of existing record do you want to copy into a new record? What fields? Edit your question with this info. – June7 Sep 21 '19 at 03:29
  • Essentially I need a complete copy, except for the autoNumber field and the allegation identifier. There are a lot of fields so I didn't list them all. – A.Birdman Sep 23 '19 at 18:23
  • I used the method linked by Gustav. Differences are that my datasheet isn't a subform. The records are listed in datasheet view on the main form, and the buttons are in the footer. After setting it up, I'm getting the Run-time error: 3027 Cannot update: Database or object is read-only. I can't find what is read-only though. – A.Birdman Sep 23 '19 at 18:26
  • Question edited to provide hopefully better info regarding where I am. This really has me stuck moving forward an important project, so all help is appreciated. – A.Birdman Sep 24 '19 at 15:01
  • If the form RecordSource includes multiple tables and the code loops through all those fields on form, then it is trying to saving data from multiple tables into multiple tables. Should be saving a record to only 1 table. Normally a form does data entry/edit for only 1 table - including other tables complicates processes. What are the other 2 tables - related lookup info? – June7 Sep 24 '19 at 19:21
  • Yes, it is a badly designed database. There are multiple allegations for a given case, all stored in one table but should be two. The other two chief related tables are basically victims and perpetrators. Currently an allegation can have 1 victim, 1 perp, but historically they allowed multiple of either. I'm using a query to show allegation, victim, and perp to allow users to keep straight which record they need. I know I need to add allegation records, and only after that add related victim or perp records. I _think_ I may be on the right track. Any suggestions still appreciated though. – A.Birdman Sep 25 '19 at 21:01
  • Code must somehow exclude fields from associated tables. So this form is bound to a non-editable dataset? – June7 Sep 25 '19 at 22:31
  • I was ultimately able to build a workaround using a version of the question suggested initially as already answered. I never was able to select a record from the multiple items form and use it to clone a new record, but if I select it and set my button onClick event to first open the record in a single record form, and on load to clone that record, it works. Thanks for the help. I few of your nudges pushed me in the right direction. – A.Birdman Sep 27 '19 at 22:21

0 Answers0