Need Help With Table Error

[Neoistheone]

Hello, I'm a newb to this forum but I'm kind of desperate. So any help someone can offer would be greatly appreciated. Thanks in advance for your time in reading this.

Please be advised, I am not a super access programmer, but I got nudged into this.

I was asked to create a Access 2002 db for one of our Admin VP's that will allow her to track office supplies from our location to 5 other locations. This db would be able to accurately compile quantities, costs, location of these supplies. I created a table that would hold all the information I wanted to know. Pretty basic stuff:

Store
Item Heading
Item Detail
Quantity cost
Comments

I wanted the user to be able to input the supplies into their respective store and fill in the above. I have a lookup wizard for the store field which has all our other stores in there. I tried to do the same thing with the Item Detail field. I had about 8 pages of items that I inputed into the lookup wizard when I finished I saved my work and the lookup had everything I'd just typed in there, so I thought I was home free. Until when i closed the table and opened it again I received an error that says the following:

"The setting for this property is too long. You can enter up to either 255 or 2,048 characters for this property."

I click ok to move past the error box.

Then all of the items I'd inputted disappear from the lookup field for Item Detail I just spent 15 minutes typing in. It's done this on all 4 of my attempts.

I'm taking this to mean that I've inputed way too many items and Access just can't handle it? I'm trying to verify if that's the case (hopefully one of you here on the forum can help me out with that)

The lookup wizard for Item Detail had information in there about the types of supplies, eg "Post Its 3 x 3" or "Copy Paper 8 1/2 x 11" and so on so the user would only have to type the beginning of it in and it and then just click off on it.

I did it that way because when they want to run a report to see how man "Post Its," are in company "a" there would be almost no way they could oops it.

Any suggestions how I can get past this, or if there is a way to keep all of those look up items in there without the error.


Or, if there might be an easier way to do this.

Thanks in advance for your advice.

Regards,

- Neo

 

[boblarson]

First, DO NOT USE LOOKUPS AT TABLE LEVEL!!!! They cause more problems than they are worth. You should NOT be entering, or editing data directly in the tables anyway, and forms can have lookups which then enter the appropriate ID number in for the text. Then you bring the ID and text back together with your queries.

Here's an article about the "evils" of lookup tables - http://www.mvps.org/access/lookupfields.htm

 

[David Eagar]

To Boblarson - I have seen you make the statement many times that lookups should not be done at table level - what is the correct way to do this? All I ever was taught, was create your tables & lookups at the same time

 

[boblarson]

To Boblarson - I have seen you make the statement many times that lookups should not be done at table level - what is the correct way to do this? All I ever was taught, was create your tables & lookups at the same time

1. Create your tables, and your lookup tables

2. Let the wizard create your form for you.

3. Select the text box it creates that you want to be a combo to select.

4. Go to Format > Change To > Combo Box

5. Go to the Row Source property of the combo box and create the query to pull the ID and text.

6. Set the Number of Columns to 2 (more if necessary, but for ID and text, 2 should be sufficient).

7. Set the column widths to 0";1" (or big enough on the second so the text shows and the 0" makes sure that the ID, even though that is what will be stored, will not show as the user doesn't need to see it, but they will want to see the text (just like the lookup in the table, but it will not cause you grief with things).

8. Make sure the bound column is set to 1 (non-zero based), if that is the ID column.

And that's pretty much it. Yes, there's a little extra work involved but it is well worth it in the long run.

 

[David Eagar]

mmmm well, the world's greatest database will have to move to version 9! I can follow what you are saying and will take your advice, but why does doing this at table level cause problems?

 

[neileg]

The basic cause of the problem is that Access performs the lookups on the fly and never shows what is really in the table. Sometimes an action depends on having the true data available and you don't get that, you get the result of the lookup. Table level lookups make simple things slightly simpler and complex things much more difficult if not impossible. To me that's a loose/loose combination.

If you do a search in these forums for inventory or stock control you'll find lots of posts on these subjects. Be warned that inventory is a non-trivial application.

 

[David Eagar]

To Neoistheone - sorry if I appear to be hijacking your post, but this is a goldmine for me

To neileg, as it happens, this so called world's greatest database is a Business Manager (purely as a learning exercise) that will involve inventory control

I suspect long before it is in a fit state to 'publish' here, I will be up to version 1,936 - I have a long way to go and learn yet

 

[Neoistheone]

Thanks

Bob - Thanks for so much for the info, when my head stops spinning I'll try your suggestions and steps. I'll come back to let you know how it worked out.

Dave - No troubles. That's why people have forums so we can help each other out. :>

 

[Neoistheone]

neileg

Neileg - Thank you as well. I can do all the programming in the world but all it takes is one person to miscount something and input it and it'll all down the drain...

Cheers

 

[David Eagar]

The basic cause of the problem is that Access performs the lookups on the fly and never shows what is really in the table. Sometimes an action depends on having the true data available and you don't get that, you get the result of the lookup. Table level lookups make simple things slightly simpler and complex things much more difficult if not impossible. To me that's a loose/loose combination.

If you do a search in these forums for inventory or stock control you'll find lots of posts on these subjects. Be warned that inventory is a non-trivial application.

Been sidetracked a bit with a minor brain explosion after a couple of grouling weeks at work (how do you spell grouling, coz that ain't it!)

Not quite sure I follow this - it appears the message is, don't do lookups at table level as they may not be accurate (?) If the tables aren't accurate, what's the point of the database? I know I need to take your advice, but just need a clearer description of why this is the preferred technique

 

[roberttran52002]

Entry validation & routing

..........

 

[neileg]

Been sidetracked a bit with a minor brain explosion after a couple of grouling weeks at work (how do you spell grouling, coz that ain't it!)

Not quite sure I follow this - it appears the message is, don't do lookups at table level as they may not be accurate (?) If the tables aren't accurate, what's the point of the database? I know I need to take your advice, but just need a clearer description of why this is the preferred technique

It's not that they are inaccurate, it's that they make proccesses more complex and sometimes prevent them working at all.

Here's a good explanation http://www.mvps.org/access/lookupfields.htm

It's 'gruelling' by the way.

 

[David Eagar]

Thanks a bunch - why is it that the longer you look at it, the worse it looks, but the answer doesn't spring up? I knew the spelling was wrong, but no matter how long I thought, the right answer did not arrive

I guess it is why forums like this exist