Create a new field (1 Viewer)

Volvo · Oct 25, 2009

Hi there!
I'm trying to create a new field in a table but I got a conversion type error in my code :

Code:

[FONT=Courier]Public Sub add_field()[/FONT]
[FONT=Courier][COLOR=#008000]'I take the last sequential number from a table[/COLOR][/FONT]
[FONT=Courier]   Dim idtt As Integer[/FONT]
[FONT=Courier]       idtt = DMax("ID", "tbl_task")[/FONT]
 
[FONT=Courier]  Dim db As Database[/FONT]
[FONT=Courier]  Dim tbl As TableDef[/FONT]
[FONT=Courier]  Dim fld As DAO.Field[/FONT]
 
[FONT=Courier]  Set db = CurrentDb[/FONT]
[FONT=Courier]  Set tbl = db.TableDefs("tbl_events")[/FONT]
[FONT=Courier]  Set fld = tbl.CreateField(idtt, Text)  'Highlighted by the debugger[/FONT]
[FONT=Courier]  tbl.Fields.Append fld[/FONT]
[FONT=Courier]  db.Close[/FONT]
[FONT=Courier]End Sub[/FONT]

Is somebody can help me to solve that error ?
Thanks!

NigelShaw · Oct 25, 2009

Volvo said:

Hi there!
I'm trying to create a new field in a table but I got a conversion type error in my code :

Code:

[FONT=Courier]Public Sub add_field()[/FONT]
[FONT=Courier][COLOR=#008000]'I take the last sequential number from a table[/COLOR][/FONT]
[FONT=Courier]   Dim idtt As Integer[/FONT]
[FONT=Courier]       idtt = DMax("ID", "tbl_task")[/FONT]
 
[FONT=Courier]  Dim db As Database[/FONT]
[FONT=Courier]  Dim tbl As TableDef[/FONT]
[FONT=Courier]  Dim fld As DAO.Field[/FONT]
 
[FONT=Courier]  Set db = CurrentDb[/FONT]
[FONT=Courier]  Set tbl = db.TableDefs("tbl_events")[/FONT]
[FONT=Courier]  Set fld = tbl.CreateField(idtt, Text)  'Highlighted by the debugger[/FONT]
[FONT=Courier]  tbl.Fields.Append fld[/FONT]
[FONT=Courier]  db.Close[/FONT]
[FONT=Courier]End Sub[/FONT]

Is somebody can help me to solve that error ?
Thanks!

Hi,

without looking too deep, i think your error is because you are entering a collected NUMBER and placing into a TEXT field instead of a NUMBER field

Code:

Set fld = tbl.CreateField(idtt, Number)

as this is also the field highlighted by the debugger, i reckon thats it

the simplest problems are usually the hardest to find

Nigel

Steve R. · Oct 25, 2009

See Allen Brownes DAO Field Types.

Also it seems that you may be mixing a value with a field name, this looks wrong since you define IDTT as both a value and field name:

Code:

 Dim idtt As Integer
       idtt = DMax("ID", "tbl_task")

Try this:

Code:

Set fld = tbl.CreateField("MyField", [B]dbInteger[/B])

Also I have limited experience in adding fields, this is what I used.

Code:

Dim tdf As DAO.TableDef
    'Initialize the Import Table.
    Set db = CurrentDb()
    Set tdf = db.CreateTableDef("tblImportTable")
    strProjectPath = CurrentProject.Path
    strSourceFile = strProjectPath & "\AnalogMay09.xls"
    
    'Specify the fields.
    With tdf
        .Fields.Append .CreateField("Author", dbText)
        .Fields.Append .CreateField("Title", dbText)
        .Fields.Append .CreateField("Date", dbDate)
        .Fields.Append .CreateField("Page", dbText)
        .Fields.Append .CreateField("Type", dbText)
    End With

PS: Well NigelShaw responded first, and he is correct. The field should be an integer. I corrected my post to reflect that.

pbaldy · Oct 25, 2009

What do these fields represent? It looks like a normalization problem.

NigelShaw · Oct 25, 2009

Steve R. said:
PS: Well NigelShaw responded first, and he is correct. The field should be an integer. I corrected my post to reflect that.

Hi,

well, i was nearly right. i should have put dbInteger which is in fact a number but my thought was to show the difference between text and numbers.

Nigel

Create a new field (1 Viewer)

Volvo

New member

NigelShaw

Registered User.

Steve R.

Retired

pbaldy

Wino Moderator

NigelShaw

Registered User.

Similar threads

Users who are viewing this thread