Database Documenter

[MrS]

Trying to solve a small issue using the Database Documenter. I have had a search an not noticed something similar.

I have a group of students who will have to create a table. In the table there will be a number of fields, some of these will require specific Validation to be performed upon them.

For each field the 'Validation Text' is set appropriately for the validation rules - this appears on the Database Documenter as I would expect.



But there is a problem with the 'Validation Rules'.

If a validation rule like '>=5 And <=10' is used it is printed correctly in the Database Documenter.
BUT if the validation rule is .......
'Len([Firstname])<=20' to validate string length or
'StrComp(LCase([codename]),[codename],0)=0 ' to ensure the data is in lowercase

This is not printed in the Database Documenter.

I have tried the various option setting etc etc but I cannot get the Validation Rules to be displayed for the validation rules that use functions.

Any thoughts would be appreciated.

[ssanfu]

That is strange. I never noticed it.

Don't know if this will help you, but here is some code to print the field properties for the field in a table.

Public Sub GetFieldPropertys(Tbl_Name As String)

    Dim dbsBE As DAO.Database
    Dim tdf As TableDef
    Dim fldLoop As Field
    Dim prLoop As Property

    On Error GoTo HandleErr

    Set dbsBE = CurrentDb
    Set tdf = dbsBE.TableDefs(Tbl_Name)

    Debug.Print
    Debug.Print "============ " & Tbl_Name & " ============"

    For Each fldLoop In tdf.Fields
        'On Error Resume Next
        Debug.Print
        Debug.Print "--- " & UCase(fldLoop.Name) & " ---"

        For Each prLoop In fldLoop.Properties
            On Error Resume Next

            Debug.Print " " & prLoop.Name & " = " & prLoop.Value
        Next prLoop

        On Error GoTo HandleErr

    Next fldLoop


    Set dbsBE = Nothing
    Set tdf = Nothing
    Set fldLoop = Nothing
    Set prLoop = Nothing
    Exit Sub

ExitHere:

    ' Error handling block added by Error Handler Add-In. DO NOT EDIT this     block of code.
    ' Automatic error handler last updated at 08-12-2002 15:07:23
HandleErr:
    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical
    End Select
End Sub

I have a button to call the sub

' button name
Private Sub Command12_Click()   'I'm lazy - should take the time to give it a proper name
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb
    
    For Each tdf In db.TableDefs
        ' ignore system and temporary tables
        If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
            '            Debug.Print tdf.Name
            GetFieldPropertys (tdf.Name)
        End If
    Next
    
    Set tdf = Nothing
    Set db = Nothing

    MsgBox "Done"

End Sub

If you just want 1 table, use

Private Sub Command12_Click()

    '    GetFieldPropertys ("YourTableName")
    GetFieldPropertys ("tblProducts")
    MsgBox "Done"

End Sub

This code prints to the Debug Window. It isn't hard to add code append to a table or to print to a csv/txt file.

[MrS]

Thanks Steve,

It is a really odd one, I thought is might be something silly that I was missing as I am moving onto Access from Filemaker in the school. Thanks for the suggestion, but unfortunately that solution is way too much for what I need.

I am trying to get some High School students to provide evidence for an assessment - they only need to build in some basic validation to specific field and provide evidence that they have done this.

Maybe have to use the Snipping tool for now.

Cheers,
Derek

[orange]

Derek,
If all you want to show is the table fields with validation rule and text, this might be a start.

'---------------------------------------------------------------------------------------
' Procedure : showvalidationInfo
' Author    : mellon
' Date      : 10-Nov-2017
' Purpose   : To go through all tables, and print those  table fields that have
' Validation Rule. Show validation rule and validation text
'---------------------------------------------------------------------------------------
'
Sub showvalidationInfo()

          Dim fld As DAO.Field
          Dim tbl As DAO.TableDef

10        On Error GoTo showvalidationInfo_Error

20        For Each tbl In CurrentDb.TableDefs

30            For Each fld In tbl.fields

40                If Len(fld.ValidationRule & "") > 0 Then
50                    Debug.Print vbCrLf & tbl.name
60                    Debug.Print "--field--" & fld.name
70                    Debug.Print vbTab & "ValRule: " & fld.ValidationRule
80                    Debug.Print vbTab & "ValText: " & fld.ValidationText
90                End If
100           Next fld
110       Next tbl

showvalidationInfo_Exit:
120       Exit Sub

showvalidationInfo_Error:
130       MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure showvalidationInfo of Module AWF_Related"
140       Resume showvalidationInfo_Exit
End Sub

Sample output:

Volunteer
--field--VolFirstName
ValRule: Like '[a-z]*'
ValText: FirstName must be alphabetic

Volunteer
--field--VolLastName
ValRule: >" "
ValText: Must have a value greater than space


Update after seeing Bulzie's post #5
The validation rule and text are in the documenter.

[Bulzie]

It worked in my database documenter(Access 2007). Pasted this from report so should be spaces between 2nd options on each line.

TabIndex: 0 TabStop: True
TextAlign: General TextFontCharSet: 0
TextFormat: Plain Text Top: 0
TopMargin: 0 TopPadding: 30
ValidationRule: Len([ID2])>2 Vertical: False
VerticalAnchor: Top Visible: True
Width: 3480

[ssanfu]

@Derek,

In my or orange's code, you can limit the table to one table and the field checked to one specific field, then write the result to a table or a txt/csv file.
The Snipping tool seems like a PITA for many students.....

Good luck with your project.....

[orange]

Hi Steve,

I updated my post ---- the validation info is in the documenter.

[ssanfu]

Thanks orange.
I tried the documenter (at 1 AM) and tried several different rules; there were some rules that wouldn't print print. I'll have to go back and retry (when I'm rested).