combobox inconsistencies

[GGG]

I've got a record box: (table name: recordbox)
Which consists of A,b,c,d,e (ignore the numbers on the far right, just
for reference purposes)

A b c d e
1. 374 ember olive 3.5 5
2. 374 embery black 3.8 5
3. 374 embery blue 3.27 8
4. 374 embery green 3.1 5.7
5. 377 imbibed green 2.8 5
6. 377 imbibed yellow 2.75 8
7. 377 biog emerald 2.33 9

It pulls its' datasource from an underlying table.

Initially, it had no primary key field as there can be multiple records
with the same number (a). I have tried this example setting unique
keys to no avail.

It shows up fine in the combobox. It's code is, "select a,b,c,d,e
from recordbox"

I select record 2.
When the combobox reports back to the onExit event, it pulls the data
corresponding to record #1.
Same would go if I selected record #6. It would report back record
#5. See where I'm going?

It's onExit action is:

Me.Description.Value = GSANbr.Column(1)
Me.Color.Value = GSANbr.Column(2)
Me.Size.Value = GSANbr.Column(3)

Me.GSANbr.Value = GSANbr.Column(0)
NSN.SetFocus


These are the ones they refer to in the select query.

The only option I can think of (since I tried the first already to no
luck), is to set an ID associated with each record. That would be
cumbersome to me, and would like to try and make this work the way I
have it set up now.

Let me know if this is possible

Thanks

 

[TC]

In a relational database like Access, with very very few exceptions,
If your tables do not have primary keys, this will cause you enormous
probems down the track. And it is probably part of your problem, right
now.

I suggest that you forget completely about your form, for the moment.
Tell us that name of each of your tables, and list the fields for each
table. (We do not need their types & lengths.) Then we can help you get
the primary keys right.

Believe me: you'll get nowhere quickly, unless the tables are
primary-keyed correctly

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[GGG]

TC (MVP Access),

This is actually a "view" of 4 tables.

Some had primary keys, others did not. But, with the problem I seem
to be having, I have gone through the tables and deduced what needed
PRIMARY KEYS, possibly to make the right record get selected.

productsMain
ProdID (pk)
CategoryCode(pk)
Description (pk)
ProdName

this is the main products table. Everything else is subtables. The
query takes additional information off the below tables to fill out the
rest of the query, prods1

categoryName
CategoryCode (pk)
CategoryName (pk)

categoryName.CategoryCode is equal joined to productsMain.CategoryCode
prodFabrics.prodNum is equal joined to productsMain.ProdID

prodFabrics
ProdNum (pk)
Fabric(pk)

prodColors
ProdNum (pk)
ProdColor (pk)

prodFabrics & prodColors are connected by ProdNum to extend those two
subtables.

All are set up in Relationships, where possible.

so, the data might look like: (from "view" Prod1)
Fabric ProdColor Description ProdID CategoryCode Manufacturer ProdName PagePriority Sizes CatalogCode CatalogPage Subcategory MIL_SPEC CategoryName
65/35 Polyester/Cotton Ripstop Navy "<li>Endorsed A
" 191 11 Atlan STU Coat 6 XS-3XL 1 4 1
Police Tactical Uniforms
65/35 Polyester/Cotton Ripstop Black "<li>Endorsed B
" 191 11 Atlan STU Coat 6 XS-3XL 1 4 1
Police Tactical Uniforms
65/35 Polyester/Cotton Vat Dyed Twill Black "<li>Endorsed C
" 191 11 Atlan STU Coat 6 XS-3XL 1 4 1
Police Tactical Uniforms
65/35 Polyester/Cotton Vat Dyed Twill Navy "<li>Endorsed D
" 191 11 Atlan STU Coat 6 XS-3XL 1 4 1


So far, with all these PK changes, the first record of a group is still
being selected in the form/subform.

Thanks for asking to take a more detailed look.
This was all done without using wizards.. maybe that's part of the
problem :-/

3 G's

 

[TC]

productsMain
ProdID (pk)
CategoryCode(pk)
Description (pk)
ProdName

Sorry, you have a fundamental error in your comprehension of primary
keys!

The primary key of a table, is that field, or combination of fields,
which uniquely identifies each record in that table. The primary key
fields are defined for the table as a whole; they do not change from
record to record. Every record in the table must, by definition, have a
different value of the primary key field(s) defined for that table. No
two recrds in a table, can have the same value of their primary key
field(s).

So, in your products table, if several products can have the same
CategoryCode, there is no way that CategoryCode can be the primary key
of that table.

I think that what has happened here, is that you are possibly
interpreting the term "primary key" in a common-sense fashion: it is an
"important" key, or a key by which you would often want to search that
table, and so on. But this is wrong, the term has a specific technical
meaning, as explained above.

In your product table, the ProdID field would almost certainly be the
primary key. Each product has a ProdID; no two products have the same
ProdID; the ProdID uniquely identifies each record in the products
table.

So:

productsMain
ProdID (PK)
CategoryCode
Description
ProdName

I strongly suggest that you go through the process again, for all your
other tables, and then post the results back here. I know this process
can be frustrating, but you simply have to get it right, or your
database will just not work correctly. And once you have done this a
few times, it all gets much easier!

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[GGG]

Hi TC, I just wanted to make this straight, if the table data seemed to
be skewed to the group in general. (1st piece of data)

Fabric= 65/35 Polyester/Cotton Ripstop
ProdColor= Navy
Description= <li>Endorsed A
ProdID= 191
CategoryCode=11
Mfg= Atlan
ProdName= STU Coat
None of the others are relevant.

In the productsMain, I can't just have the ProdID as the only primary
key. There are some instances where there is more than one instance of
191.
productsMain, as I see the data, needs two fields to have a "unique
event" primary key, ProdID and CategoryCode, as the ProdID can have as
many as twelve different CategoryCode(s), none of which will duplicate
(in this 'main table')

When adding in the Fabric, and Colors to make the existing "view", this
makes many more possibilities as certain CategoryCode(s) can have a
multitude of different fabrics and colors, all having unique
identities. There is probably no need to make those two tables with
unique keys, though, this will keep the user from putting in duplicate
records in the future.

Fabric ProdColor Description ProdID CategoryCode
Manufacturer ProdName PagePriority Sizes CatalogCode
CatalogPage Subcategory MIL_SPEC CategoryName
65/35 Polyester/Cotton Ripstop Navy "<li>Endorsed A
" 191 11 Atlan STU Coat 6 XS-3XL 1 4
1
Police Tactical Uniforms
65/35 Polyester/Cotton Ripstop Black "<li>Endorsed B
" 191 11 Atlan STU Coat 6 XS-3XL 1 4
1
Police Tactical Uniforms
65/35 Polyester/Cotton Vat Dyed Twill Black "<li>Endorsed C
" 191 11 Atlan STU Coat 6 XS-3XL 1 4
1
Police Tactical Uniforms
65/35 Polyester/Cotton Vat Dyed Twill Navy "<li>Endorsed D
" 191 11 Atlan STU Coat 6 XS-3XL 1 4
1

This "snapshot" of data is pulled from the created query, that I need
in order to pull the correct data for the application I am trying to
write.

I hope I am making some sense, as I probably wasn't exact in what my
dilemma was earlier.

thanks again.

3 G's

 

[TC]

Hi 3 G's

Thanks for persevering with this

I hear what you say: "The same product comes in many different
categories."

(Note: in these conversations, "many" just means "more than one". It
does not necessarly mean "hundreds and hundreds".)

So, ProdID is /not/ unique within the products table: many records can
have the same ProdID. Similarly, CategoryCode is not unique within that
table: many records can have the same CategoryCode. Only the
/combination/ of ProdID /and/ CategoryCode, is unique within that
table.

In that case, you need a so-called "composite" primary key. A composite
PK is a PK which comprises more than one field. In your existing
products table, the primary key would be ProdID /plus/ CategoryCode. No
two records can have the same /combination/ of ProdID and CategoryCode.
You could show the composite key, like this:

ProdID ( composite )
CategryCode ( primary key )
(other fields)

However - this is how you would normally structure this. Note that I've
chosen my own table names below. Do not confuse my names, with yours.

tblProduct
ProdID (PK)
(other fields)

tblCategry
CategryCode (PK)
(other fields)

tblProductCategory
ProdID ( composite )
CategryCode ( primary key )
(other fields)

Huh? Why three tables?

Because then you can store the necessary information >>> without any
duplication <<<.

o tblProduct stores data that applies to the product as a whole, >>>
regardless of what categories that product might come in. <<< The data
in this table depends on the product, but not on the category. You do
not need to duplicate these [product details, in every category record
for that product.

o tblCategory stores data that applies to that category as a whole,
o tblProductCategory stores data about a single product in a single
category. The data in this table depends on the /combination/ of
product /and/ category, so it can not go in either of the other two
tables.

I do not want to over-complicate the issue - but that is how you should
really structure your data, from what I can see so far.

Does that help? Or just confuse?

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[GGG]

Hi TC, thanks for continuing to suggest ways to make this work.
(actually, what you talk about are things that someone who has been
doing it as long as I have been -- creating, and working with DB's,
should know). The table in question, for the most part, is in 1st
normal, already (tweaks could be made, but it's all minor-- the main
table has the two keys in the composite table already, along with a
number of other data fields, so, making the extra table, I'm not sure
makes much of a difference.).
I did revise the table structure to allow a composite key, where those
two fields, ProdID and CategoryCode were the only two fields in the
table, and then linking them with the other existing corresponding
tables. However, after doing all that, I still have the same issues
with the combo box. It is still selecting the 1st record in a set of
2 to 15 ProdIDs.
With the cbx, I have tried using the DISTINCT & DISTINCTROW options to
no avail.
Short of assigning a "unique ID" for each combination of ProdID and
CategoryCode, I don't know what else to do with this. That would be a
pain (and I don't want the person using this system making changes in
the code-- he wouldn't know where to look) and I know there can be a
working answer to this common issue.
Looks like I'll have to start at square one and eliminate by
attrition...
All part of the debugging process!

Hi 3 G's

Thanks for persevering with this

I hear what you say: "The same product comes in many different
categories."

(Note: in these conversations, "many" just means "more than one". It
does not necessarly mean "hundreds and hundreds".)

So, ProdID is /not/ unique within the products table: many records can
have the same ProdID. Similarly, CategoryCode is not unique within that
table: many records can have the same CategoryCode. Only the
/combination/ of ProdID /and/ CategoryCode, is unique within that
table.

In that case, you need a so-called "composite" primary key. A composite
PK is a PK which comprises more than one field. In your existing
products table, the primary key would be ProdID /plus/ CategoryCode. No
two records can have the same /combination/ of ProdID and CategoryCode.
You could show the composite key, like this:

ProdID ( composite )
CategryCode ( primary key )
(other fields)

However - this is how you would normally structure this. Note that I've
chosen my own table names below. Do not confuse my names, with yours.

tblProduct
ProdID (PK)
(other fields)

tblCategry
CategryCode (PK)
(other fields)

tblProductCategory
ProdID ( composite )
CategryCode ( primary key )
(other fields)

Huh? Why three tables?

Because then you can store the necessary information >>> without any
duplication <<<.

o tblProduct stores data that applies to the product as a whole, >>>
regardless of what categories that product might come in. <<< The data
in this table depends on the product, but not on the category. You do
not need to duplicate these [product details, in every category record
for that product.

o tblCategory stores data that applies to that category as a whole,
o tblProductCategory stores data about a single product in a single
category. The data in this table depends on the /combination/ of
product /and/ category, so it can not go in either of the other two
tables.

I do not want to over-complicate the issue - but that is how you should
really structure your data, from what I can see so far.

Does that help? Or just confuse?

HTH,
TC (MVP Access)
http://tc2.atspace.com