Multiple link criteria and new records

[April]

I am working on a database to be used for auditing medical groups.
TABLE:DETAILS contains these fields:

[Review ID] (autonumber)
[Date]
[Auditor]
50 group fields [1] through [50] each a combo box that pulls from the
same list of 100+ groups

Each group is audited by reviewing 10-20 patient records. This
information is stored in TABLE:WS and contains the fields:

[ID] (autonumber)
[Review ID] links to TABLE:DETAILS (many-one)
[Group] contains value from one of the fields [1] through [50]
Other fields

The problem I am running into is that I can open the form where
[Review ID]=[Review ID] and [Group]=[1], etc. for each one, but it
will only show existing records, not add new records with both the
[Review ID] and [Group] assigned. Instead, I get a no related record
error message.

This is what I am using:
stLinkCriteria = "[Review ID]=" & Me![Review ID] & " And [Group]='" &
Me![1] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

The form has a subform and BOTH are from TABLE:WS using [Review ID]
and [Group] as link criteria.

I know it's awkward to have each group as a different item on the
Details table and then equal the same value in the WS table, but with
such a large number varying every month, I really couldn't see a
better way to do it.

I'm usually pretty good at reading existing answers, but this one has
me stumpted. Any suggestions?

Thanks! April (ap****@scanhealthplan.com)

[James Fortune]

af******@yahoo.com (April) wrote in message news:<a4**************************@posting.google. com>...

I am working on a database to be used for auditing medical groups.
TABLE:DETAILS contains these fields:

[Review ID] (autonumber)
[Date]
[Auditor]
50 group fields [1] through [50] each a combo box that pulls from the
same list of 100+ groups

Each group is audited by reviewing 10-20 patient records. This
information is stored in TABLE:WS and contains the fields:

[ID] (autonumber)
[Review ID] links to TABLE:DETAILS (many-one)
[Group] contains value from one of the fields [1] through [50]
Other fields

I'm not saying this is the way you should do it, but consider the
following structure:

ReviewDetails Groups Reviews
ReviewDetailID (PK) GroupID (PK) ReviewID (PK)
ReviewID GroupName ReviewName
GroupID ReviewDate
OtherFields...

Note: Watch out for names like Group, Detail, Date because
Access uses those names for other things and can get confused.

Reviews acts similar to an Orders Table. ReviewDetails acts similar
to an OrderDetails Table with the exception that the OrderDetails
can be changed later. Use the main form for Reviews. Once you
are on a specific Review you can set the RecordSource for the subform
so that ReviewDetails can be edited/added to. The code for adding
a new line (if you use code here) needs to ensure that the current
ReviewID gets duplicated and that an appropriate ReviewDetailID
(perhaps AutoNumber) gets assigned.

Alternatively, you can have a subform for Reviews and another subform
for ReviewDetails. On the Form_Current() event of the first subform
you can Call Form_frmMain.RequerySubformDetails(CStr(Me.ReviewI D))
where:

Public Sub RequerySubformDetails(strReviewID As String)
'Set SubformDetails.Visible = false in Design mode and make visible to
make startup act reasonable
If SubformDetails.Visible = False Then
SubformDetails.Visible = True
Exit Sub
End If
SubformDetails.Form.RecordSource = "SELECT * FROM ReviewDetails WHERE
ReviewID = " & strReviewID & " ORDER BY ReviewDetailID;"
SubformDetails.Form.Refresh
'This line might be superfluous:
SubformDetails.Form.Repaint
End Sub

is behind frmMain. Then when you click on a Review in the top
subform, the corresponding ReviewDetail records show up in the bottom
subform. There are even other ways to accomplish this. The structure
you showed makes it difficult to find any reasonable way to get what
you want. Plus, you are no longer limited to a 50 group audit (groans
coming from the auditors).

James A. Fortune