Can I create cascading combos using fields all in the same table? (1 Viewer)

darren_access · Jan 10, 2018

I only 2 Tables: Project and LineItems.

Lineitems contains a bunch of text fields to hold size, qty, mfr, model, sub-model, notes etc. tied to one project (1 to many)

I currently choose a project and then open a form/continuous subform where user enters the lineitems.

without being a fully normalized data model, can I still do something like this:

1. There is a MFR (manufacturer) text box and also MFR combobox selecting a distinct list from the lineitems tbl allows the user to either choose an existing entry or create a new one in the text box.

2. Based on the value chosen in MFR field (if one is selected) cascade several combo boxes for Model, Submodel and then notes? Keeping in mind all fields are in the same lineitems table, but using distinct selects to populate the combos.

I may go fully normalized data model in next phase but wondering if i can pull this off right now.

Thanks

MarkK · Jan 10, 2018

I would normalize the data as my job #1. The data model is the map of the reality, and that model is either correct, or it is not. If it needs to change. change it ASAP.
hth
Mark

darren_access · Jan 10, 2018

I agree actually, so I am going to add the extra tables for MFR, Model and Notations.

My ask then is while still using the sub-form based on lineItems, when a user enters a new MFR, model, or notation, how might I also update the associated tables, and not just the lineitems tbl when user moves onto the next row? The idea is to dynamically do everything from the same sub form, and not require the user to add values to the other tables first.

eg 1.
User begins a new record and there is already a MFR in the list they can use. Then associated models appear in the next combo box, from which they can choose or enter a new one. etc etc

eg 2.
User begins a new record but no existing MFR exists yet so they can type it into the text box. Etc for the other fields. then upon hitting SAVE, the next record will be able to include the new values in the combo boxes.

In the beginning, all entries will be new, but as more records are added the more automatic the entry process becomes until even the notations can be selected based on model, and just tweaked as needed.

Only if a new unique value is entered will it be added to the associated tables, and not just the lineitems table.

Does this make sense?

I want the users to populate the tables over time.

MarkK · Jan 10, 2018

What is a "line-item?" It's not clear to me what the purpose of your system is, and everything must be assessed in terms of whether is serves your purpose or not.

But I would never name a table "LineItem." Is this an Invoice or a PurchaseOrder or something like that? How is Manufacturer related? If this is a PurchaseOrder, for instance, then the Supplier is in the PO table, and the manufacturer is related to the product which is specified in the PODetail table, and so in a PO--for example--you should never have to deal with the manufacturer. The manufacturer, typically, should be a property of the product, and LinkItem (my guess at what that is) should link to the product.

But again, what is a "line item?"

hth
Mark

ps, where in Langley? I live in Chilliwack, just down the highway. Cheers,

darren_access · Jan 10, 2018

MarkK said:
What is a "line-item?" It's not clear to me what the purpose of your system is, and everything must be assessed in terms of whether is serves your purpose or not.

But I would never name a table "LineItem." Is this an Invoice or a PurchaseOrder or something like that? How is Manufacturer related? If this is a PurchaseOrder, for instance, then the Supplier is in the PO table, and the manufacturer is related to the product which is specified in the PODetail table, and so in a PO--for example--you should never have to deal with the manufacturer. The manufacturer, typically, should be a property of the product, and LinkItem (my guess at what that is) should link to the product.

But again, what is a "line item?"

hth
Mark

ps, where in Langley? I live in Chilliwack, just down the highway. Cheers,

Lineitem would essentially be a denormalized table to hold all information for a given project. It is used to print out a "schedule of equipment" for a project that includes all the meta data about a product, model, mfr, etc but also qty, Tag, (used on blueprints & different for every dwg). This all used to be done in Excel but we spent too much time repeating the same data entry over and over again with only minor tweaks such as QTY, Tag etc. just to achieve a printout and then a combined PDF with associated shop drawings for each unique item.

However, Lineitems would never be updated when a MFR, model or attachment is changed. It has to be a snapshot in time. Old projects will be edited only briefly, then a final PDF is sent to engineers for approval, after which the project will be locked down.

I hope this makes sense.

And hello from Langley/Mission. I survived the ice storm last week but the damage to trees is astonishing!

I have attached a few screenshots that include:

-data model with the new mfr, model and notes tables, not yet in use ;-)
-the data entry screen which is all text entry at present, hoping for cascading combos to save the pain
-print schedule
-print drawings

Mark_ · Jan 10, 2018

@Darren,

I am guessing you wish to look through purchases to dynamically allow Manufacturer, model, and sub-model to be selected based off of previously purchased equipment.

This is not a good idea.

Users will tend to look at this as "This is available" not "This is to help me put in data correctly". You would be better served with another table for doing these kinds of lookups. Let the end users input those values that they believe are correct, this reduces your issues. This becomes especially true when different end users want to enter the same manufacturer or product using different values (think "Microsoft" VS "Windows" when an end user tried entering Access for Windows).

darren_access · Jan 10, 2018

Mark_ said:
@Darren,

I am guessing you wish to look through purchases to dynamically allow Manufacturer, model, and sub-model to be selected based off of previously purchased equipment.

This is not a good idea.

Users will tend to look at this as "This is available" not "This is to help me put in data correctly". You would be better served with another table for doing these kinds of lookups. Let the end users input those values that they believe are correct, this reduces your issues. This becomes especially true when different end users want to enter the same manufacturer or product using different values (think "Microsoft" VS "Windows" when an end user tried entering Access for Windows).

Normally I would agree with you but there will only be 5 of us using this internally. I want to be able to choose one that exists, but if not, to enter a new one in the same place (or right next to it). Then add the new one to the list for the next row. Excel has this feature built in (as you begin entering a value in the next row it auto fills).

I'd also like the same functionality in the attachments, so we can avoid the navigation dialog.

Am i hoping for too much LOL??

Mark_ · Jan 10, 2018

For your use, and your use only, I would put in a query on your "Lineitem" file for the field you want to use in your by-value lookup. Edit it to be DISTINCT records. Change each field to a combobox and have the source be your query. Do not limit to list.

This will do what you are looking for, but I'd want to put a giant banner up letting the other users know it is drawn from existing lineitems and WON'T remove entries that are no longer valid.

Just make sure you put a giant note in your database docs somewhere letting EVERYONE know what you did and how it works. Over time you will want to work out a way to mark values as "No longer used", just to make sure they don't appear again.

Pat Hartman · Jan 10, 2018

Your schema does not look normalized to me. Why are the bottom three tables not related to the line item table?

Mark_ · Jan 11, 2018

@Darren,

This is more normalized and may work much better for you.

Projects have Project Items (ProjItem) and are associated with People (looked up from People table with a reference saved).

People have ways of being contacted (phone, alt phone, mobile, skype, Email, what have you).

When a project is updated you add an update record (T_ProjUpd) rather than just save the last person.

Each project item can also have multiple attachments.

I use T_ for tables, Q_ for queries, ect.. Normally I use a second (or third) character to help differentiate sub types of objects.

Prefacing your fields with "Fld_" is fairly redundant. The only prefixes I normally use are when the type of data helps dictate that it be included (Dt for Date/Time fields) so that I don't mistake it when coding for another type of data.

Any time you start numbering fields you are best off having them as child records in a child file. You will either discover you NEVER use the "_2" or "_3" fields or you find you really do need more than just an arbitrary number of them.

As has been posted you'd be best served by getting this portion of your database straight BEFORE you worry about making it fancy. You'll discover far less problems down the road with a strong foundation. As is I'd say your road is being laid on a bed of solid pudding.

darren_access · Jan 11, 2018

Mark_ said:
For your use, and your use only, I would put in a query on your "Lineitem" file for the field you want to use in your by-value lookup. Edit it to be DISTINCT records. Change each field to a combobox and have the source be your query. Do not limit to list.

This will do what you are looking for, but I'd want to put a giant banner up letting the other users know it is drawn from existing lineitems and WON'T remove entries that are no longer valid.

Just make sure you put a giant note in your database docs somewhere letting EVERYONE know what you did and how it works. Over time you will want to work out a way to mark values as "No longer used", just to make sure they don't appear again.

This is the approach I adopted and so far working ok. The subsequent combos are not limited to previous parent type but that's ok for now.
In reality, the number of mfr's, models and sub models are very small, so the lists won't get too long, as we only create about 2 or 3 reports a week in total, with fairly regular mfr, model and notes.
The primary objectives are to:

-reduce the painful use of excel because of formatting and repetition
-reduce time needed to locate and manually edit the shop drawings for each product, c/w project, engineer etc
-combining and formatting of both the schedule and dwgs in adobe (horrible!)

Thanks Mark!

Mark_ · Jan 11, 2018

@Darren,

If you create an MFR query it will turn out to be

"SELECT DISTINCT MFR FROM <table> ORDER BY MFR"

For Model it will be

"SELECT DISTINCT MFR, MODEL FROM <table> WHERE MFR = " & Me.MFR & " ORDER BY MODEL"

Do a search for cascading comboboxes for more detailed instructions if this doesn't answer your question.

darren_access · Jan 11, 2018

Mark_ said:
@Darren,

If you create an MFR query it will turn out to be

For Model it will be

Do a search for cascading comboboxes for more detailed instructions if this doesn't answer your question.

GREAT! Thanks again.

darren_access · Jan 11, 2018

Mark_ said:
@Darren,

If you create an MFR query it will turn out to be

For Model it will be

Do a search for cascading comboboxes for more detailed instructions if this doesn't answer your question.

Mark, in this line...

"SELECT DISTINCT MFR, MODEL FROM <table> WHERE MFR = " & Me.MFR & " ORDER BY MODEL"

is Me.MFR referring to a column in the MFR query or the combobox identifier that holds the MFR value selected?

Mark_ · Jan 11, 2018

@Darren,

When you see Me.<SOMETHING>, it is referring to the controls on your form/report. In this case you would replace Me.MFR with Me.ControlName for your combobox.

I am not sure which approach you are using for creating your cascade so I gave a fairly generic example of what the ending SQL will look like.

For a sample app, take a look in THIS THREAD for Comboex; it is post #7.

darren_access · Jan 11, 2018

Mark_ said:
@Darren,

When you see Me.<SOMETHING>, it is referring to the controls on your form/report. In this case you would replace Me.MFR with Me.ControlName for your combobox.

I am not sure which approach you are using for creating your cascade so I gave a fairly generic example of what the ending SQL will look like.

For a sample app, take a look in THIS THREAD for Comboex; it is post #7.

getting clearer now. Thanks Mark

darren_access · Jan 11, 2018

darren_access said:
getting clearer now. Thanks Mark

Alas, foiled by another wrinkle...

The controls are all in a subform, and thus updating a record caused them ALL to update

**plus it was giving me a prompt dialog for each instead of using the values in the previous combos. Obviously I missed an assignment somewhere.

Mark_ · Jan 11, 2018

For the subform, is it a datasheet, single form, split form, or continuous form?

darren_access · Jan 12, 2018

Mark_ said:
For the subform, is it a datasheet, single form, split form, or continuous form?

I forgot to mention it's a continuous form.

I only want the combos in the line where the focus is to change.

Pat Hartman · Jan 12, 2018

Here is a sample that might help you with cascading combos on a continuous subform. It will NOT work on a DS form.

Can I create cascading combos using fields all in the same table? (1 Viewer)

Registered User.

bit cruncher

Registered User.

bit cruncher

Registered User.

Attachments

Longboard on the internet

Registered User.

Longboard on the internet

Super Moderator

Longboard on the internet

Attachments

Registered User.

Longboard on the internet

Registered User.

Registered User.

Longboard on the internet

Registered User.

Registered User.

Longboard on the internet

Registered User.

Super Moderator

Attachments

Similar threads

Users who are viewing this thread