Narrowing Selections Down From Field to Field

[BJNK]

Hopefully the last time I'll need to ask questions about this same database, but you never know

My question is this. The database is for a furniture manufacturer, and they want a form for designing new products. They want to be able to name the new product give it all its information and then in the subform choose the materials that go into it. There is a table called Products that has all the product info, a table called ProdMat which contains a autonumber ID, a Product ID and the Material specifications along with quantity. And of course the Material table.

The material table is set up with 7 fields:
Category: (Sheet Stock, Molding, Glass etc)
Type: (Oak, Maple etc)
Detail: (1/16 sheet, corner etc)
Description#: PK with 6 digit number
Description: Actual Material Description
Width
Length

Now they want the form to narrow down as they go. So the first Field would be Category and goes down to Width/Length. So if Category: Molding was selected then the options narrow down in the Type, and then that would in turn narrow down the Details available and that would narrow the description and in turn the description would select the Width and Length.

I know it can be done, but Im not sure how difficult it will be. Im not sure why they cant just choose from the list straigh through, but this is what they want, so I gotta try and give it to them!

Any help is appreciated! Thank you!

 

[Darrylles]

Hi BJ,

Search Google on 'Relational Database'.

You'll soon learn "why they cant just choose from the list straigh through".

They can, but why are 'they' expecting you to know how to do this correctly? Who is they?

Kind regards,

Darrylle


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[BJNK]

Hey, sorry I may not have made that clear. I am quite familiar with relational databases, although this is the first large scale database I have ever been responsible for, and some of the intricacies - mainly the fancy presentation is new to me.

What I meant is why they want it to narrow down on each field when there are only about 50 records for this particular part and all are uniquely identifiable and fairly easily identifiable by their Description #. But thats not the point, "they" ie my boss that has a slight understanding of databases, and that actually makes it harder to work for him because his idea is skewed from reality. But they want it to narrow down what choices are available going from left to right or in my post top to bottom, and thats what I would like to try and give to them.

So to sum up a long winded post; the database is in good working order, tables are normalized, queries run and work properly (both with a little help from here) and the reports look good. I just need more knowledge/experience with creating profesional looking forms.

 

[PHV]

Something like cascading (or dependants) combos ?
Feel free to visit the FAQ areas.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BJNK]

Thank you PHV, sometimes those FAQ's sure are helpful

Now I have made a test form for the form I want to create, creating combo boxes manually and having them show exactly what I want through cascading elimination. My queries and coding is based off of this post:

http://www.tek-tips.com/faqs.cfm?fid=4289

The code and queries are edited for my information, and like I said work with my test form.

The problem is in my real form, it is not just one set of comboboxes, it is a form set up for Continuous forms. Each product requires about 15 different materials, so each record needs to reflect the new material. When I fill out one record it works great, but when I go to the next record it requerys any of the records above it and completely loses all the previous data.

The code Im using I will paste below. I do not really know VB very well, so its a trial and error thing. If anyone knows what I can do so that it only requerys the specific record it would be a huge help!

Code:

Private Sub cboCategory_AfterUpdate()
cboType.Requery
cboDetail.Requery
cboDescription.Requery
cboWidth.Requery
cboLength.Requery
End Sub

--

Private Sub cboType_GotFocus()
If Len(Trim(Nz(cboCategory, "") & "")) = 0 Then
MsgBox "Please Select Category First"
cboCategory.SetFocus
Else
cboType.Requery
End If
End Sub
--

The above code is also used in each progressing combo box to requery each based on the previous box and set the GotFocus.. It works prefect in the single form, but in continuous form it just keeps requerying everything! Please help!

Thank you!