Complete Access n00b, is this even possible...

[Andy.T]

Hi all,

First time poster and completely new to Access and development in general, though I work with devs and know a little bit of SQL (enough to be dangerous).

A friend has asked me to give him a hand setting up a fairly basic product database and frontend. In essence the data is like this...

- Manufactured Products
- Raw Materials
- Labour
- Packaging

I have the database structure pretty much there I think, e.g. products in their own table which then joins to a picklist table (of raw materials and quantities) which in turn joins to a lookup table of raw material details (the names, costs and codes of the raw materials). The labour and packaging materials follow a similar structure.

I have a basic menu system and forms for creating new raw materials and labour types.

The next job (which I always new would get a bit "lively") is to create a form that creates a brand new product along with allowing the user to pick from a list the...

- Raw materials and quantities
- Labour and quantities
- Packaging and quantities

...that make up the product.

Is it possible for a form (with subforms I guess) to write to the main table (the product table in my case) and all the joining tables (e.g. the picklist table) in one go?

I could create products in one form and then have the user go and assign the raw materials, labour and packaging to the product after in another form, but if possible it would be really nice to have it all done in one step.

What would be the best way to approach this do you think? Do you guys know of any reading or video tutorials that sound relevant to this sort of problem?

Thanks in advance for any pointers!

Andy

 

[plog]

Is it possible for a form (with subforms I guess) to write to the main table (the product table in my case) and all the joining tables (e.g. the picklist table) in one go?

Probably, but don't. The way it should work is that one form should be based on one table. With that said, you can use sub-forms so that they appear all on the same screen at one time.

I don't really have a good understanding of your data to advise more specifically. Could you post a screenshot of your relationships and then walk me through how you would set up a new product?

 

[spikepl]

It seems to me that you are working on the erroneous assumption, that you need to do something to save data. Not so. Expend the one hour it takes to do tables' and forms' tutorials, and then look in the sample databases here or download one (google access templates) and see how it ticks.

 

[Andy.T]

Probably, but don't. The way it should work is that one form should be based on one table. With that said, you can use sub-forms so that they appear all on the same screen at one time.

I don't really have a good understanding of your data to advise more specifically. Could you post a screenshot of your relationships and then walk me through how you would set up a new product?

Thanks for that Plog.

I can't post a screenshot of the relationships as my post count is too low so I'll try and describe it more clearly.

The main tables are...

Products (the stuff that gets made) ---> RawMaterialsPicklists (the "recipes" for each product) ---> RawMaterialLookup (the details of all available ingredients to make anything)

In the same way as Raw Materials are handled (recipes and ingredients), there are similar tables for Packaging and Labour.

What I want is to write a new record to the Products table at the same time as writing new records to the RawMaterialPicklists, ProductionTime and PackagingPicklists table (using options provided by dropdowns populated by the relevant lookup tables).

So I might say...

Create a product called "Birthday cake" (with SKU, intro date etc).

And then in a sub form I would be able to say that the "Birthday cake" is made up of...

2 x Flour
1 x Butter
2 x Eggs

That it takes...

1 x Hour of Manufacturing
1 x Hour of Decoration

And that it goes in...

1 x Circular box

The options for the dropdowns of RawMaterials, Labour and Packaging would come from the relevant lookup tables and the only tables I'd be writing to (other than the Products Table) would be the "recipes" type tables of...

RawMaterialPicklists
ProductionTime
PackagingPicklists

Does that make sense?

 

[plog]

That makes sense and my initial advice is correct, as is spikes. There's really no code you need to write to add data to tables. You simply bind the form to a table (set its Record Source Property) and it takes care of any changes (adds, edits and deletes) to the underlying table . Here's a broad sketch of what you should do:

Make a Products form, based on Products table
Make a Raw Materials form based on RawMaterials table
Then you put the Raw Materials form on the Products form

The key to the last step is linking the 2 forms together. You do this in the Parent/Child properties of the subform. You link them via Product. That way when you open the Products table to a specific product, it shows just the RawMaterials assigned to that product. Additionally, when you add a new product, any Raw Materials you add in its subform will be added to the correct product in Raw Materials.

Once you have that, make additional subforms for your other tables.

 

[Andy.T]

Thanks for that, is it not complicated by the intermediary table though (the "recipe" type one)?

I've succeeded in making a Products form and this can indeed make a new product (albeit one with no ingredients).

What I'm looking to do though is to show dropdowns of available "ingredients" and then write these to the recipe table that sits between the Products and Ingredients tables?

So using my Birthday Cake example, I want to write "Birthday Cake" to the Products table and I want to write to the "recipe" table to record how many of what ingredients "Birthday Cake" needs. But I want to read the available ingredients from the Ingredients table (as I don't want people making Ingredients up that don't exist).

My subform can be linked to the Products table no problem but presumably if my subform only looks at the ingredients table it wont understand how to write the "recipe" and if it only looks at the recipe table it won't understand what the available ingredients are?

Can the subform be powered by a query that joins the recipe and ingredients tables?

 

[plog]

So using my Birthday Cake example, I want to write "Birthday Cake" to the Products table and I want to write to the "recipe" table to record how many of what ingredients "Birthday Cake" needs. But I want to read the available ingredients from the Ingredients table (as I don't want people making Ingredients up that don't exist).

I didn't initially get that, but its no problem--if your tables are set up properly. I don't see where you named that table that sits between ingredients and Products, so let's call it ProductIngredients. This should be the structure (or something close) of ProductIngredients:

ProductIngredients
ProdIng_ID, autonumber, primary key of table
ID_Product, number, foreign key to Products table
ID_Ingredient, number, foreign key to Ingredients table
Quantity, number, amount of ingredient that goes into product
QuantityUnit, text, units used to measure Ingredient (e.g. Cups, Liters, Tablespoons, etc.)

This would be the Recordsource for the form which will become a subform on products. You would link it in a Child/Parent relationship with Products via ID_Product field. Then you would have these inputs on it:

textbox input for Quantity
textbox input/dropdown for QuantityUnit*
dropdown for Ingredient~

*You could make it a drop down so that you can control what units are available. This way you will standardize the values that get input and won't have people entering "Table Spoon", "Tbl Spn", "Table Spn" etc.

~This drop down would show the Ingredient name, but store the ID. This prevents users from making up ingredients and allows you to display the name while using the number that relates to it from the Ingredients table.

 

[JLCantara]

With Access every thing is possible... except a secure application.

Attached is my solution to your issue (for recipes only).

Hey! you forgot the Units table: 2 what? kilos, pounds, tons???

Good day, JLCantara

 

[Andy.T]

Thanks both that's amazingly helpful! I'm starting to look at this now.

The bakery thing was just to explain what the database is doing (which is just standard manufacture) but I thought it would be easier to explain with something relatable. And everyone loves cake right?

Everything you've posted is spot on though and completely fits the bill for this use case.

In terms of "units" I'm therefore lucky in that the units are just physical units, rather than measures so I don't think I need to worry about that at this stage. I can maybe look at that later if it does rear its head though.

The real lightbulb moment here for me though, based on what you've posted and the example JLC gave, is that my queries themselves don't need to have joins in them. i.e. So long as a join actually exists between the tables and my tables have queries that go off and get the data I can just exploit the existing table joins in the forms.

Have I got that right?

Thanks again so much.

Andy.T

 

[JLCantara]

Ah voilà!!!

Now that you need is clear, a possibility could be a 3 page form (raw materials, labour and packaging). Product def. posted in the form header, with pages in the detail section...

 

[Andy.T]

Hi guys, me again.

Thanks to the advice on here and lots more reading, watching YouTube videos and experimenting I've got loads done in terms of forms that create items of inventory, forms that allow you to view or edit them and various financial views, I'm just moving on to reports now.

Luckily reports feel a little familiar as it's still the same basic concepts of tables, queries and forms. One thing has cropped up though that I'm a bit stumped on. I'm not sure I'd know how I'd Google this so sorry for pestering you guys...

My friend would like the reports to be quite flexible (in terms of selections and sorting) and so I had the idea that I could build a form that allows you to select these criteria and then click a button which would pass those selections as variables to the report. So far so straightforward (I think)...

How would this work in practice though with changing data. Using the metaphor of the Bakery I gave before, imagine I have the following Product Types in the database...

Cakes
Cookies
Muffins

...and I want the Form that builds the reports to have checkboxes for each of those so you can report on all product types or just some of them if you prefer.

I can hardcode that easily enough (putting checkboxes for each of those existing product types) but that means the moment they add a new product type (Bread perhaps?) my Form needs to be adjusted or else they won't be able to report on it.

Is there a clever way of telling the form to add as many checkboxes as it needs (populated by all the Product Types in my "ProductTypes" table)?

Or am I trying to be too clever?

Cheers,

Andy

 

[JLCantara]

As usual your post is quite obscure: you have table records and sort criteria!

Figure a form that displays on its left side sort criteria and a command button that will trigger the report printing.

You don't say a maximum for product types. Anyway, a very simple solution is to create plenty (greater than max.) of non visible check boxes named cb_1, cb_2, etc, and at load time read the product type table and initialize the cb_... and make them visible. The code associated with the command button scans the cb_ and the checked ones are added to the filter of the report.

That's it!!!

 

[Andy.T]

As usual your post is quite obscure: you have table records and sort criteria!

I'm not quite sure I follow your meaning. Yes I do have table records and yes I want to apply a sort criteria.

It doesn't feel like what I want to do is that odd really. I want the user to be able to tick the product types they want in the report (but have this cater for product types that have yet to be created) and I'd like to have the user select how they want the report sorted (alphabetically or by price).

If I'm thinking or going about this in the wrong way please just let me know in a constructive way. I'm just here to learn what I can, but as I have said, I am a complete beginner so maybe go easy on the generalised criticism of my style of posting hey?

You don't say a maximum for product types. Anyway, a very simple solution is to create plenty (greater than max.) of non visible check boxes named cb_1, cb_2, etc, and at load time read the product type table and initialize the cb_... and make them visible. The code associated with the command button scans the cb_ and the checked ones are added to the filter of the report.

Yep that sounds like what I'm after, thank you for that. I'll read more on initialising and showing checkboxes on form load.

I didn't specify a maximum number in my post as I don't know exactly what this will be. The database has a form for creating new product types so the business will be able to add them as it sees fit. The database currently has 5 but I'm sure they will add a few more in time. My objective, as much as possible, is to have this be a fairly future-proofed, so they don't need to rely on me to make modifications for them.

Thanks again.

 

[JLCantara]

Well, if you want an absolute solution to the product type selection, just do like Access does!!!

Create an invisible intermediate that will open the report selection criteria in create mode (yes sir!) and ADD as many cb as you need (don't forget the label: cb do not come with a linked label). You will understand why form creation is so slow...
Remove the close form box and add a command that will close the form WHITOUT saving the mods.

If you have enough time and patience, a fancy way of handling this issue his to KEEP the mods and adjust the form according to actual product types!!!