Add a boolean value to an existing Access table 1

[emik]

Hi,

I'm trying to create some code so when a user adds a value to a table (I'm forcing them to do it through code) so that when the value is added, it will create a field in another table.

I'm not sure how to define it as a boolean field with the format yes/no. Right now it creates a boolean field, but the format is blank. I have the following code so far:

Dim db As Database
Dim tbl As TableDef
Dim fld As DAO.Field

Set db = CurrentDb()
Set tbl = db.TableDefs("tbl_BU_Saved_Reports")
Set fld = tbl.CreateField(temp_BU_name, dbBoolean)
tbl.Fields.Append fld

Set db = Nothing
Set tbl = Nothing
Set fld = Nothing

 

[rjoubert]

Try this...

    Dim db As Database
    Dim tbl As TableDef
    Dim fld As DAO.Field
    Dim fmt As Property
    
    Set db = CurrentDb()
    Set tbl = db.TableDefs("tbl_BU_Saved_Reports")
    Set fld = tbl.CreateField(temp_BU_name, dbBoolean)
    tbl.Fields.Append fld

    Set fmt = fld.CreateProperty("Format", dbText, "Yes/No")
    fld.Properties.Append fmt
    
    Set db = Nothing
    Set tbl = Nothing
    Set fld = Nothing
    Set fmt = Nothing

 

[emik]

Hi, thanks for the reply.

So the format is now set as Yes/No, the last problem I'm having is it doesn't change the type in the lookup from Text box to Check box. So although the format is correct, it still shows a "yes" or "no" instead of the check box.

Thank you.

 

[emik]

I don't see an edit button, but just to clear up my previous post, it's the Lookup - Display Control that I need to be a check box. Thanks.

 

[rjoubert]

You can still bind that field to a check box on a Form, and it will show up correctly. It shouldn't matter how it appears in the table...your users shouldn't be looking at the table anyways, they should be viewing the data through a form.

 

[emik]

Hey that's true, just to keep the database consistent for future developers I can change the previous fields to text.

Thanks for all your help!