By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,251 Members | 2,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,251 IT Pros & Developers. It's quick & easy.

General Form and Command button design

P: 12
Hi all

I am trying to design a database that holds contact/demographic information on a person and what studies they have participated in. I have a few ideas, but wanted to get feedback on whether I am going about this the wrong way (I've seen these designs before, but I think the design may be over my head and there may be a simpler way to do things...) Any input is greatly appreciated! Please feel free to direct me to other posts if someone else has already figured this out (I couldn't find any information in my search)

Each subject will have a form with demographic information. There is then a WhichStudy table which holds information on the studies the subject has participated in and the date they enrolled. Each study will then have it's own table with the relevant fields for that study.

Idea 1. The WhichStudy form is a subform on the demographics page. The study/ies the person has participated in are selected through a drop-down menu. This is a continuous form, so any number of studies can be selected. A command button then would open the form relevant to the study selected from the drop-down menu. I could not figure out how to get the button to open the specific form selected.

Idea 2. The demographics form on one tab, and the Studies forms a second tab. There would be a button for each study which would open a subform on that page with the relevant fields for that study. This would eliminate my problem in Idea 1 above where I cant' get a button to figure out which form to open since each form would have its own button, but I cant' figure out how to make only the relevant subform visible and all others invisible in the same space.

Idea 3. A separate tab for each study - I know how to do this, but I don't like that all the studies are visible. A person will probably only participate in 1-2 studies, so having 15 irrelevant tabs is not ideal.

I understand that Idea 3 is the simplest option, but I will put in the leg-work to figure out either of the other 2 if I know they are worth the pursuit (ie the design is sound/smart/makes sense).

Again, any advice is appreciated - thank you for your time!
Jun 19 '07 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 10K+
P: 14,534
In simple terms the first thing you have to do is design your database structure (your tables) correctly. Have a look at this tutorial on Database Normalisation.

Once you have done this then your forms will have to follow the structure of MainForm with one record and subforms for multiple related records in a one to many relationship.
Jun 20 '07 #2

P: 12
Hi, thank you for your reply. I have set up the table structure to be normalised (I believe), and I read the article (very well written by the way). Currently I have a table for demographics (Primary key = demographicID), a table for Which Study/ies the person is enrolled in (Primary Key = WhichStudyID, foriegn key = demographic ID), and a third table for Study A (primary Key name = StudyAID, foreign key = WhichStudyID).

Demographics Table to Which Study Table linked through DemographID in a one to many relationship (one person can have many studies) - the WhichStudy form is a continuous subform on the main Demographics form.

Which Study Table to StudyA table linked through WhichStudyID in a one to one relationship (when I set up the relationship, it automatically created it as a one-to-one, but I believe this is correct - for each study a person is in, there will only be one set of information linked to that study).

There will be many more Study tables (StudyB, StudyC, etc.), each with its own primary key and linked back to the WhichStudy Table through WhichStudyID.

My problem comes when I want to open a specific form for StudyA - I want that form to be opened based on the selection of "StudyA" from the drop down menu on the Which Study subform. How do I set the event procedure of a command button to open a form based on a drop-down menu selection?

Your help is greatly appreciated! Thanks!
Jun 20 '07 #3

Expert Mod 10K+
P: 14,534
Set up a form as normal based on WhichStudy with a subform for the studies.

Then in the after update event of the drop down you need to filter the form on open to the specific record

DoCmd.OpenForm "form name", , , "[WhichStudyID]=" & Me![Name of Dropdown]
Jun 20 '07 #4

P: 12
Thanks again for the response. As I understand that code, I am replacing "form name" with the name of the form I want to open? The problem is that each study will be its own form - so I don't want a specific form to open, I want the drop down menu to dictate that (through a command button). I put the following in the on click event of the command button:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command7_Click()
  2. DoCmd.OpenForm "Study A Form", , , "[WhichStudyID]=" & Me![Study]
  3. End Sub

When I tried that, after I clicked the button, I got a prompt asking for the parameter for the name of the study selected from the drop-down menu. I think the problem is that I have the WhichStudy table linked to the individual study tables through WhichStudyID, an autonumber primary key in the WhichStudy table. The study name is not connected to the WhichStudyID, so what I actually need is a filter that opens 1. the correct form based on the Study name selected from the drop-down menu, and 2. the correct record in that form based on the WhichStudyID for that person. Does that make sense? Am I structuring things wrong?

Thank you!
Jun 21 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.