Who wants a Challenge? Issues recording data to/from multiple forms

[Crystan]

Hi there!

I'm having some issues with my database and i'm constantly swearing at my computer!

I have a large DB, with multiple tables and forms. Its for a goods in and recipe trace system I'm settings up for my employer. We're only a small company so don't need anything special, just simple recording db...

I have;

1. A "Goods in" form that logs every ingredient that comes into site, and auto assigns an internal batch ref
2. A search function form that enables me to search for batch codes, dates or internal batch ref to find specific records
3. a recipe building function form that enables me to "build" recipes using the goods in information, and save the recipes and their target weights
4. Recipe collation system - select a recipe from a drop down list (that you have saved from the recipe builder) and it brings up all the recipe data in a sub window. You then fill in how much of each ingredient you used in the recipe and what ingredient batch codes you have used for each ingredient, it assigns a finished goods batch code.
** 5. a finished goods table - that records batch codes and the recipe information for that batch


My issue is between 4 & 5, the recipe collation and the FG table.

I can't seem to get it to save the information i input regarding ingredients actually used, and ingredient batch codes for the recipes. I've tried multiple things and so far nothing is working...

I've included a screenshot with outlines and information below;

Attachment 38525


So, starting at the top:

Job number is an autonumber field - Daily_FG Table
Department - which department the recipe is for (there are 3) Recipe_Ing table
Recipe_ID is the recipe ID saved from the recipe creation form that is being called: Recipe_Ing table
Todays Date is the date in 3 digit julian format - Daily_FG Table
Batch Code: a calculated field that gives a batch code based on: Department, Julian date, Mix number - used in Daily_FG and RECIPE COLLATION
Mix Number: self explanatory
Recipe name: this drop down list chooses a recipe that has been saved from recipe creation form, upon changing it loads up the recipe ID field, the instructions, the department and the recipe information for the sub form - All in Recipe_Ing table
Method: preloaded instructions/method for the selected recipe

Trace tag
-------------

Simple... copied data to make it easier for employees to write out the trace tags required - ignore this, it isn't saved



MASTER SETTINGS - COLLATION fields
-----------------------------------------------

This sub form is whats causing issues... as you can see, the ACTUAL WEIGHT and LOT_USED fields say #NAME? and can't be edited.
The fields in the sub for are for Recipe_Ing Table, not RECIPE COLLATION. When changing to RECIPE COLLATION fields, nothing works; regardless of relationships. Field properties are setup the same, relationships don't seem to work...


Recipe_Ing fields

Recipe_ID - KEY
Ingredients ID - KEY
TARGET_WEIGHT
INSTRUCTIONS
ACTUAL_WEIGHT
LOT_USED

RECIPE COLLATION fields

REC_ID
REC_NAM - KEY
ING_NAME
YIELD_TRG
YIELD_ACT
ING_LOT
BATCH_CODE_REC

DAILY_FG fields

DAILY_FG_ID - KEY (Job Number field)
RECIPE_ID
RECIPE_NAME - KEY
JULIAN
dept
BATCH CODE
FG_ID
INSTRUCTIONS
FG_DATE


IDEAL SCENARIO

The recipes should be saved with each ingredients batch code per ingredient and amount weighed out, ie the #Name? information should be able to be filled out and all the data saved for that specific batch code under 1 entry, then the recipe can be called again another day/same day (another mix number) and that data is saved under a different batch code and so on... These should be saved using the batch code as a reference.

I've tried alot of combinations over the past week and I'm confused! multiple forms are tricky!!!

ANY HELP WOULD BE GREATLY APPRECIATED!

many thanks

DCJ

[rpeare]

Your attachment is bad.

I would suggest you upload a copy of your database with no company specific/private information in it for people to review, going based on your description is not going to be super helpful.

rp