Form/Subform field and record issue

[Crystan]

My previous post wasn't well enough displayed so I am writing a new post with a copy of the database. This issue is still not resolved.

I have made a form with 4 buttons on that will show you the forms that I'm having issues with.

The two buttons on the left (Goods In and Recipe) work fine, its the two on the right that are the issue, but are linked with the data in linked tables with the other two.

On the right are 2 forms. Both called COLLATION. Both have the same layout as the bottom Collation form is a copy of the top, but what I am trying to get working! (I always back up before editing, force of habit)

So walking through... When you open the Collation form on the top, Click the new record button to clear all fields. Next, Type a 1 in the red Mix Number box. Next click the Recipe Name combo box and pick a recipe. These are recipes I've whipped up for demonstration purposes. When you choose the recipe, a lot of fields autofill. Ignore this. However the box above the Recipe name combo box (Batch Code) will autofill. THIS is important. You will also notice the subform below autofil with the recipe data. It will show Recipe ID, Each Ingredient, and the Target Weight. These fields are created when we use the Recipe form on the main page I mentioned. all working well.... The other 2 fields (Actual weight and Lot_Used) remain blank. This needs to be filled in by the user. Now close it and open the red Collation Form on the main page again.



Everything is the same here. However a new field was added to the sub form. "BC_Collated" or Batch Code was added, but you will notice NOTHING else loads up. If I remove the batch code field, everything works again. This is no good.

What it needs to do:

I need the form to save a record, where the Batch code at the top of the page is filled in for all the ingredients-linking that specific recipe information (amounts and Lot used data). So when I do a search for a batch code, it will show me all ingredients data used within that batch. So basically the first form, but the BC_Collated should be the same on all ingredients and be the same as the BATCH CODE field in the daily_fg table \ form. When starting a new record, actual amounts, lot used, and batch code should be blank ready for new entry...

Is there anyone who can help with this please?

please note - the file is 1.3mb so is in zip file


Thanks guys in advance. This is causing me to pull out what hair is left!


CrystanFOR ONLINE HELP.zip

[rpeare]

Couple things.

1. You should not use spaces in any of your object names, it's going to cause you grief in the long term (or any other special character other than an underscore _)
2. Your form Daily_FG subform is actually referencing the recipe table. Which is not what you want I suspect, especially if you are filling in actual weight and lot used. What you probably should be doing is copying the recipe ingredients to another 'batch table' (maybe Daily_FG_Ingredients) with the associated Daily_FG_ID.
3. You'll want to change the record source of your subform to reflect this change in table structure
4. You'll want to re-define the link field (Daily_FG_ID should be common to both tables)

With this structure you'll be able to review your history, the way you have it right now, each 'mix' will overwrite the last and if you have more than one batch of the same item in the works at the same time you will have your people overwriting each other's data.

If you had intended to use this structure and your 'Recipe_Ing' is supposed to be performing this, that's not what it's doing. You'd need to carry the Daily_FG_ID to this table so your database didn't get confused between multiple batches of the same recipe.

Finally, you are linking the form/subform on the two forms differently.

On Daily_FG you are linking on the recipeID, on Daily_FG_Copy you are linking them on batch code/bc collated which is why you're getting unpredictable results.

[Crystan]

Its driving me bonkers. If all the others forms didn't work Id start from scratch!

So I can log goods in fine, all that data is recorded.
Recipe creation works absolutely fine and i can pull the data up its just this part...

Its a massive mess now. I had recipe creation and goods in as separate access files but they weren't communicating at all when adding them as a linked file, so i imported all of them together so it looks a mess...

I've made a duplicate of the recipe_ing table as you suggested, added Daily_fg_id to it, setup relationship and nothing... brings up blank fields on the recipe sub form (Ingredients has Ingredient ID's and amounts in 2 columns).

n00b question...
Is there no VB code to export the data I put in to a separate table? Ie, upon new entry import Daily_fg_id to the columns, and when i press save it exports all the data fields to another table? If so I could have just used the first form I had...

I'm not good with VB so just asking :S

Cryst

[rpeare]

I've had another look at your database and it's a bit of a mess.

1. You are not carrying your primary key/foreign key values correctly. In your tables with a primary key (Ing_List) your primary key is an autonumber fields, but you're storing it as a text value in some/all other tables where the ingredient identifier is being stored
2. You are not correctly linking your subforms on a consistent link critiera as mentioned before.
3. You have an inconsistent naming convention i.e. what is the difference between Ing_List and Ingredients Table
4. You are not storing information in the correct tables. For instance in your INV_STOCK table, every time you receive the same item it looks like you are recording a G/ML Per single unit. This should be in your ingredient table, not the receipt table because presumably it would be the same for the same product time after time.
5. You should not be updating your INV_STOCK table to show quantity on hand, you should be calculating it based on your receipts vs. your usage with some method to adjust the on hand portion (probably by creating a way to add to the INV_STOCK table a negative amount to account for losses in spills/accidents, etc)
6. You are using field names that break with normal database conventions. You should not be using spaces in any of your object names (field names, table names, query names, form names, etc), you should also not use any character that is not an underscore (_). i.e. your G/ML Per single unit should be something more like G_ML_Per_Unit. Using spaces or any other special character will cause you grief in the long run.

In my mind you have a lot to fix in this database and moving forward with what you have will not work. You will need, at the very, very minimum a subtable to Daily_FG that's something like Daily_FG_Ingredients. You would copy the ingredients of the recipe on the main form to this table when you click your save button. You would need to create a unique key index on your subtable to prevent someone from accidentally adding the same ingredients multiple times.

[Crystan]

Thanks for your response, I am ... well... not good with this. everything in the DB so far has been second guessing from online tutorials and alot of scratching heads... It is indeed a mess. The imports from the recipe and goods in lists don't help. I may have to go to the drawing board again with it and start again.

In answer to your question, ING_list was used first, I later updated to the Ingredients List table, adding more information on it to simplify design ie so the person inputting doesn't have to fill in as much information...

So...

The goods in db pulls information from SUP_ID (suppliers), Ingredients Table and stores in on the INV_Stock table. I need to add a stock level count, but for now it just needs to log whats coming in, not control stock... thats another project...

The Recipe creation requires pulling information from the Ingredients Table, and storing the information along with values into the Recipe table (Recipe ID, method, name) and the Recipe_ING (Linked by recipe ID, will show the ingredients name and amounts).

The Collation should pull the recipe based on the Recipe ID (when selected from the drop down list). It needs to load up the ingredients and target weights. It needs to then save whatever information is inputted into the Actual weight and Lot_IDs into a duplicate Rec_Ing table that has the Daily_FG_ID and Recipe_ID fields?

Daily_FG_ID and Recipe_ID as autonumbers in all tables...

would this be correct?

[rpeare]

to be clear, you should be using the primary key/foreign key as your link between tables, there is a very limited set of circumstances where I would store anything but a primary key/foreign key value. When the primary key is an AUTONUMBER (which I think all of your tables are, this is a good thing) the corresponding foreign key field in any other table should be LONG INTEGER, *NOT* text. Secondly, I would suggest maintaining consistent field names. For instance if you have an ingredient table I tend to label them something like I_ID (first initial of table, followed by _ID) then use the I_ID field name in all subsequent tables where you are referencing your ingredient table. It'll make things much, much, much easier for you to see where your links are broken. I also tend to suggest you put a prefix on all your objects to make it easy to discern where to go in case of a problem i.e. tblIngredient, qryIngredientSublist, frmIngredient (Data entry screen for tblIngredient), etc.

what I would have is something like

tblIngredient
I_ID  I_Name ---> ingredient related fields
1     Eggs
2     Flour
3     Yeast

tblRecipe
R_ID  R_Name ----> other recipe related fields
1     Bread Version 1
2     Bread Version 2
3     Bread Version 3

tblRecipieIngredient
RI_ID  R_ID  I_ID  I_Qty  ---> other recipe specific ingredient fields
1      1     1     5
2      1     3     10
3      1     2     1

tblJob
J_ID  R_ID  J_Multiplier J_Date ----> other job related details
1     1     3             1/1/2019
2     1     5             1/2/2019

EDIT (forgot this)
tblJobIngredient
--this would contain the ingredient items from the recipe along with whatever other fields you needed per ingredient per job

the multiplier on the job could be used to multiply out the base recipe by whatever factor you want (I know it's not that simple in baking but you get the idea)

Finally when you 'save' your job, force an append query to run to add all ingredients related to the selected recipe to be added to the specific job.

[Crystan]

Again thank you very much for your advice.

I have since copied/renamed many of the tables and forms, renamed and reassigned the fields properties and looked at a simpler relationship set. I've included the file below. I IS trying to save the data under what I need, but for some reason the Collation sub form isn't now loading any of the ingredient data at all?

Can you have a look at what I'm done wrong? I'm a bit baffled @.@

FOR ONLINE HELP V2.zip

[rpeare]

FOR ONLINE HELP V2.zip

It's still a bit of a mess. I have reviewed the tables tblDailyFG, tblIngredient, tblJobIngredientFG, tblRecipeIngredient, tblRecipe and deleted all the fields from each table that are not necessary. There are some fields I don't know what they do or what they're intended to do so I left them alone. There are some calculated fields you do not need (Julian being one of them) calculated values should not be stored in your tables, they can be calculated any time you run a query or report etc.

I also modified frmJobIngredientFG and I think Recipe Form because I was verifying something and couldn't because the form was screwed up as well.

I've put in a 'get ingredients' button on your job form that copies all the ingredients for the recipe on the main form into your subtable and displays it in the subform

[Crystan]

Good morning, and thank you very much for the assistance. I've taken a look at the form, I really need to look up tutorials on queries!!


What a mess indeed! however I'm now in the process of removing certain parts.

I have added pieces back in and made some minor alterations and it is now working perfectly! I took the the vb code you put in for the button press and added it to the top of the On change part of the recipe select so it does it automatically. Batch code and Julian are now sorted aswell, unfortunately Department, Julian date and Mix number all make up the batch code so all have to be recorded, but this is pretty simple, even for me

However I feel it viable to add the Daily_FG_ID at the end of the Batch code for simpler search/trace results. I think its just about there! Results below


Thanks ever so much for your help! One step closer to completion!

I have a question though for future ref:

Whats the +! next to the query you made?

Kind regards

Cryst

[rpeare]

There are several types of queries the green + means append query
The pencil means update query
The red X means delete query

and so on