Boolean property - "Yes/No"

  • Thread starter Thread starter doctim
  • Start date Start date
D

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
 
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.
Click to expand...


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
 
          Set fd = tDef.CreateField(fieldName, dbBoolean)
          tDef.Fields.Append fd
          Set prp = fd.CreateProperty("Format", dbText, "Yes/No")
          fd.Properties.Append prp
Click to expand...

Dirk,

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

Tim
 
doctim said:
Dirk,

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

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
 
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
 
doctim said:
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.
Click to expand...


That seems right to me.
 
Back
Top