Check boxes selected enters several responses

[Guest]

I'm trying to make a form where the user will get a list of check boxes and
can select as many as are appropriate and then this data is entered into a
field on the associated table.

e.g. Any combo of 5 people can enter data. The user needs to enter who
entered data on this day. For this day only two people entered data. User
checks those two people and their names are entered in the field. etc...

Thanks in advance.
Peter

 

[Jeff Boyce]

Peter

It isn't a particularly good design to stuff more than one fact into one
field. It sounds like you are trying to put multiple names in one field.

A more normalized design would be to use a table with date and person, and
put one row per person/date combination. Five people on 3/3/06 would result
in 5 rows.

You'll find this much easier to query ... for example, "show me the days on
which Peter Kappes was entered" is a simple query with the new table
structure, but more of a struggle to extract from somewhere within a text
string.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

I realize that this is poor design stuffing more than one fact into one
field, but this field is essentially a "notes" field that is only used to
determine who was working that day, in case we need to go back to their field
notebooks. We are discussing making a table with a key field "people worked
ID" that is unique for each combination, but as you probably realize this
quickly becomes an extremely large table as every new employee creates
multiple new combinations.

Otherwise, we need to enter multiple records (in some cases we will have
10-100's of combo's that would require being entered individually)... Unless
there is a way to automate that data entry (which is beyond my experience in
Access/VBA), this seems like a lot of work. Any suggestions?

 

[Jeff Boyce]

Peter

I'm not visualizing the table structure you mentioned.

Perhaps I didn't understand your situation. I thought you said your users
would be selecting which of 5 data entry people worked on a particular day.

A well-normalized way to store this is a table that has two fields --
PersonID and DataEntryDate.

The PersonID field holds the PersonID of the data entry person. The
DataEntryDate holds, well, the date the person did data entry.

If you want an easy-to-use user interface for entering this, think about how
the various wizards in Access work to help you build a query ... with paired
listboxes. One listbox holds the data entry folks available, the other is
for which ones worked on the particular day. The form could consist of a
"Fill in the Data Entry Date:" control, two listboxes, and a few buttons to
help the user move data entry folks back/forth between the listboxes.

Or am I still confused about your situation...?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Peter,
I'm assuming the "list of check boxes" you cite are in fact "any combo of 5
people who can enter data". So, I'm seing a list box of 5 names (instead of a
check box in front of each name). I'm also visualizing someone in charge of
reviewing the record and identifying the persons who entered data:
(Reference your "User checks those [two] people and their names are entered
in the field").

I suggest an unbound control as a list box on your form listing the names of
the 5 persons who normally enter data. Perhaps short names would be best,
such as J.Black, T.Holmes, B.Barker, etc.

The name of the unbound control might be "Staff_unbound".

In form design, create space to place the unbound control, perhpas a
vertical box where you list the five staff person names. You should already
have a text field in your underlying table that will be populated with the
names of the staff whom the "User" (Reviewer/Supervisor?) will select as
having worked on the record that day. This text field might be named,
"Staff_string".

In form design, click on the unbound control, Staff_unbound, then open the
Property Sheet and from the list of properties, activate (create) Event
Procedures for AfterUpdate and GotFocus. On the line, AfterUpdate [Event
Procedure], click on the right side button to display space for writing in
your Event Procedure code:

Private Sub Staff_unbound_AfterUpdate()
If IsNull (Staff_string) Then
Staff_string = Staff_unbound
Staff_unbound = Null

Elseif Not IsNull (Staff_string) Then
Staff_string = (Staff_String) & ", " & (Staff_unbound)
Staff_unbound = Null
End if
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


For the GotFocus Event Procedure, use the following:

Private Sub Staff_unbound_GotFocus()
Me.Staff_unbound.Requery
End Sub

If the User (Reviewer/Supervisor?) selects three of the five names of the
staff who worked on the record the same day, the Staff_string control on the
form (i.e. table field it is bound to) will display something like,
"D.Parker, T.Diem, S.Diego, etc."

If your form lacks space, you can use a combo box (drop down list) in lieu
of a vertical list that displays all five names. Of course, if your workers
log on to a network, there are methods to capture the workers' names
automatically using a similar Event Procedure coding.

Hope this is helpful,

Tank

 

[Guest]

Hi Jeff,

Yes, your description is exactly what would work! Essentially like all of
the access wizards moving fields to be included in objects! I think that
Tanks reply is describing the same thing, but I'm just learning VBA and I
think making these without the hand holding of a wizard is beyond my
abilities. Do you know if there is a wizard or someplace where I can see the
code for how to create the listbox/action button scenario you described? Or
would you mind checking out Tank's reply to see if that is what his code is
doing? Thanks a bunch.

Cheers,
Peter

 

[Guest]

Hi Tank,

This sounds a bit different from what Jeff was talking about, but
accomplishes the same thing! I'm in the process of learning VB, so thank you
for the code. However, I literally took a class last night dealing with If
Then statements, so this is extremely new and foreign to me. I created an
unbound list box on my form, with the names of the employees, and then
followed your directions copying/pasting the code (changing the names a wee
bit to fit my tables/forms) and when I go back to the form and click on a
name I get a Run time error 424: Object required. Not sure what the problem
is, but any suggestions would be great.

Thanks for you help

Peter,
I'm assuming the "list of check boxes" you cite are in fact "any combo of 5
people who can enter data". So, I'm seing a list box of 5 names (instead of a
check box in front of each name). I'm also visualizing someone in charge of
reviewing the record and identifying the persons who entered data:
(Reference your "User checks those [two] people and their names are entered
in the field").

I suggest an unbound control as a list box on your form listing the names of
the 5 persons who normally enter data. Perhaps short names would be best,
such as J.Black, T.Holmes, B.Barker, etc.

The name of the unbound control might be "Staff_unbound".

In form design, create space to place the unbound control, perhpas a
vertical box where you list the five staff person names. You should already
have a text field in your underlying table that will be populated with the
names of the staff whom the "User" (Reviewer/Supervisor?) will select as
having worked on the record that day. This text field might be named,
"Staff_string".

In form design, click on the unbound control, Staff_unbound, then open the
Property Sheet and from the list of properties, activate (create) Event
Procedures for AfterUpdate and GotFocus. On the line, AfterUpdate [Event
Procedure], click on the right side button to display space for writing in
your Event Procedure code:

Private Sub Staff_unbound_AfterUpdate()
If IsNull (Staff_string) Then
Staff_string = Staff_unbound
Staff_unbound = Null

Elseif Not IsNull (Staff_string) Then
Staff_string = (Staff_String) & ", " & (Staff_unbound)
Staff_unbound = Null
End if
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


For the GotFocus Event Procedure, use the following:

Private Sub Staff_unbound_GotFocus()
Me.Staff_unbound.Requery
End Sub

If the User (Reviewer/Supervisor?) selects three of the five names of the
staff who worked on the record the same day, the Staff_string control on the
form (i.e. table field it is bound to) will display something like,
"D.Parker, T.Diem, S.Diego, etc."

If your form lacks space, you can use a combo box (drop down list) in lieu
of a vertical list that displays all five names. Of course, if your workers
log on to a network, there are methods to capture the workers' names
automatically using a similar Event Procedure coding.

Hope this is helpful,

Tank

I'm trying to make a form where the user will get a list of check boxes and
can select as many as are appropriate and then this data is entered into a
field on the associated table.

e.g. Any combo of 5 people can enter data. The user needs to enter who
entered data on this day. For this day only two people entered data. User
checks those two people and their names are entered in the field. etc...

Thanks in advance.
Peter

 

[Guest]

Hi Tank,

I thought it might help for you to see the syntax I used:

Option Compare Database

Private Sub List4_AfterUpdate()
If IsNull(Staff_string) Then
Staff_string = List4
List4 = Null

ElseIf Not IsNull(Staff_string) Then
Staff_string = (Staff_string) & ", " & (List4)
List4 = Null
End If
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


Private Sub List4_GotFocus()
Me.List4.Requery
End Sub

When I Debug my error comes at the DoComd line, but am at a loss to what
this line is even doing. Sorry, but as I said I'm a greenhorn at this.

Peter,
I'm assuming the "list of check boxes" you cite are in fact "any combo of 5
people who can enter data". So, I'm seing a list box of 5 names (instead of a
check box in front of each name). I'm also visualizing someone in charge of
reviewing the record and identifying the persons who entered data:
(Reference your "User checks those [two] people and their names are entered
in the field").

I suggest an unbound control as a list box on your form listing the names of
the 5 persons who normally enter data. Perhaps short names would be best,
such as J.Black, T.Holmes, B.Barker, etc.

The name of the unbound control might be "Staff_unbound".

In form design, create space to place the unbound control, perhpas a
vertical box where you list the five staff person names. You should already
have a text field in your underlying table that will be populated with the
names of the staff whom the "User" (Reviewer/Supervisor?) will select as
having worked on the record that day. This text field might be named,
"Staff_string".

In form design, click on the unbound control, Staff_unbound, then open the
Property Sheet and from the list of properties, activate (create) Event
Procedures for AfterUpdate and GotFocus. On the line, AfterUpdate [Event
Procedure], click on the right side button to display space for writing in
your Event Procedure code:

Private Sub Staff_unbound_AfterUpdate()
If IsNull (Staff_string) Then
Staff_string = Staff_unbound
Staff_unbound = Null

Elseif Not IsNull (Staff_string) Then
Staff_string = (Staff_String) & ", " & (Staff_unbound)
Staff_unbound = Null
End if
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


For the GotFocus Event Procedure, use the following:

Private Sub Staff_unbound_GotFocus()
Me.Staff_unbound.Requery
End Sub

If the User (Reviewer/Supervisor?) selects three of the five names of the
staff who worked on the record the same day, the Staff_string control on the
form (i.e. table field it is bound to) will display something like,
"D.Parker, T.Diem, S.Diego, etc."

If your form lacks space, you can use a combo box (drop down list) in lieu
of a vertical list that displays all five names. Of course, if your workers
log on to a network, there are methods to capture the workers' names
automatically using a similar Event Procedure coding.

Hope this is helpful,

Tank

I'm trying to make a form where the user will get a list of check boxes and
can select as many as are appropriate and then this data is entered into a
field on the associated table.

e.g. Any combo of 5 people can enter data. The user needs to enter who
entered data on this day. For this day only two people entered data. User
checks those two people and their names are entered in the field. etc...

Thanks in advance.
Peter

 

[Jeff Boyce]

The code seems to be reasonable. I didn't notice a "feedback" mechanism for
the user to tell what was entered. This is what I was alluding to by
suggesting a pair of listboxes.

Now, once again for the record, stuffing more than one fact in one field is
not advisable, and may (may) come back to bite you. The more typical
approach to handling a one-to-many situation such as you are facing is to
use a "child" table to hold the "many".

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Peter,

You might try typing an apostrophe before the DoCmd in the Event procedure
code. This will block the line from running. The line will turn green when
the apostrophe is placed before the first word. In essence, you will be
skipping that line when the code runs. Perhpas the procedure will work
without that line.

Jeff emphasizes the many-to-one relationship that he sees in your attempts
to track all staff working on a single record in a given day. You are right
that such a table to capture the many to one relationship will grow rather
rapidly. The table he is referring to is simple, however. Two or three
fields are all you need, such as StaffID, RecordID, and perhaps the Date
staff work on the record. But the best use of such a table is for reports
which can be designed more effectivly using this many-to-one table. However,
in reading over your notes, you simply want to have a reference to the
working staff in a "notes" field --- a rather casual capture of staff names
to be eyeballed on occasion and used now and then more specifically if
absolutely needed: I am referring to your comments: "... but this field is
essentially a "notes" field that is only used to determine who was working
that day, in case we need to go back to their field notebooks."

Capturing two, three, four, or five names in one text field is not at all
unusual in meeting needs and minimzing or avoiding a heavier structure of
maintaining another table (many-to-one --- many staff working on one record).


A query, if needed, can sort out one or more staff persons by using in this
text field an expression, such as:

Like "*"&[Enter staff name]&"*"

The asterisks are wildcards, so the staff name can be anywhere in the text,
at the beginning, center, or at the end of your string text.

Hope this helps.
Tank

Hi Tank,

I thought it might help for you to see the syntax I used:

Option Compare Database

Private Sub List4_AfterUpdate()
If IsNull(Staff_string) Then
Staff_string = List4
List4 = Null

ElseIf Not IsNull(Staff_string) Then
Staff_string = (Staff_string) & ", " & (List4)
List4 = Null
End If
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


Private Sub List4_GotFocus()
Me.List4.Requery
End Sub

When I Debug my error comes at the DoComd line, but am at a loss to what
this line is even doing. Sorry, but as I said I'm a greenhorn at this.

Peter,
I'm assuming the "list of check boxes" you cite are in fact "any combo of 5
people who can enter data". So, I'm seing a list box of 5 names (instead of a
check box in front of each name). I'm also visualizing someone in charge of
reviewing the record and identifying the persons who entered data:
(Reference your "User checks those [two] people and their names are entered
in the field").

I suggest an unbound control as a list box on your form listing the names of
the 5 persons who normally enter data. Perhaps short names would be best,
such as J.Black, T.Holmes, B.Barker, etc.

The name of the unbound control might be "Staff_unbound".

In form design, create space to place the unbound control, perhpas a
vertical box where you list the five staff person names. You should already
have a text field in your underlying table that will be populated with the
names of the staff whom the "User" (Reviewer/Supervisor?) will select as
having worked on the record that day. This text field might be named,
"Staff_string".

In form design, click on the unbound control, Staff_unbound, then open the
Property Sheet and from the list of properties, activate (create) Event
Procedures for AfterUpdate and GotFocus. On the line, AfterUpdate [Event
Procedure], click on the right side button to display space for writing in
your Event Procedure code:

Private Sub Staff_unbound_AfterUpdate()
If IsNull (Staff_string) Then
Staff_string = Staff_unbound
Staff_unbound = Null

Elseif Not IsNull (Staff_string) Then
Staff_string = (Staff_String) & ", " & (Staff_unbound)
Staff_unbound = Null
End if
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


For the GotFocus Event Procedure, use the following:

Private Sub Staff_unbound_GotFocus()
Me.Staff_unbound.Requery
End Sub

If the User (Reviewer/Supervisor?) selects three of the five names of the
staff who worked on the record the same day, the Staff_string control on the
form (i.e. table field it is bound to) will display something like,
"D.Parker, T.Diem, S.Diego, etc."

If your form lacks space, you can use a combo box (drop down list) in lieu
of a vertical list that displays all five names. Of course, if your workers
log on to a network, there are methods to capture the workers' names
automatically using a similar Event Procedure coding.

Hope this is helpful,

Tank

I'm trying to make a form where the user will get a list of check boxes and
can select as many as are appropriate and then this data is entered into a
field on the associated table.

e.g. Any combo of 5 people can enter data. The user needs to enter who
entered data on this day. For this day only two people entered data. User
checks those two people and their names are entered in the field. etc...

Thanks in advance.
Peter

 

[Guest]

Hi Tank and Jeff,

Been overwhelmed with other issues, but still am trying to get this to work.
I tried running the script with the apostrophe, but it doesn't work. If I
run this I can click on different names in the list box, but nothing happens
or is activated and no data is entered into the underlying table. Not sure
what the problem is. Anyway, looking back at all of your comments maybe I
didn't explain my problem correctly, but I think it may present other
possible solutions.

We are collecting data everyday on a research project. Anywhere from 1-7
people can go out in a day and go to anywhere from 1- ~25 locations and
conduct 1-7 different tasks. Obviously, these combinations change almost
daily. I'm looking for a way to quickly and efficiently enter that data
every day without having to enter every single combination. So currently a
record looks like Date, three locations, four observers, 2 tasks. There are
some other information included like total number of observers and total
number of start and stop time, but we aren't breaking that down by time at
each spot. Its more a reference table to quickly see who was there, when,
how long, and what they did. So your ideas of two or three list boxes, where
the user can select any number of combinations would be ideal. That being
said, is there another way that I could present these options to a user on a
form that could let them enter all of this quickly and easily?

I'm still trying to figure out the code, but any suggestions would be
greatly appreciated. Thanks for all of your help so far.

Cheers,
Peter

Peter,

You might try typing an apostrophe before the DoCmd in the Event procedure
code. This will block the line from running. The line will turn green when
the apostrophe is placed before the first word. In essence, you will be
skipping that line when the code runs. Perhpas the procedure will work
without that line.

Jeff emphasizes the many-to-one relationship that he sees in your attempts
to track all staff working on a single record in a given day. You are right
that such a table to capture the many to one relationship will grow rather
rapidly. The table he is referring to is simple, however. Two or three
fields are all you need, such as StaffID, RecordID, and perhaps the Date
staff work on the record. But the best use of such a table is for reports
which can be designed more effectivly using this many-to-one table. However,
in reading over your notes, you simply want to have a reference to the
working staff in a "notes" field --- a rather casual capture of staff names
to be eyeballed on occasion and used now and then more specifically if
absolutely needed: I am referring to your comments: "... but this field is
essentially a "notes" field that is only used to determine who was working
that day, in case we need to go back to their field notebooks."

Capturing two, three, four, or five names in one text field is not at all
unusual in meeting needs and minimzing or avoiding a heavier structure of
maintaining another table (many-to-one --- many staff working on one record).


A query, if needed, can sort out one or more staff persons by using in this
text field an expression, such as:

Like "*"&[Enter staff name]&"*"

The asterisks are wildcards, so the staff name can be anywhere in the text,
at the beginning, center, or at the end of your string text.

Hope this helps.
Tank

Hi Tank,

I thought it might help for you to see the syntax I used:

Option Compare Database

Private Sub List4_AfterUpdate()
If IsNull(Staff_string) Then
Staff_string = List4
List4 = Null

ElseIf Not IsNull(Staff_string) Then
Staff_string = (Staff_string) & ", " & (List4)
List4 = Null
End If
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


Private Sub List4_GotFocus()
Me.List4.Requery
End Sub

When I Debug my error comes at the DoComd line, but am at a loss to what
this line is even doing. Sorry, but as I said I'm a greenhorn at this.

Peter,
I'm assuming the "list of check boxes" you cite are in fact "any combo of 5
people who can enter data". So, I'm seing a list box of 5 names (instead of a
check box in front of each name). I'm also visualizing someone in charge of
reviewing the record and identifying the persons who entered data:
(Reference your "User checks those [two] people and their names are entered
in the field").

I suggest an unbound control as a list box on your form listing the names of
the 5 persons who normally enter data. Perhaps short names would be best,
such as J.Black, T.Holmes, B.Barker, etc.

The name of the unbound control might be "Staff_unbound".

In form design, create space to place the unbound control, perhpas a
vertical box where you list the five staff person names. You should already
have a text field in your underlying table that will be populated with the
names of the staff whom the "User" (Reviewer/Supervisor?) will select as
having worked on the record that day. This text field might be named,
"Staff_string".

In form design, click on the unbound control, Staff_unbound, then open the
Property Sheet and from the list of properties, activate (create) Event
Procedures for AfterUpdate and GotFocus. On the line, AfterUpdate [Event
Procedure], click on the right side button to display space for writing in
your Event Procedure code:

Private Sub Staff_unbound_AfterUpdate()
If IsNull (Staff_string) Then
Staff_string = Staff_unbound
Staff_unbound = Null

Elseif Not IsNull (Staff_string) Then
Staff_string = (Staff_String) & ", " & (Staff_unbound)
Staff_unbound = Null
End if
DoComd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub


For the GotFocus Event Procedure, use the following:

Private Sub Staff_unbound_GotFocus()
Me.Staff_unbound.Requery
End Sub

If the User (Reviewer/Supervisor?) selects three of the five names of the
staff who worked on the record the same day, the Staff_string control on the
form (i.e. table field it is bound to) will display something like,
"D.Parker, T.Diem, S.Diego, etc."

If your form lacks space, you can use a combo box (drop down list) in lieu
of a vertical list that displays all five names. Of course, if your workers
log on to a network, there are methods to capture the workers' names
automatically using a similar Event Procedure coding.

Hope this is helpful,

Tank



:

I'm trying to make a form where the user will get a list of check boxes and
can select as many as are appropriate and then this data is entered into a
field on the associated table.

e.g. Any combo of 5 people can enter data. The user needs to enter who
entered data on this day. For this day only two people entered data. User
checks those two people and their names are entered in the field. etc...

Thanks in advance.
Peter