Solved Subform unwanted behaviour (1 Viewer)

[Atthe]

Hi All,

I have used this attached example that was created by MajP. The issue I have is that I need to be able to create a new record in the form with the same Assembly NumberSpec and AssemblySAPNumber. Currently when I create a new record and type in the same Assembly NumberSpec (For example AR42-9012) it returns the same tools to the subform. The tools required may be different even though the Assembly NumberSpec and AssemblySAPNumber are the same as the tool requirement depends on the line, The only thing that would differentiate the two records would be the line they are used on.

I realise this is due no PK duplicates allowed but I can’t think of a suitable way around this

Can anyone help with a a solution

 

[bastanu]

Include the line into your PK (Assembly NumberSpec, AssemblySAPNumber, Line).

EDIT: Please review the updated file, I changed the PK in tblAssemblies to an autonumber and created a multi-field unique index on the three fields.
Cheers,

 

[Atthe]

Include the line into your PK (Assembly NumberSpec, AssemblySAPNumber, Line).
Cheers,

Thanks for the reply
Sorry could you possibly elaborate? Do you mean with an additional table?

Please see attached screen grab

Cheers

 

[bastanu]

Did you get the sample, I edited the post after your latest one so you might not have received the notification?
Cheers,

 

[Atthe]

Did you get the sample, I edited the post after your latest one so you might not have received the notification?
Cheers,

Edit - That was my fault I had one of my fields set as short text!



Yes I have just had a look all works as I need, but when I try to make the changes in my database I get 'Relationship must be on the same number of fields with the same types of data' error. Also I see you added a query, Can you explain what the purpose of this is?

Cheers,

 

[Pat Hartman]

The error is pretty clear. Check each column to ensure that the data type matches the one it is related to.

 

[bastanu]

You need to go through the following steps:

• Open the Relationships window and delete the link between tblAssemblies and tblAssemblies_Tools
• Open the tblAssemblies, add the new AssemblyID_PK autonumber field, set it as primary key, save the table and open it; the autonumber PK will now be populated. At this time you can also add the unique index on the three fields by opening the table in design view, clicking the Indexes button on the ribbon and adding it manually; note that you enter the index name only for the first field - look at how I've done it in the sample
• Import Query1 from the sample I gave and run it; what it does it replaces the existing foreign key values (Assembly NumberSpec) in tblAssemblies_Tools table (the AssemblyID_FK field) with the new autonumber values
• Open the tblAssemblies_Tools table in design view and change the data type of AssemblyID_FK from Short Text to Number (Long Integer)
• Finally reestablish the relationship between the two tables AssemblyID_PK to AssemblyID_FK

Cheers,

 

[Atthe]

You need to go through the following steps:

• Open the Relationships window and delete the link between tblAssemblies and tblAssemblies_Tools
• Open the tblAssemblies, add the new AssemblyID_PK autonumber field, set it as primary key, save the table and open it; the autonumber PK will now be populated. At this time you can also add the unique index on the three fields by opening the table in design view, clicking the Indexes button on the ribbon and adding it manually; note that you enter the index name only for the first field - look at how I've done it in the sample
• Import Query1 from the sample I gave and run it; what it does it replaces the existing foreign key values (Assembly NumberSpec) in tblAssemblies_Tools table (the AssemblyID_FK field) with the new autonumber values
• Open the tblAssemblies_Tools table in design view and change the data type of AssemblyID_FK from Short Text to Number (Long Integer)
• Finally reestablish the relationship between the two tables AssemblyID_PK to AssemblyID_FK

Cheers,

Thanks for the detailed explanation all working great!

Cheers

 

[Atthe]

Good Morning,

Although this worked fine on my test database I have now tried to implement it into my live database and I am facing the following problem:

I think the relationship between the two tables is causing an issue as in tblToolsForAssemblies and tblAssemblies as the data in Assembly NumberSpec needs to be short text (Despite the field name).when I am running a report I am getting Type mismatch error

I hope this makes sense

Cheers

 

[Atthe]

Solved - my query that runs the report did not have the correct relationship between the two tables

Cheers

 

[bastanu]

Great news!

 

[Pat Hartman]

Using different names for the PK/FK pairs just causes confusion. Who would expect AssembliesSpec to map to AssembliesPK. Using AssembliesFK - AssembliesPK at least makes some sort of sense.