Boolean property - "Yes/No"

[doctim]

I'm trying to create a boolean field and set it's property to "Yes/
No", using VBA from an Excel spreadsheet (Office 2007, Win XP Pro
SP2). Code snippet is:

Set fd = tDef.CreateField(fieldName, dbBoolean)
Set prp = fd.CreateProperty("Format", dbText, "Yes/No")
fd.Properties.Append prp

I get a 3219 error at the last line. All variables have been declared
and I've included a ref to DAO 3.6 Object library. Rest of the code
runs fine, it's just setting the property that I've got problems with.
Any suggestions.

Thanks

Tim

 

[Dirk Goldgar]

in message

I'm trying to create a boolean field and set it's property to "Yes/
No", using VBA from an Excel spreadsheet (Office 2007, Win XP Pro
SP2). Code snippet is:

Set fd = tDef.CreateField(fieldName, dbBoolean)
Set prp = fd.CreateProperty("Format", dbText, "Yes/No")
fd.Properties.Append prp

I get a 3219 error at the last line. All variables have been declared
and I've included a ref to DAO 3.6 Object library. Rest of the code
runs fine, it's just setting the property that I've got problems with.
Any suggestions.

I'm not sure, but it may be that yuo need to append the field to the
tabledef's Fields collection before appending the property to the field's
Properties collection. Try this:

Set fd = tDef.CreateField(fieldName, dbBoolean)
tDef.Fields.Append fd
Set prp = fd.CreateProperty("Format", dbText, "Yes/No")
fd.Properties.Append prp

 

[doctim]

          Set fd = tDef.CreateField(fieldName, dbBoolean)

          tDef.Fields.Append fd
          Set prp = fd.CreateProperty("Format", dbText, "Yes/No")
          fd.Properties.Append prp

Dirk,

Thanks, but I still get the same error on the last line.

Tim

 

[Dirk Goldgar]

Dirk,

Thanks, but I still get the same error on the last line.

Has the tabledef itself been appended to the TableDefs collection yet? If
you just created the tabledef and haven't appended it to the TableDefs
collection, then I believe you'll get that error. Try this sequence:

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim f As DAO.Field
Dim prp As DAO.Property

Set db = CurrentDb
Set tdf = db.CreateTableDef("NewTableName")

With tdf
Set f = .CreateField(fieldName, dbBoolean)
tdf.Fields.Append f
db.TableDefs.Append tdf
Set prp = f.CreateProperty("Format", dbText, "Yes/No")
f.Properties.Append prp
End With

Set prp = Nothing
Set f = Nothing
Set tdf = Nothing
Set db = Nothing

 

[doctim]

Dirk,

Thanks - that works. Just need to work it into the rest of the code. I
think what I need to do is:
1. define all the fields (CreateField() & Fields.Append)
2. create the table (TableDefs.Append)
3. then go back and set the properties of the fields.

Tim

 

[Dirk Goldgar]

Dirk,

Thanks - that works. Just need to work it into the rest of the code. I
think what I need to do is:
1. define all the fields (CreateField() & Fields.Append)
2. create the table (TableDefs.Append)
3. then go back and set the properties of the fields.

That seems right to me.