Access 2003 add new record when i go to enter data it shuts down

I have built an access database that is giving me alot of problems. when I add a new record and when I go to enter data it shuts down the application. I have vb code also to copy a record and it does find you can change any data in it. I am using the new record on the navigation bar. I have rebuilt the database and still get the error.  any suggestions.
PAMHARTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rick_RickardsCommented:
Is it shut down the application while your code is being run?  If so can you post the code?
0
PAMHARTAuthor Commented:
The copy code works fine and you can change data on it.
I don't have any code for a new record. It shuts down when i go to enter data on the new record. What code do I need to show? Is there a way I can upload my zip file of the database?
0
Rick_RickardsCommented:
There is a way to zip and upload you're application if needed although I'm only familiar with downloading those files people post, haven't actually posted one myself.  I suspect there is help in the support area on how to do this.  I have to leave right now but if you haven't found it by the time I get back I'll see if I can find it.

To clarify, is it closing the db the moment you advance to a new record?  If so the code that might be most relevant would be the Form's OnCurrent Event.  On a new record nothing else would fire unless the form was being opened at the same time in which case the following events and code would run.

OnOpen
OnLoad
OnCurrent
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PAMHARTAuthor Commented:
I don't have anything on those events for the form. I do have an after update event to run a macro to run a query. I have some calculations coming from the subform I thought the null value maybe giving me the problem so I did an if statement for null to be zero but I still get the error.
I can advance to a new record it closes when you go to enter your data. I have checked every field in the underlay table and I can't find anything. Do you have an email address so  I can send you my zip file? The attach file will not let me attach it.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can't directly send email to Experts; it's a violation of your member agreement.

You can, however, zip and post your file to www.ee-stuff.com, then post a link back here for that download. This way all Experts have a chance to review and respond to your question.
0
PAMHARTAuthor Commented:
Ok, Thanks
Here is the link
https://filedb.experts-exchange.com/incoming/ee-stuff/6735-KSD.zip 

I hope i did it right.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You need to decompile your application. To do that, build a shortcut with the Target like this:

"full path to msaccess.exe" "full path to you .mdb file" /decompile

After doing that, open the VB edit window and click Debug - Compile. Then, back at the main Access window, click Tools - DAtabase Utilities - Compact and Repair.

0
Rick_RickardsCommented:
Running the app on this end I have yet to get it to crash but after looking at the code I have a number of suggestions.  If you're paitient and the question remains open I'll see if I can't post them for you before the day is out.  I think you'll find the revised code a lots simpler, compact and reliable.  You'll also find several techniques I think you'll find usefull the other forms as well.

Meanwhile some food for thought would be to remove the use of the Requery method on the following events.  In the first case it serves it is out of place.  The AfterInsert event fires as soon as a new record is created but before it has actually be saved.  In the 2nd case, the After Update Event will acknoledge any new record created once that event fires so unless you have other users in the application that may have added records while you adding the one just saved there would be no need to requery the form.

Private Sub Form_AfterInsert()
    Me.Requery
End Sub

Private Sub Form_AfterUpdate()
    Me.Requery
End Sub

I'll have more for you latter.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Mine crashed immediately ... that odd, wonder why.
0
Rick_RickardsCommented:
Interesting that one might crash while mine does not.  If it helps I ran it from a machine with 4 versions of office 97, 2K, 2002 and 2003 installed complete with all the latest service packs.  Even so I did see several things in the code that could invite problems hence my hope to post another version when time permits.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I've got the same, except I've also got 2007. I do NOT have the 2003 SP3 installed, however ... other than that, all mine are up to date.

0
PAMHARTAuthor Commented:
Thanks you all!
I will wait for your simple code. I am new at building databases and coding.
I really appreicate the help.
0
Rick_RickardsCommented:
Here's the revised code for you're Main Form Named...   Operation Header Form
Option Compare Database
Option Explicit
 
Private Sub copyBtn_Click()
    Dim strSQL As String
    Dim strSQLFields As String
    Dim db As DAO.Database
    Dim rstS As DAO.Recordset   'Recordset Source      '
    Dim rstD As DAO.Recordset   'Recordset Destination '
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim lngCount As Long
    Dim lngSourceID As Long
    Dim lngKeyID(1) As Long     'Key for the Source and Destination - It's a Long Array with 2 elements lngKey(0) and lngKey(1) '
    
    If Me.Dirty Then Me.Dirty = False
    If Me.NewRecord Then
        MsgBox "Can not copy a new record", vbCritical, " Copy Canceld"
    Else
        lngKeyID(0) = Me.ID.Value   'ID is the Primary Key - in this case the value of the original record '
        
        Set db = CurrentDb
        strSQL = "SELECT [Operations Header].* FROM [Operations Header] WHERE ID = " & lngKeyID(0) & ";"
        Set rstS = db.OpenRecordset(strSQL, DAO.dbOpenSnapshot)                  'Recordset of  the Source  (original) record - Snapshot as we only need to look at it's data   '
        strSQL = "SELECT [Operations Header].* FROM [Operations Header];"
        Set rstD = db.OpenRecordset(strSQL, DAO.dbOpenDynaset, DAO.dbAppendOnly) 'Recordset for the Copy (Destination) record - Append only as we only intend to add data to it '
        
        rstD.AddNew                     'Proceed to add 1 record to the Destination recordset '
        For Each fld In rstS.Fields     'Loop through every field in the Source Recordset     '
            If fld.Name <> "ID" Then    'Skip the ID field as it's the AutoNumber Primary Key '
                rstD.Fields(fld.Name).Value = rstS.Fields(fld.Name).Value   'Copy each fields Value from the Source (rstS) to the  Destination (rstD) '
            End If
        Next fld
        lngKeyID(1) = rstD.Fields("ID").Value   'The Autonumber will automatically be assigned - before saving the record capture it here - we'll use it latter'
        rstD.Update 'Now we can save the destination record '
        
        'What follows is just cleanup '
        rstD.Close
        rstS.Close
        Set rstD = Nothing
        Set rstS = Nothing
        Set db = Nothing
        'Done with the cleanup....    '
        
        'Now we copy Child records displayed in the sub form if there are any   '
        If Me.[Operations Details SubForm].Form.RecordsetClone.RecordCount Then
            Set tdf = db.TableDefs("Operations Details")
            For Each fld In tdf.Fields
                If fld.Name <> "ID" Then
                    strSQLFields = strSQLFields & ", [" & fld.Name & "] "
                End If
            Next fld
            strSQLFields = Mid(strSQL, 2)
            
            strSQL = "INSERT INTO [Operations Details](ID, " & strSQLFields & ") " & _
                     "SELECT " & lngKeyID(1) & " AS ID, " & strSQLFields & " " & _
                     "FROM [Operations Details] " & _
                     "WHERE [ID] = " & lngKeyID(0) & ";"
            CurrentDb.Execute strSQL
        Else
            'Note: even if child records are not copied the Parent still is.
            MsgBox "Main record duplicated, but there were no related records.", vbExclamation, "No related records to copy"
        End If
        
        'Now that everything is copied move to the new (copied) record'
        Me.Requery  'Requery main form to insure it sees the record added earlier - child records will be requeried once focus moves to the new record '
        Me.Filter = "ID = " & lngKeyID(1)
        Me.FilterOn = True
            
        'As long as we don't exceed the field lenght we'll suffix the Operation_Number field with "-1" as the original code did.       '
        'Note: this doesn't guaruntee that the copy's Operation_Number is unique.  Added only to be consistent with the original code. '
        With Me.Operation_Number
            If Len(.Value) < 48 Then .Value = .Value & "-1"
        End With
        Me.Dirty = False    'having changed the value of 1 field we save immediately at which point the copy is done                   '
 
    End If
End Sub
 
Private Sub CreateNewCustomer_Click()
    Application.RunCommand acCmdRecordsGoToNew
End Sub
 
Private Sub Find_Work_Order_Click()
    If Me.FilterOn Then Me.FilterOn = False
    Me.Operation_Number.SetFocus
    Application.RunCommand acCmdFind
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rick_RickardsCommented:
Here's the revised code for your Sub Form named.... Operations Details SubForm

Note: There is a possible problem you could have within this form's code.  If the subroutine named ConfigCtlLength() is run from the Forms OnCurrent Event and the focus of the sub form is on the Control Named "Length" the code could crash if the Operation Code was Not Like "S*".

In short, anytime you disable a control it's essential that you insure that the application's focus is not already on the control.  Even if the code does error out, however, it shouldn't be so catastrophic as to cause the application to close.  One thing you might consider to prevent yourself from ever encountering this error is to establish control over the focus of the sub form when the OnCurrent Event Runs.  You could do this like so...

Private Sub Form_Current()
    Me.Operation_Code.SetFocus
    ConfigCtlLength
End Sub

Though this isn't the only way one can approach this problem I thought you should be aware of the issue.  As for the execution of ConfigCtlLength() from the AfterUpdate Event of Operation_Code you need not worry there as that code will not run unless the Control named Operation_Code already has the focus.

Another issue was the "update sams" Macro being run from the Form's AfterUpdate Event and AfterDeleteConfirm Event.  There are two issues.  The most serious was that it tried to execute 2 queries the first causes the user to be prompted (this can be avoided by using code as seen in the last 3 procedures) and the 2nd issue was the execution of a 2nd query that didn't exist (this causes an error).  

Once again, nothing in the code quite explains why the application would simply crash/close entirely, however, using the code provided here I wouldnt be surprised if the problem went away.

As for the "Update Sams" macro, it probably can be deleted if it is not being used elsewhere.

Though there is plenty more we could review I'm reluctant to go on further as that would risk information overload.  As for getting the application to run w/o crashing/closing mysteriously and ungracefully I believe you'll find the code above and below accomplish that end.  If not, let me know where it leaves us and I'll take another stab at it.
Option Compare Database
Option Explicit
 
Private Sub Form_Current()
    ConfigCtlLength   'Note you may want to run a line of code as follows before this one...   Me.Operation_Code.SetFocus     See notes in post above for explanation. '
End Sub
 
Private Sub Operation_Code_AfterUpdate()
    ConfigCtlLength
End Sub
 
Private Sub ConfigCtlLength()
    If Me.Operation_Code Like "S*" Then      'If Sew code
        Me.Length.Enabled = True
        Me.Length.Locked = False
        Me.Length.TabStop = True
    Else
        'If focus in on Length we'll need to move focus elsewhere '
        Me.Length.Enabled = False             'IF not sew code
        Me.Length.Locked = True                 'Disable Editing
        Me.Length.TabStop = False
    End If
End Sub

Open in new window

0
Rick_RickardsCommented:
Just noticed the last 3 procedures for the sub form "Operations Details SubForm" were missing.  Posted below.  These were the procedures that eliminate the need for the macro named "update sams"

Private Sub Form_AfterUpdate()
    RunUpdateQuery
End Sub
 
Private Sub Form_AfterDelConfirm(Status As Integer)
    RunUpdateQuery
End Sub
 
Private Sub RunUpdateQuery()
    CurrentDb.QueryDefs("update pfd_Handling in Operation Details").Execute
End Sub

Open in new window

0
PAMHARTAuthor Commented:
Thank you so much!
You are awesome. This will help me alot.
0
PAMHARTAuthor Commented:
Thanks so much Rick this worked and I learned alot from it.
0
Rick_RickardsCommented:
My pleasure to be of help.

Rick
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.