CODEWITHSUNDEEP
ms-access
Sean McKenzie
Sean McKenzie

Reputation: 909

FormOpen Event procedure not triggered with command button, but trigged when switching from design to form view in MS Access

I have an MS Access database with a set of forms to enter vegetation data for a large monitoring project. I have one form called frmTransect with a button that opens a second form called frmLPI which is set up as an unbound main form with a subform called frmLPIDetail bound to a sql server database table. The main form has just two unbound fields, DataObs and DataRec, both of which are comboboxes. These two field are set up with an AfterUpdate event procedure to populate their corresponding fields in the subform, Data_observer and Data_recorder. This works perfectly. I wanted to have the unbound fields autopopulate with the last value in the subform of Data_observer and Data_recorder when the form is lauched again. To do this I used a FormOpen event procedure. Below is the code:

Private Sub Form_Open(Cancel As Integer)

Me.TransectOID = Me.OpenArgs
Dim rs As DAO.Recordset
Set rs = Me!frmLPIDetail.Form.RecordsetClone

If rs.RecordCount > 0 Then

If Not rs.BOF Then
rs.MoveLast
rs.MovePrevious
End If

If Not IsNull(rs!Data_recorder.Value) Then
        Me.DataRec.Value = rs!Data_recorder.Value
        Me.frmLPIDetail.Form.Data_recorder.DefaultValue = """" & Me.DataRec.Value & """"
End If

If Not IsNull(rs!Data_observer.Value) Then
        Me.DataObs.Value = rs!Data_observer.Value
        Me.frmLPIDetail.Form.Data_observer.DefaultValue = """" & Me.DataObs.Value & """"
End If
        rs.MoveLast
        rs.MoveFirst
    
    While Not rs.EOF
        rs.Edit
        rs!Data_recorder.Value = Me.DataRec.Value
        rs!Data_observer.Value = Me.DataObs.Value
        rs.Update
        rs.MoveNext
    Wend
End If
rs.Close
Set rs = Nothing
Me.Dirty = False
End Sub

Here is where things get weird. When I click the command button on frmTransect, frmLPI opens, but the FormOpen event procedure above doesn't get launched. However, if I switch into design view, and then back into Form View, it does trigger, and works as intended! How can I get this event procedure to launch when I open the frmLPI using the command button in frmTransect? Any help would be much appreciated.

Upvotes: 0

Views: 156

Answers (1)

Sean McKenzie
Sean McKenzie

Reputation: 909

And it turned out it was as simple as adding a Me.Refresh and Me.Requery to my code block:

Private Sub Form_Open(Cancel As Integer)

Me.TransectOID = Me.OpenArgs
Me.Refresh
Me.Requery

Dim rs As DAO.Recordset
Set rs = Me!frmLPIDetail.Form.RecordsetClone

If rs.RecordCount > 0 Then

rs.MoveLast
rs.MovePrevious

If Not IsNull(rs!Data_recorder.Value) Then
        Me.DataRec.Value = rs!Data_recorder.Value
        Me.frmLPIDetail.Form.Data_recorder.DefaultValue = """" & Me.DataRec.Value & """"
End If

If Not IsNull(rs!Data_observer.Value) Then
        Me.DataObs.Value = rs!Data_observer.Value
        Me.frmLPIDetail.Form.Data_observer.DefaultValue = """" & Me.DataObs.Value & """"
End If
        rs.MoveLast
        rs.MoveFirst
End If
rs.Close
Set rs = Nothing
Me.Dirty = False

End Sub

Upvotes: 0

Related Questions