subform problem. Pls help!

[dvh]

hi all,
I can't seem to find any topics related to my prob, be it here or Google so here i am asking you all for help!
I have a main form built on a table named "tblStockItems" and a subform built on a query named "qry" which is based on tblUsedIn. The main form shows each item in tabular format. The subform is supposed to show in datasheet view the subcomponent of the items on the main form if applicable(meaning if the item on the main form doesnt have subcomponent then subform shows nothing)
The problem arises as i cant link the subform to the mainform. Reason being there is no link field between the 2 tables. They are related by a field with Yes?No data type( value 0 or -1).
Could anyone enlighten me? or give me some directions to go from here?
Thanks a lot in advance
p/s pls tell me if i need to clarify the matter. looking to hearing from you!!

 

[Oldsoftboss]

Read up on relationships, Primary Keys and Foreign Keys. You need a Primary Key in your main table - AutoNumber and a Foreign Key in your subform table - Number. Then create a relationship

 

[Oldsoftboss]

Read up on relationships, Primary Keys and Foreign Keys. You need a Primary Key in your main table - AutoNumber and a Foreign Key in your subform table - Number. Then create a relationship

 

[dvh]

i understand a bit on relationsip and stuff but this is different case here as i'm creating an additional feature based on already-built tables. As mentioned, tblStockItems and tblUsedIn are only related by a field Assy with Yes?No data type( value 0 or -1). And there is no relationship at all for all the tables. No primary key as well. There is nothing i can do about that i think . Is there any way to work around that?
Sorry if i cant make myself clear and thanks again for your help!

 

[Oldsoftboss]

Do you have access to the table design?
If so, you need to add primary keys and foriegn keys to the tables.
Can you create new tables? I suggest you have a table of StockItems and a table of parts, then a join table to bring the 2 together.

Dave

 

[Oldsoftboss]

How big is the Db? Can you post a cut down version or email a sample?

 

[dvh]

thanks a lot Oldsoftboss for your reply!
I have found a way to work around that w/o having to add any Primary key(i thought i wouldnt work personally but i may be wrong)Anyway, in the subform, i still use the same query accept that i set the criteria for that particular item i want, so it just shows what i want. A simple solution indeed!
sorry if i didnt make myself clear and again thank you!

 

[Oldsoftboss]

You should ALWAYS have a primary key with datatype set to auto no.

Searching for and referencing records will be very difficult later as unique records will be frustrating to contend with.

 

[shuff]

Setting the Access issues aside for a moment... In plain English, how do *you* know which subform items go with any particular item in the main form? What is the logical connector between a main record and the subform record(s)? There must be some "connector" even for the human eye to match stuff up. If there is no pairing of relational fields, then would a subform, based on a query that checks for certain values work? For example, to display a record in the subform, it would be a requirement that FieldA in the main form equal "X" and FieldB in the main form be greater than "Y" - something like that? That way, you are querying the results of the Main Form and then, based on the values found, deciding which, if any records to display in the subform.

Hope that helps!