VBA Appending a New Field - Can a Listbox Look Up property be added to the Created Field?

[DittoBird]

HI folks,



I've created a proc that creates a table, then creates fields - as per the snippets below. Of course, the CreateField method has just the three arguments shown in the second snippet. But I've never done the following before:

Is there anyway to modify the new field to have a look up listbox with, in this case, just two values, say "Archibus;Trina"? I don't need list box headers or anything and the user will be allowed to add whatever they want into the field besides the two choices.

In the second snippet below, tbNew is the variable set for the table, i.e.

    Dim db As DAO.Database
    Dim tbNew As DAO.TableDef

    Set db = Access.CurrentDb

    Set tbNew = db.CreateTableDef("Some New Table Name")

This is the part with which I am familiar and works perfectly. Other fields have been left out for clarity.

Just hoping it is possible to add the ???? code:

    with tbNew 
        .Fields.Append .CreateField("ArchTrina", dbText, 10)

        'Next, add listbox look up for the above field...
        ???????
        
    End With

    'Now add the defined table
    db.TableDefs.Append tbNew

Thanks in advance for any help or what to look for in the MS documentation.

I hope I've been clear enough. Admonishments are entirely appropriate if I have not.

Thanks in advance,
--
Tim

[orange]

Listbox is typically a control on a Form. It gets its data from a table or a list.
see this for example.

[pbaldy]

Also, most of us would recommend against a lookup field at the table level:

http://www.theaccessweb.com/lookupfields.htm

[DittoBird]

Thank so much Orange, but I'm talking about adding this:

Good grief, can't upload an image, hang on, off I go to imgur

Can't even do that, work computers are locked down so much.

Anyway, that's not what I'm talking about.

Take care,
--
Tim

[DittoBird]

Also, most of us would recommend against a lookup field at the table level:

http://www.theaccessweb.com/lookupfields.htm

Thank you, Paul, I very much appreciate the advice. Though you are singing to the choir; normally I would too and I personally prefer a look up table myself.

It really isn't an issue here as I am not developing an application. All I'm doing is creating a table of table fields from a linked Access database where someone imported data from an MS SQL application (Archibus) and added fields of her own which are hoped to be included in a system I am involved with bringing in (SAP). For my own reference, not another user, I want to indicate which fields from her table are from Archibus and which from her. Security won't be an issue. I am not interested in creating forms and other functionality.

The reason I am automating the creation is because 1) there are a lot of tables involved and 2) I want to be able to redo the set up for similar MS databases and Excel sheets across my department to be able to see what kinds of data different people are using and trying desperately to accommodate it all, somehow, into whatever the SAP system has available.

FWIW, In Oracle I used to use a varChar2 data type column (field) with Yes and No or True and False as look up values to do the same sort of thing as a Jet/ACE boolean. Different RDBMS of course, but it never caused a problem. When I was working with Jet tables in A2003 and earlier as well as A2007, I would stick to just the linked tables.

Is the Access Web still being maintained, do you know? It was usually my first stop for a lot of questions and code snippets in the 2000s to abut 2015.

All the best,
--
Tim

[Minty]

I'm not sure the lookup field table properties are exposed anywhere, as they effectively are like a "hidden table" access maintains in the background.

You can list field names from a table really easily in a combo or a list box using the Field List property as the row source?
So on a form that might help, but can't see how you would apply that in a table creation situation.

[DittoBird]

I'm not sure the lookup field table properties are exposed anywhere, as they effectively are like a "hidden table" access maintains in the background.

You can list field names from a table really easily in a combo or a list box using the Field List property as the row source?
So on a form that might help, but can't see how you would apply that in a table creation situation.

Thanks Minty. I'll have to be an extra step, then.

That solves my question!
--
Tim

[Gicu]

Hi Tim,

Please have a look at this thread, I think it could help you with your quest:
https://stackoverflow.com/questions/...2013-using-sql

Cheers,
Vlad

[pbaldy]

Is the Access Web still being maintained, do you know? It was usually my first stop for a lot of questions and code snippets in the 2000s to abut 2015.

Yes, but the domain changed and the forward may have ended. Try going to:

http://www.theaccessweb.com/

[DittoBird]

Yes, but the domain changed and the forward may have ended. Try going to:

http://www.theaccessweb.com/

Oh, wonderful, the 1998 - 2017 is reassuring to see as compared to 1998 - 2009.

Thanks

[DittoBird]

Hi Tim,

Please have a look at this thread, I think it could help you with your quest:
https://stackoverflow.com/questions/...2013-using-sql

Cheers,
Vlad

Ah, those properties. I always forget to look there. This is fantastic, Vlad. Thanks so very much.

Bookmarked for current and future use (with the provisio indicated by Paul, of couyrse!)
--
Tim