Having problems understanding sub-form selections and uses of comb

[jeff]

Access '02:
Hi all, I guess i'm having fundelmental problems getting my sub-forms and
combo boxs to work for what i'm trying to attempt.
I've posted my first problem with use of 'drop down combo box" and getting
it to show me the text in the destination field already. I've made progress
with that.

Now i can't get a grip on the use of sub-forms and combo boxes for my next
part.
So i don't burden you to much can anyone recommend any useful visual demos
or examples for these. The online help is of no use to me for my needs, and
i can't find anything specific at the online support center.
I'm definately between a rock and a hard place.

My problem sounds straight forward.
I have a table called "Profile", containing names, address, and all kinds of
other stuff about people. Basically, I have all my info in just one table.
I'm finding out this probably isn't the wright way to do this and i will have
to do a complete redesign in the near future.
Anyway, all i want to do is to track the workshops that people attend (date,
wkshp name, Wkshp Descript).
Some people will be attending only one workshop, while others more than one.
Ultimately, i will do reports, queries, etc on these.
I am getting stuck with the use of sub-forms and combo boxes to achieve this
basic task.
Also, since i've been using only one table for all of my data, i'm not use
to how to link or associate diff table or fields to each other.
--------------------------------------
In order to track the workshops that people attend i created a new 'table'
called "workshops attended" (date, wkshp name, wkshp descript,autoId field).

In order to populate this table i create a 'form' called "Workshops
Available".
I can use this 'form' to populate the "workshops attended" table just fine.

Now i want to go under each person and select which workshops they have
attended over time and have that info stored. This is where i'm getting
stuck.

I guess i create another 'table' called something, but not exactly sure how
to work the structure/linking/relationships.

Can anyone please assist.

 

[Richard]

My problem sounds straight forward.
I have a table called "Profile", containing names, address, and all kinds of
other stuff about people. Basically, I have all my info in just one table.
I'm finding out this probably isn't the wright way to do this and i will have
to do a complete redesign in the near future.

Hi Jeff,
If I were you I would close the database down for a while and get a pencil
and paper and write down some ideas.

Research table normalization, this is the foundation you build the database
on.
Without this first step you will have crummy performance, queries and
reports. Every thing else comes after this step.

Good Luck
Richard

 

[John Spencer]

You will need a third table. Your current table should be WorkshopsAvailable.

The third table WorkshopsAttended table will consist of two fields
==ProfileID - Number field type Long (Assumes you have an autonumber field in
Profile table
==WorkShopID - Number field type Long
==DateAttended -


You now need a continuous form based on the Workshops attended. You will use
this form as a subform. I would

==add a combobox bound to WorkshopsAttended.WorkshopID. The row source of the
combobox would be
SELECT WorkshopID, WorkshopName, WorkshopDescription
FROM WorkshopsAvailable
ORDER BY WorkshopName
Set the column widths property on the combobox to 0 to hide the first column.
==Add the DateAttended field

Save the form and then add it to your Profile form (which should be in single
mode) as a subform. Link the Profile form and the subform on ProfileId
fields. Now when you create a new record in the subform, it will
automatically be assigned the profileId. You can then pick from the combobox
to select a workshop type and enter the date attended.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County