Main form with subform

[DNuding]

I have a main form based on Table A with a subform based on Table C. These
are linked via (master and child) 2 fields, ShowId and ShowName.

On the subform, I have a combo box whose source is Table B, field is Class#.
Table B also stores ShowId and ShowName. When an item is selected from the
Class# combo drop down list, the value of Class# is stored in Table C, Class#
field.

I have read many of the posts on this list and can not figure out how to get
the results I need.

When the main/sub form is opened, I would like to be able to click on the
dropdown combo box on the subform and only see the records that correspond to
the ShowID and ShowName on the main/sub form and in Table B.

Table A (Shows)
ShowID ShowName
1234 Test
5678 Test1

Table B (Class Lists)
ShowID ShowName Class#
1234 Test 1
1234 Test 2
1234 Test 3
5678 Test1 1
5678 Test1 2
5678 Test1 3

Table C (Class Entries)
Class# ClassName
1 English 2 Gait
2 Western 2 Gait
3 English 3 gait

If I scroll through the shows, I want to click on the Class# field and only
see the class numbers from Table B in the combo box listed for that
particular show.

If the ShowId and ShowName for Test1 are in those fields in the main/sub
form, I want to see the class numbers available to pick just for that show.
(The combo box actually has 2 columns so that the class# and class name are
visible ).

Thanks for any assistance.

 

[Tom van Stiphout]

On Thu, 30 Oct 2008 21:24:01 -0700, DNuding

I stopped reading after your first paragraph. Delete the ShowName from
TableC, and the corresponding relation. Your violating important
database design rules. The point of an RDBMS is that you can look up
the ShowName via the relationship (on ShowID) between the two tables.

-Tom.
Microsoft Access MVP

 

[DNuding]

Ok, I can do that.

With that done, how do I get the combo box on the subform to show me the
items in Table B that correspond with the ShowID in Table A.

Thanks for your help.

 

[Tom van Stiphout]

On Fri, 31 Oct 2008 04:37:00 -0700, DNuding

I'm sorry I can't follow your original post. I think you have some
things backwards. You appear to have a classic many-to-many
relationship, with Shows and Classes on the one-sides, and ClassList
as the junction table.
That is equivalent to the situation in the Northwind sample database,
with the M:M between Orders and Products via junction table
OrderDetails. Check out the Orders form and note how its subform
allows you to create records in the junction table, and a dropdown on
the Products table. Maybe that will help you figure it out.

-Tom.
Microsoft Access MVP

 

[DNuding]

I am back to work on this problem after a couple months away. I am working
on setting up the table relationships as you suggested, using Northwinds as
an example.

I deleted the ShowName from Table C as you suggested.

The Northwinds Products table and the Orders table both have a 1 to Many
relationship to the Order Details junction table.

In my circumstance, I have created a similar relationship from Table A to
Table C (junction table) with a 1 to Many relationship.

I am having a problem creating the 1 to Many relationship from Table B to
Table C on the related field. The relationship windows says the relationship
is Indeterminate.

How do I resolve this so that I can proceed to test my form?
Thanks,
DN

 

[strive4peace]

please tell us your 3 tablenames, the primary key in each, and the
fieldnames and data types of the fields you are using to set relationships

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[DNuding]

I am working on creating a horse show management program that maintains
information on many individual shows. Each horse show has it's own show bill
(class sheet). Each class from the show bill will have many entries.

Table A - tblHorseshownew
ShowID-PK-text
ShowName-PK-text

Table B - tblShowbillnew
ShowID-PK-text
Class#-PK-number

Table C - tblClassentriesnew
ShowID-text
Class#-number

I have a 1 to Many relationship from Table A to Table B. I am not able to
create that relationship from Table C to Table B. I think part of the
problem is that I need to be able to store duplicate information in Table C
in the ShowID and Class# fields.

I wanted my subform Class# combo box to be able to only show the classes
from the show selected on the main form so that as class entries are created,
only the classes from that show are available.

If you need additional information, please let me know and thanks for
considering my question.

 

[strive4peace]

"I am working on creating a horse show management program that maintains
information on many individual shows. Each horse show has it's own show
bill (class sheet). Each class from the show bill will have many entries."

If I understand correctly, I see something like this:

Shows
- ShowID, autonumber, PK
- ShowName, text
- ShowDate, date
etc

Classes
- ClassID, autonumber, PK
- Classname, text

ShowClasses
- ShoClasID, autonumber, PK
- ShowID, long integer, FK to Shows
- ClassID, long integer, FK to Classes

PK = Primary Key
FK = foreign Key

I see you are using text for your primary keys ... that is fine -- but
personally, I like using a meaningless piece of information like an
autonumber and then using a long integer in related tables for linking.
These key fields can be transparent to the user

If you want to assign a 'code' to the show, you can define another field
called ShowCode that is text.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*