How do i keep a field or two in a form from updating between records?

[johka801]

I am VERY NEW to access and biting off more than I can chew. My company is a small consulting firm that does laboratory analysis. We have been using excel to track laboratory analysis results and I KNOW it's not the right way to do so. Basically, what I want to achieve is to create a form that looks up a BATCH ID (a quality control number for a group of client samples), pull in the associated job number and description for that BATCH ID, and then allow the user to fill in AUTONUMBERED fields (1 - XX) for each sample. The samples will have various data points (description, color, analytical results, etc). Where I am having trouble is in "PINNING" that BATCH ID, Job #, and Job description when going from sample 1 to sample 2, etc.

Ideas? Help? I'm a newbie. Be gentle.

[TheSmileyCoder]

Hi Johka801 and welcome to Bytes.
Your doing good for a fresh player in the forums. Its always nice to know both your problem, but also your "level" of expertise if you will. Makes it easier to reply.

The first thing to recognize is that your information must be stored in at least 2 different tables. One should the batch information, and the other should hold the sample information, with a link back to the Batch Table. Otherwise you will be filling in Batch information repeatedly, with high risk of error.
I strongly urge you to read this article
Datebase Normalization And Table Structures
A good data structure is 90% of the job.

Now with the information in the Batch table, you would go on to create a Main form, and with the Sample table another form should be created. Now the sample form, can be placed inside the main Batch form (just by drag and drop actually) and Access should come up and ask you how the two forms should be linked together. If you have named the BatchID field the same in both tables, access will usually link together on that field automatically.

Now whenever you write sample information in the subform, it will automatically be linked to the batch and its ID, and you only have to maintain the Batch information one place.

Hope That Helps
Anders Ebro // TheSmileyCoder

[johka801]

Ok. I have created two "dummy tables" just to test this. One has my batches and their associated job numbers and clients. The second table has the batch id, sample numbers, and sample results. Here is my question:

The main form will pull from the batch table and ask the user to select the batch id from a drop down. The second form will be embedded in the main form and ask the user to enter results for the samples and these will be stored in the table for sample results. Is this correct? And. Should I be referencing the batch ID in both forms?

[zmbd]

The Parent and Child forms will be linked on the keys.

This is most basic:
Tbl_Batch
[Batch_pk]AutoNumber - Primary Key for table
[Batch_lot]text(25)
[Batch... remaining fields...

Tbl_Results
[Results_pk]AutoNumber - Primary Key for table
[Results_FK_Batc h] One to Many relationship with tbl_Batch - this is a foreign key refering the the tbl_batch and relates the two tables.
[Results_... remaing results fields....

frm_Batch: has a record source based either a query or the table tbl_batch

subfrm_results: has a record source based either a query or the table tbl_results. This need not show the field [Results_FK_Batc h]; however, this must be part of the recordset the form is based on. I show the linking field during the intial design just to make sure the master/Child link is setup properly and then remove it for final form design.

Add subfrm_results to the parent form frm_Batch, if you have set the table relationships on the correct fields then the parent-child-link fields should be set for you by Access

You can check that the relationship was created betwen the two forms while in the design view by:
- opening the properties
- in the dropdown list select (using my example) "subfrm_results " then the data tab.
- You should see two fields [Link Master Fielda] and [Link Child Fields], these entries would be (for my example):
[Link Master Fielda]=Batch_pk and [Link Child Fields]=Results_FK_Bat ch

bol
-z