Rows combined into single form, different values go to different controls

[pbuethe]

I have a working form with two levels of subforms, based on my table structure. However, I would really like the form to match the layout of the paper form which is scanned to populate the tables but whose format is very different.

The table structure is as follows:

tblARVMaster
CaseID (PK)
FormID

tblARVDirty
CaseID

arvDate
PageSeq
ColumnNo
arvEvent
EventAns
Value1
Value2

CaseID is not unique in tblARVDirty. One CaseID may have many PageSeq, each PageSeq has up to 4 ColumnNo, each ColumnNo has 1-13 Events.

The screen needs to look like the paper, where each page has 4 columns or "stripes". The layout of each column includes the following :

Date
__/__/__

Viral Load Assay
_______

CD4 Count
#____ ___%

Visit Type
O HIV Specialist
O Other Visit
O Non-Visit Event

Adherence Rate
O Not Measured
O Measured, Not Quantified
O Quantified No Percent
O =
O > ___%
O <

Other Events
[ ] Expired
[ ] Incarcerated
[ ] Transfer
[ ] New Patient

Depending on what the value of arvEvent is, a different area of the form is populated. In general the labels such as Viral Load Assay, CD4 Count, Visit Type, etc., except for Other Events, correspond to arvEvent. Blanks such as # and % correspond to Value1 and Value2. Option groups (O) correspond to EventAns. Checkboxes ([ ]) also correspond to arvEvent.

Right now my query for each column selects all records in that column and the master-child links of the (continuous) subform take care of the caseID and PageSeq. I would like to take all rows with one CaseID, PageSeq, and ColumnNo and disseminate their events/answers/values on a single form (as opposed to continuous) as illustrated above.

I tried writing code to loop through the records, which I put in the subform Load event. But nothing showed in the subform. I have never written code to use recordsets before so I was not surprised when it didn't work. There also should be a Save button on the form to allow editing of the form and saving the changes back to the table.

I hope I have explained the situation sufficiently. Thanks for any help (and thanks for your patience in reading this post).

 

[pbuethe]

Here is a sample of the code I tried. It may help explain what I am trying to do.

Private Sub Form_Load()
On Error GoTo Err_FormLoad
    Dim db As DAO.Database
    Dim rsCol As DAO.Recordset
    Dim strCriteria As String
    
    Set db = CurrentDb()
    Set rsCol = Me.RecordsetClone
    strCriteria = "[CaseID] = " & QUOTE & Me.CaseID & QUOTE
    strCriteria = strCriteria & " AND [PageSeq] = " & QUOTE & Me.PageSeq & QUOTE
    
    Me.Filter = strCriteria
    Me.FilterOn = True
    
    Me.txtSecError = rsCol![arvSecError]
    Me.txtDate = rsCol![arvDate]
    
    Do While Not rsCol.EOF
        Select Case rsCol![arvEvent]
            Case "VL" 'Viral Load Assay
                Me.txtViralLoad = rsCol![arvValue1]
                'need code to read modifiers
            Case "CD4" 'CD4 Count
                Me.txtCD4Count = rsCol![arvValue1]
                Me.txtCD4Percent = rsCol![arvValue2]
                'need code to read modifiers
            Case "VT" ' Visit Type
                Me.fraVT = rsCol![Answer]
            Case "Adh" ' Adherence Rate
                If rsCol![Answer] >= 11 Or rsCol![Answer] <= 13 Then
                    Me.fraAdh = rsCol![Answer]
                Else
                    Select Case rsCol![Answer]
                        Case "EQ"
                            Me.fraAdh = 14
                        Case "GR"
                            Me.fraAdh = 15
                        Case "LS"
                            Me.fraAdh = 16
                    End Select
                    Me.txtAdhRate = rsCol![arvValue1]
                End If
            Case "Tx" ' Decision Regarding Therapy
                Me.fraTx = rsCol![Answer]
          
            Case "Ex" ' Expired
                Me.chkEx = -1
            Case "Inc" ' Incarcerated
                Me.chkInc = -1
            Case "Tr" ' Transfer
                Me.chkTr = -1
            ...
       End Select
       rsCol.MoveNext
    Loop
    
End Sub

Is there a syntax error? Should the code be in a different event? Should it be in the main form and not the subform? Do I need a Querydef? Do the recordsource query of the subform and the master/child links stay the same?

Again, I am trying to take all of the rows that would be displayed by the continuous form and change them into different fields on a single form. Also, reverse the process with a Save button.

e.g.
CaseID PageSeq ColumnNo Event EventAns Value1 Value2
ADTP0001 000269-620 3 Adh EQ 50
ADTP0001 000269-620 3 CD4 <null> 339 14
ADTP0001 000269-620 3 VL <null> 5886
ADTP0001 000269-620 3 VT 1

Should look as:
Viral Load Assay
5886

CD4 Count
# 339 14 %

Visit Type
(HIV Specialist is selected)

Adherence Rate
(= is selected) 50 %

Any guidance is appreciated. I really have no experience in this type of coding.

 

[pbuethe]

Just thought I would post an update on this problem. I have abandoned this code. I have now created an intermediate table, tblARVEvents, which matches the layout of the form (one record per column of the form). I created a bunch of append and update queries to populate the intermediate table. The Access subforms are based on this intermediate table. Now all the data is displayed nicely (I even managed to fit all 4 columns on the screen without needing a horizontal scroll bar), and changes are saved to the intermediate table.

Now the problem is how to propagate the changes that were made to the intermediate table (tblARVEvents) back to the original table (tblARVDirty). I need to click a button on the form and have it save all the data for that case back to tblARVDirty. To simplify references, I have also added a unique key (autonumber) to tblARVDirty. But then it looks like I would need to add 13 fields to tblARVEvents, one to reference where each event came from. And I would need to add these references to the queries I created before. I think I should make it so that tblARVEvents is populated case by case; I did it all at once before. I am thinking out loud until I get some inspiration. Any advice is also welcomed.

 

[GabrielR]

Possible Solution

Hi,

I am in no way expert on Unbound fields, but your problem could be solved by the use of an unbound form that gets data from different tables and then transfers the data once a save button is pressed...

Maybe someone can step in and provide some light into this issue?

Gabriel

 

[pbuethe]

I did end up doing what I suggested. Before the form is opened, clear the intermediate Events table and populate it with the active case. The use of keys for each event helps for the initialization and the saving. It works very well.

Also, since there may be many forms (for different tables) open at once, and an Exit button on each form will call a routine that closes all of them (exiting the entire case and returning to the Find Record form to select a new case), I have the saving routine called by the routine that closes all the forms. Therefore I do not need a Save button, and the saving is transparent to the user and she does not need to remember to click a separate button.