Simulated grouping on form (1 Viewer)

[max1]

Hi,

The following code refers to a continuous form based on a temporary table.

In order to simulate grouping I set a textbox to value 1 and have conditional formatting give it a black background in order to draw a line every time part of a string in another field changes.

The highlighted line refers to the textbox 'RingNumber' containing a parsed string from the temporary table the form is based on. NextVal is the value of that same text box from the next record. Thus the current and next records are compared to change the value in textbox 'Text91'.

The problem is that while 'Forms!frmtbltmpDrillRecon!RingNumber' correctly refers to the current record only, 'Forms!frmtbltmpDrillRecon!Text91' refers to every record on the form. Ie Text91 is changed for every single record. Can anyone explain why?

Sub SimulateGrouping() 'Simulates grouping by formating text box

Dim rstForm As Recordset
Dim Keyvalue As Integer
Dim NextVal As String



Set rstForm = Forms!frmtbltmpDrillRecon.Form.Recordset


Do While Not rstForm.EOF

    Keyvalue = Forms!frmtbltmpDrillRecon!IDtmpDrillRecon
    NextVal = NextRecVal(Forms!frmtbltmpDrillRecon, "IDtmpDrillRecon", Keyvalue, "VulcanHoleID")
    
    
[COLOR="Red"]   If Forms!frmtbltmpDrillRecon!RingNumber <> NextVal Then Forms!frmtbltmpDrillRecon!Text91 = 1[/COLOR]

rstForm.MoveNext

Loop

rstForm.Close


End Sub

 '*************************************************************
      ' FUNCTION: PrevRecVal()
      ' PURPOSE: Retrieve a value from a field in the next form
      '          record.
      ' PARAMETERS:
      '    F        - The form from which to get the next value.
      '    KeyName  - The name of the form's unique key field.
      '    KeyValue - The current record's key value.
      '    FieldNameToGet - The name of the field in the next
      '                     record from which to retrieve the value.
      ' RETURNS: The value in the field FieldNameToGet from the
      '          next form record.
      ' EXAMPLE:
      '    =PrevRecVal(Form,"ID",[ID],"OdometerReading")
      '**************************************************************
     '*************************************************************
      ' FUNCTION: NextRecVal()
      ' PURPOSE: Retrieve a value from a field in the next form
      '          record.
      '**************************************************************
        Function NextRecVal(F As Form, KeyName As String, Keyvalue, FieldNameToGet As String)
            
        Dim RS As Recordset

        On Error GoTo Err_NextRecVal

        NextRecVal = 0 ' The default value is zero.

            
        Set RS = F.RecordsetClone ' Get the form recordset.

            
            Select Case RS.Fields(KeyName).Type ' Find the current record.
               
               Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE ' Find using numeric data type key value?
                  RS.FindFirst "[" & KeyName & "] = " & Keyvalue
               
               Case DB_DATE ' Find using date data type key value?
                  RS.FindFirst "[" & KeyName & "] = #" & Keyvalue & "#"
               
               Case DB_TEXT ' Find using text data type key value?
                  RS.FindFirst "[" & KeyName & "] = '" & Keyvalue & "'"
                  
               Case Else
                  MsgBox "ERROR: Invalid key field data type!"
                  Exit Function
                  
            End Select

            ' Move to the next record.
            RS.MoveNext

            ' Return the result.
            NextRecVal = Left(RS.Fields(FieldNameToGet), 6)

Bye_NextRecVal:
            Exit Function
Err_NextRecVal:
Dim fld As Field
  For Each fld In RS.Fields
        ' Print field names.
        Debug.Print fld.Name
    Next
            Resume Bye_NextRecVal
            
            RS.Close
            
         End Function

 

[arnelgp]

if Text91 is unbound, it will be populated with same data.

 

[max1]

Yep, thanks, that is the issue..

What I am getting at is the fact that I can refer the value of an unbound textbox in the current record with 'Forms!frmtbltmpDrillRecon!RingNumber'
but how do i change the value of an unbound textbox in the current record?

 

[max1]

The highlighted line refers to the textbox 'RingNumber' containing a parsed string from the temporary table the form is based on.

Does this count as unbound?