Access 2010 form won't behave properly with my tables

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.

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.

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.  

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.

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

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.  

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?

Ditto what TheHiTechCoach said: I have not yet seen an Inventory App that needed multiple tables to store products.

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!  

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

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.  

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.  

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?

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?    

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.

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.

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.  

I replied to your Hire Me inquiry via email. Did you get those? If not, check your spam/junk folder.