projects database save listbox selections

[taya621]

Hello Z!
Thank you so much! I googled how to do that and this is what I found:



The default file format for both Access 2010 and Access 2007 is the .accdb file format. In fact, when you create a new database in Access 2010, the file format displayed in the title bar is Access 2007. As a result, you can use Access 2007 to open a database that was created in Access 2010 without first using the Save As command. However, Access 2010 includes some new features that require Access 2010 to run. Depending on the feature, one of the following behaviors might occur when you try to use the database in Access 2007:

• You cannot modify the object that uses the new feature.
• You cannot open the object that uses the new feature.
• You cannot open the database in Access 2007 at all.

For a complete listing of the Access 2010 features that can cause compatibility problems with Access 2007, see the article Backward Compatibility between Access 2010 and Access 2007.

Can you not open the file??

[jzwp11]

I was able to open the file, but got a message similar to what you posted. I was able to see the tables, but I did not see any relationships in the relationship window. I was not sure whether you were using any of the new features, so I stopped there. I can take another look at it this evening when I get home. I only have Access 2003 here at work--way behind the times here!!

[taya621]

Lol JZWP11,
I think I'm the one behind the times not understanding Access like you do! Do you mind if I show you some things through pictures and you can walk me through a few things. It is just driving me crazy that I can't figure out how to save my selections to somewhere, I feel like i'm missing something so easy. I've attached a picture of the relationships I have. Also, a picture of the listboxes that users can choose employees to put into workgroups. I can't get the final selection on the right to save to anything and I want it to go into a column in my projects table.

What do you think? Is there any hope for me? Thanks!!!

[jzwp11]

I'm not sure how the double list box functionality works since I have not used that before, but I found an example database on Roger Carlson's website that appears to be similar to what you need. The example databases are movelist1.mdb and movelist2.mdb . The code involved will need some modification in order to work for you since you have to tie the people and the project together in t_ProjectEmployees.

BTW, I would also consider creating a new table that holds the choices for your "What is the opportunity for..." field of your project table and then just reference the foreign key in the project table

tblOpportunityChoices
-pkOppChoiceID primary key, autonumber
-lngOppChoiceNo
-txtOppChoiceDesc

lngOppChoice would be a number field that holds the number while txtOppChoiceDesc holds the text.

[jzwp11]

Since I had never done the double list box functionality, I thought that I would give it a shot. I've attached the database I came up with; hopefully it will help you out.

[taya621]

Hey JZ,
that looks great, I can't believe you just did that real quick.
That's exactly what I need except I want to save who was assigned to that project in a column in the projects table so that I can eventually make a report that shows the new project + the people that were assigned to.... isthat possible?

[jzwp11]

Since many people are assigned to a project, that describes a one-to-many relationship which requires a separate table which you already have (T_ProjectEmployees). Having the project employees again in the project table violates normalization rules.

To show the employees assigned to a project in a report, you would construct a query using your 3 tables and then use that as the record source for the report. I've modified my example database to include such a query and the report. The DB is attached.

[taya621]

Oh... now I see. That's fantastic. I was trying to make saving the selections part of the form's functions but I didn't even think of a query, duh!
Ok so, if your up for it, I'm wondering how I can insert checkboxes by each project and somehow allow the user to sign up for the checkboxed projects that way... or must it be done through a form?

[jzwp11]

I'm not sure I understand what you are asking exactly; it sounds like you want your users to get into your tables and check a box. Your users should never have access to your tables. They should only interact with the data using forms.

If I understand correctly, it sounds like you can use the same double list box concept. You would have available projects on the left and projects already assigned to that employee on the right. The user can then select which projects from the left and move them to their assigned projects.

[taya621]

Jz,
No you're right I don't want my users to access the tables. The listbox concept is a possibility but I would like to them to be able to view (much like in a report) the current projects, read their description and deadline and other members assigned, etc and then they can decide if they want to sign up for that. I though a checkbox would be the most convenient and then when they are done selecting their projects, they can press ok or something and be signed up and added to the assign members of that project... does that make sense?

[jzwp11]

I would think that a form/subform design would be best. The main form would be based on the project table while the subform would be based on the project employee table. The subform would be in datasheet view and would use a combo box from which the employee could select their name. Since you have the table relationships already set up, Access should link the subform to the main form automatically.

[taya621]

Hey J,
I've been wrestling with this for a while and I can't seem to get it the way I want. Do you have an example?

[jzwp11]

I've modified my earlier DB; it is attached. You'll want to look at the form: frmProjectsWithProjectEmployees

[taya621]

Oh I see... but if the employee wanted to change the other employees on the group, they could...right?

[jzwp11]

Yes, technically the could. Do you not want everyone to have that capability? If that is the case, then you could make the form un-editable, but then you would have to give the person a way to add themselves. This could be accomplished by adding a button to run an append query, but the query would need to know who the person is. I would suggest a combo box, but then the person could add someone other then themself. The other option is to identify the user when they open the database (i.e. a login form) and base the append query on the user.