Many to Many relationship - Creating a data entry form

[Takiman]

Can you please assist with the below data entry form i am trying to create that involves a Many to Many relationship--thanks in advance:

My M:M logic:
- An Audit can have many Error Messages. And an Error Message can belong to many Audits.
- There are only three error messages.

Requirements:


- I would like to create a data entry parent Form called frmAudit with a child Subform called subErrorMsg.
- For every new AuditID in the parent form , I would like the Subform to automatically create three new records in field ErrorMsgID with the values 1,2 & 3 respectively. And next to each ErrorMsgID in the subform I would like to have the field Error count, which will let the user enter the Error Count for each of these error messages.

This data would then obviously be recorded in the junction table: tblAuditErrorMsg.

Can you please assist with any information that will help me set this data entry parent/child form?

As a FYI I have created the following tables (shown below) showing the M:M relationship. I have also included some dummy data for each table further below, and an example of what I would like the form to look like.

tblAudit AuditID (PK - autonumber) AuditName 1	tblErrorMsg ErrorMsgID (PK – autonumber) ErrorMsgName 1 Incorrect Details 2 Omitted Details 3 Overwrite
tblAuditErrorMsg AuditErrorMsgID (PK – autonumber) AuditID (FK) ErrorMsgID (FK) ErrorCount 1 1 1 20 2 1 2 15 3 1 3 11 4 2 1 8 5 2 2 23 6 2 3 43 7 3 1 12 8 3 2 42 9 3 3 3

frmAudit (example):

AuditID: 1
AuditName: January Audit

Error Messages (Enter the error count for each each error message below):

1. Incorrect Details Error Count: _____
2. Omitted Details Error Count: _____
3. Overwrite Error Count: _____

[ranman256]

TErrorMsg does not need to be in the relation.
it is a feeder table to provide the only choices for the child table.
all 3 feeder records can be added to the child record with 1 query.

you CAN use ErrorMsgID as a field, but I wouldn't. The 3 ErrorMsg are unique so they can serve as keys.
Keys in English ,more easily identifiable.
but using numeric keys works too, it's just that I don't make more fields than I need.

the child table cannot produce the 1,2,3 counter for each parent. AutoNum fields count up and don't start over.
besides, you don't need them. Having 3 child records can be counted by access. Count(field).
Access knows there are 3.
The user can see there are 3.
theses no need to create a lot of vb work in order to literally have the numbers 1,2, and 3 show on the record.
these numbers do not provide any value for the work that will be put into it.

[Takiman]

I have included tblErrorMsg because when the 3 records get automatically created in the Subform (i.e. ErrorMsgID with value 1, 2 and 3) I will use it as a LookUp to the ErrorMsgName in tblErrorMsg (i.e. 1 = Incorrect Details, 2 = Omitted Details, 3 = Overwrite). Therefore, my intention is for the Error Message to be displayed in the Subform based on the value 1,2 or 3 (if that makes sense). Also ErrorMsgID is not the PK for the Subform, it is the FK as per the Diagram I provided. The PK is AuditErrorMsgID (which is the Autonumber) for this Subform.

All I want is: for every new record created in the main form, the subform will automatically create 3 records (i.e. the Error Messages) I can then enter the Error Count value for each.

Is this possible? Is there some code/script to automatically create three records in the Subform based on the AuditID in the Main form being created. I am new to access so thanks for help so far.

[Micron]

You should not be storing the error count.
For each main form record ID, you'd have to make 3 appends to the many side, one record for each main form id where each record contains the error data and matching audit data. As long as the other fields are not set to disallow Nulls, it should work. If the error side fields cannot contain Nulls, I'd say it's not possible.

[Takiman]

You should not be storing the error count.
For each main form record ID, you'd have to make 3 appends to the many side, one record for each main form id where each record contains the error data and matching audit data. As long as the other fields are not set to disallow Nulls, it should work. If the error side fields cannot contain Nulls, I'd say it's not possible.

Thanks Micron -- I agree to making 3 append records on the many side -- I assume you meant 3 records (in the subform) for each Main form ID? because that is what i need. I will also need to ensure that the subform is limited to only show 3 records for each main form ID. Any ideas on how to bring this together -- it maybe some brief steps if possible -- or if not any tips is good. thanks

[Micron]

Yes, that's what I meant. Where the subform record limit was variable, I used something like this on the BeforeInsert event on the subform

    If DCount("*", "tempTableForSubform", [FieldThatYouHaveAppendedTo] = Me.ControlOnSubformLinkedToField) > 3 Then
    MsgBox "Only 3 records allowed!", vbOKOnly 'or don't message at all
    Cancel = True
    End If

I don't know of a way to actually lock in 3 records, I only know a way to enforce a limit. If there is a chance that the user may cancel this operation, I would consider having 2 temp tables (one for the main record portion, the other for the sub) native to the front end where temp records can be built. If there's a cancellation, it's easier to wipe the table clean than it is to find and delete unfinished records from the primary tables. This code is based on that approach. If you use a different approach, you'll have to modify the DCount expression, but the idea would be basically the same - cancel the update.

[Takiman]

Thanks Micron this helped i was able to limit the amount of records created to 3! I used similar code to what you had that did the trick.