Solved Combobox with multicolumn (1 Viewer)

[Atthe]

Hi All

I have a combobox with 3 columns (Bound Column 1) from tblAssemblies, the issue I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3. The user selects an entry from the drop down, but if they select an entry of which the first two fields are the same then when the user runs the report the query pulls data for both entries rather than the unique entry.

Row Source for the combobox

SELECT tblAssemblies.AssemblyMaterialNumber, tblAssemblies.AssemblyNumberSpec, tblAssemblies.Line FROM tblAssemblies ORDER BY tblAssemblies.AssemblyNumberSpec, tblAssemblies.AssemblyMaterialNumber;

I am aware this may be a data structure issue but I can't think of a way to deal with the problem

Thanks

 

[CJ_London]

you need a tblAssemblies.PK in your select code and bind the control to that

 

[Atthe]

you need a tblAssemblies.PK in your select code and bind the control to that

Yes that sounds like a good option but I already have PK set as AssemblyNumberSpec and one autonumber field in the table, Is there any way around this?

 

[MajP]

I would think you would want cascading combos to allow you to pick a line then the assembly from another.
But if not your where condition to open the report is likely

"tblAssemblies.AssemblyMaterialNumber = " & me.cmboName.colum(0) & " AND tblAssemblies.AssemblyNumberSpec = " & me.cmboName.colum(1) & " AND tblAssemblies.Line = " & me.cmboName.column(2)

Remember columns are zero indexed. Also my guess some of those are not numbers but strings and would need to add single quotes around them
tblAssemblies.AssemblyMaterialNumber = '" & me.cmboName.colum(0) & "' AND

"

 

[moke123]

I have a combobox with 3 columns (Bound Column 1) from tblAssemblies,

PK set as AssemblyNumberSpec

Just checking that you are aware that a combobox is zero based while the bound column is 1 based, so if your bound column is 1 then the data used from your combo is column 0. PK set as AssemblyNumberSpec bound column would be 2.

 

[CJ_London]

I already have PK set as AssemblyNumberSpec

in that case this can't be true

I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3

But you have an autonumber field, use that as your PK

 

[MajP]

I assume this relates to

Creating multiple records from one form

Hi All, I have two tables in my database [tbltools] which has 10 fields (tools1 to tools10) and [tbltoolgroups] which has 7 fields (toolgroups1 to toolgroups7) I am in the process of creating a form that the end user can use to define a tool group (will be stored in [tbltoolgroups]) The basic...

www.access-programmers.co.uk

So I would expect a junction table as I described. Your junction table would have a PK to uniquely define the group
(assembly, line)
This does not look like you used a junction table.

 

[Atthe]

I assume this relates to

Creating multiple records from one form

Hi All, I have two tables in my database [tbltools] which has 10 fields (tools1 to tools10) and [tbltoolgroups] which has 7 fields (toolgroups1 to toolgroups7) I am in the process of creating a form that the end user can use to define a tool group (will be stored in [tbltoolgroups]) The basic...

www.access-programmers.co.uk

So I would expect a junction table as I described. Your junction table would have a PK to uniquely define the group
(assembly, line)
This does not look like you used a junction table.

Yes it does, I have just checked and I have used a junction table as you advised although I named it tblToolsForAssemblies with the PK on AssemblyToolGroupNumber however I didn't use it for the combobox.

As the PK is different for every tool within the assembly how would I avoid duplicates in the combobox (I only want to see every assembly along with the corresponding Line and Material Number)?

Please see attached photo

Thanks

 

[MajP]

Maybe there is a 2nd junction table.

I think an assembly has some unique properties but describes a generic assembly
Material number and spec

An assembly can be put on to many lines if I understand correctly.

tblAssemblies, the issue I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3

A generic assembly on a specific line is unique

That would be like I have a list of different generic circuit boards (manufacturer, type, power ...) with generic information about that kind of circuit board.
If have another table where I assign it to an assembly and provide the specific SN then in the other table it is no longer a generic circuit board, but represents one and only one specific.

So you would then have a junction table tblAssemblyLine. It would have an assembly and line ID from the assembly table and the Line table. And this would have a PK.
So assembly 123 on line 1 is different from assembly 123 on line 2.

Then if tools are associated to a specific Assembly on a specific line the next junction table would have a part ID and the fK from tblAssemblyLine in order to assign parts to a specific assembly on a specific line.

Now if there really is no related line information besides just the line number then you do not need another table for a Line. If the only repeated information in the assembly table is the Material number and Spec number then you could skip the junction table.

But that means that in tblAssembly an assembly is uniquely described by it Material number as well as the line number.

AssemblyNumberSpec and one autonumber field in the table

You can create a unique composite key made up of two fields, but this is a PITA.
It would be AssemblyNumberSpec and LineNumber which uniquely describe a specific assembly.
The only PK you need is the autonumber. Call that field Assembly_ID.
Now you can do a unique index to ensure you do not get duplicates. You cannot have the same assemblynumber and line number so the combination must be unique.
Now in your combo you include Assembly_ID and in your report as well. You can hide it on both but when you pick a value from the combl
Assembly123 on Line 5 will have a Unique Assembly_ID. You should open the report to that Assembly_ID.

NO PK can be repeated so AssemblyNumberSpec by itself cannot be a PK. Having AssemblyNumberSpec and an Autonumber as a PK makes no sense, because only a unique autonumber is needed.

 

[Pat Hartman]

Combos and Listboxes will not work correctly unless they have a single UNIQUE identifier that you make the bound column.

So, the table needs an autonumber PK so you have ONE column which is unique. To enforce business rules, you can make a three-field unique index. You will need to use the index dialog to do this. You can't do it by setting the index property on each field in the table design since with the exception of the PK, the table design does NOT give you a way of choosing multiple columns for indexes.

You would need to redesign the tables if you were to use cascading combos. Each of the three fields would be a separate table. That may or may not be a useful solution.

to solve the problem of what shows when the combo is closed if you use the autonumber PK and the three field unique index, you can concatenate the three fields in the RowSource query.

Select PK, fld1 & "-" & fld2 & "-" & fld3 as LongName
From YourTable
Order By fld1 & "-" & fld2 & "-" & fld3

the combo will then have only two columns. Column 1 would be bound. The Columns property would be 0;3 so that the first column, the PK would be hidden and the second column would be 3 inches wide. Make it as wide or narrow as you need to display all three values fully.

 

[Atthe]

Maybe there is a 2nd junction table.

I think an assembly has some unique properties but describes a generic assembly
Material number and spec

An assembly can be put on to many lines if I understand correctly.

A generic assembly on a specific line is unique

That would be like I have a list of different generic circuit boards (manufacturer, type, power ...) with generic information about that kind of circuit board.
If have another table where I assign it to an assembly and provide the specific SN then in the other table it is no longer a generic circuit board, but represents one and only one specific.

So you would then have a junction table tblAssemblyLine. It would have an assembly and line ID from the assembly table and the Line table. And this would have a PK.
So assembly 123 on line 1 is different from assembly 123 on line 2.

Then if tools are associated to a specific Assembly on a specific line the next junction table would have a part ID and the fK from tblAssemblyLine in order to assign parts to a specific assembly on a specific line.

Now if there really is no related line information besides just the line number then you do not need another table for a Line. If the only repeated information in the assembly table is the Material number and Spec number then you could skip the junction table.

But that means that in tblAssembly an assembly is uniquely described by it Material number as well as the line number.


You can create a unique composite key made up of two fields, but this is a PITA.
It would be AssemblyNumberSpec and LineNumber which uniquely describe a specific assembly.
The only PK you need is the autonumber. Call that field Assembly_ID.
Now you can do a unique index to ensure you do not get duplicates. You cannot have the same assemblynumber and line number so the combination must be unique.
Now in your combo you include Assembly_ID and in your report as well. You can hide it on both but when you pick a value from the combl
Assembly123 on Line 5 will have a Unique Assembly_ID. You should open the report to that Assembly_ID.

NO PK can be repeated so AssemblyNumberSpec by itself cannot be a PK. Having AssemblyNumberSpec and an Autonumber as a PK makes no sense, because only a unique autonumber is needed.

So I am looking at my relationship window and It seems like I haven't followed the correct approach.

what fields and PK should I have in tblToolsForAssemblies?

Thanks

 

[oleronesoftwares]

So I am looking at my relationship window and It seems like I haven't followed the correct approach.

Can you share the image?

what fields and PK should I have in tblToolsForAssemblies?

It depends on the tables that tblToolsForAssemblies will relate with and the type of relationship.

Basically a Pkey can have
1. One to many relationships with another Pkey(AKA Foreign key)
2. One to One relationship with another Pkey(AKA Foreign key)

In rare instances there can be a many to many relationships(but don't bother yourself with this)

 

[Atthe]

Can you share the image?


It depends on the tables that tblToolsForAssemblies will relate with and the type of relationship.

Basically a Pkey can have
1. One to many relationships with another Pkey(AKA Foreign key)
2. One to One relationship with another Pkey(AKA Foreign key)

In rare instances there can be a many to many relationships(but don't bother yourself with this)

 

[oleronesoftwares]

the issue I am facing is in tblAssemblies the first two fields are allowed to contain the same
data but are differentiated by field 3. The user selects an entry from the drop down, but if they select an entry of which the first two fields are the same then when the user runs the report the query pulls data for both entries rather than the unique entry.

Your relationships seems ok , in your select query which fields are allowed to have duplicate values and which is not allowed to?

 

[MajP]

Going back to the original problem

SELECT tblAssemblies.AssemblyMaterialNumber, tblAssemblies.AssemblyNumberSpec, tblAssemblies.Line FROM tblAssemblies ORDER BY tblAssemblies.AssemblyNumberSpec, tblAssemblies.AssemblyMaterialNumber;

Your PK is assemblynumberSpec. You need to bind the combo to the 2nd column. Then you open the report to
"assemblynumberspec = '" & me.cmboboxName & "'"
or if real number no single quotes.
I assume you are opening the report to assemblyMaterialNumber which is not unique.

 

[Atthe]

Going back to the original problem

SELECT tblAssemblies.AssemblyMaterialNumber, tblAssemblies.AssemblyNumberSpec, tblAssemblies.Line FROM tblAssemblies ORDER BY tblAssemblies.AssemblyNumberSpec, tblAssemblies.AssemblyMaterialNumber;

Your PK is assemblynumberSpec. You need to bind the combo to the 2nd column. Then you open the report to
"assemblynumberspec = '" & me.cmboboxName & "'"
or if real number no single quotes.
I assume you are opening the report to assemblyMaterialNumber which is not unique.

Yes thanks a lot for your time all solved now