Full Version: Table Properties
UtterAccess Forums > Microsoft® Access > Access Forms
MrMSAccess
I am trying to create a table in code where I can set all the properties as well. I want to be able to set the Display Control to "Combo Box", "Row Source Type" to "Value List" and then populate the "Row Source" property. Is there a way to do this in VBA?
theDBguy
Welcome to UA!
It is highly recommended that lookup fields at the table level should be avoided. Check out this link for more info.
MrMSAccess
DB Guy,
I'm sorry, I'm not sure what your link has to do with what I'm trying to do here. Maybe it would help if I C & Ped my code. I'm trying to set the "Display Control", "Row Source Type" and then populate the row source type in the table. I know I would place it where I have my properties reference but I just don't know the syntax to use. Any ideas?
CODE
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Set dbs = Application.CurrentDb
Set tdf = dbs.TableDefs("Table2")
Set fld = tdf.Fields("ControlType")
With fld
    .Properties("AllowZeroLength") = False
End With
Set dbs = Nothing
Set tdf = Nothing
Set fld = Nothing
MrMSAccess
Ok, Ok...Now I get what you're saying and I agree with that, however in this case, I'm developing a custom application to be used in conjunction with Access development projects. The table that I am looking to set these lookup values on is a temp table that is created and used for the initial setup and then deleted. I just need to know how I would reference the "Lookup" portion in the table for the fields I specified in code.
theDBguy
The link I gave you has everything to do with what you're trying to accomplish; however, it is meant to discourage you from doing what you're trying to do. Honestly, I don't know the code you would need to use, but even if I knew it, I probably wouldn't tell you. Not to be selfish, but just trying to help you avoid any future problems for creating lookup fields at the table level.

Of course, if you still insists on continuing with what you want to do, I cannot stop you or anyone here who might want to give you the solution you're looking for.

If I misunderstood your request, I humbly apologize.

Good luck with your project.

Edit Added:

Sorry, I was still typing this reply when you posted your last comment.
Edited by: theDBguy on Thu Feb 21 16:21:54 EST 2008.
MrMSAccess
Well, like everything theres cases where something is needed. In general using lookups in tables like that is not a good idea at all, however in this case with the type of application I'm developing it's the best solution. Thanks.
MrMSAccess
That thread doesen't say how to do it either. Guess I'm back to square one. Thanks for answering anyway.
theDBguy
I'm sure that if you keep trying, you will eventually find what you're looking for. But I sincerely hope that you find a better solution to your situation than what you're wanting to do. Perhaps, if you could explain why you are having this dilemma, we could give you a better alternative. Good luck.
datAdrenaline
I don't have a problem with defining display controls at the table level ... the BIG caution I give is that NEVER NEVER NEVER HIDE the BOUND column!!!! .... Also, I advise on the speed degredation and other negative ills ... Many times I do it during development (so I can get most of the good stuff into a form!). Then prior to release, I reset my display controls to Text Box where appropriate.
The property of the field you will maniplate is named "DisplayControl", which accepts an integer which are part of the AcControlType enumation, the valid values are:
AcControlType.acTextBox (109)
AcControlType.acListBox (110)
AcControlType.acComboBox (111)
Also,
To set the Display Control property ... use code that looks something like this:
CODE
Private Sub SetDisplayControl()
    
    Dim db As DAO.Database
    
    Set db = CurrentDb
    
    'Set the Display Control first ...
    With db.TableDefs("yourtablename").Fields("yourfieldname")
        .Properties("DisplayControl") = AcControlType.acComboBox
    
        'Now add the addition properties (Access adds them automatically if done through
        'the user interface ... but in code, you need to add them your self, so I check
        'to see if I can set one of the expected properties, if its there, NO error
        'is generate, and then we just set the value, otherwise ... we create and set
        'the properties
        On Error Resume Next
        .Properties("RowSourceType") = "Value List"
        If err <> 0 Then
        
            .Properties.Append .CreateProperty("RowSourceType", DataTypeEnum.dbText, "Value List")
            .Properties.Append .CreateProperty("RowSource", DataTypeEnum.dbText, "'Text1';'Text2'")
            .Properties.Append .CreateProperty("BoundColumn", DataTypeEnum.dbInteger, 1)
            .Properties.Append .CreateProperty("ColumnCount", DataTypeEnum.dbInteger, 1)
            .Properties.Append .CreateProperty("ColumnHeads", DataTypeEnum.dbBoolean, False)
            .Properties.Append .CreateProperty("ListRows", DataTypeEnum.dbInteger, 10)
            .Properties.Append .CreateProperty("ListWidth", DataTypeEnum.dbText, "")
            .Properties.Append .CreateProperty("LimitToList", DataTypeEnum.dbBoolean, False)
            
        Else
            
            .Properties("RowSourceType") = "Value List"
            .Properties("RowSource") = "'Text1';'Text2'"
            .Properties("BoundColumn") = 1
            .Properties("ColumnCount") = 1
            .Properties("ColumnHeads") = False
            .Properties("ListRows") = 10
            .Properties("ListWidth") = ""
            .Properties("LimitToList") = False
            
        End If
        
        .Properties.Refresh
            
    End With
    
End Sub

Note ... even though I created this code in the VBA editor, I still think of it as AIR CODE!!! ... hope this helps!
MrMSAccess
Brent,
think that's what I'm looking for. Thanks so much.
DBGuy,
The reason that I need it like this is because I'm creating a packaged app. The table that I'm setting the "Lookup" portion on is merely a temporary table that is needed in the function, so the user can enter some informationthat is needed in the function. After that it's performed, the table is deleted.
datAdrenaline
>> so the user can enter some informationthat is needed in the function. After that it's performed, the table is deleted. <<
It seems from that description, you would be better off to use a Form with unbound combo box controls (which can be populated at design time, or run time) ... pop the form open, have the user input the needed info, then click an "Ok" button of sorts ... then that button code can hide the form (.Visible = False) .. then the info on the form is available for your code to provide the information necessary for your code ...
Orarely, if ever, think it is necessary to use a table object as a user input mechanism
theDBguy
I totally agree with Brent. It's much easier to change the rowsource of comboboxes in forms by code and you don't get the bloat problem from creating and deleting temporary tables. Thanks to Brent for the code sample. Good luck with your project.
MrMSAccess
I thought about doing it the way you describe but it's a little more complex than just hiding the form and using unbound controls.
ithout getting into too long of an explanation here. It's a type of wizard setup applicatiion where the developer would open this form and there would be a list of the available queries in the database for them to select, they would then enter the name of the form, they want to create. The next step would contain a form with the table that I create telling the user to select which fields they want to include in their Filtering Criteria form (Field Name, Type of Control e.g. List Box, Combo Box, Single Text Box, Ranges, >=, Yes/No fields etc.). This table includes all the fields names that are in the query that the developer selects to build off of. Once they select all their settings then a form is created with all the filtering controls and correct SQL synta, the table is than deleted.
I think if you actually saw what I'm doing, you'd understand better why in this case, this is actually the best way to do it.
datAdrenaline
>> I think if you actually saw what I'm doing, you'd understand better why in this case, this is actually the best way to do it. <<

Orespectfully disagree ... You can use a disconnected recordset, and bind that recordset to your "unbound" form. Check out the attachment (in A2000 format) to this post. I basically tried to recreate a small portion of what you described....

A form with a list of queries (I included tables to), when the user selects the recordsource, the sub-form is bound to a disconnected recordset (meaning, there is NO table behind it!!). The user can then select the display control to use for the field in question ... The way I imagined it is that if NO display control type was selected the Field would NOT be included in the genereated form. When you HIDE the field definition form, you can loop through the subforms recordset (using the .Recordset or .RecordsetClone property) in order to build your filtering form, but do take note in that the recordset returned by the subform will be ADO.

Check it out, and tell me what you think.

Please note, that on the form "frmRecordSource_FieldList" I used a BOGUS recordsource in order to design the form with ease and to prevent the ?Name from appearing upon initial load of "frmRecordSources".
MrMSAccess
Brent,
just downloaded it. I keep getting an error thrown at me but I see what you're doing with it. Very nice. I can definitely get some use out of your sample in the future, so thanks for that. As far as the app I'm building though this wouldn't work. I know you guys are never going to believe me but in this case with it being a canned app the only way that I can do it is the way that I said. Thanks for the input though.
datAdrenaline
>> I know you guys are never going to believe me but in this case with it being a canned app the only way that I can do it is the way that I said.<<
I am not nearly as familiar with YOUR app as you are! ... so ... I beleive you! ... Plus your technique does not have "serious flaw" or anything like that. I just appreciate you considering other options, that are typically considered over creating and droping tables! ... thumbup.gif
...
If your up for it, care to share what error are you getting with my sample? ... could it be the lack of reference for the DAO or ADO library in VBA?
MrMSAccess
Sure. I'm getting "Run-time error '7965'. The object you entered is not a valid Recordset property." It's throwing the error on this line.
et Me.subfrmFieldList.Form.Recordset = rstA
I'm sure it's probably something I'm doing wrong like not having a correct reference set or something. Any suggestions?
datAdrenaline
Hmm .... what version of Access are you using? I created it in A2003 with A2000 format ... Which, now that I say that, I *think* I need to set some additional properties on an ADO recordset for A2000 to have the ability to update the records?? ... Does the code compile? ... I D/L'd the attachment, ran the app, and made a screen shot so you can see the output ...

datAdrenaline
Here is a screen shot of my references ....

MrMSAccess
Brent,
I'm running 2000. I have the same exact reference as you do set. The only difference that I have is that my Microsoft Access Object Library is 9.0 vice 11.0.
LPurvis
Hi all.
(Popping in here at Brent's behest).
I'm afraid I'm the harbinger of bad news though.
Though recordset form binding was introduced in Acc2000 - it was, IMO, incomplete functionality.
(Very good and a vital, bold step forward - but incomplete).
Both in the assignment of a Disconnected ADO recordset to a form (raising the error as you see) and similarly the assignment of a Created ADO recordset (which can also be described as being "disconnected" - though technically it has never been connected to anything - so I prefer "Created".. having only just had a peek at Brent's demo mdb I see that's actually what we're talking about here).
In the articles I've done on form binding to recordsets I've always had to state that this is Acc2002+ functionality.
(It's well known that Acc2002 introduced binding to list controls - but it also improved form binding in this respect too).
AFAIK no reference manipulation will make any difference.
It's an internal form issue in 2000.
FWIW Brent's given you good info - for example specifying a LockType on the recordset is the only thing that's making is editable.
And it's a fair bit more effort to get it to be appendable... but doable - of course ;-)
It's just that you'll need Acc2002 to take advantage of it. :-(
MrMSAccess
Leigh,
Thanks for the explanation. Hopefully we'll upgrade here someday,so I can use Brent's example.
datAdrenaline
Thanks Leigh ... From our PM's/email I was hoping there was a work around ...
.. I guess there is ... you can create a temp table as the OP is doing! ... laugh.gif ...
LPurvis
Yep - I often refer to recordset creation as a more "modern" equivalent to local tables. But local tables are a *very* powerful tool in Access' bag.
Odon't view one method as superior to the other particularly - each has it's time for implementation.
But all the recordset methods require Acc2002. (Including those with my meddling techniques :-)
datAdrenaline
Seeing as how this is driving me crazy and I really don't like temp tables ... despite their virtues ... (unless you do them in a remote temp database too!). I have posted another tableless (and recordsetLESS) solution ... its attached to this post. The screen shot is the next post ...
datAdrenaline
Here is the screen shot ...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.