I know this has been discussed plenty of times here but I have searched for an answer as to why this is a bad idea with no luck. Microsoft actually has a tutorial on how, why, and when to use multiple values at table level. I simply want a combo box on my form that allows me to pick multiple projects which a component is used on. My inventory screen allows user to create a new part. It is so simple to just make a "Project" field in my "Inventory" table which allows multiple values. Then my form would show this as a combo box with muliple values which would allow users to check individual boxes for each project the component is used on. My query criteria for report X would then be display components if "projects" contains X. Then I could copy/paste forms and change the X to Y or Z and my reports all stay simple and easy. Can anyone tell me why this is a bad idea and if so, how to accomplish my goals otherwise. I have tried to make a junction table with just the projects listed but I can't figure out how to select multiple project on my form. I don't really want to use a subform if I don't have to. Thanks for any input.
Multiple Values at Table Level (1 Viewer)
- Thread starter Demetious
- Start date
- Local time
- Today, 12:03
- Joined
- Jul 9, 2003
- Messages
- 16,303
I refer you to Allen Browne’s website in particular Microsoft Access Tips for Serious Users Provided by Allen Browne. Last updated April 2010. VBA Traps: Working with Recordsets
Read this and then if you have any more questions please post them.
10. Nested recordsets
Read this and then if you have any more questions please post them.
10. Nested recordsets
I found a post that Bob posted back in 2001 that sums it up for me. Now to figure out how to do it using subforms. Thanks guys.
http://www.mvps.org/access/lookupfields.htm
http://www.mvps.org/access/lookupfields.htm
- Local time
- Today, 12:03
- Joined
- Jul 9, 2003
- Messages
- 16,303
There is certain amount of confusion here which I would like to clear up.
When you said “Multiple Values” I assumed you meant “multi-value fields”. However when I look at your reply, I’m thinking you mean when a Lookup field in a table displays a looked-up value, not the actual value stored in the underlying table.
These are two entirely different cases.
I can’t really speak for multi-value fields because I am a developer of the Clan of “Many professional developers who avoid the complex data types”
However I can speak for a lookup fields in a table, these are not a problem I use them all the time.
The only issue with these looked-up fields is that the table can be set to display the related text and not the actual number that is stored in the table. This (the automatic displaying of the “wrong value” ) is very confusing for anyone new to programming in MS Access.
When you said “Multiple Values” I assumed you meant “multi-value fields”. However when I look at your reply, I’m thinking you mean when a Lookup field in a table displays a looked-up value, not the actual value stored in the underlying table.
These are two entirely different cases.
I can’t really speak for multi-value fields because I am a developer of the Clan of “Many professional developers who avoid the complex data types”
However I can speak for a lookup fields in a table, these are not a problem I use them all the time.
The only issue with these looked-up fields is that the table can be set to display the related text and not the actual number that is stored in the table. This (the automatic displaying of the “wrong value” ) is very confusing for anyone new to programming in MS Access.
