MS Access and Forms – Create a Filtered Autonumber for Child Records

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.

VBA Function Boolean Switch to test for specific character sets within a field

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    

Ranking of Child Records according to Groups

Imagine you have a school full of Students and they have done a variety of exams. All results are collected in a table and you would like to obtain rankings by subject. How can you automatically rank all the students for whom you have results.

The table T01Student
PKID Students Marks Subject
1 Tony 34 Maths
2 Bob 32 Maths
3 Thor 48 Maths
4 Jack 42 Geography
5 Tom 41 Geography
6 Kate 45 Geography
7 Sid 26 Geography
8 Michael 40 Chemistry
9 Colin 50 Chemistry
10 Hannah 60 Chemistry
11 Geoff 5 Chemistry
12 Jim 2 Chemistry

It is then possible to use the following query to get a ranking

SELECT (select count(*) from T01Student as tbl2 where T01Student.marks < tbl2.marks and T01Student.subject = tbl2.subject)+1 AS rank, * INTO TempRank
FROM T01Student;

rank PKID Students Marks Subject
2 1 Tony 34 Maths
3 2 Bob 32 Maths
1 3 Thor 48 Maths
2 4 Jack 42 Geography
3 5 Tom 41 Geography
1 6 Kate 45 Geography
4 7 Sid 26 Geography
3 8 Michael 40 Chemistry
2 9 Colin 50 Chemistry
1 10 Hannah 60 Chemistry
4 11 Geoff 5 Chemistry
5 12 Jim 2 Chemistry

Then use a simple select query to order by subject then rank – Note Depending if you want to count down from the top so the lowest “Marks” gets the highest rank reverse the < symbol or reverse the order of rank - here I have highest mark is no 1. Subject rank Students Marks Chemistry 1 Hannah 60 Chemistry 2 Colin 50 Chemistry 3 Michael 40 Chemistry 4 Geoff 5 Chemistry 5 Jim 2 Geography 1 Kate 45 Geography 2 Jack 42 Geography 3 Tom 41 Geography 4 Sid 26 Maths 1 Thor 48 Maths 2 Tony 34 Maths 3 Bob 32 If for some reason you are wanting to store the rank so that you can artificially alter the ranking then it would be possible to use make table to create a new table with the ranking and then update a position field with the rank in the ranking query based on the PKID