subform not displaying data

[GabberGod]

I have 2 subforms attached to a mainform.

the work in the same way, using the same fields off the mainform, just displaying different data from different tables that related to the main form data (i.e mainform = drawings, subform 1 = locations of drawings, subform 2 = items on drawing.

anyway the primary keys are the same, so they both have the same parent child links.

Either way 1 subform displays the data as expected, the other displays nothing.

Is there a reason for this? I cant see what i could have done wrong, as both subforms were setup at the same time.

 

[TheAceMan1]

How are ya GabberGod . . . . .

Is the [blue]MainTable[/blue] in a [purple]one-to-many relationship[/purple] with [blue]both subTables?[/blue]

See Ya! . . . . . .

 

[GabberGod]

Im not too bad

no drawing location is a one to one (not by choice, i cant change it from one ot one, to one to many)

again it is joined in the same fashion as the other table. but one is one to many and the other one to one.

how can i change this?

basicly the relationship is based on 7 (primary key) fields. relationship 1 is one - many, but relationship two which is identical in every way except table name is one - one

 

[TheAceMan1]

[blue]basicly the relationship is based on 7 (primary key) fields.[/blue]

Wow . . . . . . . [blue]Unless its absolutely necessary[/blue], I'd find a way to chop the PK count down before I did anything else. If the logic was meant to guarentee uniqueness for instance, then you only need one PK per table. If however, there's a method to the madness . . . prepare yourself . . . . I hope you can live through it! [purple]Compound PK's cause great headaches for programmers, as well as take the ease out of future planning.[/purple] Two or three is usually headache enough . . . . but 7! . . . .They also keep you from having that Bud!

At any rate, to change relationships, in the [blue]Relationships Window[/blue], right-click a relation line & delete in the window that pops-up. [purple]Be aware: if you want to delete a parent relationship, you have to delete any children first, cascading back to the parent.[/purple]

[blue]Your problem obviously has to do with the Compound PK![/blue] Also a [blue]one-to-one[/blue] relationship is usually the [blue]spliting up of a single table[/blue]. [purple]You've indicated duplicate tables here![/purple] Although this possible, I query its use!

Post the PKs and their structure in the relationships . . . .

At you leisure, have a look at the following:

Normalizing Tables

Table Relationships

Fundamentals of Relational Database Design

See Ya! . . . . . .

 

[GabberGod]

yep 7, It has to be. its based on a project code and the a number, the number is a technical drawing number it contains 6 pieces x-xxxx-x-x-xxxx-x, the project code simply allos for drawings form more than 1 project to be entered into the db.

then each of those drawings is distributed to a number of locations (hence subform 1)

also each drawing has a number of items on it required to build that piece(hence subform 2).

The reason for spilting it into 3 tables is simply the 3 tables all hold different information that needs to be used for different things in different times and there is no set number of items or locations, it could be 1 it could be 1000 depending on the project.

Anyway back to the relationship.

Project(text)
Partition(text)
section(text)
subsection(text)
type(text)
drawing no(text)
revision(text)

these are the fields in the relationship. they are identical on both sides of the relationship, also they are the primary key in the drawings table and part of the primary key in the other 2 tables.

finally this is the exact same as the relationship between drawings and items and that relationship is 1:m. seems strange to me that 1 works and the other not.

 

[Edski]

GabberGod,
Is drawing location one-one or one-many?

no drawing location is a one to one

then each of those drawings is distributed to a number of locations

 

[TheAceMan1]

GabberGod . . . . .

[blue]Post the PKs and their structure in the relationships . . . .[/blue]

[purple]I meant in the Relations Window![/purple]

See Ya! . . . . . .

 

[GabberGod]

i dont understand what you want me to post???

according to access its one-one but logicly its one-many.

as for each drawing therer can be an infinte number of locations

 

[TheAceMan1]

GabberGod . . . . .

So sorry . . . . Either I didn't get you latest post, or I somehow deleted it. . . Forget my last post.

[blue]Is the database open to redesign or development of a new one on the side?[/blue]

If so, then I'd like to spell out new table structure and see how you like it. Bear in mind, [blue]your Project & Drawing number Schema will be maintained![/blue]

If not, I have to be direct and say: [purple]I'm not gonna fight with 7 PrimaryKeys![/purple]

See Ya! . . . . . .

 

[Edski]

according to access its one-one but logicly its one-many.

I'm still trying to wrap my brain around this one!

 

[GabberGod]

ok now i have recreated the relationships

i now have drawing 1 : M Drawing Locations
i also have drawing 1 : M Drawing Items

ok so basicly i now have what you asked for, but it is still not displaying my fields in drawing location sub-form.

finally what is your idea for table redesign??? im all ears, but skeptical if it will actually cause less work. that is unless you use autonumbering which seems like pointless data when there is already a unique numbering system built into drawings im working with.

 

[GabberGod]

doesnt matter guys, thanks for your help it is now fixed

Although as usual with Access i dont know what i did to fix it, it just started working out of no where