Visual Basic Database Tutorial - Part 3, Page 9
Well that's all fine and dandy, but we've still not thought about the customer orders section at the bottom of our form. In English, we want it to display both the order date and the actual shipping date. But how do we do that in code?
The Reposition event of the Data control fires every time the user moves to another record. And that's just what we want - to update the Orders section every time we move to another Customer. So, our code looks like this:
Private Sub datCustomers_Reposition() datOrders.RecordSource = "select OrderDate, " & _ "RequiredDate from Orders where " & _ "CustomerID = '" & _ datCustomers.Recordset.Fields("CustomerID") & "'" datOrders.Refresh End Sub
This code fires every time our first Data control - datCustomers, the one that holds customer information - is "repositioned". In the code, we're changing the RecordSource property of our second Data control to retrieve the OrderDate and RequiredDate fields for all Orders that have a CustomerID same as the one in the datCustomers.
The code - datCustomers.Recordset.Fields("CustomerID") - allows us to retrieve a singular field within our Recordset. In this case, we're asking it to give us the CustomerID field.
So, in brief, every time the user moves from one record to the next, the Reposition event occurs. You respond to this by refreshing the Orders Section and filling it with all OrderDate and RequireDate fields that have the same CustomerID as the current Customer record.
Page 9 of 10