How do I define column properties using VB 1

[AccessGuruCarl]

Create a new Access Database using VB.

I have 2 procedures. One creates a new Access .mdb file.
The next sub creates the tables.

My question is how do I set/format booleen valued properties to a checkbox. During execution it creates a Textbox!

Next issue is formatting a valuelist, and populating the table with the values, instead of populating a combo at run-time.

Here is a sample of create tables code...

Dim catDB As ADOX.Catalog
Dim tblNew As ADOX.Table
Dim col  As ADOX.Column
   
    Set catDB = New ADOX.Catalog
' Open the catalog
    catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sDatabaseToCreate
' Create the tables
'===============================================
' Create tblRaceInfo Table
    Set tblNew = New ADOX.Table
    tblNew.Name = "tblRaceInfo"
        
    Set col = New ADOX.Column
    ' First Create an Autonumber column
    With col
        .ParentCatalog = catDB
        .Type = adInteger
        .Name = "RaceID"
        .Properties("Autoincrement") = True
        .Properties("Description") = "Unique Identifer - " & _
            "Do Not Alter"
    End With
    tblNew.Columns.Append col
     ' Now add the rest of the columns
     With tblNew
         ' Create fields and append them to the Columns collection.
        With .Columns
            .Append "TitleLine1", adVarWChar
            .Append "TitleLine2", adVarWChar
            .Append "TitleLine3", adVarWChar
            .Append "RaceType", adVarWChar  'VALUELIST
            .Append "LaneCnt", adVarWChar
        End With
         ' Set column specfic attributes.
            With .Columns("TitleLine1")
                .DefinedSize = 50
                .Attributes = adColNullable
            End With
            With .Columns("TitleLine2")
                .DefinedSize = 50
            End With
            With .Columns("TitleLine3")
                .DefinedSize = 50
            End With
[!]            With .Columns("RaceType")
                .DefinedSize = 25
****            Needs to be a ValueList  - Car,Truck,Boat
            End With[/!]
            With .Columns("LaneCnt")
                .DefinedSize = 2
            End With
     End With
' Add the new Table to the Tables collection of the database.
     catDB.Tables.Append tblNew
     Set col = Nothing
     Set tblNew = Nothing
'===============================================
'
'===============================================
' Create tblRacers Table
    Set tblNew = New ADOX.Table
    tblNew.Name = "tblRacers"
        
    Set col = New ADOX.Column
    ' First Create an Autonumber column
    With col
        .ParentCatalog = catDB
        .Type = adInteger ' adText does not exist
        .Name = "RacerID"
        .Properties("Autoincrement") = True
        .Properties("Description") = "Unique Identifer - " & _
            "Do Not Alter"
    End With
    tblNew.Columns.Append col
     ' Now add the rest of the columns
     With tblNew
         ' Create fields and append them to the
         ' Columns collection of the new Table object.
        With .Columns
            .Append "DenNumber", adInteger
            .Append "FirstName", adVarWChar
            .Append "LastName", adVarWChar
            .Append "CarNumber", adInteger
            .Append "VehicleWeight", adInteger
            .Append "ClassID", adInteger
            .Append "RankID", adInteger
            .Append "PassedInspection", adBoolean  'CHECKBOX
        End With
         ' Set column specfic attributes.
        With .Columns("DenNumber")
            .Attributes = adColNullable
        End With
        With .Columns("FirstName")
            .DefinedSize = 50
        End With
        With .Columns("LastName")
            .DefinedSize = 50
            .Attributes = adColNullable
        End With
[!]        With .Columns("PassedInspection")
           ' access uses DisplayConrol... Didn't work
           ' .displaycontrol = 106
****        NEEDS TO BE A CHECKBOX[/!]
        End With
     End With
' Add the new Table to the Tables collection of the database.
     catDB.Tables.Append tblNew
     Set col = Nothing
     Set tblNew = Nothing

Thanks in advance....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JoeAtWork]

During execution it creates a Textbox!

I don't understand - the only way a textbox is created in VB is if you draw one on a form.

How you define a table column and how you choose to display it's data are completely different things. There is no such thing as a "checkbox" column, there are however fields whose data type is Boolean.

In your procedures you are creating tables, and you are defining the fields for those tables. You define it's data type (text, number, Boolean, etc.), whether it is required or not (allows Nulls), how big it can be, etc. You do not define how it is displayed.

When you build your UI (forms) is when you say how it should be displayed. If you want it to be a checkbox, than draw a textbox on the form. If you would rather have a dropdown with "Yes" and "No" options, then do that. You will also have to write the code that populates these controls, and saves their updated values back to the database.

instead of populating a combo at run-time

In VB you populate dropdowns at runtime.

In short, the VB environment does not mash up the UI and data elements the way Access does. It very correctly keeps these two concepts seperate.

 

[BobRodes]

<In VB you populate dropdowns at runtime.

You don't have to, unless you're pulling values from the database, which of course is what Carl might mean he wants to do.

Carl, I'm going to assume you're an Access guy who's attempting to apply his experience in Access to VB6. If so, it's important to understand that there is no such thing as a "CurrentDB" in VB. The fact of Access.exe being open is what makes there to be a currently open database, and it's not open in VB. In VB, there is no database available unless you specifically open it, which you can (pretty much) only do at runtime.

To populate your dropdown list box prior to runtime, just go to the list property and enter the values you want. To enter a value and go to the next one, hit Ctrl-Enter rather than Enter. Use Enter when you're done.

If you are trying to populate your list box with values from a database, you have to do that at runtime. For various reasons I'll leave for another discussion, you're best off iterating through the records you want to use and adding them one by one to your listbox.

HTH

Bob

 

[TysonLPrice]

I don't understand - the only way a textbox is created in VB is if you draw one on a form."

What about?

Private Sub Command1_Click()
    Dim TextControl As TextBox
    Set TextControl = Form1.Controls.Add("Vb.TextBox", "Text" & 1)
    With TextControl
        .Left = 2000
        .Top = 1000
        .Width = 2000
        .Height = 2000
        .Visible = True
    End With
End Sub

 

[SBerthold]

As Bob said, you cannot do what you want in the same manner as you are used to in MS Access.

You do not format a db field to display as a check box or combo box, or list.

Actually, the db table fields in Access are not really formated as a check box either. It is the grid (or form), where the data is displayed on, which gets told what type of control to show on it, such as a check box overlaying the boolean value, if the grid has this possibility.
If not, you need to code this yourself, or get a third party control which does, or use something like the DataRepeater (more of Bob's field of experience, I think)

 

[jadams0173]

Carl,

I use access 2k and VB6 together a lot. I understand what you are asking with regards to the checkbox. My experience has been to use the YESNO data type(-1=CHECKED 0=UNCHECKED). It's not a checkbox but still has the same meaning. I do most of mine using Creat Table and Alter Table so I'm not that familiar with adox.

Where conn is an open ado connection

conn.Execute "ALTER TABLE TBL_BOM ADD ID COUNTER", , adCmdText + adExecuteNoRecords 'ADDS AN AUTONUMBER 
conn.Execute "ALTER TABLE TBL_BOM ADD PRIMARY KEY (ID)", , adCmdText + adExecuteNoRecords 'ADDS THE PRIMARY KEY TO THE AUTONUMBER

SQL = "ALTER TABLE test123 ADD PICKSCOUNTED YESNO"
conn.Execute SQL, , 129

 

[AccessGuruCarl]

Thanks for the posts...

Just to verify then, If you open Access, then open a table in design view add a few fields, set one of the data types to Yes/No(booleen), If you look at the second tab for the field properties "Lookup" the Display Control option is set to Checkbox... With combobox options of Check Box, Text Box, Combo Box. Table view does show checkboxes.

By default it chooses Check Box when building the table in Access when you select the data type as Yes/No, however when tables are built in VB, when you open the db to review the table design.. This field displays a Textbox in table view not a checkbox.

From the posts earlier, It is not possible to create the checkbox field then?

Bob...

To populate your dropdown list box prior to runtime, just go to the list property and enter the values you want. To enter a value and go to the next one, hit Ctrl-Enter rather than Enter. Use Enter when you're done.

Sorry, I don't understand. Can this be done in code? Or are you talking about opening access to add the fields.

Thanks again.





AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JoeAtWork]

From the posts earlier, It is not possible to create the checkbox field then?

There's no such thing as a "Checkbox" field. There are Boolean fields (or "YesNo" as Access likes to call them). What it looks like in VB when you open the table is irrelevant, since you can't use that view for your user interface. You will be building the user interface yourself (either with a grid, or with individual controls), at which point you decide how you would like to view the data.

I actually find it rather annoying in Access when it applies the auto-formatting in the datasheet view. I often need to see the raw data, which the auto-formatting can misrepresent.

 

[AccessGuruCarl]

Thanks for the confirmation...



AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[BobRodes]

<Can this be done in code?
No, you can only do it if you know what your selections are prior to runtime and are willing to hardcode them. For example, "1Q, 2Q, 3Q, 4Q" is a list that is highly unlikely to change over time.

<Or are you talking about opening access to add the fields.
No, I'm not. Access does a couple of things when you open it: it opens the underlying database and it binds various forms and controls to it. This binding means that it automatically looks up data in the database when it gets opened and populates things like list boxes. In VB, you have to do all this yourself. (While you can do data binding in VB as well, it's not considered particularly stable.) Typically, you will open an ADO recordset and iterate through it, populating your list box as you go. Generally you'll do this in a separate procedure, and call that procedure at the end of the Form_Load event handler proc.

You then have to write routines for cursor positioning, UI refresh, and data manipulation.

By the way, there is in the listbox control the ability to set the Style property to Checked, and you will see checkboxes along the left side of your list selections. (This is not available with the dropdown list box.)

HTH

Bob

 

[AccessGuruCarl]

Thanks for info Bob...

For this app, I do know what the values would be.

Do you have a sample of hard coding it. For future reference.

Thanks again...

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[BobRodes]

Sure. Put a list box on a form. In the properties window, click on the List property. You'll see a dropdown box in the right column saying (List). Click on this, and a dropdown box will open. Type in your values, one by one, using Ctrl-Enter each time you enter a value. (Ctrl-Enter will keep you in the dropdown box, Enter will make you have to open it again.) After entering the last value, hit Enter. Et voilà! You have your hard coded list.

 

[gmmastros]

>> Ctrl-Enter will keep you in the dropdown box

That's the one thing I learned today that I plan on remembering! Thanks for the tip.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

Found it by accident...