Error checking for table fields null value constraint
Hi all. I want to check for not null value constraint for all tables in database . I tried the following but when i run it i get the following error:
Compile error:
Invalid Next control variable reference
I be happy if some one one help me fix this error.if i remove the bold lines the program workes well but it does not put not null for table fileds that requries value.
picture of the output without the bold part
http://i5.photobucket.com/albums/y18...07/notnull.jpg
Code:
Option Compare Database
''This module displays field name and type in a massage box
Function ShowFields(pTable As String) As String
Dim db As Database
Dim rs As Recordset
Dim I As Integer
''Dim j As Integer
Dim n As Integer
''Dim NL As String
Dim strHold As String, ST As String
''Dim x As Variant
''NL = Chr(13) & Chr(10) ' Define newline.
Set db = CurrentDb
Set rs = db.OpenRecordset(pTable)
n = rs.Fields.Count
ReDim x(n - 1, 1) As String
'''ST = "Create Table " & pTable & vbCrLf
''adding Create table and table name to statement
ST = "Create Table " & pTable & vbCrLf & "("
For I = 0 To (n - 1)
For Each fld In tbl.Fields
ST = ST & rs.Fields(I).Name & " " & FieldType(rs.Fields(I).Type) & "," & vbCrLf
If fld.Required = True Then
ST = ST & " NOT NULL" & " "
Else
ST = ST
End If
Next I
rs.Close
db.Close
Set db = Nothing
ShowFields = ST '' returns the fields name to main function
End Function
on click even code
Code:
For Each T In db.TableDefs
'''Skip the system tables
If Left(T.Name, 4) <> "MSys" Then
''' this line determines the primary key of the table
''' by calling GetPk function from module
pk = Left(GetPK(T, db), InStr(1, GetPK(T, db), "<-") - 1)
cont = cont & ShowFields(T.Name) & vbCrLf & " primary key " & "(" & pk & ")" & vbCrLf & ")" & vbCrLf
End If
Next T
''' This line of code post the generated table statment to a massage box
MsgBox cont
Error checking for table fields null value constra
