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
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
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
Code:
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
Last edited: