dcsimg
Results 1 to 4 of 4

Thread: [Resolved] Settinng DAO "dbBoolean" (True/False) format properties?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Feb 2008
    Posts
    151

    Resolved [Resolved] Settinng DAO "dbBoolean" (True/False) format properties?

    G'day folks,

    I'm using my program to create tables in an existing DAO database.
    The amount of tables depends on user input.
    It all works fine except for the "dbBoolean" (True/False) fields.
    They aren't the typical checkbox in appearance. Just a regular text field with a zero by default.

    I've managed to set the default value to "0" using the following example code.
    Code:
    Set FD1 = TD.CreateField(MyTableField, dbBoolean) 
    
    TD.Fields.Append FD1
    
    DB.TableDefs(MyDB).Fields(MyTableField).Properties("DefaultValue") = "0"
    When that worked I thought it only logical that to format the field would be the same

    Code:
    DB.TableDefs(MyDB).Fields(MyTableField).Properties("Format") = "True/False"
    But alas logic / common sense doesn't always pay off in the world of MS (Misery & Suffering). lol
    After hours of searching I've only found long winded answers in "VBA" and using SQL" or some garbage. All of which fly over my head.

    Any help will be appreciated, cheers.

    P.S.
    I went to bed two hours ago but couldn't stop thinking about it. So if I don't reply for a few hours please don't take it to heart.
    Last edited by AccadaccA; Jun 14th, 2019 at 04:58 PM. Reason: Resolved
    I rely heavily on trial and error, mostly only succeeding in the latter.

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,460

    Re: Settinng DAO "dbBoolean" (True/False) format properties?

    Hi AccadaccA,

    I use the DAO extensively. However, I must admit that I've never bothered with the Format property of a TableDef.Field. Regarding all the standard DAO properties, you can see these in the Object Viewer (that is, if you have the DAO referenced in your project, which I believe you do). Here's a screenshot of that complete list:

    Name:  DAO.png
Views: 29
Size:  26.0 KB

    Now, regarding any of those, you don't need to use the Properties() collection to get to those. You can just directly reference them, as follows:

    Code:
    DB.TableDefs(MyDB).Fields(MyTableField).DefaultValue = False
    Now, notice that I also didn't use a string. Rather, I used the False constant, which is better for a dbBoolean type.

    ------------

    Ok, regarding your question, yes, there are additional properties in a MS-Access database that aren't in the DAO object hierarchy. One is your "Format" property. Another is the "Description" property, which I have used. And interestingly, I assigned it pretty much like you tried to assign the "Format" property, but with a few more lines of code. Here is the code I use to assign it, as well as retrieve it:

    Code:
    
    Private Property Let DbFieldDescription(sTable As String, sField As String, sDesc As String)
        Dim fld As DAO.Field
        Dim prop As DAO.Property
        '
        Set fld = dbTheDatabase.TableDefs(sTable).Fields(sField)
        '
        ' Create property in case it's not already there.
        Set prop = dbTheDatabase.CreateProperty("Description", dbText, " ")
        On Error Resume Next ' In case it already exists.
            fld.Properties.Append prop
        On Error GoTo 0
        '
        fld.Properties("Description") = sDesc
    End Property
    
    Private Property Get DbFieldDescription(sTable As String, sField As String) As String
        ' Must use error trapping because it'll error if the "Description" property has never been used.
        ' It simply won't exist.
        On Error Resume Next
            DbFieldDescription = dbTheDatabase.TableDefs(sTable).Fields(sField).Properties("Description")
        On Error GoTo 0
    End Property
    

    And, as a further note, I always use that code after the table (and its fields) is actually created. In other words, it's executed after my .Append and .Refresh methods are executed on the TableDef.

    I'll leave it to you to see if the same approach will work with the "Format" property. Also, it wouldn't surprise me if the "True/False", "Yes/No", and "On/Off" options for a boolean field were constants of some kind (and not actually strings). If that's the case, I'm not sure where to find them. You might just try setting the "Format" field to 1 (or 2 or 3) and see what happens. I'm sure, if they're enumerated constants, they're in MSDN somewhere. I just don't know where.

    Maybe that'll get you part of the way there.

    Good Luck,
    Elroy

    EDIT1: Also, be sure to recognize that the format property has nothing to do with how the data is stored (or how it's seen by VB6). The only thing that property is used for is to specify how the field is displayed when viewing the field within MS-Access.

    EDIT2: Just an FYI, my dbTheDatabase variable is a global of the actual opened DAO database.
    Last edited by Elroy; Jun 14th, 2019 at 01:53 PM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Feb 2008
    Posts
    151

    Resolved Re: Settinng DAO "dbBoolean" (True/False) format properties?

    G'day Elroy,

    Thank you for putting so much time into your reply.
    Much appreciated, champ.

    I've only just woke up and still on my first coffee so the cogs aren't lubricated enough to start turning yet. lol
    I read through your reply twice through blurry eyes, changed the format code as you've displayed...
    Code:
    DB.TableDefs(MyDB).Fields(MyTableField).DefaultValue = False
    .... and added the extra properties code you shared. Thanks for that.

    I'm sorry to have to ask, how do I call it after creating the table?

    Quote Originally Posted by Elroy View Post
    EDIT1: Also, be sure to recognize that the format property has nothing to do with how the data is stored (or how it's seen by VB6). The only thing that property is used for is to specify how the field is displayed when viewing the field within MS-Access.
    You know you're right. It doesn't matter how the fields look in Access as much as how they respond with the program.
    For that matter they don't even need to be Yes/No data types.
    I can assign "0" / "1", "Yes" / "No", "True" / "False" or anything in a regular Text field and my program will give the same response depending on what I tell it to write and what it should look for in those fields.
    Sorry if I'm sounding like I'm rambling through blurred vision, it's because I am. lol

    Thank you for openning my eyes to the fact, my good man.
    I rely heavily on trial and error, mostly only succeeding in the latter.

  4. #4
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    5,460

    Re: Settinng DAO "dbBoolean" (True/False) format properties?

    Quote Originally Posted by AccadaccA View Post
    I'm sorry to have to ask, how do I call it after creating the table?
    Hi AccadaccA,

    In my code, I just do all the work to create the table and the fields (and actually all the indexes and relationships too). And then, I go back and call that Let DbFieldDescription procedure. You can certainly make DB structural changes after you've got things created, and that's how I've found that these "extra" fields work best. Again, I haven't tried it with "Format" but I'm guessing it'll work.

    Quote Originally Posted by AccadaccA View Post
    You know you're right. It doesn't matter how the fields look in Access as much as how they respond with the program.
    For that matter they don't even need to be Yes/No data types.
    I can assign "0" / "1", "Yes" / "No", "True" / "False" or anything in a regular Text field and my program will give the same response depending on what I tell it to write and what it should look for in those fields.
    Ok, yes, all of that is correct. However, if you need a boolean field, that's what I'd create. Again, that "Format" field really has nothing to do with the MS-Access field type (other than certain field types have certain formats they accept). Internally, if you create a boolean field, that's what it'll be regardless of the "Format" setting.

    Maybe you already understood that. But, when you went on about using a text field as a boolean (which you can certainly do), I wasn't sure you understood that.

    Anyway, best of luck.

    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width