Adding multiple check boxes in a form

N

Naqibullah

Registered User.
Local time
Today, 04:18
Joined
Feb 7, 2013
Messages
86
Dear colleagues i have searched the web alot but couldn't find my answer,
Let me clear what problem i faced,
i have a form named Member, this form has field named hobbies, this field has multiple values selection i.e. a user could select football, cricket, listening to music and etc. these values are stored in another table and i have looked up the values in my form named member, i know access has multiple value option in combo box while we look up values from another table, but i don't want that the check boxes appear in combo box, they should be listed right in form so that i can see them without going inside the combo box and at the same time the values of these populated check boxes should be stored in a single field
please help
Regards
 
Naqibullah said:
... and at the same time the values of these populated check boxes should be stored in a single field
Click to expand...
Why - storing multiply values in one field is a bad idea?
 
thanks for your quick response,
this is not a multiple value field values, but it is only one field having list of options from which a user can select multiple values, but all those values related to one field say Hobbies, because a person may have more than one hobbies
 
Sorry - I can't follow you here - please explain/show some sample data.
 
ok sure,
in my form there is a field named select your hobbies, under this field we have multiple option such as Cricket, football, volleyball, soccer, baseball and etc. so the user may select cricker, football and soccer as his hobbies, of course these values should be stored in access table under hobbies, i know that access has a lookup wizard which allows us to select multiple values in one field, but access brings these check boxes in a combo box you select combo box then check the required options then you press ok, this is not what i want, what i want is, the check boxes should be right on form so there should be no need of going and opening the combo box on form and then selecting check boxes, hope i clarified the issue
 
It is a little tricky, but I've made a small sample for you, open the form "tblMember".
 

Attachments

thanks alot,
your answer was pin to the point, but is there any way that we could populate the access lookup wizard with multiple values on our form so that it should not be in the form of combo box (we have to click on the combo box then select values and then press ok) rather it should be right on the form as a list of check boxes, exactly same like the one you have created for me using multiple tables and many to many relationship.
Regards
 
I can read what you write, but my brain can't "create the picture" of what you exactly mean, so can you post a printscreen of what you want, (and what you have)!
 
Hmm - the only think I get is the database I send you.
 
I did a project a few years ago for a customer which had a subform, which listed something similar. In your particular case this would list the hobbies, so I will now substitute hobbies for my customers list.

The hobby would be added by creating a new record in the subform by clicking on a combobox and selecting a new hobby.

This was okay but when the list became very long it was difficult to see at a glance which hobbies were in the list.

So I created a pop up form which showed the entries; now displayed on a tab control as check boxes.

I think it would be possible to place the check boxes directly on your form however I can't see why you would take up all this extra form real estate when a pop up works just as well.

Incidentally I don't think I have this code immediately available, it may well be on one of my old hard drives but where it is I'm not sure!

I didn't complete the development of it because you needed to place check boxes on the tab control in advance. The problem was, you never had any idea how many checkboxes you needed.

I did think of making multiple tabs alphabetically a to z and dividing the hobbies amongst those tabs.
I also considered having an extra tab where the operator could store the most frequent entries, then just by pressing a button, have the most frequent entries added to the list. In fact you could have more than one default "frequent entries" one for male, one for female, one for student, etc. However the project was one of those that got more and more complicated and involved, so I stopped before I went mad. It did work and I may well pursue it in the future.
 
Oh... sorry for the inconveniance.... please find the attached
 

Attachments

  • 2.png
    2.png
    22.7 KB · Views: 111
  • 1.jpg
    1.jpg
    94.6 KB · Views: 112
Sorry I am not able to help you, there are some contradictions in what you want and what is possible to do without a bigger programming work.
Luck with you project.
 
thanks for your great job, but when i started to use this method (the database you sent me) i found some vb coding that i couldn't understand, if it is possible please send me commented coding so that i could understand...
Regards
 
i started to design my database as per the one you sent me but i came across some functions you used in that check boxes like after check box update, and on current event of the form, can you please elaborate those functions as i couldn't understand
regards
 
Naqibullah said:
i started to design my database as per the one you sent me but i came across some functions you used in that check boxes like after check box update, and on current event of the form, can you please elaborate those functions as i couldn't understand
regards
Click to expand...

You have a table holding all the available hobbies, name "tblHobbies".
You also use this table to show which hobbies a certain member has when you cycle through the members. For this you use the field "CheckedHobbies".
Each time you go to another member the form's "On Current" event is triggered.
Therefore when you "go" to another member set the field "CheckedHobbies" to false. If it is not a new member, then aftewards set the field "CheckedHobbies" to True, for the hobbies the member has and the requery the subform, (the subform's recordsource is table "tblHobbies").
Code: 
Private Sub Form_Current()
  CurrentDb.Execute "UPDATE tblHobbies SET CheckedHobbies = False "
  If Not Me.NewRecord Then
    CurrentDb.Execute "UPDATE tblHobbies INNER JOIN tblMemberHobbies ON tblHobbies.HobbiesName = tblMemberHobbies.HobbiesName SET CheckedHobbies = [Hobbies] " _
    & "WHERE MemberNo=" & Me.MemberNo
    Me.Query2.Requery
  End If
End Sub
Each time you change the hobbies for a member, then write the changes to the table "tblMemberHobbies".
First delete all the hobbies a member has, (it is much easier as first check if a member already has the selected hobby or not), then afterwards write back to the table "tblMemberHobbies", all the selected hobbies a member has.
Code: 
Private Sub Hobbies_AfterUpdate()
  Me.Refresh
  CurrentDb.Execute "DELETE * FROM tblMemberHobbies " _
  & "WHERE MemberNo =" & Me.Parent.MemberNo
  CurrentDb.Execute "INSERT INTO tblMemberHobbies ( HobbiesName, Hobbies, MemberNo) " _
  & "SELECT tblHobbies.HobbiesName, tblHobbies.CheckedHobbies," & Me.Parent.MemberNo & " AS Expr1 " _
  & "FROM tblHobbies " _
  & "WHERE CheckedHobbies=True"
End Sub
 
You must log in or register to reply here.

Similar threads

J
Auto-Filling 2 Form Fields
2
Replies
32
Views
667
Gasman
Gasman
C
Combo boxes
Replies
1
Views
250
pbaldy
pbaldy
V
Missing Value in combo Box while Opening another form using openArgs
Replies
3
Views
316
Uncle Gizmo
Uncle Gizmo
J
Combo Box to filter and Allow Edits
Replies
4
Views
287
Pat Hartman
P
A
Solved Access Checkboxes choosing their own alignment
Replies
16
Views
518
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom