Form Does Not Return when Changes Made - Instead Locks up Database

[Pierkes]

Hi Twinnyfo,

Thank you very much for replying. Tried your suggestion and it now returns from the frm_traject_det ails to frm_trajecten but the the program does not react to anything anymore.

The only thing i can then do is stop the program and restart it...


Any suggestions on how to proceed?

[twinnyfo]

Form Does Not Return when Changes Made - Instead Locks up Database

Hmmm.... That sounds like something more than what we began with!

It sounds like there is a problem with the form frm_trajecten.

Does the system hang immediately upon going to frm_trajecten? You can step through the code to determine exactly what point the system is hanging....

[zmbd]

Form Does Not Return when Changes Made insteads Locksup Database

[[Edit]]
1) Split from thread

http://bytes.com/topic/access/answers/958359-error-opening-form-based-record-nr

[[Edit]]

Posted code original message line 26 duplicates line 29.
Delete Line 26
-
Lines 5 thru 12 are still encodeing for a Macro, I'll have to think about that
-
Copy Line 2 over the current line 13 to re-enable the error trapping.
-
Please run menu>debug>comp ile
(refer to the following link for basic trouble shooting steps and how to set your options (^_^) > Before Posting (VBA or SQL) Code )
>>> You will have to repeat the compile step until it returns no errors as it stops on the very first error found.

[Pierkes]

Hi Twinnyfo and ZMB,

Thanks for helping me.
I changed the code with your suggestions ZMBD and it now looks like;

Private Sub tr_naam_traject_Click()
On Error GoTo tr_naam_traject_Click_Err
    On Error Resume Next
    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If (MacroError.Number <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
        Exit Sub
    End If
    On Error GoTo tr_naam_traject_Click_Err
    DoCmd.OpenForm "frm_traject_details", acNormal, "", "[ID_traject]=" & Nz(ID_traject, 0), , acDialog
    If (Not IsNull(ID_traject)) Then
        TempVars.Add "CurrentID", ID_traject.Value
    End If
    If (IsNull(ID_traject)) Then
        TempVars.Add "CurrentID", Nz(DMax("[ID_traject]", Form.RecordSource), 0)
    End If
    DoCmd.Requery ""
    DoCmd.SearchForRecord , "", acFirst, "[ID_traject]=" & TempVars!CurrentID
    TempVars.Remove "CurrentID"

    Exit Sub

tr_naam_traject_Click_Err:
    MsgBox Error$
    Exit Sub

End Sub

The code makes sure i go to the form [frm_traject_det ails].
When i do not change anything in that form and go back to de form [frm_trajecten] it works fine and i can then select another record to go to by clicking the hyperlink (which is the name of the client).

However, when i change anything on the form [frm_traject_det ails], the program goes back to the form frm_trajecten and when it displays the form, it completely freezes. Only way out then is shut down ms access and start it again.

Now what ? Any suggestions are very welcome !

For your info, here are all the events of the form frm_trajecten;

Private Sub Form_Load()
Me.cmb_AM = "*"
Me.cmb_soort_traject = "*"
Me.cmb_resultaat = "5"
Me.cmb_ad = "*"
Me.cmb_prodjr = "*"
Me.cmb_holding = "*"
Me.cmb_dam = "*"
Me.cmb_ORG = "*"
Me.Requery
End Sub

Because it is a split form, i use the upper part to give the user ways to select the correct data that is then displayed in the lower part of the form. In the lower part of the for, the names of the clients (with hyperlinks) etc. will appear.

Private Sub Form_Open(Cancel As Integer)
OpenAllDatabases True
End Sub

To make sure the backed databases are opened.

Private Sub Form_Close()
OpenAllDatabases False
End Sub

This is the module;

Sub OpenAllDatabases(pfInit As Boolean)
  ' Open a handle to all databases and keep it open during the entire time the application runs.
  ' Params  : pfInit   TRUE to initialize (call when application starts)
  '                    FALSE to close (call when application ends)
  ' Source  : Total Visual SourceBook

  Dim x As Integer
  Dim strName As String
  Dim strMsg As String
 
  ' Maximum number of back end databases to link
  Const cintMaxDatabases As Integer = 2

  ' List of databases kept in a static array so we can close them later
  Static dbsOpen() As DAO.Database
 
  If pfInit Then
    ReDim dbsOpen(1 To cintMaxDatabases)
    For x = 1 To cintMaxDatabases
      ' Specify your back end databases
      Select Case x
        Case 1:
          strName = "M:\AIPENS\SATDbe.accdb"
      End Select
      strMsg = ""

      On Error Resume Next
      Set dbsOpen(x) = OpenDatabase(strName)
      If Err.Number > 0 Then
        strMsg = "Trouble opening database: " & strName & vbCrLf & _
                 "Make sure the drive is available." & vbCrLf & _
                 "Error: " & Err.Description & " (" & Err.Number & ")"
      End If

      On Error GoTo 0
      If strMsg <> "" Then
        MsgBox strMsg
        Exit For
      End If
    Next x
  Else
    On Error Resume Next
    For x = 1 To cintMaxDatabases
      dbsOpen(x).Close
    Next x
  End If
End Sub

Any ideas where things go wrong ?
Thanks very much for thinking with me !
Pierkes

[zmbd]

1) Split from thread

http://bytes.com/topic/access/answers/958359-error-opening-form-based-record-nr

2) It is very importaint that we understand what you are doing here:

Because it is a split form,

The term split form when it comes to ACC2007 and newer refers to: http://office.microsoft.com/en-us/ac...010075994.aspx
What this does is displays the SAME recordset in two ways.
The first way is as an individual record.
The second way is as a datatable.
Usually this form is created by the splitform wizard.... is this how you created the form?

3) Insert a stop command in your "Sub tr_naam_traject _Click" code between lines 2 and 3.

Run your form as normal and see if you can get the application to "lock"

Most likely the debuger will open with that stop command highlighted. Using the [F8] function key, "step" slowly thru your code until you get to the line that appears to "lock" your database.

[Pierkes]

Hi zmbd,

Tried your suggestion but i do not get an error message when starting the form frm_traject_det ails.

However, i saw that when the frm_traject_det ails is opened, the main form frm_trajecten is "locked" or "not accassible" the form's name tab is less highlighted, indicating you cannot work with the form.

When frm_traject_det ails is closed, frm_trajecten should be accessible again, but it is not.

How can i make sure, that frm_trajecten is accassible again.
(te be clear, the program does not seem to lock up, but the form frm_trajecten still thinks another form is open so i cannot work with it ?)

I hope this is a little more clear and you can help me ?,
Pierkes

[zmbd]

1) Please take a momement to read #2 of my last post.
Are you using a true split form as decribed therein?

2)For form {frm_trajecten}

Private Sub Form_Close() 
OpenAllDatabases False 
End Sub

Place a STOP command before the call to OpenAllDatabase s
Run as you would and see if this code is executing after you open the form {frm_traject_de tails} - it shouldn't be.

3) Have you provided all of the code behind {frm_traject_de tails} that refers to {frm_trajecten} ?

[Pierkes]

Hi zmbd,

Turns out that i was looking in the wrong place....ouch !

On the frm_traject_det ails form i have a button to close the form and go back to the frm_trajecten form.

This was the code;

Private Sub cmdClose_Click()
On Error GoTo cmdClose_Click_Err

    On Error Resume Next
    
    If Me![Status Res] <> 5 And Me![Soort traject] < 6 And Me![chk_reden] = False Then
        MsgBox ("Het resultaat van dit traject is " & [Status Res].Column(1) & ", geef aan wat de reden(en) hiervoor zijn aub!")
        Me![Redenen van winst of verlies].Form![cmb_reden_cat].SetFocus
    Cancel = True
    Exit Sub

    End If
    
    If (Form.Dirty) Then
       DoCmd.RunCommand acCmdSaveRecord
    End If
    
    If (MacroError.Number <> 0) Then
      Exit Sub
    End If
    If Not Me.Dirty Then
    DoCmd.Close , ""
    End If

cmdClose_Click_Exit:
    Exit Sub

cmdClose_Click_Err:
    MsgBox Error$
    Resume cmdClose_Click_Exit

End Sub

Turns out that the if statement caused the trouble.
No i moved the if statement to the beforUpdate event;

Private Sub Form_beforeupdate(Cancel As Integer)
DoCmd.Echo True
Dim ctl As Control

If Me![Status / Resultaat] <> 5 And Me![soort traject] < 6 And Me![chk_reden] = False Then
MsgBox ("Het resultaat van dit traject is " & [Status / Resultaat].Column(1) & "," & vbCrLf & "geef aan (in het tabje 'Status en resultaat' wat de redenen hiervoor zijn aub!")
Me![Redenen van winst of verlies].Form![cmb_reden_cat].SetFocus
Cancel = True
Exit Sub
End If

DoCmd.Echo False
End Sub

Now it generates a nice message, sets focus to the right control and when the form eventually closes, there is no problem anymore...

pfff....what a journey.
Thank you very much for your help !

Pierkes