vba - How to display table values for a single event, multiple rows for a single attribute, in text boxes, as if I were using a crosstab query

Question

I have a form that should display a slope value for different transects on a plot, all tied to a single event, using different text boxes.

For example: There is a monitoring event record. The StandTransectSlope table has 3 rows for one EventID: slope for the UP (upper, ID=1) transect, slope for the BL (bottom left, ID=2) transect, slope for the BR (bottom right, ID=3) transect.

I have 3 text boxes: one box to display the slope value for each of the transects (1-3).

I did have a crosstab query behind this form so that I could, easily, assign those different slope values from each row by the transect name. But then I found out the crosstab query is not updateable. Users need to be able to add new data, as well as view data already entered. How can I code to allow this?

Currently, I have the following code on the Form_Load event (I'll have to also add code for AfterUpdate for the form):

Private Sub Form_Load()

Dim ID As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

strSQL = "SELECT * FROM xrefCOMN_StandEventTransectSlope WHERE EventID =" & Me.EventID

Set rs = db.OpenRecordset(strSQL)

'Set values for slope text boxes by ID
Select Case ID
    Case 1
        Me.txtSlopeUP.Value = DLookup("Slope", "xrefCOMN_StandEventTransectSlope", "EventID=" & Me.EventID)
    Case 2
        Me.txtSlopeBR.Value = DLookup("Slope", "xrefCOMN_StandEventTransectSlope", "EventID=" & Me.EventID)
    Case 3
        Me.txtSlopeBL.Value = DLookup("Slope", "xrefCOMN_StandEventTransectSlope", "EventID=" & Me.EventID)
    Case Else
        'Nothing
End Select

End Sub

标签： vbams-access