Populating a table from a subform. Access 2002 - Beginner

[Json]

I have two tables:

1. Records the how many, when and who emptied from a coin operated machine.
2. Collects the explanation when the money collected does not tally with the expectation derived from the readings taken.

I could have just added a column to the end of the first table but it would have had relatively few entries and that appears to break the normalisation of tables rule set out here... so let's do this right


I have two queries:

1. Calculates the expected machine total for each record and provides the basis for a form to collect the data for table one.
2. *error here?* Is supposed to collect an explanation when the hand count does not equal the machine total. Is used to produce form 2.

I have two forms

1. Collects the data for table 1
2. Is supposed to collect the data for table 2 and is included as a subform in the footer of form 1.

My problem is that whilst if I manually put an entry in table 2 it will display in the subform when the associated record is displayed. If there is no entry nothing is displayed ^.-.
I want the form/ subform to force an entry if there is a discrepency

Explanation required: [Reading]-DLookUp("[Reading]","Body Drier","[TransID] = " & [TransID]-1)-Hand count <> "0"

and I am at a bit of a loss.
I do not know whether I have made an error with my tables, queries or forms. It just doesn't do what I would like it to do...
I have included the database with the names stripped out and enough data entered to show what it is doing, along with a few changes to the financial details ( I doubt it would matter in this instance but this is for work and it is their information not mine )
Can someone please point me in the right direction?
Thanks in advance

 

[jzwp22]

In your Body Drier table you did not designate the TransID as the primary key field, so you ended up with an intermediate relationship in the relationship window. If I only ever had 1 note per transaction, I would just have the field in the Body Drier table. However, if I wanted to track the resolution of a discrepancy which would require several follow-up actions, then I would have a separate table and have that table on the many side of a one-to-many relationship with the Body Drier table.

Just as some other notes, it is best not to have spaces or special characters in your table or field names. Also, it is best not to use Access reserved words as field or table names (typical one are name, date, month, year to name a few).

 

[Json]

I hadn't noticed the primary key error... It was originally but I have done so much fiddling now I can't remember what I did

Thank you on the field names.... that is an easy fix

Do I have the right elements to do what I would like? I want a field box to appear or activate when a record is entered if the totals do not tally and force an entry.

Thanks again....

[edit to add] .... and eventually I want to follow up the discrepencies even it is just with a acknowledgement from an internal audit.

 

[jzwp22]

I cannot tell how you are making the subform visible or not, but I was thinking about putting some code in the before insert event of the subform to check to make sure that there was some text present. Something along these lines (air code, not tested):

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.Parent.[machine total] < Me.Parent.[hand count] And Nz(Me.Reason_for_error, "") = "" Then
    MsgBox "You must provide a reason for the discrepancy"
    Cancel = True
    End If
End Sub