Access showing subdatasets in a table view

[Infofetish]

I am new to Access and ma just getting into creating tables that relate to each other. so the issue I have is that I have multiple subdatasets that relate to one table and I want to be able to see a drop down of each of these datasets in the one table. how can you do that- it seems to make you select one and then it uses that one all the time- I don't see what the easy way to go and make that change is. Help???

 

[boblarson]

First off it is subdatasheet, not subdataset. Second, subdatasheets are a related table based on relationships that have been set. However, as you have discovered, only one subdatasheet can be used per table. The same subdatasheet can be referenced from multiple related tables, but not vice-versa.

Personally, I do not use subdatasheets because they involve working directly in the tables, which I would classify as not included in good practices. Users should not be interfacing directly with queries or tables as you do not have control over what they can and can't do when they do this. And that can put your data integrity at risk. At least while using forms you can steer them in the correct direction, you can validate input much better (and with more user friendliness) than simply using validation rules in tables.

So anyway, I just wanted to chime in on it like that. Plus I've observed, that in certain circumstances, subdatasheets can be the source of some database slowdown when you are using the database in a multi-user environment.

 

[Infofetish]

So I think I understand you issue with them...sorry for miss naming them- as I mentioned I am a novice.

I think what your saying is that if I want to show the subdatasheets in the table view them I have to make copies of the inital table and change the subdatasheets to reference the ones I want in each copy. Do I thme make a relationship between all related fields in the copies of the tables?

How do you changte the referenced subdatasheet in the new table. I can't seem to figure it out?

 

[Infofetish]

well now I don't know what to say- I deleted a bunch of relationships to fix a few things and now my sub datasheet is not showing the data it should? I am thinking I may have to rebuild the database??

 

[boblarson]

1. Sorry it took so long to get back to you but I don't always visit on the weekends. So, no you should NOT add tables or anything for subdatasheets.

2. You should NOT use subdatasheets. Build forms and stay out of the tables.

Is that a bit more clear?

3. You shouldn't have to rebuild but you will likely have to redo your relationships. But again, I say - stay out of the tables for input - so do not use subdatasheets. Create forms to interface with the data. Working in tables gives you very LITTLE control over what happens and what gets entered or even allows someone to change the design. So use forms, use forms, use forms.