Main form, edit value on Subform (all records) HELP!

[morfusaf]

OK,

So I have a 3 Tables, Events, People, PeopleAtEvents

I have a form to create people(works great)
I have forms to create Events - formCreateEvent, On this form I have a subform so that you can add which people attended this event- subformPeopleAtEv

If the event is a certain distance(like over 50 miles away) it is called a long distance trip, where the member will get paid extra (Perdiem). To get paid extra they have to do travel Vouchers....

So on the formCreateEvent I have a checkbox to select Long distance or not, if it is longdistance it brings up(makes visible) a combo box, as well as 2 things in the Subform... (DTS_complete, and DTS_comments).

My problem is, on the DTS complete I have 3 available options on the combobox.... N/A (this is the Default and what I want to be stored in the event that the trip is not long distance).... COMPLETE and NOT COMPLETE... so i can later run reports to see if the members have completed thier paperwork to get paid.

What I want to happen is, if the trip is not longdistance, then DTS_Complete = N/A and not visible... (I got the visible/notvisible part to work.

The problem I have is when I add more than 1 person (I click next record on the subform) to the event. and I make a change, then decide oh wait it wasn't actually a long distnace and go back to uncheck the longdistancetrip checkbox.... the code I have is to set the DTS_Voucher = N/A.... however, it only sets the currently visible record(so which ever person is showing.... ) I need it to be able to set DTS_Voucher = N/A to all the people's records ( on that event). that I add on the subform....

The code I am currenlty using is below.


I have a checkbox with the following code. (also have this code in the Current so when form is opened for updates).

Private Sub LongDistanceTrip_AfterUpdate()
     If Me!LongDistanceTrip = False Then
 
     Me!cboPerdiem.Value = Null
     Me!cboPerdiem.Visible = False
     Me!lblPerdiem.Visible = False
     Me.SubformPeopleatEv!DTS_Complete.Visible = False
     Me.SubformPeopleatEv!DTS_Comments.Visible = False
     Me.SubformPeopleatEv!DTS_Complete = "N/A"
 
    ElseIf Me!LongDistanceTrip = True Then
 
     Me!cboPerdiem.Visible = True
     Me!lblPerdiem.Visible = True
     Me.SubformPeopleatEv!DTS_Complete.Visible = True
     Me.SubformPeopleatEv!DTS_Comments.Visible = True
    End If
End Sub

 

[jzwp22]

I personnally would get rid of the N/A choice in your combo box since it is the event (via the checkbox) that governs whether a person is eligible to get paid. Having N/A is redundant with the checkbox.

The reason only the first record is updated is because it is the current record. You will have to run an update query to update all records in the subform's record source.

 

[VilaRestal]

This should do it I think:

Private Sub LongDistanceTrip_AfterUpdate()
     Dim b As Boolean
     b = Me!LongDistanceTrip
     Me.cboPerdiem.Visible = b
     Me.lblPerdiem.Visible = b
     Me.SubformPeopleatEv.Form.DTS_Complete.Visible = b
     Me.SubformPeopleatEv.Form.DTS_Comments.Visible = b
     If Not b Then
         Me!cboPerdiem.Value = Null
         CurrentDB.Execute "UPDATE PeopleAtEvents SET DTS_Complete = 'NA' WHERE EventID = " & Me!EventID
         Me.SubformPeopleatEv.Form.Requery
     End If
End Sub

(Although I'm guessing at the EventID field. Perhaps you've called either something else - they are the join fields between Events and PeopleAtEvents)

But I do agree with jzwp. The NA option is redundant.