"Data Type Conversion Error" when trying to add Fields to a Table thru VBA

[Guest]

H

Table Transport details all transport costs incurred from an Origin to a Customer. The table has a field for Customers (destinations) and all other fields refer to an Origin (of which there are about 10. Therefore 11 columns in this table).

It is possible that in the future an Origin (ie. another field) may be added to this table. I have tried to allow for this in VBA by

NewOrigin = cmbOrigin (ie, user enters details of new Origin into a form

Set fld = CurrentDb.CreateTableDef("Transport").CreateField(NewOrigin, Number

CurrentDb.CreateTableDef("Transport").Fields.Append fl
CurrentDb.TableDefs.Refres

I keep encountering an error "Data type conversion error". It seems to be associated with my field type being specified as 'Number'. Any ideas on how I can overcome this?

I look forward to a reply

Thank
Jad

 

[Allen Browne]

Several issues:

1. Different libraries have a Field object. Explicitly declare a DAO.Field.

2. CurrentDb creates a reference each time it is called. You need to create
a Database variable so you have a more persistent reference.

3. The field name needs to be in quotes.

4. The field type needs to be one of the constants.

Try something like this:
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs("Transport")
tdf.Fields.Append tdf.CreateField("NewOrigin", dbLong)

Set fld = Nothing
Set tdf = Nothing
Set db = Nothing


More importantly, should this data be stored in a related table instead of
adding more fields to the same table?

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

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

Table Transport details all transport costs incurred from an Origin to a

Customer. The table has a field for Customers (destinations) and all other
fields refer to an Origin (of which there are about 10. Therefore 11 columns
in this table).

It is possible that in the future an Origin (ie. another field) may be

added to this table. I have tried to allow for this in VBA by:

NewOrigin = cmbOrigin (ie, user enters details of new Origin into a form)

Set fld = CurrentDb.CreateTableDef("Transport").CreateField(NewOrigin, Number)

CurrentDb.CreateTableDef("Transport").Fields.Append fld
CurrentDb.TableDefs.Refresh

I keep encountering an error "Data type conversion error". It seems to be

associated with my field type being specified as 'Number'. Any ideas on how
I can overcome this??

 

[MacDermott]

I'd suggest instead you make a separate table - TransportOrigin
It could have a field to associate it with the record in the Transport
table, and a field for the Origin.
(If the order of the Origins is important, you could also have a
field for that.)
You'll have a record in this new table for each populated Origin field
in your current table.
If you're only using 3 Origin fields, you'll have 3 records.
If you need 11 (or 12, or 20), you can accommodate this without changing
your table design.

HTH
- Turtle

Hi

Table Transport details all transport costs incurred from an Origin to a

Customer. The table has a field for Customers (destinations) and all other
fields refer to an Origin (of which there are about 10. Therefore 11 columns
in this table).

It is possible that in the future an Origin (ie. another field) may be

added to this table. I have tried to allow for this in VBA by:

NewOrigin = cmbOrigin (ie, user enters details of new Origin into a form)

Set fld = CurrentDb.CreateTableDef("Transport").CreateField(NewOrigin, Number)

CurrentDb.CreateTableDef("Transport").Fields.Append fld
CurrentDb.TableDefs.Refresh

I keep encountering an error "Data type conversion error". It seems to be

associated with my field type being specified as 'Number'. Any ideas on how
I can overcome this??