UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Problem Updating Table Properties    
 
   
MrMSAccess
post Feb 25 2008, 04:52 PM
Post#1



Posts: 136
Joined: 21-February 08



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
Go to the top of the page
 
accesshawaii
post Feb 26 2008, 10:05 AM
Post#2


UtterAccess VIP
Posts: 5,169
Joined: 17-June 04
From: From Hawaii - Now in Wisconsin...Am I Nuts?


Have you tried refreshing the database tables and refreshing the properties in the table?
Go to the top of the page
 
Bob_L
post Feb 26 2008, 10:09 AM
Post#3


Utterly Banned
Posts: 7,038
Joined: 5-December 02



Bad, bad, bad, bad, bad -----> Lookup fields in tables are really NOT the way to go:
http://www.mvps.org/access/lookupfields.htm
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 10:19 AM
Post#4



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
Bob_L
post Feb 26 2008, 10:48 AM
Post#5


Utterly Banned
Posts: 7,038
Joined: 5-December 02



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?
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 11:06 AM
Post#6



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
Bob_L
post Feb 26 2008, 11:25 AM
Post#7


Utterly Banned
Posts: 7,038
Joined: 5-December 02



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.
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 11:55 AM
Post#8



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
accesshawaii
post Feb 26 2008, 12:08 PM
Post#9


UtterAccess VIP
Posts: 5,169
Joined: 17-June 04
From: From Hawaii - Now in Wisconsin...Am I Nuts?


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
Go to the top of the page
 
NoahP
post Feb 26 2008, 01:16 PM
Post#10


Retired Moderator
Posts: 10,493
Joined: 12-January 01
From: Lexington/Louisville KY USA


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.
Go to the top of the page
 
dannyseager
post Feb 26 2008, 01:50 PM
Post#11


UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK


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.
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 02:53 PM
Post#12



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
accesshawaii
post Feb 26 2008, 03:39 PM
Post#13


UtterAccess VIP
Posts: 5,169
Joined: 17-June 04
From: From Hawaii - Now in Wisconsin...Am I Nuts?


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.
Go to the top of the page
 
dannyseager
post Feb 26 2008, 04:06 PM
Post#14


UtterAccess VIP
Posts: 13,031
Joined: 2-March 04
From: Leicester, UK


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...
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 04:21 PM
Post#15



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
CyberCow
post Feb 26 2008, 04:31 PM
Post#16


UdderAccess Admin + UA Ruler
Posts: 19,557
Joined: 27-April 02
From: Upper MI


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.
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 05:05 PM
Post#17



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
CyberCow
post Feb 26 2008, 05:13 PM
Post#18


UdderAccess Admin + UA Ruler
Posts: 19,557
Joined: 27-April 02
From: Upper MI


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)
Go to the top of the page
 
MrMSAccess
post Feb 26 2008, 05:41 PM
Post#19



Posts: 136
Joined: 21-February 08



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.
Go to the top of the page
 
CyberCow
post Feb 26 2008, 06:36 PM
Post#20


UdderAccess Admin + UA Ruler
Posts: 19,557
Joined: 27-April 02
From: Upper MI


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.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    20th July 2019 - 07:26 AM