Creating headings when a field changes in a form

[martinbanks]

I am creating an order entry system and am having a few hiccups/complaints. I am very new to Access (about 2-3 months experience) so don't know many of the possible solutions I could explore, so I'm really just looking for points in the right direction, I don't expect anyone to write my database for me

I have created a form for the entry of an order, on which it currently holds up to 9 product options, of which each product can have up to 10 variations. To explain; it is foam cutting for settee parts, so a product can have for example a chair, 2seater, 3 seater, footstool etc as some of it's variants. (Ideally, I will up it to about 20 product options once I have ironed out other wrinkles, but for now 9 is working ok.)

This form has an Order Number text box, and has combo boxes to select a customer which narrows down the 9 product combo boxes to just that customer's products. Then once a product is chosen it fills in the (up to) 10 variants next to text boxes where the staff will fill in the amount ordered.

Then a button at the bottom is clicked once the order is all inputted, which runs 9 append queries, one for each product, that add to 'tblOrders' the product details and the order number. Finally, it also runs a select query on 'tblOrders' and outputs it in a form which displays the items needed to be cut to make up the order.

Most of what I need to do now is formatting/tidying up work, the first of which is what I am asking for help with here;

Each product will comprise of a number of different foams. I need each foam to be in a separate box, or be split up and headed in some way. At present it is a continuous form which in design view has only one line. The query sorts the items by 'FoamGrade', so they are all together.

Do I need VBA code or a formula or other, to get it to see that the entry in the 'FoamGrade' field has changed, insert a blank line and put that foam grade as a sort of header? (I will then hide the 'FoamGrade' field on the form as it will be redundant.)

 

[boblarson]

...Most of what I need to do now is formatting/tidying up work, ...

Sorry to say that I don't think it is. Your design appears to be flawed if you say select 9 options up to 20. If those are separate fields then you do have a normalization problem. If you design it properly normalized then you can add any number of options by adding RECORDS not fields. I don't know if I read that wrong or not but that's how it came across.

As for your other problem - the headings - I think it might be useful if you can upload a copy of your database (with bogus data of course) so we can see what you are talking about. I'm not exactly getting a clear picture in my mind. (or if you can't or won't, then maybe a few screenshots, but that might not be as helpful).

 

[martinbanks]

Sorry to say that I don't think it is. Your design appears to be flawed if you say select 9 options up to 20. If those are separate fields then you do have a normalization problem. If you design it properly normalized then you can add any number of options by adding RECORDS not fields. I don't know if I read that wrong or not but that's how it came across.

I think I have confused you with my poor use of terminology. I am fairly sure that the tables etc are normalised correctly. I was saying that the form shows the same grid of boxes for 9 products, but sometimes a customer will order more products than that on one order. Making the form show 20 instead of 9 will be something I will look at later, wasn't really worth mentioning I suppose and it will only be a problem on the rare long order.

As for your other problem - the headings - I think it might be useful if you can upload a copy of your database (with bogus data of course) so we can see what you are talking about. I'm not exactly getting a clear picture in my mind. (or if you can't or won't, then maybe a few screenshots, but that might not be as helpful).

I wanted to embed pictures to save me trying to (poorly) explain what is on my screen, but I couldn't see how to do that. I'm more than happy to share the screenshots (or even the database, nothing in it is sensitive or private, just item dimensions).

 

[boblarson]

I think I have confused you with my poor use of terminology. I am fairly sure that the tables etc are normalised correctly. I was saying that the form shows the same grid of boxes for 9 products, but sometimes a customer will order more products than that on one order. Making the form show 20 instead of 9 will be something I will look at later, wasn't really worth mentioning I suppose and it will only be a problem on the rare long order.

So you don't have repeating fields for the options? You just have a junction table like:

xrefOrderOptions
OrderOptionsID - Autonumber (Primary Key)
OrderID - Long Integer (Foreign Key)
OptionTypeID - Long Integer (Foreign Key)


And that way you can store as many options as necessary?

I wanted to embed pictures to save me trying to (poorly) explain what is on my screen, but I couldn't see how to do that. I'm more than happy to share the screenshots (or even the database, nothing in it is sensitive or private, just item dimensions).

See here for how to upload images and place them inline here.

 

[Simon_MT]

This sounds like a sort of Bill of Materials but rather than having the constituent parts there is the ability to customise each Order and then build the Order. The alternative is to have a file with each and every product permutation, while this maybe normalised it causes a bloated Product list and one that becomes too onerous to manage.

So back to your question about Headings, there is no straightforward way to signify when each FoamType. There is conditional formatting on a different foam type or putting a control on the Form Header and color coding (to FoamType) the Form Header's background.

Unless things have changed, images on Continous forms are problematical as the Control is re-iterated throughout the subForm. I got away with a DoubleClick action but this worked because there was no Bill of Materials, each Product is unique.

Simon

 

[martinbanks]

Nice one, pictures should make it clearer.

Pic 1 is the form for entering, as you can see it accommodates up to 9 products (the bottom row is just partly visible) and each product, once selected, fills in with it's variations (a max of 10, though generally there are just 5)

Pic 2 shows the Orders table that it appends the info to

Pic 3 shows the output to factory workers. I am at their mercy and they are crying out for the foams to be headed, like in Pic 4 (which I have knocked up in paint).

 

[Simon_MT]

Perhaps, one way to get around this is to create a report which will Group with Headers and Footers, a tabulated form is another option but not a very good one!

Simon

 

[boblarson]

...crying out for the foams...

FOAMS? What are foams?

 

[martinbanks]

FOAMS? What are foams?

The different grades of foam.

Anyway, it seems like I've been in the wrong section and should be using reports.

Thanks for all the help.

 

[gemma-the-husky]

FOAMS? What are foams?

i think the foams are the different raw materials that go into the manufacture of the seating.

this is probably quite complex. we have a data analysis issue - managing and analyisng the constituent parts of the order. and maybe a optimisation / linear programming problem - cutting the materials in the most effective way