TMSpurr said:
Trying to append data to a master data table. I can copy
the fields in to the master table and that works fine,
field for field and row for row, no problem. However, I
am getting a validation rule violation when trying to
append the data. I have no validation rules set up and
can't find the issue. I've check data types and sizes,
and the fact that I can paste the data in okay has got me
puzzled. Please Help! Hot project and I'm stuck. Thanks
in advance!
Hi TM,
Would you mind starting a new module,
copy and paste the following code into
the module, save the module (as say "modUtil"),
make sure you have reference to DAO library,
compile it to verify okay (watch word wrap),
then in Immediate window
run the function on your master data table.
?fListTableFields("nameofyourtable")
Then copy and paste results back here?
Or...turn your append query into a make table
query, run it, then run this function on the table
you just made, then compare the two results.
I would look specifically at these 2 properties
for reasons you are getting this error:
Required = ?
AllowZeroLength = ?
Thanks,
Gary Walter
'***start code ****
Option Explicit
Public Function FieldType(intType As Integer) As String
'from post by Dan Artuso
Select Case intType
Case dbBoolean
FieldType = "dbBoolean"
Case dbByte
FieldType = "dbByte"
Case dbInteger
FieldType = "dbInteger"
Case dbLong
FieldType = "dbLong"
Case dbCurrency
FieldType = "dbCurrency"
Case dbSingle
FieldType = "dbSingle"
Case dbDouble
FieldType = "dbDouble"
Case dbDate
FieldType = "dbDate"
Case dbText
FieldType = "dbText"
Case dbLongBinary
FieldType = "dbLongBinary"
Case dbMemo
FieldType = "dbMemo"
Case dbGUID
FieldType = "dbGUID"
End Select
End Function
Public Function FieldOutput(fldTemp As DAO.Field) As Variant
' adapted from Access Help
Dim prpLoop As DAO.Property
Dim strProp As String
' Enumerate Properties collection of passed Field
' object.
For Each prpLoop In fldTemp.Properties
' Some properties are invalid in certain
' contexts (the Value property in the Fields
' collection of a TableDef for example). Any
' attempt to use an invalid property will
' trigger an error.
On Error Resume Next
strProp = strProp & prpLoop.Name & " = " & _
prpLoop.Value & vbCrLf
On Error GoTo 0
Next prpLoop
FieldOutput = strProp
End Function
Public Function fListTableFields(pT1 As String) As Boolean
On Error GoTo Err_fListTableFields
Dim db As DAO.Database
Dim tdf1 As DAO.TableDef
Dim fld As DAO.Field
Dim fld1 As DAO.Field
Dim idx As DAO.Index
Dim strName1 As String
Dim strType1 As String
Dim varProp1 As Variant
Dim i As Integer
Set db = CurrentDb
Set tdf1 = db.TableDefs(pT1)
Debug.Print "Table: " & pT1
For i = 0 To tdf1.Fields.Count - 1
Debug.Print "----------------------------"
Set fld1 = tdf1.Fields(i)
varProp1 = FieldOutput(fld1)
strName1 = fld1.Name
strType1 = FieldType(tdf1.Fields(i).Type)
If strType1 = "dbText" Then
strType1 = strType1 _
& " (" & tdf1.Fields(i).Size & ")"
End If
'is it a primary key?
For Each idx In tdf1.Indexes
If idx.Primary Then
' Found a Primary Key
For Each fld In idx.Fields
If fld.Name = strName1 Then
strType1 = strType1 & " (pk)"
Exit For
End If
Next fld
Exit For
End If
Next idx
Debug.Print "Field: " & strName1 & vbCrLf _
& "Field Type: " & strType1 _
& vbCrLf & varProp1
Next i
Debug.Print "----------------------------"
db.Close
fListTableFields = True
Exit_fListTableFields:
Set fld1 = Nothing
Set tdf1 = Nothing
Set db = Nothing
Exit Function
Err_fListTableFields:
MsgBox Err.Description
Resume Exit_fListTableFields
End Function
'**** end code ****
