Solved Closing a continuous form after double clicking on the last record (1 Viewer)

Sam Summers

Registered User.
Local time
Today, 16:30
Joined
Sep 17, 2001
Messages
939
Hi Sam,

The form gets the right value for the HazardID but the second form opens blank because of its record source is not updatable. You don't need all of those tables, just have a look at the update file. Please note that I also deleted the very tiny controls in the upper left, I suggest in the future you just set the Visible Property to No and the background to a color that reminds you that is a hidden control.

Cheers,
Vlad
Thank you Vlad, i will take a look. I am slowly learning with the help of you guys on the forums
 

Sam Summers

Registered User.
Local time
Today, 16:30
Joined
Sep 17, 2001
Messages
939
Hi Sam,

The form gets the right value for the HazardID but the second form opens blank because of its record source is not updatable. You don't need all of those tables, just have a look at the update file. Please note that I also deleted the very tiny controls in the upper left, I suggest in the future you just set the Visible Property to No and the background to a color that reminds you that is a hidden control.

Cheers,
Vlad
Hi Vlad,
Based on your version i now have it fully working apart from the doubleclick from the subform on the form 'HazardList' which works but i keep getting the message - "You cant go to the specified record"
I have tried so many things and different fixes online but still the same message!
This is the code i am now using:-

Code:
Private Sub Form_DblClick(Cancel As Integer)
On Error GoTo Err_Form_DblClick

    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardID] = " & Me.HazardID
    
If Not Me.NewRecord Then
    DoCmd.OpenForm "SelectAffected", _
        WhereCondition:="HazardID=" & Me.HazardID
End If
    
    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then
        DoCmd.Close acForm, "HazardList", acSaveYes
    Else
        DoCmd.GoToRecord Record:=acNewRec
       ' DoCmd.GoToRecord Record:=Me.NewRecord
    End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
    MsgBox Err.Description
    Resume Exit_Form_DblClick
End Sub
 

bastanu

AWF VIP
Local time
Today, 09:30
Joined
Apr 13, 2010
Messages
1,401
Sam,
Please explain in simple words (not code) what are you trying to achieve in the double-click event (review my comments in your code below).
Code:
On Error GoTo Err_Form_DblClick

    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardID] = " & Me.HazardID
  
If Not Me.NewRecord Then
    DoCmd.OpenForm "SelectAffected", _
        WhereCondition:="HazardID=" & Me.HazardID 'Vlad: So if you are in an Existing record open SelectedAffected on this record -already opened by previous Docmd.OpenForm line
End If
  
    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then 'Vlad: if you are on the last record close the form and save design changes
        DoCmd.Close acForm, "HazardList", acSaveYes 'Vlad: acSaveYes saves design changes for the form, not the form's data
    Else
        DoCmd.GoToRecord Record:=acNewRec 'Vlad: if you are on any other record than last open a new record on SelectedAffected form as that is the one with the focus
       ' DoCmd.GoToRecord Record:=Me.NewRecord
    End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
    MsgBox Err.Description
     Resume Exit_Form_DblClick

Cheers,
 

Sam Summers

Registered User.
Local time
Today, 16:30
Joined
Sep 17, 2001
Messages
939
Sam,
Please explain in simple words (not code) what are you trying to achieve in the double-click event (review my comments in your code below).
Code:
On Error GoTo Err_Form_DblClick

    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardID] = " & Me.HazardID
 
If Not Me.NewRecord Then
    DoCmd.OpenForm "SelectAffected", _
        WhereCondition:="HazardID=" & Me.HazardID 'Vlad: So if you are in an Existing record open SelectedAffected on this record -already opened by previous Docmd.OpenForm line
End If
 
    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then 'Vlad: if you are on the last record close the form and save design changes
        DoCmd.Close acForm, "HazardList", acSaveYes 'Vlad: acSaveYes saves design changes for the form, not the form's data
    Else
        DoCmd.GoToRecord Record:=acNewRec 'Vlad: if you are on any other record than last open a new record on SelectedAffected form as that is the one with the focus
       ' DoCmd.GoToRecord Record:=Me.NewRecord
    End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
    MsgBox Err.Description
     Resume Exit_Form_DblClick

Cheers,
Hi again,

I implemented changes where your comments are but this one could be where the issue is:- DoCmd.GoToRecord Record:=acNewRec 'Vlad: if you are on any other record than last open a new record on SelectedAffected form as that is the one with the focus

I want to go to the next record on the current subform 'HazardListSubform' where the code is running from
 

bastanu

AWF VIP
Local time
Today, 09:30
Joined
Apr 13, 2010
Messages
1,401
Hi Sam,
Maybe try this:
Code:
On Error GoTo Err_Form_DblClick

    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardID] = " & Me.HazardID
'
'If Not Me.NewRecord Then
'    DoCmd.OpenForm "SelectAffected", _
'        WhereCondition:="HazardID=" & Me.HazardID 'Vlad: So if you are in an Existing record open SelectedAffected on thhis record -already opened by previous Docmd.OpenForm line
'End If
    
    
'    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then 'Vlad: if you are on the last record close the form and save design changes
'        DoCmd.Close acForm, "HazardList", acSaveYes 'Vlad: acSaveYes saves design changes for the form, not the form's data
'    Else
        
        Dim rs As DAO.Recordset, h, t
        With Me
      
            Set rs = .RecordsetClone
            rs.Bookmark = .Bookmark
            rs.MoveNext
            If Not rs.EOF Then 'If not last record
                .Bookmark = rs.Bookmark
            Else
                'last record
                Set rs = Nothing
                DoCmd.Close acForm, "HazardList"
            End If
            Set rs = Nothing
        End With
       ' DoCmd.GoToRecord Record:=acNewRec 'Vlad: if you are on any other record then last open a new record on SelectedAffected form as that is the one with the focus
       ' DoCmd.GoToRecord Record:=Me.NewRecord
    'End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
     MsgBox Err.Description & " " & Err.Number
    
     Resume Exit_Form_DblClick

Cheers,
 

Sam Summers

Registered User.
Local time
Today, 16:30
Joined
Sep 17, 2001
Messages
939
Hi Sam,
Maybe try this:
Code:
On Error GoTo Err_Form_DblClick

    DoCmd.OpenForm "SelectAffected", acNormal, , "[HazardID] = " & Me.HazardID
'
'If Not Me.NewRecord Then
'    DoCmd.OpenForm "SelectAffected", _
'        WhereCondition:="HazardID=" & Me.HazardID 'Vlad: So if you are in an Existing record open SelectedAffected on thhis record -already opened by previous Docmd.OpenForm line
'End If
   
   
'    If Me.Form.CurrentRecord = Me.Form.RecordsetClone.RecordCount Then 'Vlad: if you are on the last record close the form and save design changes
'        DoCmd.Close acForm, "HazardList", acSaveYes 'Vlad: acSaveYes saves design changes for the form, not the form's data
'    Else
       
        Dim rs As DAO.Recordset, h, t
        With Me
     
            Set rs = .RecordsetClone
            rs.Bookmark = .Bookmark
            rs.MoveNext
            If Not rs.EOF Then 'If not last record
                .Bookmark = rs.Bookmark
            Else
                'last record
                Set rs = Nothing
                DoCmd.Close acForm, "HazardList"
            End If
            Set rs = Nothing
        End With
       ' DoCmd.GoToRecord Record:=acNewRec 'Vlad: if you are on any other record then last open a new record on SelectedAffected form as that is the one with the focus
       ' DoCmd.GoToRecord Record:=Me.NewRecord
    'End If

Exit_Form_DblClick:
    Exit Sub

Err_Form_DblClick:
     MsgBox Err.Description & " " & Err.Number
   
     Resume Exit_Form_DblClick

Cheers,
Thats it! At last. Thank you SO much
 

Users who are viewing this thread

Top Bottom