Solved

Access 2010 form won't behave properly with my tables

Posted on 2011-03-09
20
608 Views
Last Modified: 2012-06-21
I have created a database in Access 2007 and then upgraded to 2010 to try to correct this problem.  This particular form will not correctly display my data when I tell it to.  I have included an image that shows the incorrect and correct displays.  The Produce field does what I want which is gives me a drop down list that I can edit if I need to so if a new item comes up I don't have to close this form and open the table to add the new data.  Works great!  The Produce Description field won't work this way.  It gives me this double arrow field that goes up and down and doesn't give me the entire list to pick from.  It only displays one option at a time.  The other fields won't properly allow editing.  I have set them up in the same way so I can edit the tables if I need to but the form won't display them this way.  I have tried creating a brand new form from scratch and it doesn't change the outcome.  The control page is also displayed below to show how I have set up those controls.  

<img src="http://home-spun.com/stor/database.jpg">
<img src="http://home-spun.com/stor/controls.jpg">

Please let me know if you need anything further to help.  I have been trying to solve this for DAYS!  Please help!  
0
Comment
Question by:loopykd
  • 8
  • 8
  • 2
  • +1
20 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 35088102
On the form what type of control is it? The Produce field is a combo box. The other one looks more like a listbox. Try changing it to a combobox and see if that gives you the desired result.
0
 
LVL 21
ID: 35088342
I am not sure I follow what you are tying to do.

Would it be possible to post a sample database with the form and  tables etc to make it function. Be sure to remove any confidential data. Be sure to include some sample/test data.
0
 

Author Comment

by:loopykd
ID: 35088423
Thank you both!  Shaydie, if I do that (which I have done) it makes no difference.  I will post the database (as it is experimental and there is no confidential data) for you all to look at if you like.  Please send me your e-mail address and I will share it with you through Dropbox.com.  If you prefer another method, please let me know.  

0
 
LVL 84
ID: 35088532
Your [Produce Description] field is a Multivalued Field. Is there a reason you have is setup as such? I'd assume that you would only have one Description associated with a single record, but I'm not familiar with your data.

Additional comment:

I'd strongly advise you to avoid defining lookup fields directly in your tables. There is no good reason to do so, and it can mask quite a few issues.

I'd also advise you to avoid the use of spaces in your table and field names. This can become troublesome as you get more advanced objects and such. Instead of [Product Name], you should use names like ProduceName, for example.

It appears that your [Produce Data] tables does not have a Primary Key defined. All tables should have a PK defined. While this isn't causing your issue, it is something you should address before you get much further along in your database.
0
 
LVL 21
ID: 35089890
Please attach the database to a reply to this thread.

I think you form issue are based on the design of your tables.

I also see you have lots of repeating tables. All items really probably need to be in a single table.  I view this as using the table names a category data. It really should be stored in a field not the object name. If you were to add another product line using your method would require adding at least three new table, new queries and new forms, etc.. When all you should have to do is create a new record for the type and your done.  


3-9-2011-7-30-18-PM.jpg
0
 

Author Comment

by:loopykd
ID: 35090106
The reason I am using the different tables is the different items use very different descriptions and other data.  For example, meats, use different cuts and different grades.  Without seeing the database, it's hard to understand.   Thank you and I hope I have designed properly.  I think I have.  
0
 
LVL 21
ID: 35094656
loopykd,

The reason I am using the different tables is the different items use very different descriptions and other data.

I have heard that a lot in the n 30+ years specializing in inventory control systems, I have never had a inventory that I could not put into a single master table.  I see no reason why you can't you a single master table if properly normalized.

I would expect to see a separate spreadsheet of each type if done in Excel. I would not expect to see this in a well design relational database.

I Why do you need a separate table just for description? Are you doing that so the memo field is in a separate table?  Do you need multiple descriptions per item?  Like a long description and short description?
0
 
LVL 84
ID: 35095284
Ditto what TheHiTechCoach said: I have not yet seen an Inventory App that needed multiple tables to store products.
0
 

Author Comment

by:loopykd
ID: 35097103
Ok.  You guys know way more than me so I will see what I can do.  I will consolidate the tables and try to make it work that way.  I will bow to your greater knowledge, wisdom, and experience.  Thanks for your help and in a couple days, I will come back and tell you all if that worked for me.  Thanks!  
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:loopykd
ID: 35119855
I have consolidated all tables and even recreated the field with the problem.  I have added Primary Keys to all tables as you all have suggested.  All suggestions and recommendations have been followed and it still doesn't work.  I have attached a copy of the file for any help anyone can give me.  I would like to enter the data using the form I have created.  The form doesn't work however, in table view, it works just fine.   Grocery.accdb
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 250 total points
ID: 35120417
Here are some of the suggestions and recommendations that you did not follow:

1) All tables should have a PK defined..  tables still without a primary key.
2) relationships should be with the primary key fields in your look up tables.
3) Do not use table level look ups. See: The Ten Commandments of Access
4) avoid the using of spaces in your table and field names.  Also some of your field names are reserved words which can cause issues.

See if the attached helps:




 Grocery-HiTechCoach.accdb
0
 

Author Comment

by:loopykd
ID: 35121855
Well this is more than frustrating because maybe this works and maybe it is a wonderfully efficient database but I completely don't understand it now.  It's completely useless to me.  
0
 

Author Comment

by:loopykd
ID: 35121981
Ok, let me be a little clearer.  Your advice is appreciated and I am sure very accurate.  I will score all answers as such but I am confused and frustrated.  I wanted to design this database as the very very beginnings of a much larger project.  I won't get into that now but as I don't understand how to enter data now.  I don't understand how the tables relate to each other or how to read it.  This is all important stuff as you know.  I was enjoying working with this file and trying to understand it all and I was.  Now I don't.  I am sure everything is efficient and has proper syntax and everything but I feel like a second grade math student.  Now I have no idea what to do next or how to proceed.  
0
 
LVL 21
ID: 35122603
Most people feel confused and frustrated when learning to properly normalized a database. It is not easy. I really think it is an art that you only master from experience.  

I wanted to design this database as the very very beginnings of a much larger project.
This is true of most databases. They never seam to be completed. done. They are constantly growing and evolving as your needs change. That is why getting a good table design give the database a good foundation to grow upon.

TIP: I find that it helps to use very descriptive field names and also use the Description column to explain the field even more if needed.

Some of the fields in your Grocery data did not make sense. For what is the the Start and End dates? If the the start and end of ta sale's promoti0on for the sale price? Is it teh start and end data of the manufacturing of the product? Is is the start and end data for when the store carrys the product?

The basic relationships between all the tables has not changes. Take a look look at the relationship window. What has changes is what fields are used to create the relationships between the tables.

About what you called a description table:
By using a separate table to store each element of the description in a record  is the same concept as the multi-value field you were attempting to use.. I find the multi-value field to have lots of limitations and comparability issues. So I have avoided it.

I feel like a second grade math student.
Don't feel bad. You are not alone in your feelings. When I started out I felt the same way.  
 
This is a type of inventory control/management database. In the database world inventory control is one of the most difficult tasks to design and program. You really are trying to tackle one of the more difficult concepts in database design: Inventory.
 
I took some time and created the form GroceryItemsEntry to show how to use all the look up tables with a combo box.

Now I have no idea what to do next or how to proceed.  
What questions do you have?
0
 

Author Comment

by:loopykd
ID: 35122765
I don't even know how to ask any questions at this point.  I think the best way to go about this, is for me to get to what you did myself.  This way I can understand what it means and how to get there.  This is how I understood relationships and why they were important.  Also, do you recommend that I use Access 2010 or Access 2007?  I currently own 2007 but downloaded 2010 trial to fix this problem and I don't know if I should purchase it or not.  I would not mind telling you my purpose but not here publicly. Is there a way I can explain it more privately?  Is your business consulting in this capacity?  Do you teach?    
0
 
LVL 21
ID: 35122910
AFAIK, Access 2010 will not handle your needs any better than 2007or any previous version of Access.

Is your business consulting in this capacity?  Do you teach?
Yes and Yes.   You can view my profile and click  the Hire Me button to contact me.
0
 
LVL 21
ID: 35132884
Are you wanting to track historical data on the pricing by using the Start and end dates?

If yes  then this data need to be moved to a transactio0n table wher there is a separate record for each price change.
0
 

Author Comment

by:loopykd
ID: 35134101
Yes, I think I would like to but I don't have a purpose for it at this time.  Possibly in the future I will find a use for it.  
0
 
LVL 21
ID: 35141546
I replied to your Hire Me inquiry via email. Did you get those? If not, check your spam/junk folder.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now