Continuous form auto populate records

[Indigo]

I am using Access 2003 and I have a mainform with several subforms. All forms are bound. I have restricted the subforms to show a maximum of 3 records each. But really, what I want to do is auto-populate the text boxes with zeroes for 3 records each.

I want the user to change the value, but I want three records for each main record to show a zero value. I am thinking that I need to run a query with VBA when the form loads to achieve this, but I need to account for if the user changes the value to something other than zero.

My main table is called "Element" with a primary key (ElementID) among other fields. One of the tables that has a one to many relationship with Element is "ErgoArm". It contains a foreign key (ElementID) along with 4 number fields bound to the text boxes on the subform. These number fields are there to maintain ergonomic data used for calculations for each element in a process. Each element may have more than one value for each of these number fields but no more than 3 values. For example, one
element may require a worker to raise his arm straight up and then straight out again - this is two movements in one element require two data values. Some elements may not have any values - hence the zeroes.

Can anyone point me in the right direction??

 

[jzwp22]

Each element may have more than one value for each of these number fields but no more than 3 values. For example, one
element may require a worker to raise his arm straight up and then straight out again - this is two movements in one element require two data values.

Are you saying that 1 field has multiple values and that the field is numeric? How do you plan on separating them?

If my iterpretation of you post is correct, I have to question your table structure. If an element can have many movements and each movement can have many values (not more than 3) then that requires a different table structure as follows:

tblElements
-pkElementID primary key, autonumber
-txtElement

tblElementMovements
-pkEleMoveID primary key, autonumber
-fkElementID foreign key to tblElements
-txtMovement

tblElementMovementValues
-pkEleMoveValID primary key autonumber
-fkEleMoveID foreign key to tblElementMovements
-valuefield

 

[Indigo]

Hmm.... let me better explain the table structure:

Table ErgoArm has a Primay key (ErgoArmID), a foreign key (ElementID), and 4 number fields called: i.e. ArmMovementPoint, ArmBurden, ArmTime and ArmCoefficient. Each of these is to have 3 values for each ElementID.

Are you telling me that if I change my table structure to match what you suggest that I will be able to easily achieve my goal of only three records per movement per element?

My concern with your suggested structure is that the arm movement value is specific to the ElementID, not to the ErgoArmID.

 

[jzwp22]

I'm telling you that your table structure is not normalized. Having an improperly structured database will cause you problems in the long run.

What about things other than the arm?

Can you define what an element is and provide an example? I'm thinking that "Arm" should actually be a record in the second table (tblElementMovements) so perhaps my tables are not named properly

tblElements
-pkElementID primary key, autonumber
-txtElement

tblElementMovements
-pkEleMoveID primary key, autonumber
-fkElementID foreign key to tblElements
-txtMovement


tblElementMovementValues
-pkEleMoveValID primary key autonumber
-fkEleMoveID foreign key to tblElementMovements
-valuefield

Out of curiosity, can you explain what other tables you have that form one-to-many relationships to the elements table?

You may want to look more into normalization; check out this site for a basic summary.

 

[Indigo]

It just so happens that I am trying to re-write an old database that is a big mess and causing problems -- each movement was recorded as "armmoverment1", "armmovement2", "armmovement3" all on the element table.... There are many many movements, actually, so I separated the arm movement into one table, the hand movements into another, tools into a third and lifts into a fourth all with a one to many relationship back to the element table.

The overall database has a process table - jobs to be performed, the element table - each step required to perform the jobs and then the above mentioned tables that break down the movements needed to perform the job for ergonomic analysis.

So a process would be to "Build a Widget", the elements, broken into steps (i.e. Step one, pick up part a from parts table, Step two, insert part a into slot b...). If step one required the worker to reach up to retrieve the part and then bring it down to waist level, there would be 10 points for the upwards reach assigned to the ergo value on that element.

 

[jzwp22]

Whether it is an arm, a hand, a leg or some other type of item, you would not need a separate table for each type. I would have a table that holds the item types and then tie that to your element table. Then the element-item would have multiple types of movements for which you would record values

tblElements
-pkElementID primary key, autonumber
-txtElement

tblItems (or whatever you want to call them) (1 record for each: arm, hand etc.)
-pkItemID primary key, autonumber
-txtItem

tblElementItems
-pkEleItemID primary key, autonumber
-fkElementID foreign key to tblElements
-fkItemID foreign key to tblElments

tblMovementTypes (1 record for each type of movement; MovementPoint, Burden, time etc.)
-pkMoveTypeID primary key, autonumber
-txtMovementType

tblElementItemMovements
-pkElementItemMoveID primary key, autonumber
-fkEleItemID foreign key relating back to tblElementItems
-fkMoveTypeID foreign key relating back to tblMovementTypes
-datavaluefield

I don't know how tools and lifts fit into your process. A lift is a type of tools, so perhaps, you need only 1 table for hardware stuff. Are you recording just what tools are needed for an element or are the tools and body movements related somehow?

 

[Indigo]

Tools are separated because the gripping and or weight of the tool can have ergonomic factors. "Lifts" does not refer to equipment but rather the ergonomic burden of lifting parts and the height the part may need to be lifted to.

So, if I change my table structure as you suggest -- how can I ensure that only 3 records are recorded per movement per element?

 

[jzwp22]

You would need a bit of code in the before insert event of the subform (I assume) that is tied to tblElementItemMovements that counts the number of existing records. If 3 records already exist, the code would not allow a new record to be added. I would guess that you will need something along these lines (not tested):

IF DCount("*","tblElementItemMovements", "fkEleItemID=" & me.fkEleItemID)=3 THEN
  msgbox "The record limit has already been reached"
  'some code to clear/cancel the current attempted record here perhaps cancel=True
End if

 

[Indigo]

Hmmm.... but that's not exactly what I am looking for, I want to auto-populate to a maximum of 3 records. So if 3 records do not yet exist, I want the subform to automatically populate with 3 records of zero value. If one record exists, I want the subform to auto populate with 2 records of zero value.

 

[jzwp22]

Adding records can be done with an append query or in this case an append query embedded in a loop in code. But I have to ask, what if nobody ever fills in the data? Then you will have 3 records that will just clutter up any data analysis you try to do. You will have to include a filter in any query you do to ignore the records. Adding the criteria is not an issue other than remembering to do it. The point is why create records that may not be necessary?

 

[Indigo]

I'm sorry, but it is not creating records I don't need. I have to have 3 records for each element regardless to whether there is a value greater than zero or a zero value. I want to add this functionality in case the user forgets to add the additional zero value records when they are entering a new element in for a process.

As I said in my original post:

...what I want to do is auto-populate the text boxes with zeroes for 3 records each.

I want the user to change the value, but I want three records for each main record to show a zero value. I am thinking that I need to run a query with VBA when the form loads to achieve this, but I need to account for if the user changes the value to something other than zero.

So user is entering a new element in for a process: The new element may have arm burden points but no tool points, but I still need to show 3 records for the tool points at a zero value. I realize that this may not seem logical, but I have to show it this way to get the support / sign off from my H&S group. I have no choice.

 

[jzwp22]

OK, as I mentioned you can do it with an append query embedded in a loop. The key is to what form event should that code be tied.

Using the table structure I proposed, I assume that when a new "element-item" is added (i.e. arm) to tblElementItems, you would need to add 3 records to tblElementItemMovements with each having a different fkMoveTypeID


tblElementItemMovements
-pkElementItemMoveID primary key, autonumber
-fkEleItemID foreign key relating back to tblElementItems
-fkMoveTypeID foreign key relating back to tblMovementTypes
-datavaluefield

But, I was going back through one of your earlier posts and saw this:

and 4 number fields called: i.e. ArmMovementPoint, ArmBurden, ArmTime and ArmCoefficient. Each of these is to have 3 values for each ElementID.

This implies that there are 12 values total just for the arm. Am I correct? If so wouldn't you want to create 12 records? Also, this seems like it might change the table structure...

If we say that an item=arm and an arm can have 4 movement types (movementpoint, burden, time, coefficient) and each of these can have 3 values then we need to alter tblElementItemMovements and add an additional related table as follows

tblElementItemMovements
-pkElementItemMoveID primary key, autonumber
-fkEleItemID foreign key relating back to tblElementItems
-fkMoveTypeID foreign key relating back to tblMovementTypes


tblElementItemMovementDetail
-pkEleItemMoveDetailID primary key, autonumber
-fkElementItemMoveID foreign key to tblElementItemMovements
-datavaluetype?
-datavalue

Also, when you say each has 3 values, how do you distinguish the three values? What do they represent?

 

[Indigo]

This would require a course in Ergonomic Burden analysis....

I'm not going into that now.

Okay, playing around on my own I was working with something like this:

    Dim strQuery As String
    Dim HoldElementID As Integer
 
    HoldElementID = Forms!frmElement!ElementID
 
        strQuery = "INSERT INTO ErgoArm (ArmMovementPointLH, ArmTimeLH, ArmPostureBurdenLH)" & _
                    " VALUES (0,0,0)" & _
                    " SET ElementID = " & HoldElementID & ""
    Debug.Print strQuery
      CurrentDb.Execute strQuery, dbFailOnError

(No, I have not yet restructured my tables - I wanted to see if I could write the code first).

But I am missing a semi-colon at the end of my SQL statement. This is the first time I have written anything with "INSERT INTO"... I have used UPDATE and DELETE and never needed a semi-colon.... I'm not sure where the end of my statement is.

 

[jzwp22]

You do not need the semi-colon. I assume that a field related to the elementID is in your ErgoArm table. You have to populate that value. The SET clause is used only in Update queries

Dim strQuery As String
    Dim HoldElementID As Integer
 
    HoldElementID = Forms!frmElement!ElementID
 
        strQuery = "INSERT INTO ErgoArm ([COLOR="Red"]fkElementID[/COLOR], ArmMovementPointLH, ArmTimeLH, ArmPostureBurdenLH)" & _
                    " VALUES [COLOR="red"](" & holdElementID & ",[/COLOR]0,0,0)" 
 
    Debug.Print strQuery
      CurrentDb.Execute strQuery, dbFailOnError

 

[Indigo]

Ah.... thank you -- yes, that's it. I just realized that SET was only for an Update query. D'oh.

Okay, back to working on getting the rest of this database running smoothly. Thanks again for your support.

 

[jzwp22]

You're welcome. Good luck with your project.