Month: December 2015

The following uses a function and the before update event of a form.

Sometimes it can be useful to have some kind of order field in the child records to indicate the order or version numbers of items. Although an incrementing Primary Key can be used child records may be in the thousands and if related to the parent you may want a simple almost ranking within the group. Which may be more meaningful when viewed filtered according to the parent.

A particular case may be where you are storing documents which have some kind of version.

 Public Function GetChildCount(OrderNo as Integer) As Integer

Dim intCount as Integer
intCount = DCount("FKID","[ParentTable]","[FKID]=" & OrderNo)
GetChildCount = IntCount + 1

End Function

This counts the number of records with the same FKID in the table called ParentTable with a FKID equal to OrderNo

Then within the before update event of the Sub_Form

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me.Order = GetChildCount([SiteID])
End If

End Sub

The If statement just ensures that when you edit a record the order is not updated to the count of the child records if a count already exists in the field Order.

Boolean Switch to test for specific character sets within a field. This codes tests whether a field contains blanks or the specified characters only and will return -1 if true and 0 if false. If a character occurs that is not within the LValid_Values it will return 0 as false. This is different from identifying whether a field contains the listed characters. This can be useful for identifying characters in a field that you are wanting to alter the variable type. MS Access (and other databases) will delete field values that cannot be converted so if possible you want to identify values with illegal characters. This code can be used to identify this. Change the value of LValid_Values to represent the allowable characters and then you can reference the function in a query to identify illegal records and values. My primary use case is testing for numerical values in a string field which I am looking to alter so that I can change it into a long integer variable type. This is particularly useful for hunting down things like letters in house numbers or slashes in flat identities.

Function CharCheck(targetField) As Boolean

   Dim LPos As Integer
   Dim LChar As String
   Dim LValid_Values As String
   
   'Start at first character in strField
   LPos = 1
   
   LValid_Values = ".0123456789"
    'Test each character in strField
   While LPos <= Len(targetField)
      'Single character in strField
      LChar = Mid(targetField, LPos, 1)
    
      'If character is not LValid Value, return FALSE
      If InStr(LValid_Values, LChar) = 0 Then
         CharCheck = False
         Exit Function
      End If
    
         'Increment counter
       LPos = LPos + 1
       
   Wend
  

   'Value is LValid Value, return TRUE
   CharCheck = True
 
End Function