Bob Collison
asked on
Access 2016 Form Before Update Creates Blank Record
Hi Experts,
I'm having weird problem.
I have a Form bound to a table with a Primary Key whos properties include:
- Required: Yes
- Allow Zero length: Yes.
- Indexed: Yes (no Duplicates.
There are two records in the table.
The Form Before Update code checks the to see if the Primary Key [EVENT_FI_TRAN_ACCT_KEY] and if it is Null does an 'Undo' and Exit Sub. See code below.
If Me.EVENT_FI_TRAN_ACCT_KEY & DblDblQuote & DblDblQuote = DblDblQuote & DblDblQuote Then
'Empty Form. No update.
Me.F46010EventFITranRecMod e = "E" 'Transaction Record Mode: E=Empty. i.e. No Add or Update.
Me.Undo 'Reset Form. Won't update data. Stay on same record.
Exit Sub
Else
Me.F46010EventFITranRecMod e = "U" 'Transaction Record Mode: U=Undetermined.
GoTo STEP_120 'Step 120-Check If Event Finance Transaction Account Key Has Changed.
End If
When I scroll past the second record record an empty record is created in the table and I can no longer navigate either forward or backward.
There is no error produced.
I suspect that the 'freeze' issue is caused by the blank record.
How can I ensure that the blank record is not created?
Thanks,
Bob C.
I'm having weird problem.
I have a Form bound to a table with a Primary Key whos properties include:
- Required: Yes
- Allow Zero length: Yes.
- Indexed: Yes (no Duplicates.
There are two records in the table.
The Form Before Update code checks the to see if the Primary Key [EVENT_FI_TRAN_ACCT_KEY] and if it is Null does an 'Undo' and Exit Sub. See code below.
If Me.EVENT_FI_TRAN_ACCT_KEY & DblDblQuote & DblDblQuote = DblDblQuote & DblDblQuote Then
'Empty Form. No update.
Me.F46010EventFITranRecMod
Me.Undo 'Reset Form. Won't update data. Stay on same record.
Exit Sub
Else
Me.F46010EventFITranRecMod
GoTo STEP_120 'Step 120-Check If Event Finance Transaction Account Key Has Changed.
End If
When I scroll past the second record record an empty record is created in the table and I can no longer navigate either forward or backward.
There is no error produced.
I suspect that the 'freeze' issue is caused by the blank record.
How can I ensure that the blank record is not created?
Thanks,
Bob C.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi John,
I have been continuing to try and debug the problem.
Finally I seem to have found the problem which is.
In the Form On Current Event if the Primary Key was Null I was setting various fields that are displayed to Null so they wouldn't display left over values. This works ok for Unbound Fields but it doesn't work for Bound Fields.
As soon as I commented out all of the code like the following the problem disappeared.
'Me.EVENT_FI_TRAN_SEQ_ID = ""
So the problem is solved.
Thanks for your quick initial response.
Bob C.
I have been continuing to try and debug the problem.
Finally I seem to have found the problem which is.
In the Form On Current Event if the Primary Key was Null I was setting various fields that are displayed to Null so they wouldn't display left over values. This works ok for Unbound Fields but it doesn't work for Bound Fields.
As soon as I commented out all of the code like the following the problem disappeared.
'Me.EVENT_FI_TRAN_SEQ_ID = ""
So the problem is solved.
Thanks for your quick initial response.
Bob C.
ASKER
I replaced the Me.Undo with Cancel = True and blank record wasn't created.
However I still can't move forward or bac using the navigation keys.
Thanks,
Bob C.