Results 1 to 6 of 6
  1. #1
    VicM is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Location
    US-PA & FL
    Posts
    52

    Append checkbox field to table via VBA throws error setting property

    Hi folks,



    I'm using Access 2010 on a Win7 Pro box.

    I want to append a checkbox field to an existing table using VBA.

    I have appended the field to the table, but it shows basically as text with the number 0 in the field. I have other checkboxes in the table which was originally created in design mode. I've scoured thru Google attempting to figure out how to convert the field to a checkbox. But I've come up short.

    The code I'm using to append the field is as follows:

    Code:
    Sub AddTableFields(who As String)
        Dim db As DAO.Database
        Dim t As DAO.TableDef
        Dim f As DAO.Field
    
        Set db = CurrentDb
        Set t = db.TableDefs("InventoryControl")
        
        Set f = t.CreateField(who, dbBoolean)
        t.Fields.Append f
        f.Properties(25) = "Yes/No"     'Format property: these two properties I got from querying an existing checkbox in the table.
        f.Properties(26) = 106            'Display Control property
    
        
        Set f = Nothing
        Set t = Nothing
        Set db = Nothing
        
    End Sub
    When I execute this sub, the field is appended but I get RunTime 3265: Item not found in this collection, at the f.Properties(25) = "Yes/No" line

    Any suggestions would be appreciated.

    Thanks,
    Vic

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Surely, if you create it as boolean, then it is already a yes/no field?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I don't see 25 or 26 in the DAO field properties list. Yes/No would be a format, not a field type?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think it is the lookup property of the Yes/No field, maybe this link helps:
    https://stackoverflow.com/questions/...e-and-checkbox
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    VicM is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2017
    Location
    US-PA & FL
    Posts
    52
    Gentlefolk,
    Thanks for your suggestions.

    1. Welshgasman: Yes it is a Yes/No type. But in design mode the Format shows nothing and in the Lookup tab, the Display Control shows Text.

    2. Micron: I mentioned that I got those numbers for Format & DisplayControl respectively by enumerating thru an existing checkbox field in the table.

    3. Gicu: Your link pointed me in the right direction. I added the following code and it gave me exactly what I wanted.

    Code:
    Code:
        Dim p As Property
    .
    .
    .
        Set p = f.CreateProperty("DisplayControl", dbInteger, acCheckBox)
        f.Properties.Append p
        Set p = f.CreateProperty("Format", dbText, "Yes/No")
        f.Properties.Append p
    .
    .
    .
    So I thank you all for your responses. And to Gicu for pointing me in the right direction.

    Take care,
    Vic

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think it's the displaycontrol property:
    PropName: Type Value: 1 << Boolean
    PropName: Name Value: TEST
    PropName: Size Value: 1
    PropName: SourceField Value: TEST
    PropName: DisplayControl Value: 106
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 04-13-2021, 02:13 PM
  2. Replies: 2
    Last Post: 04-30-2019, 03:44 AM
  3. Replies: 7
    Last Post: 06-07-2018, 04:49 PM
  4. Replies: 18
    Last Post: 12-15-2016, 02:17 PM
  5. Checkbox OldValue property gives Error 3251
    By Monterey_Manzer in forum Programming
    Replies: 11
    Last Post: 05-06-2014, 03:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums