Error message "Edit record failed because the default alias represents a record which is read only" (1 Viewer)

p_d_hodgson

New member
Local time
Today, 12:39
Joined
Jul 15, 2020
Messages
11
Hi,
Hopefully @AccessJunkie will pick this up, or someone who knows what this error means. I am getting this error in a very simple situation. I have am using Access 365 and two main forms and one subform:
The first form (Form1) shows details from table A and has a subform showing showing the related data in table B. The form has two buttons which have macros to open the second form (originally just simple macros, but now converted to VBA).
The second form (Form2) only has the fields of table A, two of which are disabled (the pk field, and a date field which is the creation date of the record having a default value "=Date()"). No macros exist on Form2 (I have checked, re-checked and re-re-checked every control in the form on the Events table of the properties).
The buttons on Form1 are for "New" (opens Form2 in acFormAdd), and "Modify" (opens Form2 with the currently displayed item from table A) in acDialog mode.
The error occurs when pressing "New"... Form2 displays an empty form except for the ID field (which says "New") and the creation date field (which shows the current date). If I enter data up to and including the last data entry field, then Tab, or press the Close [X] button, I get the error. Pressing [OK] will (for Tab) refresh Form2 for a new record, or for [X] will close the form and return to Form1. In both cases the data is saved.

I am really stuck... Why is this happening? What do I have to do to eliminate this error? Or, is there a way to just suppress this message?

Many thanks in advance,
Paul
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
Defaullt Record locking should be set to No Lock.

do you use same Query on Form1 and Form2.
better to use separate queries on each.

you may also remove the acFormAdd parameter on opening the second form.
instead have a blank recordset by setting the recordset to:

select * from yourTable where (1=0);

'''''''
use this code on opening Form 2 when "Modify" button is pressed.
supposing that your autonumber is ID:
Code:
Private Sub cmdModify_Click()
DoCmd.OpenForm "Form 2", , , , , acDialog, Me.ID

End Sub

for "New" button:
Code:
Private Sub cmdNew_Click()
DoCmd.OpenForm "Form 2", , , , , acDialog, 0

End Sub
we passed the ID (as OpenArg,s if modify, 0 if new) to Form 2.

form 2 need to process the OpenArgs when it opens:
Code:
Private Sub Form_Load()
Dim lngArg As Long
lngArg = CLng("0" & Me.OpenArgs)
Me.RecordSource = "select * from yourTableName where id = " & lngArg
End Sub

Requery the Form 1 when Form 2 closes:
Code:
Private Sub Form_Unload(Cancel As Integer)
    Dim lngID As Long
    Dim frm As Form
    Me.Dirty = False
    Set frm = Forms![Form 1]
    lngID = Nz(frm!ID, 0)
    frm.Requery
    If lngID > 0 Then
        With frm.Recordset
            .FindFirst "id=" & lngID
        End With
    End If
    Set frm = Nothing
End Sub
 
Last edited:

p_d_hodgson

New member
Local time
Today, 12:39
Joined
Jul 15, 2020
Messages
11
Many thanks Arnelgp... I tried that and am still getting the same results... error on New.
Both forms are set to "No Locks"
The refresh was already being done, as there was a combo box that allowed to you to select the record from tableA in Form1,a nd that was working fine.

Following your examples, the code is now:
<In "New"> : DoCmd.OpenForm "frmCustomer", acNormal, , , acFormEdit, acDialog, 0
<In "Modify"> : DoCmd.OpenForm "frmCustomer", acNormal, , , acFormEdit, acDialog, Me.ID
<In Form_Load for Form2> : Me.RecordSource = "SELECT * FROM tblCustomers WHERE ID = " & Nz(Me.OpenArgs, "0")

When I tried without the acFormEdit parameter both buttons gave a NEW form (i.e. Modify did not show the record for the ID passed in)

Many thank for you assistance...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
ID is usually numeric.

...WHERE ID = " & Nz(Me.OpenArgs, 0)
 

p_d_hodgson

New member
Local time
Today, 12:39
Joined
Jul 15, 2020
Messages
11
Many thanks again, Arnelgp... made that change again with no joy! Sorry for the trouble... and thanks for the assistance.

I tried to generate a small database to replicate the issue, but it seems to work fine (except I can't seem to get refresh to work! :( ). In this case I created the "New" and "Modify" Buttons using the "Command Button Wizard", then converted the macros to VBA, and the code it generated was:
DoCmd.OpenForm "Form2", acNormal, "", "[ID]=" & ID, , acDialog
The "New" button code I manually edited to:
DoCmd.OpenForm "Form2", acNormal, "", "[ID]=" & 0, , acDialog

When I tried this format of VBA code for real application (removing the Form_Load code), I still get the "Default Alias" error, so I have reverted back to your suggested format with the Form_Load code. Still giving the error n "New".
Could it be one of the multitude of Property settings on the form itself... I've tried playing with 'Filter on Load' but to no joy (currently set to No)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
here is a working sample.
 

Attachments

  • checkRecordlocking.zip
    33.9 KB · Views: 147

p_d_hodgson

New member
Local time
Today, 12:39
Joined
Jul 15, 2020
Messages
11
Many thanks again Arnelgp... I've looked at the database you kindly provided, and it appears to be (basically) the same code as I have. The only thing that I can see that is extra is there is a Form1_BeforeInsert that cancels the insert (which I do not have), could this be something I need?
I have tried to determine exactly where the problem resides by adding event handlers on Form2 for data changes, each just calling MsgBox and saying which event is being handled. The main part of the sequence I get is:
Form_Load
As soon as I enter anything into any of the fields: Form_BeforeInsert fires (no record ID is allocated at this point)
The ID gets allocated, I fill out the rest of the fields, the press [X]: Form_BeforeUpdate fires
The "EditRecord... " error occurs
Form_AfterUpdate fires
Form_AfterInsert fires

As mentioned before, I had no macros in the form. Now I have the Form_Load macro as you suggested, a new button which just applies some 'beautification' to the text (for example, using "StrConv(FirstName, vbProperCase)"), and the 4 events Form_<Before/After><Update/Insert> which just popup the message boxes.

I had wondered whether it could be the default value for the Creation date, but as that gets displayed at the very start when opening the form, I think that is not likely.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:39
Joined
May 7, 2009
Messages
19,169
Form 2 is opened without acFormAdd.
if you have Property on [Form 2], DataEntry set to Yes, revert it to No.
 

p_d_hodgson

New member
Local time
Today, 12:39
Joined
Jul 15, 2020
Messages
11
Hi Arnelgp,
Thanks, I have tried all that you have suggested, and all I can think of... The most telling test I have tried is to create a new Form2 with no code at all, change the OpenForm call to refer to the new form, added the Form_load to determine new/amend, and I still get the error when trying New! So all I can think is that it must have done something in Form1 that is causing the error. If I could understood what the error means (what is a "Default Alias"?) then I might be able to figure out what the error is pointing to.
Thank you very much for all your help so far, any other pointers would be gratefully received.
Regards,
Paul
 

p_d_hodgson

New member
Local time
Today, 12:39
Joined
Jul 15, 2020
Messages
11
Hi Arnelgp,
I have managed to overcome the problem by splitting the database (front end/back end) and completely rebuilding Form1 in a new front end. The main difference in my new Form1 is that I use the fields in the main part of Form1 to create/edit record information in Table A and have no separate Form2. Thanks for all your help it was much appreciated.
Regards,
Paul
 

Users who are viewing this thread

Top Bottom