Access 2007 Review Database

[KC]

I am trying to create a way to "rate performance" on specific criteria. I am
trying to modify an "Order" form to fit this project. It all can be
modified except I can't get the correct detail to come up. For example, I
want to review an ambulance run.

1. What protocol was used? This i've defined in a table - protocols (Kind
of like a customer)
2. What items are required under each protocol? These are in a table under
protocol detail. (kind of like order details)
3. How can I get the details to show on a form when the appropriate
protocol is chosen? In other words, ONLY specific items for each one chosen?

Thanks

 

[Allen Browne]

You already have these tables:
- Item (one record for each type of item that can be used in an order
detail)
- Protocol (one record for each "customer")
- ProtocolItem (one record for each default item for each protocol)
- Order (one record for each order, with a ProtocolID foreign key)
- OrderDetail (line items in the order, with OrderID and ItemID foreign
keys.)

Now your question is: When they add a new Order, how can I automatically add
the default items to OrderDetail for the Protocol supplied in the order?

Presumably you have set up an Order form, with a combo for selecting the
Protocol, and a subform for the OrderDetail line items. Use the AfterInsert
event procedure of the Order form to add the detail items.

The code will be something like this:

Private Sub Form_AfterInsert()
Dim strSql As String
If Not IsNull(Me.ProtocolID) Then
strSql = "INSERT INTO OrderDetail, (OrderID, ItemID ... ) " & vbCrLf
& _
"SELECT " & Me.OrderID & " AS OrderID, ItemID, ... " & _
"FROM ProtocolItem WHERE ProtocolID = " & Me.ProtocolID & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError
Me.[OrderDetail].Form.Requery
End If
End Sub

To get the SQL statement right, you can mock up a query, switch it to SQL
View (View menu in query design), and copy what you see there. If you do
this kind of thing often, this utility will help you take a working query
into your code:
Copy SQL statement from query to VBA
at:
http://allenbrowne.com/ser-71.html