A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Set fld1 = NewTbl.CreateField (set attribute Question)



 
 
Thread Tools Display Modes
  #1  
Old August 26th, 2006, 08:27 PM posted to microsoft.public.access.forms
Robert Nusz @ DPS
external usenet poster
 
Posts: 7
Default Set fld1 = NewTbl.CreateField (set attribute Question)

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
  #2  
Old August 27th, 2006, 06:00 AM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Set fld1 = NewTbl.CreateField (set attribute Question)

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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



  #3  
Old August 28th, 2006, 01:20 PM posted to microsoft.public.access.forms
Robert Nusz @ DPS
external usenet poster
 
Posts: 7
Default Set fld1 = NewTbl.CreateField (set attribute Question)

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

--
Robert Nusz @ DPS


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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




  #4  
Old August 28th, 2006, 01:27 PM posted to microsoft.public.access.forms
Allen Browne
external usenet poster
 
Posts: 11,706
Default Set fld1 = NewTbl.CreateField (set attribute Question)

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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

--
Robert Nusz @ DPS


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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



  #5  
Old August 28th, 2006, 02:18 PM posted to microsoft.public.access.forms
Robert Nusz @ DPS
external usenet poster
 
Posts: 7
Default Set fld1 = NewTbl.CreateField (set attribute Question)

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,
--
Robert Nusz @ DPS


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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

--
Robert Nusz @ DPS


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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




  #6  
Old August 28th, 2006, 03:27 PM posted to microsoft.public.access.forms
Douglas J. Steele
external usenet poster
 
Posts: 9,313
Default Set fld1 = NewTbl.CreateField (set attribute Question)

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

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Robert Nusz @ DPS" wrote in
message ...
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,
--
Robert Nusz @ DPS


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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

--
Robert Nusz @ DPS


"Allen Browne" wrote:

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Robert Nusz @ DPS" wrote in
message
...
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






 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:26 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.