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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|