Link to home
Create AccountLog in
Avatar of Sacha Walter
Sacha WalterFlag for Canada

asked on

Access 2010 Moving to next record in a subform

Hi Experts,

I have a form with tabs.  On one of the tabs called 'History' I have a subform with master and child fields linked by 'id'.

Every time a user updates a selection on the main form, I would like to record the user's action in the history tab.  The code below works, but is not sequential.  i.e. the subform only shows the last action.  If the user made one selection, I would like it to record in the subform junction table, but if the user then made a different selection, I would like another record to populate the junction table...in effect showing the history of the user's updates.

My code is as follows (I don't show all of the cases but they are similar to the ones detailed below):

Private Sub Status_Stage_1_BeforeUpdate(Cancel As Integer)
Select Case Me.Status_Stage_1
   Case 0
       Me.Confirmation_Stage_1 = 1111
       Me.Action_Required_Phase_1 = "Yes"
       Me.[2_History_Sub_Form].Form.user_action_id = 12
       Me.[2_History_Sub_Form].Form.user_action_type_id = 0
       
    Case 4
       Me.Confirmation_Stage_1 = 1111
       Me.Action_Required_Phase_1 = "Yes"
       Me.[2_History_Sub_Form].Form.user_action_id = 12
       Me.[2_History_Sub_Form].Form.user_action_type_id = 4
             
   Case 3
       Me.Confirmation_Stage_1 = 1111
       Me.Action_Required_Phase_1 = "Yes"
       Me.[2_History_Sub_Form].Form.user_action_id = 12
       Me.[2_History_Sub_Form].Form.user_action_type_id = 3
       
       Me.Status_Stage_2 = 3
       Me.Confirmation_Stage_2 = 3333
       Me.Action_Required_Phase_2 = "No"
       
       Me.Status_Stage_3 = 3
       Me.Confirmation_Stage_3 = 3333
       Me.Action_Required_Phase_3 = "No"
         
End Select
       Me.[2_History_Sub_Form].Form.LogonID = fOSUserName()
       Me.[2_History_Sub_Form].Form.TimeStamp = Now()
       Me.[2_History_Sub_Form].Form.Refresh
End Sub

How do I get the junction table to log the next entry?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Bitsqueezer
Bitsqueezer
Flag of Germany image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Avatar of Sacha Walter

ASKER

Awesome thank you!