Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 15th, 2012, 10:12 PM
Registered User
 
Join Date: Jan 2012
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default lookup and edit records

I have a DB to track inventory for a seasonal business. My DB has one main table. When I get my inventroy for the season I would like to use a form that allows me to enter the info on price, quantity, etc for the season. I would like to be able to enter the item# (primary key), have the name come up (verifies that I have the correct record), then enter price, quantity. Any ideas?
 
Old January 16th, 2012, 12:59 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

I would expect you two have at least two tables.

1) Inventory
2) Inventory Stock levels


You can create a form for entering stock levels. You could use a combo box to select the item then enter the others details.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old January 16th, 2012, 08:23 PM
Registered User
 
Join Date: Jan 2012
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I really need to be able to enter the item #, then when I enter the other data it goes into the correct fields for that record. I don't want to have to page down through a list to find the corect one. What I have been doing is entering the data into a new table then running an "update query" to put it in the main table.
 
Old January 16th, 2012, 09:01 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by Dale View Post
I really need to be able to enter the item #, then when I enter the other data it goes into the correct fields for that record. I don't want to have to page down through a list to find the corect one. What I have been doing is entering the data into a new table then running an "update query" to put it in the main table.
When using a combo box you can definitely just type in the data. You are not required to have to page down through a list to find the correct one.

You should not heed to run an update query after your data entry. You should be done. If you are updating your main table then you have a spreadsheet not a relational database.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Last edited by HiTechCoach; January 16th, 2012 at 09:10 PM..
 
Old January 17th, 2012, 08:37 AM
Registered User
 
Join Date: Jan 2012
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

I have tried the combo box, but it does not move me to the correct record, it only tries to change the current record when I type in the items #. What am i missing? What I want to do is type in the item#, and then based on that number, have the correct record pulled up so that I can enter the new data in that record.
 
Old January 18th, 2012, 02:19 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by Dale View Post
I have tried the combo box, but it does not move me to the correct record, it only tries to change the current record when I type in the items #. What am i missing? What I want to do is type in the item#, and then based on that number, have the correct record pulled up so that I can enter the new data in that record.
I don't follow.

You should be adding a new record each time to the Inventory Stock levels
table fr then new new season. Why would you be editing an existing record to add he new stock level?
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old January 18th, 2012, 02:20 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

I would recommend reading this: Inventory Control: Quantity on Hand
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015
 
Old January 18th, 2012, 11:17 PM
Registered User
 
Join Date: Jan 2012
Posts: 6
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Ok, here is what I have. One table, primary keys is the field item #, other fields include name, descr, and even a picture of the item. In the same record, there are 3 more fields for each year, Price, quantity, and leftover. When I get my stock for the season, I want to have a form that when I type in the item number, the name currently associated with that number comes up, and then allows me to enter the data for the price and quantity into the field for this season. I do not want to make a new record, only update some fields for current records.
 
Old January 20th, 2012, 06:42 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

Quote:
Originally Posted by Dale View Post
Ok, here is what I have. One table, primary keys is the field item #, other fields include name, descr, and even a picture of the item. In the same record, there are 3 more fields for each year, Price, quantity, and leftover. When I get my stock for the season, I want to have a form that when I type in the item number, the name currently associated with that number comes up, and then allows me to enter the data for the price and quantity into the field for this season. I do not want to make a new record, only update some fields for current records.
What you are describing is a spreadsheet design not a properly designed (normalized) relational database.

I do believe the reason you are finding your method/approach difficult is due to the the fact that you are not following the rules of data normalization. That means your are trying to use a single table to do what really requires multiple tales.

While it is possible to do figure out what you as asking, it should be avoided. If I told you how It would be like telling you it is OK to use a hammer to drive in a screw when I know a screw drive is the proper way.

I am willing to help you how do it is the correct way if you are willing to learn.
__________________
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015





Similar Threads
Thread Thread Starter Forum Replies Last Post
edit records in a datagrid Hughesie78 Pro Visual Basic 2005 0 June 10th, 2009 05:43 AM
Edit records problems Indo77 Classic ASP Databases 0 August 11th, 2005 09:35 AM
Edit or Delete records tsimsha Classic ASP Basics 6 October 30th, 2004 03:26 AM
how to know who edit or add records? drachx General .NET 1 October 13th, 2004 05:36 AM
how to know who edit add records? drachx SQL Server 2000 9 October 13th, 2004 04:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.