(IMG:style_emoticons/default/pullhair.gif) I have literally spent hours trying to figure out this problem and it's probably not that complicated to solve except I don't know VBA code and I'm easily confused (IMG:style_emoticons/default/wink.gif)

Ok here goes:

I'm working on a database for my (very) small organization to track who is who and who does what. I have my who table set up, I have my what table set up, I have a junction table that combines the two. So tblpeople, tblroles, tblpeopleroles. I've made a form to enter the contact information for the people. What I NEED to do is be able to set up a way to choose the various roles for each person, (they each may have more than one) and have it feed to that junction table. I'm kind of lost on the best way to do this using the normalized method, i.e. not multivalue field.

I have explored using the multivalue field, but there are things about it I don't like. For one thing, it looks like I'd have to add roles to my people table which I'm not sure is a good idea. So I'm good with using a junction table instead to store peopleid & roleid. What I miss about the mvf is the sweet little combo box with checkboxes.

I really want to get the hang of this making a selection box on a form for entering NOT just view data because I can see applications further along in my design.

Part of the problem I've run into in getting help is the answers all involve just being able to view data already there and I need to add and/or enter new data.

Oh, I'm in Access 2010

Thanks

Hi,

The usual approach looks something like this:

1. Create a form (main) based on the People table
2. Create a form (subform) based on the Junction table
3. Use a Combobox in the subform for the Roles based on the Roles table
4. The main form is set as a Single Form, while the subform is set as Continuous Form (or Datasheet)

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

EDIT: If you like the checkbox GUI, you will need some VBA. Take a look at Noah's demo in the Code Archive.

This post has been edited by theDBguy: Apr 17 2012, 10:48 AM

Wow! Thanks for answering so quickly! I was right - I was missing the obvious. Ok, now how do make this multiselect? I tried using a listbox instead of a combobox but for some reason it's not adding the record to the junction table.

Well, I got a listbox to work but I sure don't like how it looks or feels. It just seems clunky to have to keep moving down a row and clicking to pop up the whole list again instead of seeing the whole list and picking, check, check. If that makes sense? This will need to be the most friendly and simple to use UI I can build for my end users. I've looked at the link you gave, and the one using a temporary table might be what I need, but the code scares me. Since I know so little code, I'm hesitant to try and adapt this to my needs.
Any suggestions about how to design the listbox so it's less "clunky" would be appreciated or any other suggestions.
Thanks again

Hi,

It's been a while since I looked at the demo but didn't it also have a Listbox as an option?

Just curious...

Yes, it does have three different listbox options. I've saved the file so I can rummage around in there and see what I can do with it. I like the look of the 3rd option which uses a temporary table. Oh how I wish I could read the VBA language behind this so I could really understand what is going on and learn from it.
(IMG:style_emoticons/default/smile.gif)

Yeah, I thought so. If you're pressed for time, you may have to live with using the simpler form/subform setup with a Combobox until you can upgrade the design to one that uses a more complicated piece of code.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Well I've been working with linked file using the form for using unbound checkboxes to build what I want because it looks and works like exactly what I want. So far what I've done is to make my db exactly like the sample one except I've changed table/field names where I needed to. I purposely kept these changes as simple and minimal as possible so mostly it's just changing State to Role etc. I've copied the code from the on current into my form on current and then gone through and carefully changed only the names where needed. Actually doing this has been a great way for me to understand the code better. I have a pretty good sense of what it's actually doing, though the language is still somewhat mysterious. Anyway, it seems to be working, at least I'm not getting any error messages now but...for some reason the checkboxes and labels are not showing on my form and nothing I do or change seems to make a difference. Grrr...I have the sample saved and open so I can switch back and forth checking every possible element and everything is exactly the same. This is driving me crazy any help appreciated!

Hi,

I think the best way to help you is to see what you got. Are you able to post a zip copy of your db with test data?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Wait - hold the phone! I got it to work!!!!! (IMG:style_emoticons/default/woohoo.gif)
I went back through the whole thing piece by piece and though I'm still not sure what I "fixed" it worked.
Thanks for the help - I'm sure I'll be back
(IMG:style_emoticons/default/smile.gif)

Hi,

Congratulations! I'm glad to hear you got it sorted out.

(IMG:style_emoticons/default/yw.gif)

Good luck with your project.

Ok, I'm back (IMG:style_emoticons/default/smile.gif)
I'm wondering how to alter the code so that my checkbox/labels do NOT get hidden. I can see why it would be nice when you have a huge list like the 50 states to only have what you need showing, but my list is at most 10 items long, so having them showing all the time on each record is not a problem. I want to open to a new form for entering a new person so I put a macro to open to a new record in the on load and I get my lovely form BUT the list is hidden.

Hi,

I'm afraid I don't understand. I don't see anywhere in the demo that labels and checkboxes get hidden.

What did you end up doing?

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

I haven't gotten it figured out yet.

I think it might be here:
'enable or disable all the checkboxes
'if a new record, Not Me.NewRecord = False
'if not a new record, Not Me.NewRecord = True
EnableDisableCheckBoxes Not Me.NewRecord
'Hide all checkboxes so that only those needed will be displayed
HideAll

or here
Private Sub HideAll()
Dim x As Integer

For x = 1 To 60
Me("chk" & x).Visible = False
Me("chk" & x).Value = False
Me("lbl" & x).Visible = False
Next x
End Sub

Hmm, okay, I think I see what you mean now. Try commenting out these two lines and see what happens:

'EnableDisableCheckBoxes Not Me.NewRecord

and

'HideAll

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

(IMG:style_emoticons/default/iconfused.gif) That didn't work. Could the problem lie in where I'm putting the event. It's a simple findrecord new macro I put in the on load event of the form.
I sure appreciate you taking time to help me.

This post has been edited by ginmarie: Apr 24 2012, 11:48 AM

Hi,

I think I understand what you mean now. Uncomment the two lines from my earlier post, we need them back. Then, look for the text in bold below and move it where I have it showing:

'Now populate the check boxes accordingly
'Open a recordset of all items for this person
If Not Me.NewRecord Then
strSQL = "Select StateID From tblPeopleStates Where PeopleID = " & Me.PeopleID
Set rs = CurrentDb.OpenRecordset(strSQL)

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

(IMG:style_emoticons/default/woohoo.gif) That worked! THANK YOU!!!!
Wish I could take a class from you (IMG:style_emoticons/default/smile.gif)

Hi,


(IMG:style_emoticons/default/yw.gif)

Glad to hear you got it to work! Good luck with your project.

You've been so helpful I hope it's okay to just continue this thread with my new, though related question. I've been trying to figure out how to open this form as new so I can add a new record. I've tried adding a macro to the on open event, and it does open blank so I can fill in all the contacts info, but... two of the role labels change to a lighter text, and more importantly, my check boxes are dead. That is, I can't check any of them. I'm sure it has too do with the code in the on current, and what I'm asking it to do which applies to the current record which is of course empty. What do I need to add or edit to accomplish this?