Full Version: How to exit from "acNewRec" to save record?
UtterAccess Forums > Microsoft® Access > Access Forms
Jenny1982w
I have an form in Access97 that takes input for an Oracle linked table.
I use "DoCmd.GoToRecord , , acNewRec" to add records to this table.
Then I use the Form_BeforeUpdate event to check if the primary key exists or not.
How do I get access to save the record instead of inserting it at this point?
Any examples of code would be greatly appreciated.
Thanks
Jenny
Jenny1982w
I use the btnAddRecord_Click sub-routine to add new records & check for errors in the Form_BeforeUpdate sub. I exit out of the Form_BeforeUpdate if the primary key already exists but then an error is trapped by the Err_btnAddRecord_Click function as "You can't go to the specified record. You may be at the end of a recordset"
How can I check for errors in the beforeUpdate event & "gracefully" exit out of the addrecord sub.
Any help is greatly appreciated.
The two functions I use are :-
XXXXXXXXXXXXXXXXXXXX
Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click
DoCmd.GoToRecord , , acNewRec
Exit_btnAddRecord_Click:
Exit Sub
Err_btnAddRecord_Click:
MsgBox Err.Description
Resume Exit_btnAddRecord_Click

End Sub
XXXXXXXXXXXXXXXXX
Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("[P_KEY]", "tbl_account", "[P_KEY] = '" & [strPKey] & "'") > 0 Then
MsgBox ("This Number Already Exists!")
Cancel = True
Me.Undo
Me!P_KEY.SetFocus
Exit Sub
End If

End Sub
XXXXXXXXXXXXXXXXX
Jack Cowley
I am a very strong believer in using an autonumber as the primary key for a table. Having said that change the error handler in the command button to read:
sgBox Err.Number & " " Err.Description
Create a record that will cause the error. Note the error number. Now change your error handler to this:
Err_btnAddRecord_Click:
If Err.Number = PutTheNumberYouGotEarlierHere Then
Resume Exit_btnAddRecord_Click
End If
MsgBox Err.Number & " " & Err.Description
Resume Exit_btnAddRecord_Click
hth,
Jack
Jenny1982w
Wow!... That worked great! Thanks a ton, Jack.
I am an Access newbie & I have a quick question for you. How does one validate data at the beforeupdate sub-routine (or the beforeInsert sub-routine) & then handle the errors at the add, delete or save button function?
Any pointers would be of great help.
Thanks again
notworthy.gif
Jack Cowley
You are very welcome!
You do not need a save button as Access saves your data when you move off the record or close the form. (You do need a save button if your form is unboung, but I doubt that it is.) If you are getting errors on the other buttons then use the same code as I suggested earlier and you should be OK...
Good luck!
Jack
Jenny1982w
OK, I thought I'd post a follow-up. This a bug in Access 97 that crops up when using acNewRec, cancel = true or the cancel event in the beforeupdate event.
The MicroSoft Support article on this is at
http://support.microsoft.com/default.aspx?...kb;EN-US;128195
Thanks again Jack for all your help
Jenny
Jack Cowley
Jenny -
You are welcome and thank YOU for the article! May your project continue without any more problems!!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.