Need help with entering data into subforms on a main form

[Dave 14867]

Hello,

I am an inexperienced user trying to use access to give me a way to ensure data entered into documentation is correct and complete. We have records we are required by reg's to create and maintain which are all filled out by manufacturing folks manually. Quality(me) has to then review them and sign them off. I have tried to recreate the fields necessary that get populated in the paper docs in tables.

The issue I have is 255 field limit. I want to be able to enter all of this data into a form and have it populate the tables, which I had to create 3, that I want the data stored in. I finally came up with adding tabs in the main form so I could get all of the various fields onto 1 main form and the user can just select the appropriate tabs in the subform to enter data in the separate sections.

I am sure there is a better, cleaner, way to do it, but this seems to make sense to me and tries to replicate what is on the paper doc.

My issue is that the fields in the subforms aren't linked to the tables correctly I guess ( based on the small green triangle in the top left of each field). Can someone please help me understand how to correct this, I have attached a copy of the file. I am using access 2010.

Thanks in advance

Dave

 

[isladogs]

As much as I admire your dedication in adding all those fields, you have already described the problem. You've hit the field limit.

You need to restructure your table designs to drastically simplify how the data will be stored.
There are 9 different fields for P1 and these are duplicated for P2 through to P20. That's 180 in all.

Basically you're trying to make a spreadsheet & not a database

Read up on data normalisation to see how this should be done in Access

There are many suitable references on this forum and online.
For example:
http://rogersaccessblog.blogspot.co.uk/2008/12/what-is-normalization-part-i.html

It will make your life MUCH easier to restructure now rather than later

 

[Dave 14867]

ridders,

Yes, I agree with you, I was just trying to create a spread sheet. After reading the article you suggested, I have created new tables which I think make more sense, please take a look.

Now, how do create a form that looks like the original ones I had and how do I link each line, does that make sense?

Any help would be greatly appreciated as I have other docs to replicate. I have attached the updated file.

 

[isladogs]

You do realise this is quite an ambitious database for someone who describes himself as inexperienced....

Its difficult for me to comment meaningfully on some of this without seeing some 'example' data. I have no idea what any of this actually means!

I suggest you re-post with an explanation of the process and a few 'realistic' records in each table - it doesn't need to be real data to help me or another forum member understand ...

In the meantime:

1. Your SinglePaddleTable is much better already.
Now take it further ...

Rename P1_HCL_Bath as HCL_Bath or better still HCLBath (lose the underscore in all fields to make everything easier to read/code)
Similarly P1_EDAC_Start=> EDACStart etc for remaining fields

Could you also combine any of these fields e.g. Time fields?

2. Your Single Puck Preparation & Membrane Activation tables still have lots of fields. Suggest you also 'prune' these in a similar way?

3. The green triangle on form fields indicates that field is 'missing'.

It shouldn't be difficult to create something like your original idea for form layout once I've seen sample data.
BUT you are making the mistake of trying to make the design look like the actual form results. This isn't how its done

Each section will be easier to do as subforms so the layout can be different for each one. Each subform design WILL be simpler than you have now

For example, the design for section 7.0 Activation should be a continuous (sub)form with a header row (field names) and a 'field row' with the data for each paddle. In form view you'll of course get 20 rows of data

Take your time over this.
Hopefully the next post / response will help you move forward a long way

 

[Dave 14867]

ridders,

I have made the changes you suggested but maybe not correctly if that makes any sense. I created a new table for "Times" since start and stop times are frequently recorded, but that table may need more work I found out when I was trying to enter data, so I left that for now waiting to get more input from you on how to best organize it. I have attached the last attempt and a doc with the actual hand written data in it so you can see what I am attempting to do. Ultimately I want to be able to write code to see if entries fall within acceptable boundaries and if not, hi-lite, the field on the form for closer evaluation etc., so that we can catch error on the written doc before they are archived and the product used in production. This is for a medical application so the regs are strict.

Thanks so much for your assistance on this. I am beginning to better understand how the tables should be setup.

Dave

 

[Dave 14867]

Ridder,

Here is the other file that show the document used, it was to big to include in the previous post.

Thanks

Dave

 

[isladogs]

Zip file only contains the db (with sample data) - no separate doc.
UPDATE - now got them both

Suggest you compact your db - I do so & it dropped from around 8MB to 1.3MB

Is there anything in particular I need to use the Word doc for? Obviously as its handwritten, its more difficult to read ...

I'll try & look at it this evening (UK time) or over the weekend failing that

Where are you based & will you want to do any more on this between this evening & Monday?

Ultimately I want to be able to write code to see if entries fall within acceptable boundaries and if not, hi-lite, the field on the form for closer evaluation etc., so that we can catch error on the written doc before they are archived and the product used in production. This is for a medical application

We can go into that in more detail after I've had a chance to look at it properly

 

[Dave 14867]

ridders,

I may keep trying to further clean things up but not worried about it until Monday if you don't have the time, I am in New York State and I see you are in the UK so I understand, I just appreciate your help and guidance on this.

I have others I have tried to do but ran into the same issue, but again, that was because I was trying to set them up like a spreadsheet, so what I learn from this example I can apply to the others which will be awesome.

Again, thanks for you assistance on this

Dave

 

[isladogs]

That's great.

If you get any further today, please upload & i'll use the later version when I do look at it

 

[Dave 14867]

ridder,
I tried a different approach with the tables, see if this makes more sense, essentially I too each main section of the doc and created a table for that section (without repeating fields for diff paddles etc). I did run into an interesting issue when trying to create the form though which is very strange, I am trying it on my home system today to see if it is repeatable.

Attached is the latest attempt.

Thanks

Dave

 

[isladogs]

Didn't have time to look at the previous version
If time, I'll look at this version instead but not till tomorrow...

I did run into an interesting issue when trying to create the form though which is very strange, I am trying it on my home system today to see if it is repeatable.

What was the issue?

 

[Dave 14867]

When playing around with new forms, and adding fields to them, some of the old field names were available with the underscores in the names, it makes no sense, I started the new file from scratch, didn't import anything so I don't understand how it could be pulling some of the old names.

Dave

 

[isladogs]

When playing around with new forms, and adding fields to them, some of the old field names were available with the underscores in the names, it makes no sense, I started the new file from scratch, didn't import anything so I don't understand how it could be pulling some of the old names.

Dave

Offhand, I haven't a clue.

Just to say its unlikely I will get to look at your db today as I'm busy doing other things. Will respond when I've done so.

 

[Dave 14867]

Ridder,

Not a problem really, I haven't had a chance to play around any more either, too many things to get done around the house, we'll pick up again tomorrow or whenever you have time.

Thanks

Dave

 

[Dave 14867]

ridder,

Here is the same file only i populated the tables with 2 sets of data, hopefully correctly.

 

[Dave 14867]

ridder,

I made some progress I believe. Here is the latest with some data populated in the tables. Now that this seems to be working, I need some assistance on creating a query that will look at values in all tables and for high-lighting fields with unexpected values based on an equation using fields from different tables.

Thanks
Dave

 

[isladogs]

I like what's happening here.
You seem to be making good progress without me ...

Anyway that's fine. Very happy to advise on the query but you'll need to give me more detail to understand what exactly you mean.

 

[Dave 14867]

ridders,

If you look at the Equipment Materials table you'll see that there are 4 Lot #'s in it. I want to have a query where I can search for any 1 of those lot #'s and have a form display all Membrane Lot #'s that contain that lot number. Currently I have 4 different queries set up that can do that. I would like to have just one that will search for the lot number I input and have it search all 4 fields of all records for that lot #, does that make sense?

Thanks

Dave

 

[Dave 14867]

another thing I want to be able to do is create code that will check the value inputted into a field in the Puck Preparation subform to see if it is correct based on previous entries. The "MembranesRemaining" field in the Puck Preparation section for a given record should equal the "Qty" field from the "Membrane" Table - the "QtyMembranes". if it doesn't, I want to hi-lite "MembranesRemaining" field in vbyellow or backcolor=vbyellow. I just cant get the equation correct to be able to use the "Qty" field in the "Membrane" table. This would be required for each record in the "Puck Preparation" table that has the same MembraneID.

If you look at record 1 you can see that the "Qty" (Batch Size) in the "Membrane" Table is 20, and the "QtyMembranes" (Number of Membranes) in the Puck Preparation section, is 20, "PuckToStorage" (Number of Pucks to Storage)is 20, and the "MembranesRemaining" (Number of Membranes Remaining) is 20.

However the operator made 2 mistakes, the "QtyMembranes" (Number of Membranes) in the Puck Preparation section should be 28, the "PuckToStorage" (Number of Pucks to Storage) should be 20 and the "MembranesRemaining" (Number of Membranes Remaining) 8. This is exactly one of the reasons this is needed. So when we (Quality) reviews these docs, we can input the data and find errors such as these ( and possibly in the future go to using the database real time to enter data once it is all validated).

Does that make sense?

There are also other things I would like to do but baby steps.

Thanks

Dave

 

[isladogs]

Hi

I've spent a couple of hours looking at the tables / queries in your last upload.
Its definitely progress in the right direction but I think you need to go further
My first bit of advice - stop thinking in spreadsheet mode (or use a spreadsheet)

For the moment, I'm ignoring your forms - one thing at a time...

Modified version uploaded

1. Tables
I've removed all spaces from table names.

You still had / have some duplicated fields in each of your tables
Normally data should only appear in 1 table unless it is used as a foreign key to link data.
So for example, MembraneID is the primary key in Membranes table & a forign key in the others
MembraneLot should only be in the Membrane table - I've removed it from the others

Fields with the same name but different data in 2 or more table should be renamed to avoid confusion
e.g. Storage in Membrane & Activation table renamed as StorageCondition & StorageTime

There are other duplicated fields e.g. Init / StorageInit ; Date/StorageDate in several tables.
Please do the same as I have above - place in one table if identical or rename if different.

I also still think you have too many fields with similar names e.g. in Equipment Materials all the 61/62/63/64 fields seem to me as an outsider to be unnecessary duplication
Either break this down into several smaller tables or do something like this:
a) 3 fields ToolNum / CalDue/ Init with an extra field Timer (values 6A/6B)
b) fields LotNum / Init (or OpInit) / Exp with an extra field Lot (values 61/62/63/64)

Also 6Checker & 6Date relate to step 8 so why number 6?

Then use queries to pull the data needed from each table
e.g. qryMembraneID is a modified version of your Membrane Query but still has too many fields

2. Queries
I see you are using parameter queries which is good.
However, if the user doesn't know which values exist, they may enter an invalid value & be confused by the query output - no results

For example: Membrane Query has parameter MemberID which currently must be 1 or 2.
I had no idea so entered something else.

Suggest you instead run the query using the after update event on a combo box or listbox on a suitable form
I've called it frmMembrane (change this to whatever you want) & used a listbox with 2 columns with Table/Query type row source:

SELECT DISTINCT Membrane.MembraneID, Membrane.MembraneLot FROM Membrane;

You can then see the results for that membrane in one of 2 ways
a) run the query qryMembraneID
The event code would be:

Private Sub lstMembraneID_AfterUpdate()
    DoCmd.OpenQuery "qryMembraneID"
End Sub

b) Use a subform based on a filtered version of qryMembraneID (preferred)

Private Sub lstMembraneID_AfterUpdate()
    Me.fsubMembrane.Visible = True
    Me.fsubMembrane.Requery
End Sub

I've given you both methods but a) is currently disabled using a ' at the start of the code line

Both methods have the same issues;
i) too many fields so you have to scroll horizontally
ii) there are 2 records for each paddle for MembraneID=1 due to there being 2 records in the QtyMembrane field in PuckPreparation table i.e. 20 & 8. This was mentioned in your other post and needs dealing with as well

======================================
A similar method would be used for all 4 search queries though one extra step needed - 2 combo/listboxes
The first is used to select the field 62LotNumber etc - the row source uses field names
Then the second combo / listbox is used to select the value for that field
After that you run a query or show the results in a subform (preferred)

I've started this for you but will leave you to complete it using a similar idea to the previous form