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?
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?
Code:
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