Access MS Access 2000 help req.

[sireid]

Urgent help needed please.



I’m building a database for my studies and have hit a major snag with the final form build/design. The db is for a mug ordering system. The mugs are defined by…

The supplier – The Category – The Type – The Style

(whereby The Style is the final name for a mug).

The relationships have been designed as per the first attachment.

(see first screengrab)

Before I go on, do these relationships look realistic?


What I want for the final form to provide is 3 option boxes on the left, which would have to be setup as cascading fields. So when a Category name was chosen, it would then list the relevant Types in the next option box, and when the Type was chosen, the relevant Styles would be listed in the Style box. When the customer had chosen the final mug style, I would then assign a macro to copy the details to an adjacent form which would include customer details and final purchase cost.
(See attached second screengrab)



Can anyone please shed some light on this, particularly the cascading field’s option, as I am at my wits end trying to solve this?

I have looked at the help file “Cascadinglistsdemo” to no avail.

Cheers

SR

 

[masterkeedu]

Your relationships seem troublesome...

i have found sometimes relationshhips get very intrusive.. so try this maybee.


basically for each unique mug, #40563,,, you will have a supplier, a category and a type, along with price and description....
Fake Coompany Name > coffe mugs > small >14.95 > "small brown coffe mug"
A many-to-one rellation ship links the foriegn keys to the other 3 tables.

so looking at style table with subtables, any style id will list(as sub tables) its attributes pulled from 3 outside tables.
price and description are part of the unige style ID.

no style key is repeated twice, and no supp, cat, or type are repeted in thier own table. They are repeated in the style table, in the unique mugs records that they apply to.,


make a copy, so we dont mess up any nice progress youve made
1) sales ID in sales table references a specific "style ID", linked to style table.
2) style table has ; style id(primary, unique), desc., price, supp. ID, cat. ID and type ID
3) 3 tables "suppler, category and type" are linked by thier primary keys to each corresponding foreign key in style table.

ok now it gets fun, hopefully



you need 3 queries, one called supplier, it will search style table for any records containg (form1.combo1) ., it need only return corresponding categoriees offered by that supplier.

the next will be category, and will do the same as above AND those that have category (of styles) euqla to combo2. , it need only return types offered in that category.

third will searcxh by supp. AND cat. AND type,,, to fill final combo box(4) with all styles under thatv type in that category offered by that vendor.

STAY WITH ME

these queries will be called one at a time by corresponding combo boxes(insert some query call in "on change" event of combo box.)
example, for combo box 1 youll need to set 2's list property to the query.

combobox2.row source=queries!supplier.categories

im sorry this is very rough code its been months since i played with access and its goodies, but the period(.) and a little scrolling will find the right key for row source.

you clcik box 1(which is set up in properties(control source) to look in supplier table as , listing all supplier ids and thier name)
after you clcik the code runs the query and sets combo box2 to its results.
Clicking two will fill the third and so on

this is like cascading, in a way,,,the lower combo boxes will be null until upper parameters are supplied.

after upper ones are selected , the lower ID and description will popuylate the list.

the form will save the final selected "style ID" to the sales table.(properties>control source)




sorry that prolby is the most confusing rampt ever...

 

[sarong17uk]

Muggins!!

Try this!!