Unbounded Text Boxes for Filtering of Products Table

[drow]

Hello All,

I have been working for some time to develop a pipe fabrication database. You can find more information on the background of this project at the following two posts:

https://www.accessforums.net/databas...ign-32250.html

https://www.accessforums.net/databas...und-41969.html

What I am now trying to accomplish is the forms which the user would interface with to add "products". In this case, the "product" is based on the user specify a certain set of variables which leads to a unique productID and associated price. To establish a price the user must specify the following variables (my intent was to have these as cascading drop downs).

• Component
• System
• Task Category
• Task Type
• Task
• Rating
• Size

For each unique combination of these variables there is a set price. In the attached images I have shown the relationship diagram for this project, some sample data from the products table, as well sample data for each table that feeds into the product table (apparently there is a 5 attachment limit so the remaining tables will follow in a second post).



The form that I am trying to complete would allow the user to select and store variables into the "tblDrawingLineItems" table. I would like to have the form in a table style where, when the user has complete one row, they move down one row and repeat the process. I am struggling to create this form right now because I want to be able to have cascading combo boxes to have the user input the variables to reach a product ID, and then have this product ID stored in "tblDrawingLineItems". However, if I make a unbounded combo box with a row source being a query based on "tblProducts", this unbounded combo box has the same value for every row in the form. Do I need to then have a field in the "tblDrawingLineItems" that stores the selection for each variable? I am probably missing something vary simple and fundamental (lesson: don't work on access projects at midnight).

Anyway, if anyone out there can make sense of my question and has some input, that would be greatly appreciated as I am hoping to meet a self-imposed deadline of completing this form by tomorrow PM.

Please let me know if any other information would be helpful - further attachments to follow.

[drow]

Remaining attachments... I have removed pricing as I am uncertain of confidentiality restrictions surrounding this data.

[ItsMe]

Using a form in Data Sheet view or a Continuous form for data entry can be tricky. Add to it the desire to include values from unbound controls to your current record, as you move from one record to another in DS view, and you are working harder than necessary. Use a subform in DS view to show a summary and a Single Form view to edit/add records.

A complex solution I use is to write complex code (not necessarily advanced, mostly DAO) that allows the user to add records to a "Stage" from a filtered DS view subform. The "Stage" (another subform in DS view) acts as a summary of records selected from the list of available records. Unbound controls, of various types, on the main form add values to the selected (Staged) records via a submit button.

[drow]

Hi ItsMe,

Thanks for your quick reply. Just to clarify, for this specific case are you recommending that the data from tblProducts be inserted as a subform within the form that is the control source for tblDrawingItems. In the sub-form the user would then filter accordingly to select a product (i.e. ProductID), and then add this product to the product ID for the form? Please correct me if I am wrong but I dont believe that access has a way to add a subform to a continuous form?

Alternatively, would it be better (if a continuous form were desired) to add fields within tblDrawingItems to store each of the selections that the user makes when specifying their inputs for tblProducts to obtain a ProductID? I would like to use these selections later for additional processing anyway. Initially I was thinking that I should not because when a ProductID is known, these choices must be explicit. Furthermore, storing each of these selections would increase the amount of redundant data stored. However, if it made the functionality work, I would consider it.

Thanks again.

[ItsMe]

First, I am not making a recommendation. I am only commenting on a couple subjects that may be relevant to your scenario.

To add to my first comments, there should not be any duplication of data in your DB. There is usually only one reason to duplicate data and that is to improve the performance of data retrieval.

I believe any approach I may take to address your current scenario would be a far departure from any technique you have yet tried. Consider the fact that forms have the ability to hold variables in memory. If you were to add fields to your tables I would consider adding Yes/No fields to help keep track of the status of records. Another consideration would be to use Yes/No fields in conjunction with a "SessionID", unique to a user and their interaction with the data, and relative to variables held in memory. New variables created by the same user would necessitate a new, unique, SessionID.

[drow]

Hi ItsMe,

Sorry I was not trying to pin you to something there, just trying to understand what you were describing. With regards the other fields you have discussed, these all seem like logical fields to include. Right now I am focusing on building the pricing tables as this is the "core" functionality of the tool, but once this is complete I look at incorporating these other fields.

Do you have any samples that you would be willing to share to highlight the functionality you discussed in your first post? I think I need to see it and be able to poke around to fully understand what you are describing.

Thanks.

[ItsMe]

I could provide samples but I feel they would be too small considering the broad scope of things. Goes hand in hand with me not trying to recommend anything. I am not fully understanding the logic of your approach and it is doubtful you would begin to understand mine without breaking it down first.

So I just made some comments to, perhaps, have a dialogue that could touch on a couple small topics. A small topic could be supported with some code samples.

I understand the variables and different combinations of said variables create different formulas. I manage these business rules a specific way. It is not likely I would deviate from my approach. It would require many hours of my time to test and implement a different approach that would, most likely, not benefit me.

My recommendation would be to try and get it done with single form view first. Your problem may be too complex to manage in single form but you are going to need a way to keep track of which record is being edited. Managing constraints is critical and you will have a large juggling act on your hands with so many possible combinations to manage.

[drow]

Hi ItsMe,

That is fair. I was not expecting you to commit hours of your time to help me - I just thought if there was something relevant to aid in the discussion. I will work on managing this process in a single form as a first step.

However, maybe it is just that I am naive, but I imagine this must be similar to other businesses, just on a larger scale with regards to the number for options. Say, for example, you have three cars: car A, car B, and car C. There are three options for car color: yellow, red, and fuchsia. The combination of car and color would determine a price, and the options could be a cartesian product, or only certain colors could be available for certain cars (but obviously everyone would want fuchsia as an option). I could be wrong, but I would view this problem as a smaller version of the one that I face in that rather than a single product having a price, criteria must be established to determine a price.

Anyway, I will proceed with single form and see how that goes.

Thanks for the ongoing support.

[ItsMe]

No, yours is not a unique business rule. It is easily recognizable. The car analogy works fine. You can interact with web based apps that offer pricing for vehicles that are customized to the user's specification. As you select variants for various fields within a single record, the field is analyzed for design conflicts and then added to the formula. The formula calculates at the end and offers a report.

I can imagine this whole process being logged in one record in an Events table. You do not need continuous forms or DS view to accomplish this. An option group, listbox, or combobox could provide user input for any given option/field. If you were to add bells and whistles, you could introduce subforms and a lot of code.