Set fld1 = NewTbl.CreateField (set attribute Question)

[Robert Nusz @ DPS]

I want to have the first field (fld1) named transNo as the primary key field
in a table in the current database. I want this field to be autonumbered and
autoincrement as each record is written. The code fails to build the table
with these last lines of

CurrentDb.TableDefs.Append NewTbl
strSQL = "ALTER TABLE TempUnits " & _
"ADD CONSTRAINT PK_TempUnits " & _
"PRIMARY KEY(transNo)"
CurrentDb.Execute strSQL, dbFailOnError

because I had coded fld1.Attributes = dbAutoIncrField. When I comment out
the
fld1.Attributes = dbAutoIncrField statement, it defines my table just fine
but doesn't allow me to have an autoimcrementing key field. What do I need
to do to allow this to work with the following code.

Function funcCREATE_TEMPUNITS():
Dim NewTbl As TableDef
Set NewTbl = CurrentDb.CreateTableDef("TempUnits")
Dim fld1 As Field ' for transNo
Dim fld2 As Field ' for UnitNumb
Dim fld3 As Field ' for CuNumb
Dim fld4 As Field ' for transInvoice
Dim fld5 As Field ' for transDate
Dim fld6 As Field ' for transAmnt
Dim fld7 As Field ' for transTypeDC
Dim fld8 As Field ' for transBegDate
Dim fld9 As Field ' for transEndDate
Dim fld10 As Field ' for transDueDate
Dim fld11 As Field ' for transTypeRE
Dim fld12 As Field ' for transDesc
Dim fld13 As Field ' for transWattsUsed
Dim fld14 As Field ' for transWattsRate
Dim fld15 As Field ' for transForm
Dim fld16 As Field ' for transCheckNum
Dim fld17 As Field ' for transPaidDate

Set fld1 = NewTbl.CreateField("transNo", dbInteger)
'fld1.Attributes = dbAutoIncrField
Set fld2 = NewTbl.CreateField("UnitNumb", dbText, 4)
fld2.AllowZeroLength = True
Set fld3 = NewTbl.CreateField("CuNumb", dbInteger)
Set fld4 = NewTbl.CreateField("transInvoice", dbBoolean)
Set fld5 = NewTbl.CreateField("transDate", dbDate)
Set fld6 = NewTbl.CreateField("transAmnt", dbCurrency)
Set fld7 = NewTbl.CreateField("transTypeDC", dbText, 1)
fld7.AllowZeroLength = True
Set fld8 = NewTbl.CreateField("transBegDate", dbDate)
Set fld9 = NewTbl.CreateField("transEndDate", dbDate)
Set fld10 = NewTbl.CreateField("transDueDate", dbDate)
Set fld11 = NewTbl.CreateField("transTypeRE", dbText, 1)
fld11.AllowZeroLength = True
Set fld12 = NewTbl.CreateField("transDesc", dbText, 40)
fld12.AllowZeroLength = True
Set fld13 = NewTbl.CreateField("transWattsUsed", dbInteger)
Set fld14 = NewTbl.CreateField("transWattsRate", dbCurrency)
Set fld15 = NewTbl.CreateField("transForm", dbText, 15)
fld15.AllowZeroLength = True
Set fld16 = NewTbl.CreateField("transCheckNum", dbText, 10)
fld16.AllowZeroLength = True
Set fld17 = NewTbl.CreateField("transPaidDate", dbDate)

MsgBox "funcCREATE_TEMPUNITS NewTbl.Fields.Append "
NewTbl.Fields.Append fld1 ' for transNo
NewTbl.Fields.Append fld2 ' for UnitNumb
NewTbl.Fields.Append fld3 ' for CuNumb
NewTbl.Fields.Append fld4 ' for transInvoice
NewTbl.Fields.Append fld5 ' for transDate
NewTbl.Fields.Append fld6 ' for transAmnt
NewTbl.Fields.Append fld7 ' for transTypeDC
NewTbl.Fields.Append fld8 ' for transBegDate
NewTbl.Fields.Append fld9 ' for transEndDate
NewTbl.Fields.Append fld10 ' for transDueDate
NewTbl.Fields.Append fld11 ' for transTypeRE
NewTbl.Fields.Append fld12 ' for transDesc
NewTbl.Fields.Append fld13 ' for transWattsUsed
NewTbl.Fields.Append fld14 ' for transWattsRate
NewTbl.Fields.Append fld15 ' for transForm
NewTbl.Fields.Append fld16 ' for transCheckNum
NewTbl.Fields.Append fld17 ' for transPaidDate

CurrentDb.TableDefs.Append NewTbl
strSQL = "ALTER TABLE TempUnits " & _
"ADD CONSTRAINT PK_TempUnits " & _
"PRIMARY KEY(transNo)"
CurrentDb.Execute strSQL, dbFailOnError
End Function


Thanks In advance --
Robert Nusz @ DPS

 

[Allen Browne]

You also need to set the attribute indicating the Long is a fixed field,
i.e.:
fld1.Attributes = dbAutoIncrField + dbFixedField

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Robert Nusz @ DPS]

Allen Browne,

Thank you for responding to my questions on field attributes and access
forms / table field coding. I'm sure that your response will help others as
it has helped me. I've been unable to locate a good book on Access coding /
VB coding that would clarify a lot of the questions that I've had to post.
Is there a book that you know about that would have the information that you
have shared with myself and others that would assist us in learning more
about Microsoft Access and VB coding? I'd be glad to purchase a copy as I'm
always being asked to assist others in Access projects here at work as well
as my own personal use and needs. Once again, thanks for your support on
this website it is much appreciated.

Robert Nusz
DPS

 

[Allen Browne]

Programmatically creating tables is not something the average user needs to
do, so you may be ready for the heavyweight called Access Developers <insert
version number> Handbook, by Ken Getz et al, published by Sybex.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Robert Nusz @ DPS]

Allen Browne,

Thanks, I appreciate the name of the book, I'll see if I can get one ordered
as soon as possible. I like the access product, and hope to learn more as I
go.

Thanks Again,

 

[Douglas J. Steele]

See http://www.developershandbook.com/ for details.