Full Version: Problem Updating Table Properties
UtterAccess Forums > Microsoft® Access > Access Forms
MrMSAccess
I have a function that creates a table and then changes the DisplaySettings to a combo box with a value list setting and populates the fields. When I try to run the code, I get an error when it tries to create the control saying it doesen't exist. Now, what's strange about this is if I manually go into the created table and change anything on it, it doesen't matter what and I run the code to change the display settings, everything works perfectly, so I'm not sure what's going on here. Here's the code I'm using. (Also, I've already gotten the lecture on why I shouldn't be setting the values like this in a table, so if you could refrain from that I'd appreciate it. LOL. Just let's say that I've got my reasons for needing to do it this way and keep it at that.)
CODE
Dim rstCreate As New ADODB.Recordset
Dim dbs As Database
Set dbs = CurrentDb
Dim fld As Field
Dim strFieldName As String
DoCmd.DeleteObject acTable, "tbl_FilteringType"
'Create the table
dbs.Execute "CREATE TABLE tbl_FilteringType " _
     & "(ft_Field TEXT, ft_RangeType TEXT); "
    
'Set the value list
With dbs.TableDefs("tbl_FilteringType").Fields("ft_RangeType")
.Properties("DisplayControl") = AcControlType.acComboBox
If Err <> 0 Then
.Properties.Append .CreateProperty("RowSourceType", dbText, "Value List")
.Properties.Append .CreateProperty("RowSource", dbText, "'List Box';'Combo Box';'Text Box';'Text Boxes (Range)';'Text Boxes (>=)';'Text Boxes (<=)'")
.Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
.Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
.Properties.Append .CreateProperty("ListRows", dbInteger, 10)
.Properties.Append .CreateProperty("ListWidth", dbText, "")
.Properties.Append .CreateProperty("LimitToList", dbBoolean, False)
Else
.Properties("RowSourceType") = "Value List"
.Properties.Append .CreateProperty("RowSource", dbText, "'List Box';'Combo Box';'Text Box';'Text Boxes (Range)';'Text Boxes (>=)';'Text Boxes (<=)'")
.Properties("BoundColumn") = 1
.Properties("ColumnCount") = 1
.Properties("ColumnHeads") = False
.Properties("ListRows") = 10
.Properties("ListWidth") = ""
.Properties("LimitToList") = False
End If
.Properties.Refresh
End With
accesshawaii
Have you tried refreshing the database tables and refreshing the properties in the table?
Bob_L
Bad, bad, bad, bad, bad -----> Lookup fields in tables are really NOT the way to go:
http://www.mvps.org/access/lookupfields.htm
MrMSAccess
Dan,
I have tried all that and it still doesen't work. Any other ideas? Anyone?
Bob,
I'm fully aware of this, I've been given the whole 9 yards on it and I respect what others have told me and agree with them, however I need to do it this way. That's why I wrote the below blurb in my initial post,I'm trying to avoid the lectures and find a solution.
Bob_L
Sorry to be such a pain on this, but I always love it when someone says "Let's just say I have my reasons." Usually when that statement is made it is because someone is doing something that they know is not right but don't want to share the reason because there either isn't one, or they are too embarrassed to share it so that an alternative, more correct method, may be suggested. Why doesn't anyone just come out and state the reason?
MrMSAccess
Bob,
Well, you're certainly entitled to your opinion, so if you want to go on thinking that people such as myself "Don't really have a reason or are too embarassed to try an alternate solution" than that's your business. I on the other hand even though I am fully aware that theres other developers out there who know more than I do and I can learn from have enough experience from my years of development to realize that there are times when you have to go against the grain in a manner of speaking to accomplish the end product, which is the case here.
HAs I said, I have gotten the whole 9 yards on the bad points of doing it this way. In another posting an MVP was nice enough to show me how to accomplish what I needed even though he made it clear that he doesen't agree with the way I'm doing it and he even provided me with a sample he had written as an alternate way of doing it, which wouldn't work with what I'm trying to develop, which is a distributable application but it was definitely something that I could use in the future. When I posted afterwards thanking him for the code and saying that it just wasn't going to work with what I'm doing, he accepted that I have my reasons for doing it this way instead of making assumptions such as you're doing and that I totally respected.
Anyway, I'm new to these boards and I really wasn't looking for a debate in here, I was just hoping to get some ideas to help me along with this project.
Bob_L
I usually will suggest ways to accomplish it, even if I don't necessarily agree with it, but I still ask the question - if you know that it isn't the norm, why can't you share the reasons why? It would seem to me that if you are forthright and honest about everything, instead of hiding behind the generic "I have my reasons" you might get a better response.
MrMSAccess
Bob,
Well, if posting a blurb explaining why I didn't want to go through the whole explanation and that I'm fully aware that it's not the norm classifies me as hiding behind the generic "I have my reasons" then guilty as charged. It couldn't have anything to do with the fact that I've already gone through all this and explained my reasons and what I was developing in another post, I suppose.
Why do you want to give me a hard time on this? If it's really so imperative that you know my reasons then please by all means look at my other thread, all you would have to do is a simple search on my name, I believe it's probably the only other thread that I've posted in here.
Frankly, I'm not really concerned at what it seems to you or if you think I'm too stubborn to accept alternatives, I'm embarassed or whatever it is you think. Let's make it simple. You're right, Bob on everything. I don't care, all I care about is whether or not someone would be able to offer me any kind've assistance with the actual question I asked in the first place, which seems to have gotten lost through all this nonsense.
accesshawaii
Enough guys.
Let's try to find a solution for MrAccess instead of giving him a bad first impression of UA, Bob.
Odid lookup your other post MrAccess and here's the link if you want to see his reasons behind it, Bob. Previous Post
I will try to find an answer for you on this. Don't get frustrated, there are a lot of good people in here who can provide you with alot of answers and help in your development. I know this probably has not been a very good first impression for you but theres a great group of people in here including Bob though you might not feel that way now with your guy's little altercation. LOL
NoahP
A most basic tenet of Utter Access is to provide help so people will learn Access the right way, not with workarounds.
oo many times a workaround is an indicator of deeper issues that are the real cause of the problem. By just giving people exactly what they ask for, without asking probing questions to determine if there is a bigger problem is NOT really helping anyone. It generally only adds another layer to the workaround 'onion' that will eventually come back and bite the OP.
Asking for the reasons someone wants to go against best practice is not, IMO, asking too much at all.
dannyseager
You are... it's just not the advice you wanted....
Is Noah has already said.. UA doesn't promote bad design, you'll struggle to get a work around that "goes against the grain"... especially if you can't give a better reason than "however in this case with the type of application I'm developing it's the best solution".
Please do not think that anyone in this thread (or this site) is trying to give you a hard time.... but we are trying to help you do the job right.
MrMSAccess
Danny,
get that UA does not promote bad design and I couldn't agree more with that.
<
I have stated the reasons behind why this is the best solution. I had stated that I had given the reasons for this in a previous post. One of the other posters even posted a link to my other post, which gives my explanantion, so yes, Right, wrong, or indifferent I do feel like I've been given a hard time with this.
When I go through all this and try alternate methods etc and then I'm told by another poster that basically I'm stubborn to accept alternate ideas or I'm too embarassed to say I don't know how to do it the right way, it's frustrating.
Bottom line, I appreciate everyone's concern over teaching the correct practice but it is not going to work in my case. I've been through all this before, so I know this is the best method of doing it. The definition of insanity is when you do something that does not work but yet you keep doing it the same way over and over again, which is what I'd be doing by trying the alternate methods again, which do not work.
I'm sorry to sound so annoyed but I've been working on this application for awhile now and this is pretty much the last piece to my puzzle of completing it. If someone can assist me with my question, I would be very appreciative, if not then that's fine, I'll just have to keep plugging to figure out how to do it on my own.
accesshawaii
I tried figuring something out for you but wasn't able to come up with anything. I feel for you. I know how frustrating it is when you just need that one more thing to complete a project you've been working on for months. Sorry, I couldn't be more help. Good luck.
dannyseager
Sorry can you post the reasons for not following the normal tried and tested methods... I've just re read your previous post and can not see the reason...
MrMSAccess
Sure, Danny.
I am developing a packaged app for developers. Theres a few reasons why the tried and tested will not work. Number 1 reason being that recordset form binding does not work in Access 2000 and earlier versions. This app is intended to be compatible with any version of Access. This reason alone is enough of a reason as to why I need to do it the way I'm saying. Please read my previous post on this where I did go into more specifics about it. Here is the thread My Other Post
Like I said, I'm sorry if I seem frustrated. Between a few of the comments earlier in this thread by one poster, which I felt were completely unnecessary and being this close to finally having this done but not being able to close it because of one missing piece has just helped to expand on that frustration.
CyberCow
The evidence produced by the problems you are getting with the method you are using now should be enough to convince you that crazy.gif doesn't work well. Face it. This is MS Access and there are at 17 different ways of doing most anything. Tried and true methods will get your further with less effort and in the long run too.

Oused to think that lookup fields in my tables were the way to go. It made everything so easy. Till it came to stuff like adding records, generating reports and not confusing the end user.

6 years after delivering a db I created with lookup fields in the tables, the client hunted me down and ask for some revisions. I had my reasons for using the lookup fields then. It was convenient at the time and made it easy to do some form design work. But I was horrified at what I had done because the revisions they wanted meant I had to completely re-work the table structures I so stubbornly insisted on. I just could justify adding that redesign time to their billing because of my arrogance. So I ate about 20 hours in data model redesign; which in turn effected the existing forms and reports - another 15 hours. At least now I will not be afraid to revisit that project.

The point is, using lookup fields in tables is liitle more than planting land mines. "I tread the path I walk in fear of mines I layed"

It's not about debate. It's the membership here giving REAL help or none at all. We're not going to "candy coat" anything.
MrMSAccess
Cyber, Thing is I do know that Lookup fields are not the ideal way to go. I appreciate everyone bringing it to my attention but trust me, I do know this.
give up, I explained my reasons as to why I need to do it this way. It's not about candy coating or anything else, this is the only way that I can do this. REAL help to me would be to just accept and trust what I'm telling you in the fact that this is the way that I need to do it. If you don't know how to do it or are not willing to assist me in it then fine, I can accept that, no hard feelings or anything else but to sit here and constantly tell me not to do something when I know even though nobody else is going to believe me is the only way to do this particular app is pointless.
So, if nobody is willing to help me for whatever reason they may have, that's fine. I'll just start from square one and try to figure it out.
CyberCow
Why can't what you're trying to do be don in a form that "looks" like a table but gives all the real functionality of a form? It wouldn't create the problem of having a lookup field in a table and might still approach your answer with a solution.
He ARE here to help and willingly so. Can you explain more of what exactly it is you're doing with this gizmo? What is the concept of this project?
(more after I get home from work and dinner)
MrMSAccess
Cyber, please see my previous post. I explained what I'm working on in that one. It pretty much covers what I need to do. As far as having a form, I guess it might be possible to create a form to display as continuous forms and to create combo box controls and populate them and then after the procedure is done, delete the form and table. That would require a fair amount of coding though and it really wouldn't be practical for my app. I appreciate the feedback but I think I'm going to call it a day and try to come up with something tomorrow. Have a good night.
CyberCow
I see no need to delete the form. It can be recycled for use with the generated table(s).
on't forget that if you are going to use a combobox based on data in a record, on continuous form, you will need to requery the combo in the form's OnCurrent property.
OK, have a good night.
datAdrenaline
Well MrMSAccess .... you have definately taken your abuse regarding lookup definitions in tables!!! .... I personally still don't like the fact that you have to create a table ... anyway ... your code halts because you do not have an error handler ... also, apparently a programmatically created table's fields does NOT get the "DisplayControl" property.. so we need to create it!. When I created the code in the other thread, I was using an existing manually created table ... so ... check out this to see if it does the trick for you ... you will see that since you are creating a new table, there really is no need for the If Err<>0 Then. Notice that in the previous thread I set the error handler to go to the next line of code if an error was raised, I then checked for the error. If an error existed, I CREATED the properties, if an error did not occur, then I just modified them. Also, with the discovery that a program created table does not get the "DisplayControl" property, you need to create it too! ... one more thing, I would suggest using the datatype identifier of CHAR simply because TEXT will translate to a MEMO datatype if you execute the CREATE TABLE statement through a connection object (currentproject.connection.execute ....)
For more information on SQL datatypes ...
Link for JET SQL datatypes
http://office.microsoft.com/en-us/access/HP010322481033.aspx
Link for ANSI SQL datatypes
http://office.microsoft.com/en-us/access/HP010322291033.aspx
Code for your consideration ...
CODE
Public Sub MakeMeATable1()
    
    Dim dbs As DAO.Database
    Dim strFieldName As String
    
    'Initialize
    On Error Resume Next
    Set dbs = CurrentDb
    DoCmd.DeleteObject acTable, "tbl_FilteringType"
    Err.Clear
    On Error GoTo 0
    
    'Create the table
    dbs.Execute "CREATE TABLE tbl_FilteringType" & _
                " (ft_Field CHAR, ft_RangeType CHAR);"
      
    dbs.TableDefs.Refresh 'Not needed, I just like to do it
    
    'Create the properties, since we have a NEW table ...
    With dbs.TableDefs("tbl_FilteringType").Fields("ft_RangeType")
    
        .Properties.Append .CreateProperty("DisplayControl", dbInteger, AcControlType.acComboBox)
        .Properties.Append .CreateProperty("RowSourceType", dbText, "Value List")
        .Properties.Append .CreateProperty("RowSource", dbText, "'List Box';'Combo Box';'Text Box';" & _
                                           "'Text Boxes (Range)';'Text Boxes (>=)';'Text Boxes (<=)'")
        .Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
        .Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
        .Properties.Append .CreateProperty("ListRows", dbInteger, 10)
        .Properties.Append .CreateProperty("ListWidth", dbText, -1)
        .Properties.Append .CreateProperty("LimitToList", dbBoolean, False)
        
        .Properties.Refresh
    
    End With
    
End Sub

Note that you MAY want to HIDE this table with the code found by clicking here
I hope this helps .... also hope I did not "put anybody off" but posting this ... I figured I started this with the other thread, so I might as well support my own code ... even if its AIR CODE!!! ...dazed.gif
strive4peace
hi MrMSAccess (what is your name?)

the error message is telling you what is wrong ... the property does not exists, you must create it:

change this:
.Properties("DisplayControl") = AcControlType.acComboBox
to this:
.Properties.Append .CreateProperty("DisplayControl", dbInteger, CInt(acComboBox))

... but this is just a start... there are other things wrong.
you are dimensioning variables that are not being used like rstCreate and fld -- anyway, if fld was something you were using, you should Dim it as DAO.field

you should also release any object variable you assign
set dbs = nothing

there is no error handler
you cannot assume that the table exists to delete

...and the list goes on. Although it is not necessary, you should also indent your code.

You will find most here reluctant to tell you how to do something that is a bad idea... we want to help you, not give you duct-tape and bailing wire. Also, with a UA name like 'MrMSAccess', we also probably expect a bit more...
strive4peace
Hi Brent!
.. don't know how I missed your post...the "informer" didn't jump in when I clicked 'submit' -- had this open for awhile...
MrMSAccess
Thanks guys, I really appreciate it. I'm going to try it now.
yberCow,
The reason that I would have to delete the form is because this is a distributable app I'm making. Because of it being this way, I'm kind've limited on the methods that I can use.
HAs you know certain methods won't work in earlier versions of Access, also, since it is going to be distributable, I'm trying to develop it with a minimal amount of objects, just code. That's why I've been so persistent about saying that even though lookup definitions are definitely not the preferred route to take, it really is pretty much the only thing that is going to work with what I'm doing. The lookup fields are used only one time during initial setup and that is it.
Anyway, I appreciate you, Danny, Brent, and Crystal assisting me in this. Once I get it completed, I'll see about posting it. Thanks again, guys.
datAdrenaline
Due to my propensity to not use temp tables ... and everyones dislike for lookups in table definitions, I have posted a table-less solution on the other thread ...
Table Less and Recordset-LESS Solution
CyberCow
Fair enough. Now that you've explained the scenario, it makes better sense. The use of a lookup field in an entirely temporary table seems harmless enough.

Now I'm gonna see what Brent is on about. (He usually has some goodies) And hey Brent! What's wrong with temp tables anyway? I'm interested in hearing your opinion as to why.
datAdrenaline
>> What's wrong with temp tables anyway? <<
othing really ... its a technique that can have its purpose, and MrMSAccess's my well be one of them! ... I do think the technique can easily get OVER used (ie: Make Table Queries when folks can just use a SELECT Query) some points I don't like about them are:
- Creates db bloat
- I don't really like to mess with object creation/deletion in the FE
- Personal preference .. smirk.gif
All the "bad" points can all be accomodated for .... especially if you create a TempDB to hold the Temp Table ... then link to the temp table .... so ... ulitmately, its like a lot of things, if used in the right context and with the right philosophies, they can be a powerful tool .... kindof like using Lookup definitions at the table level ... I use them during development, but then remove the table definition lookups at release ... so ... ultimately, a temp tables are fine (I just don't prefer them), we as developers just have to know the "side effects" of using them ... and deal with it accordingly.... Not unlike many other techniques we may employ to create an app! dazed.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.