Full Version: another dlookup question
UtterAccess Forums > Microsoft® Access > Access Forms
I have a form frmlocations bound to a table tblLocations. In the table the zip code field is a lookup into the tblZipsLA table which has zipcode, city and region. On the form, I want to autopopulate the city after the zip code is filled in and am using a dlookup to do that but it doesn't work and I don't know why. I have the lookup on the "on enter" event of the city control.
DLookUp("[City]","tblZipsLA","[ZipCode]= " & [locZip])
Can someone tell me what the problem is? I feel I am very close to getting it.
The expression should be the ControlSource of the City Control. You do NOT want to to store the info in your table since its already in the tblZipsLA. But a better way would be to use a Combobox that selects the Zips and then uses the Column property to display the other info
ook at the Northwind sample for how this works.
Never mind - I answered my own question after I double checked another post
I used code instead of expression and I did
locCity=DLookUp("[City]","tblZipsLA","[ZipCode]= " & [locZip]) in the after upate of the locZip control and it was perfect ---
That still doesn't address the issue of redundancy. Its fine as long as locCity is UNBOUND. But if locCity is a bouind control you have a denormalized database.
I gotta tell ya as much as I appreciate the responses it get, sometimes ya just gotta do what ya just gotta do and sometimes it is just not feasable to do what you are suggesting for lots of reasons.
When you ask questions on a forum like this - they are usually the sentence in the middle of one of 100 paragraphs in a book of 100 chapters so you really do not know the whole story or the whys and wherefores.
In my case, the City needs to be stored in each of the tables where there is one. Period. To link all 20 of these different tables to the one zip table for every query, every form and every report would be more work and confusion than the cost of the small redunancy. My database is not denormalized.
Back in 1982, I learned to write structured cobal and structured fortran programs in college. When I first when to work as a PL/1 programmer in my first job, I wrote structured programs. And they were good. I never had any "go to's" One day my boss came to me and said "I know you learned how to write good structured programs but in this company, we measure productivity by lines of code per hour - WRITE A GO TO and stop all this coding to get around it!!!" I never forgot that - as I moved into database development - the same logic applies - sometimes ya just gotta break one of the rules in the name of efficiency and productivity- and if you would like - I can quote several published references that agree with that.
I do appreciate you taking the time to respond to me however.
You will not find a lot of agreement with that attitude here. While I don't go as far in normalizing as some of my colleagues here, I do believe that a properly normalized database makes things a lot easier on the developer in terms of data entry and reporting.
The fact that you have 20 different tables that have address information is all the MORE reason to use a single keyfield to join to the ZIP code table. If these 20 tables are very similar in strucutre, then that indicates another design flaw. That you would say that linking to the Zip table would be MORE work, also shows a lack of experience with dealing with relational database structures.
You are, of course, free to design your database any way you want to. But when (not IF), you encounter problems cause by improper normalization you will find it harder to get help here. Generally we promote best practices.
sigh! I can not believe I am actually going to argue this point with a clown but since you attacked my experience level I will - no one said that normalizing was not a good thing -I follow that practice 99.9% of the time. The only point that I was making is that sometimes you really don't know what the whole situation is so it is best not to offer more than just a straight answer to a simple question nor to make a blanket statement about someone's experience.
exaggerated about the 20 tables with city in them. I have three - a trainer, a student and a location and each of them has an address that is specific to them and them alone. Because this is a database that will be doing a lot of mailouts based on those and because the city and zip code are not going to change (very often) once entered, it makes no sense to link each one of those to a zip code table that has to be called every time a label or a letter or a document with their address on it has to be created. That then means that every "report" would have to be based on a query rather than a table. The reason I have the link to the zip code table in the first place is that when the data entry person is entering the student that is on the phone or standing in front of her, she does not have to type the city in which is what she would have to do if there were no lookup table provided for her increasing the chance of mistyping. She types in the zip code and then the city and state and region are prepopulated.
And for the record, when most people ask a question here - that is what they are doing - they are not usually looking for a lesson in design. There is more than one way to design a normalized database. You shouldn't be so quick to attack someone else's decision as "bad" or insituate that everything they might do is flawed.
You have been a member here for a significant length of time so I'm a bit surprised you haven't picked up on one of the main principles of UA. That is that we promote "Best Practices" in database design. Ask any of the long time VIPs and they will tell you the same thing.
Yes that is true, but proper design is so integral into a well run database, that its often impossible to separate the two. If you just want answers, regardless of the impact on design, then its possible that UA is not the best place for you to seek help.
Oagree that I may not know the whole situation. But there are some things that are cut and dried. Your reasoning that it makes no sense to link the tables to a zipcode table doesn't hold water. What makes no sense is to have the city name repeated. Using a zip code table to eliminate having the operator type in City/State info is the good design. Displaying the info upon selection is also good. But storing the info in multiple places is NOT good design. It appears just laziness in not wanting to add another table to a query.
In fact I see a potentially redundant table here. It might be better to have a single people table with a flag indicating whether they are a trainer or student.
I was not attacking your level of experience. I was giving you the benefit of mine. I've been designing databases for over 20 years. I've seen the value of normalization. There aren't that many different ways to normalize. And having the same data in multiple places is clearly a violation of normalization.
In closing, I suspect the reason you are arguing with a clown (or anyone for that matter) over this issue, is that you know I am right, but don't want to admit it to yourself. So rather than admit that you have a flawed design you attack back.
Hey Cyndee,
You didn't ask for my 2 cents worth, but I'm going to throw it in any way. I think you may have jumped to conclusions. I can not see where Scott called your years of experience into question. Scott is right from the stand point that anyone at UA, that answers questions here, tries to be helpful in all ways. "Just answering the question," is not being helpful in all ways, in a lot of cases. There are way too many people here, that ask questions, that do not have any where near the experience level of yourself or Scott and when they ask questions, just to get something to work, in a lot of cases, is not the BEST Scott or anyone else could do for them. I, for one, am very glad that this attitude is at this forum and have benefited way too many times because of it. You have 20 years experience and by your last post, some what admit that doing the way you are, is against normalization rules, so why wouldn't you expect Scott, trying to be helpful, to step in and tap you on the shoulder and say this is not the best way to do this? You yourself know that it's not.
I'm not trying to dog pile anyone and Scott certainly does not need me to help him defend his points. I just wanted to jump in because I, for one, appreciate Scott's and everyone else's attitude here, of not "just answering the question."
Ya know - you are right - this is no place for me because I live in the real world and I simply do not have time to deal with pompous clowns who want to do nothing more than make sure the whole world knows they are right and have no room to entertain any other opinion except their own -- because apparently being right is all that matters to you - all I did was ask a simple question and for that I am have been attacked - and yes you did attack my level of experience - which is over 30 years (you have insituated that I am lazy, do not know what I am doing, and am a poor designer and I take extreme exception to that) - I maintain and will always maintain that there are some instances the rules simply do not apply - and furthermore - there are many ways to do something but rather than simply saying - "oh well yea that may be true in this instance " you continue to hammer me that I am wrong and I am flawed - because what you apparently want is for everyone to agree with you and know that you are the almightly clown that has all the answers - I should have asked first because I obviously could not possibly know anything since I have not had the benefit of sitting under your guidance for my 30 years. If you will provide your contact info, I will be sure to send any database business I have your way because from the sounds of your tone, you are the only one that can do a good job.
leave you, sir, and this forum with the thought that perhaps you ought to do a little more reading and a little less talking - as I said, I could provide you with many published experts who agree that good design is relative.
Perhaps you should try being a little more civil and appreciate that YOU asked a question and Scott provided you with the best advice.
If you were not after the best advice then you are on the wrong web site
R. Hicks
For what it's worth ...

I am in 100% agreement with the advice that Scott has provided throughout this thread.
You may not want to put the effort into fixing the problem at hand but the problem will still exist and the structure will remain unnormalized due to the redundancy of the data stored in the fields of the tables ...

The only thing I attacked was the apparent design of this application. And I attacked it in trying to help you. For my efforts to help you attacked me. The rest of my responses were a defense of my position and the UA community. Yes I did insuate that you were lazy, but only after you attacked me and this site and after you indicated that you felt basing a report on a query was too much trouble.
actually agree with you that there are instances where it makes sense to be a bit lax in applying normalization. But I disagree that this is one of them based on what you have said.
Maybe you need to consider that you asked a question, not only did you get an answer to that question, but you also got advice on the best way to design your app. Your response was not to appreciate the effort I made, but to attack me for making it. And you continue to attack getting more abusive and strident, while I simply try to answer your points. One more issue here is that many people learn just by reading what other people post. That is one of the reasons we (and I do mean we co9vering the UA community) have principles aBOUT the way we help. When we answer questions it not only for the specific asker, but for others who might also be reading this thread.
What's ironic here is that in attacking me for allegededly thinking I am the only one who is right you are stridently insisting that I am wrong and you are in the right. Well, clearly, from the reaction of others (Thanks Shane, Danny, & Ricky) I am not the only one thinking the way I do. Whihc just goes to further show how wrong you are in this instance.
I am saddened that you seem to be judging the advice you have been given on the avatar Scott has chosen to represent himself here. Would you prefer we judge you or your posts based on the avatar YOU have selected? Scott has given you good advice and responded far more civily in this discourse than you have.

Ofind your response somewhat odd for an "educator", as Scott has simply been trying to educate you on the best way to handle your design. Perhaps you feel you already know it all, as many so-called educators are prone to think.

I leave you with the following quote from your auto signature here at UA:
"Aim for the sun - you may not reach it but you will fly higher than if you never aimed at all!"

I would argue you do not seem to be "aiming for the sun" in the design of your application, or in your discourse.
I really wasn't going to respond because honestly this has gotten way out of hand - I think Scott is attacking me and he thinks I am attacking him - I am sorry for that - so let's stop already - we all know that we can never make our intentions or emotions really very clear in the written word - I apologize if anyone was offended by what I have said.
I have received a lot of good advice over the years from UA and have appreciated it very much - and I appreciated it this time but here is why I got uptight about the advice "this" time.
Oasked a question and before I could get an answer I realized myself what I needed to do. I really think that once I said it needed to be done this way - that should have been the end of it in my opinion. I did not think it was necessary to explain all the reasons but I will - first of all, this is not something that I chose to do - I was asked by someone else to do this, I was asked to do it a specific way, I was not given a lot of time to do it and was not given a lot of information about what was going to be done with it. It was not an "application" that was going to be used in an interactive environment. I was not being asked to maintain it. They wanted something quick and easy to use. The tables were going to be used to enter this information and at the end of x time frame, they were going to be exported to some other place to be done with something else as standalone (what I don't know because I was not told), the tables were going to be cleared and they were going to start all over. So to my way of thinking, in this environment, doing what Scott suggested was not the best solution. It would take too much time for what the end result needed to be. And I guess I objected to being called lazy for that decision. Do I think he is right about his overall design advice - you betcha - but not in this case - and that was the end of my argument - I just didn't think I needed to explain all that because for future posterity - how many people are really going to find themselves in a situation like that? -
So again - I apologize - for having offended you or anyone else who thinks I was not as nice as I could have been.
good post and kudos for the apology. I'm going to throw in my 2c for what it's worth.
The main reason for Scott posting his second reply to you would have been because other members would also read your thread. If no reponse was made to your solution, some may assume that this was a normally acceptable way to deal with the issue at hand. Scott would have responded as much to advise the other members that under anything other than extreme circumstances, this method would be poor practice. If you had wanted the discussion ended, you should simply have not responded to Scott's post.
Kind regards
Jerry Dennison
Very good explanation Cyndee. This helps clear a lot of the air, as I was wondering as well why you seemed so adamant about maintaining the current structure.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.