AfterUpdate code to change field values in current record only indatasheet view

  • Thread starter Thread starter RussCRM
  • Start date Start date
R

RussCRM

I have a list on a form in datasheet view similar to this:

Last First BedAssigned Breakfast Lunch Dinner
Bed All
Smith John 12 [X] [X]
[X] [X] [X]
Thomas Tom 7 [ ] [ ]
[X] [X] [ ]
Doe Jane 9 [X]
[ ] [X] [X] [ ]

When a user clicks on/off the All checkbox, I want to change the
values of Breakfast, Lunch, Dinner, & Bed accordingly. For instance,
when they click the All box, it sets all the others as checked and
turns them off when unchecked.

Here's what I have so far, but it ends up checking on/off all the
boxes in my datasheet, not just the ones for the current record. What
am I missing?

Private Sub chkAll_AfterUpdate()
If chkAll.Value = True Then
chkBreakfast.Value = -1
chkLunch.Value = -1
chkDinner.Value = -1
Else
chkBreakfast.Value = 0
chkLunch.Value = 0
chkDinner.Value = 0
End If
End Sub
 
I have a list on a form in datasheet view similar to this:

Last First BedAssigned Breakfast Lunch Dinner
Bed All
Smith John 12 [X] [X]
[X] [X] [X]
Thomas Tom 7 [ ] [ ]
[X] [X] [ ]
Doe Jane 9 [X]
[ ] [X] [X] [ ]

When a user clicks on/off the All checkbox, I want to change the
values of Breakfast, Lunch, Dinner, & Bed accordingly. For instance,
when they click the All box, it sets all the others as checked and
turns them off when unchecked.

Here's what I have so far, but it ends up checking on/off all the
boxes in my datasheet, not just the ones for the current record. What
am I missing?

Private Sub chkAll_AfterUpdate()
If chkAll.Value = True Then
chkBreakfast.Value = -1
chkLunch.Value = -1
chkDinner.Value = -1
Else
chkBreakfast.Value = 0
chkLunch.Value = 0
chkDinner.Value = 0
End If
End Sub
Click to expand...

Sounds to me like you're trying to store the data in the Form, not in a Table.
Forms don't contain data! Are these checkboxes bound to table fields, or just
unbound checkboxes on the form? The latter will NOT work.
 
John,

Since I'm only using the values of the checkboxes to run an action
query or macro, I don't really have a need/place to store them in a
table. Is there another way to do this? Basically, what I want
eventually is something like this

If the breakfast checkbox is checked, add a record to the Services
table like this:

ServiceID Last Name First Name ServiceType Bed
1 Smith Tom Breakfast

Or for a bed checkbox:
ServiceID Last Name First Name ServiceType Bed
2 Doe Jane Bed 9

Can I do this without another table since I don't want to keep this
data after I run the action query/macro?
 
John,

Since I'm only using the values of the checkboxes to run an action
query or macro, I don't really have a need/place to store them in a
table. Is there another way to do this? Basically, what I want
eventually is something like this

If the breakfast checkbox is checked, add a record to the Services
table like this:

ServiceID Last Name First Name ServiceType Bed
1 Smith Tom Breakfast

Or for a bed checkbox:
ServiceID Last Name First Name ServiceType Bed
2 Doe Jane Bed 9

Can I do this without another table since I don't want to keep this
data after I run the action query/macro?
Click to expand...

Probably the easiest thing would be to run separate Append queries in the
AfterUpdate event of each checkbox. In the "Breakfast" checkbox the query
would append a single record with Breakfast as the servicetype; in the All
checkbox it would append as many records as there are servicetypes.

You should certainly NOT store the name (first or last!) in this table of
services, just a PersonID linked to a table of service recipients!
 
John,

Thanks a lot for your help. In reality, I do have it set up that
way. On my list, I display the names just so the user knows who it
is, but in my table, I use a link to a "Guest" table.
 
Back
Top