We help IT Professionals succeed at work.

Record is deleted error when adding new record

lmgregg
lmgregg asked
on
Medium Priority
279 Views
Last Modified: 2012-05-12
I have a form that lists all purchase orders with a button to add a new Purchase Order.  

Sometimes this causes Access to crash and the new PO record is corrupted - all fields show #Deleted.

Is there a problem with the following code or a better way to add a new record?  The [PO] field is required and is the primary key.

On Error GoTo Err_newb_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim NextCount As Integer
   
    Me.FilterOn = False
    Me.OrderByOn = True
    Me.OrderBy = "PO"
    DoCmd.GoToRecord , , acLast
    NextCount = 1 + [PO]
   
    DoCmd.GoToRecord , , acNewRec
    [PO] = NextCount

    Refresh
   
    stDocName = "PO (Form)"
   
    stLinkCriteria = "[PO]=" & Me![PO]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_newb_Click:
    Exit Sub

Err_newb_Click:
    MsgBox Err.Description
    Resume Exit_newb_Click
Comment
Watch Question

Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Why not have just this to go to the new record position ...

Private Sub newb_Click()

DoCmd.GoToRecord , , acNewRec

Me![PO] = DMax("[PO]","YourTableName") + 1

End Sub

Then ... to get the Next PO number ...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Ignore my last sentence ...

mx

Author

Commented:
Thanks, mx - I'll give your code a try..

A little more info:

The app is split into 3 dbs - a front end which resides on users workstations + 2 backend dbs containing the tables which on are a network server.

Other forms use similar code to create new records without any problems.

Could this be a network issue?  The crash seems to happen to only 1 user but that may be because he is usually the only one who creates POs.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Well, sure it could be a network issue or otherwise ... kind of hard to troubleshoot random occurrences.

Just seems like a lot of code to go to a new record and grab the next PO number.

mx

Explore More ContentExplore courses, solutions, and other research materials related to this topic.