Solved How to force MS Access display a primary key on the forms after entering data in the first field (1 Viewer)

[nector]

Sorry people, this question was asked here before, but I seem to see where the problem is coming from and if the permanent solution is found then it will help others as well.

When MS Access is used as Front End with MYSQL then whichever form you use for entering new data the Primary Key does not appear instantly like it happens when you are using it within Access (FE = Access, BE = Access) , the primary key will only be visible after leaving the form and so the subform will have no reference key when entering line details which results the forms showing #DELETED all the fields ,but when you refresh the forms manually then the data is visible because at that time both primary keys for the parent form and subform are visible.

Temporarily Solution

I have placed a short code as below on every form:

Me.Recordset.Requery

This works but the data disappears until I recall it using the code below

Dim db As DAO.Database
Dim strSQL As String
Dim prm As DAO.Parameter
Set db = CurrentDb
strSQL = "SELECT intrlData FROM [tblCustomerInvoice] WHERE [InvoiceID] =" & [Forms]![frmCustomerInvoice]![CboCreditDebits]
With db.CreateQueryDef("", strSQL)
For Each prm In .Parameters
prm.Value = Eval(prm.Name)
Next
    With .OpenRecordset(dbOpenSnapshot, dbSeeChanges)
        If Not .EOF() Then
         Me.txtInvoicetoedit = .Fields(0).Value
    End If
    End With
End With
Set prm = Nothing
Set db = Nothing
Me.Filter = "InvoiceID = " & Me!CboCreditDebits.Value & ""
If (Me.txtInvoicetoedit <> "") Then
    Beep
    MsgBox "This document is already approved cannot be edited", vbOKOnly, "Internal Audit Manager"
    Me.FilterOn = False
Else
    Me.FilterOn = True
End If
Dim Records As DAO.Recordset

    Set Records = Me![sfrmLineDetails Subform].Form.RecordsetClone

    If Records.RecordCount > 0 Then
        Records.MoveFirst
        While Not Records.EOF
            Records.Edit
           
            Records.Update
            Records.MoveNext
        Wend
    End If
    Records.Close
MsgBox "Please save the invoice after editing", vbInformation, "Internal Auditing Manager"
Me.Requery

This is very cumbersome, I would not want to continue with this , is there a way to force the primary key visible immediately the first field is captured either in the parent form or subform

Desired Results

(1) Once the code is run (Me.Recordset.requery), I must stay on the same record
(2) Users should not see any #DELETED error in all the fields at all

 

[theDBguy]

Have tried simply?

Me.Dirty = False

 

[Minty]

I can't believe when you leave the main form to go to the sub form, that the record isn't automatically saved and and the PK generated at that point. Are you sure you have the behaviour correct, and it's not something else?

The DBGuy's solution is the most obvious, unless you have other constraints on the form/table.

 

[nector]

theDBguy​

Ok

Where do I put it, which event??

Me.Dirty = False

 

[nector]

Okay this appear to work

Private Sub Form_AfterInsert()
Me.Dirty = False
End Sub

 

[The_Doc_Man]

Minty and I appear to be on the same page, or at least close to it.

To the best of my understanding, the PK doesn't get chosen when a new record FIRST becomes dirty. The PK isn't actually chosen until the record is about to be saved, i.e. just AFTER the Form_BeforeUpdate event and just BEFORE the Form_AfterUpdate event - it is the Access internal "record update" event for which there IS no corresponding form event.

You cannot display the PK early unless you force a SAVE in some manner. Note that forcing an early SAVE means that you now must rely on other means to validate the non-key fields because the whole record gets saved at that point, not just the PK. And by your own description, there will be blanks or zeros in other fields because you want that number after data gets entered to the very first field. You would be saving an incomplete record, leaving you vulnerable to saving bad data because you have to now detect when the record IS ready to be saved, but the best place to do that is the _BeforeUpdate event and you just complicated the process by having times when you would save incomplete records.

Not only that, but you make it much harder to "back out" of that new record if you realize during data entry that for some reason you should not save it yet. Now it has an assigned PK and, if it was an autonumber case, you have permanently consumed a PK number that you can't get back and can't use again. If your auditors require contiguous numbering, you just blew that requirement.

When you have a related sub-form (i.e. there is a relationship or a form-based linkage between the sub-form's child link field and the parent form's parent link field), AND the parent's field is the PK, then Access is forced by its own rules ("no orphan records allowed") to assign a PK. And now there is the problem of having to validate the parent record because you forced the creation of the child record. Which is why you SHOULD complete the parent before working with the children.

Then there is one more quibble. The earlier discussion centers around a "generated" key. If you are displaying an autonumber PK, that key has NO VALUE to anyone. It belongs exclusively to the database architecture. It isn't a user's account number. It isn't a stock number. A true autonumber key has no intrinsic meaning except that it provides parent/child linkages for child records, if any. To use an autonumber PK for any other purpose exceeds the intended design of such a key. Your early display of a PK is therefore of questionable procedural value. The final question is therefore, if you SHOW that PK, what is the user going to do with it? What CAN they do with it?

 

[Pat Hartman]

the primary key will only be visible after leaving the form and so the subform will have no reference key when entering line details which results the forms showing #DELETED all the fields ,but when you refresh the forms manually then the data is visible because at that time both primary keys for the parent form and subform are visible.

That is not correct. I don't know what is wrong with your form. Sounds like you don't have the master/child links set correctly or you are incorrectly using an unbound form.

When you add a new record on the main form, the subform is empty and should show no rows. When you finish the data entry in the main form and move focus to the subform, Access saves the main form record so the PK from the main form is available for the subform to use when entering data. At that point, the PK should be visible.