CODEWITHSUNDEEP
ms-accessvbams-access-2010
paratrooper
paratrooper

Reputation: 465

Trigger an event while navigating to next record

I need to set the record source of a particular dropdown in a form based on the current record i.e when I click the next record, the record source should get updated.

But I'm unable to find any event for doing so. This needs to be done without adding any extra/new buttons.

I tried using the "Form Current" event but apparently it gets triggered only once when the form is loaded, and not when I move to the next record within the same form.

Any suggestions?

Sample Code:

Private Sub Form_Current()

 MsgBox ("hello")

 Dim strSQL As String

 strSQL = "SELECT DISTINCTROW StudentTeachingStatusTbl.ID, StudentTeachingStatusTbl.SuccessStatus FROM StudentTeachingStatusTbl WHERE Active = -1" & _
        "or ID IN (Select SuccessID from StudentTeachingExperiencesTbl where StuId = " & Me.Text2.Value

 If Not IsNull(Me.StudentTeachExp_SubF.Controls("SemesterID")) Then
    strSQL = strSQL & " and SemesterID = " & Me.StudentTeachExp_SubF.Controls("SemesterID").Value
 End If

 strSQL = strSQL & ")"

 Me.StudentTeachExp_SubF.Controls("SuccessID").RowSource = strSQL               
 End Sub

Upvotes: 2

Views: 11120

Answers (2)

Minty
Minty

Reputation: 1626

Looking at your code you are changing a sub form record source, so are you changing record on the subform or the main form?

If it's the sub form you need to use the subforms OnChange event, not the parent forms event.

Upvotes: 1

robv
robv

Reputation: 66

The "On Current" event is triggered when the form focuses on a new record. This does happen when opening a form as a consequence the form having a record on it.

The "On Load" and "On Open" are triggered once when opening the form.

Try adding a MsgBox to your "On Current" event code for testing purposes.

Good Luck.

Upvotes: 3

Related Questions