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 September 8th, 2004, 08:52 PM
Authorized User
 
Join Date: Sep 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Form question

I am struggling with a database and making things work inside it. As I've stated before I have limited experience with Access but have put several very simple databases together. The one I'm trying to put together right now is not simple, at least for me, and so I struggle. I got some good help a few days ago so I thought I'd try again. I'm not sure if I can completely explain what I'm trying to do but I'll make an attempt at it.
I have a database that tracks production and defects. What I have right now in a form that is working is;
A Date field;
A product field that when filled automatically populates the cost field.
I also have an employee field that when filled automatically populates the shift field.
I also have a few fields that track; qty needed; qty produced; qty defective; and qty remaining.
All of this works wonderfully thanks to some help from Vijay G.
Where I'm stuck right now is with combo boxes for entering defect descriptions and a command button to add a new record. What I want to do is fill the defect description boxes with the description and have another entry for the quantity of these defects. (I already have them set in a table). I've been trying to write some code that would change the value of all the defect qty fields to the amount entered and when I click on the "add new record" command button these fields would return to zero (saving the information in a table).
I hope this description is good enough to allow someone to have a go at helping me. If not let me know. Here's the error message I'm getting that says "The name for the menu, command, or subcommand you entered is invalid". I'm not sure what that means or what I should be doing to fix it. Here 's the code I've been working on. I've done my best to keep this short but hopefully detailed enough to get some feedback.
Thanks,
Don........


Private Sub CmdNewRecord_Click()
On Error GoTo Err_CmdNewRecord_Click

'Change the value of defect boxes equal to amount entered
'Then set the form back to zero

    Forms![Defect Table Form]![Defect 1] = Forms![Defect Table Form]![Defect 1]
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, acMenuVer70
Exit_CmdNewRecord_Click:
    Exit Sub

Err_CmdNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_CmdNewRecord_Click

End Sub

 
Old September 9th, 2004, 03:15 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 128
Thanks: 0
Thanked 1 Time in 1 Post
Default

How many tables were affected from these form/s do you have problem with?

Using Sub functions in Access is complex.
What I use that I have successfully been using for more than
6 years now is ADODB code (or Active Database Object code).
Create a code then file it in modules in Access.
How I do it is in every event on Access form field I link it
to this codes. These codes will insert/update field values to related
record in the tables.

For me to help you. You need to give the table structures in your
access db. Then solve one form at a time.
John

 
Old September 9th, 2004, 06:14 PM
Authorized User
 
Join Date: Sep 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

John,
I'm not sure what you mean in your last paragraph. I'd like to learn lots more on databases but I'm not sure where or how to start. I'm grateful for all the help I can get but on the other hand I'd like to understand what I'm doing so next time I can figure things out on my own........hopefully.
Anyway can you help me figure this out or can you steer me in the right directions?
Thanks again,
Don........

 
Old September 10th, 2004, 12:48 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 128
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,
You need to define the fields on all the tables.
Then what fields will it be related from one table to another
table/s. For example you have an expense logbook database.
Under this logbook database, you have three tables with fields/attributes/columns.

1st table - payee:
payee_id: 1001
payee: Dconsutant
address1: 123 Herald St
city_st_zip: Houston, TX 77099

2nd table - account:
account_id: 5678
account_name: BA
account_type: checking

3rd table - voucher:
voucher_id: 1001
payee_id: 1001
payee: Dconsutant
address1: 123 Herald St
city_st_zip: Houston, TX 77099
date: 09/09/2004
amount: 350.00
account_id: 5678
account_name: BA
account_type: checking

Then for example you have a form named check_payments
where the source fields come from voucher table:

What will happen is you will have combo/list box
field in this form where the values are coming from
another table. These will be:
payee_id from payee table --> after selecting it or checking it
will automatically populate the ff:
payee: Dconsutant
address1: 123 Herald St
city_st_zip: Houston, TX 77099

account_id from account table --> after selecting it or checking it
will automatically populate the ff:
account_name: BA
account_type: checking

Let me know if this example will work with your problem.
John
















 
Old September 10th, 2004, 07:05 PM
Authorized User
 
Join Date: Sep 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm not sure if it will work for my problem or not. I'm not sure how much space I can get on this forum but I'll try to detail my database.

Table 1: Departments Table 5: Supplier
Fields: Fields:
OperationID - Number SupplierID - Number
OperatonName - Text CompanyName - Text
Description - Memo ContactName - Text
Table 2: Employees: Address - Text
Fields: City - Text
EmployeeID - Text State - Text
Last Name - Text Postal Code - Text
First Name - Text Country - Text
Job Title - Text Phone - Text
Hire Date - D/T Fax - Text
QualificationDate - D/T Table 6: Defect Table
OperationName - Text Fields:
Shift - Text Date - D/T
Table 3: Products ProductName - Text (lookup to Product table)
Fields: EmployeeName - Text (Lookup to Employee table)
ProductID - Number OperationName- Text(lookup to Employee table)
ProductName - Text Shift - Text(lookup to Employee table)
Unit Price - Currency Quantity Produced - Number
Supplier ID - Number Quantity Defective - Number
Table 4: Shift Total Defects - Text
Fields: Unit Price-Currency Ulookup to products table
Shift - Text Quantity Needed - Number
                         Quantity Remaining - Number
                         ThruPut - Number
                         Defect 1 - Text
                         Defect 2 - Text
                         Defect 3 - Text (and two others 4&5)
Forms:
Defect table Form (source: Defect Table 6)
Fields:
Date
ProductName (Auto fills Unit Price)
Operation
Operator Name (Auto fills Shift)
Quantity Needed
Quantity Produced
Quantity Defective
Quantity Remaining Expresion(Qty Needed - Qty Produced +Qty Defective)
Defect 1,2,3,4,5
All of this works pretty good. Here's where I'm stuck.
I added the defect fields so I could track specific defects.
I want to select a defect in the defect fields, be able to add an amount to the defects. Have this amount added together and populate the Quantity Defective Field, and when I hit the add new record button it all goes back to zero. (example: Enter a defect -broken, add an amount - 2, hit add new record - back to zero)
Whew! I think that pretty much explains it.......I hope.
Thanks for all your interest and help. I'd really like to be able to do this on my own but so far I can't get it. I did try to write some code (what I posted earlier) but it doesn't seem to work.
Thanks again,
Don............

 
Old September 10th, 2004, 07:09 PM
Authorized User
 
Join Date: Sep 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

John,
Sorry for the lousey post earlier. The formatting didn't hold to what I thought it was.
I can try again if you'd like?
Don........

 
Old September 11th, 2004, 05:54 AM
Authorized User
 
Join Date: Sep 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I've thought about this and decided to repost.

I'm not sure if it will work for my problem or not. I'm not sure how much space I can get on this forum but I'll try to detail my database.

Table 1: Departments
Fields:
OperationID Number
OperatonName - Text
Description Memo

Table 2: Employees
Fields:
EmployeeID Text
Last Name Text
First Name Text
Job Title - Text
Hire Date - D/T
QualificationDate D/T
OperationName Text
Shift Text

Table 3: Products
Fields:
ProductID Number
ProductName Text
Unit Price Currency
Supplier ID Number

Table 4: Shift
Fields:
Shift Text

Table 5: Supplier
Fields:
SupplierID Number
CompanyName - Text
ContactName - Text
Address - Text
City - Text
State - Text
Postal Code - Text
Country - Text
Phone - Text
Fax - Text



Table 6: Defect Table
Fields:
Date - D/T
ProductName - Text (lookup to Product table)
EmployeeID - Text (Lookup to Employee table)
OperationName- Text(lookup to Employee table)
Shift - Text (lookup to Employee table)
Quantity Produced - Number
Quantity Defective – Number
Total Defects - Text
Unit Price-Currency (lookup to products table)
Quantity Needed - Number
Quantity Remaining - Number
ThruPut - Number
Defect 1 - Text
Defect 2 - Text
Defect 3 - Text (and two others 4&5)

Forms:
Defect table Form (source Defect Table 6)
Fields:
Date
ProductName (Auto fills Unit Price)
Operation
Operator Name (Auto fills Shift)
Quantity Needed
Quantity Produced
Quantity Defective
Quantity Remaining Expresion(Qty Needed - Qty Produced +Qty Defective)
Defect 1,2,3,4,5

    All of this works pretty good. Here's where I'm stuck.
I added the defect fields so I could track specific defects.
I want to select a defect in the defect fields, be able to add an amount to the defects. Have this amount added together and populate the Quantity Defective Field, and when I hit the add new record button it all goes back to zero. (example: Enter a defect -broken, add an amount - 2, hit add new record - back to zero)
Whew! I think that pretty much explains it.......I hope.
Thanks for all your interest and help. I'd really like to be able to do this on my own but so far I can't get it. I did try to write some code (what I posted earlier) but it doesn't seem to work).
Thanks again,
Don.........


 
Old September 14th, 2004, 12:36 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 128
Thanks: 0
Thanked 1 Time in 1 Post
Default

Don,
This is what I recommend to your database.
On your Defect Table, instead of creating 5 fields: defect 1,
defect 2, defect 3, etc.
just create two fields with names and data types as:
1) defect_type as Number.
2) defect_description as Text

consequently,
create another table named defect_type with two fields:
1) defect_type as Number (should be the primary key)
2) defect_description as Text

On the defect table you can create a lookup for defect_type from
defect_type table and automatically update the defect_description field as well.

Also, I do not understand the following fields in the Defect table:
1) Total Defects (and why is it text, shldn't it be a number)
2) ThruPut (what does this serve)

On your Form you then could have the defect_type fields
as an option box or combo box meaning you can choose only one value from option 1 to 5.

I am pretty sure, your table definition and form design will be easier to understand.

John





Similar Threads
Thread Thread Starter Forum Replies Last Post
Hello! And A Question on Form Opacity... robzyc C# 4 August 1st, 2007 11:05 AM
Form question Scott Rider General .NET 4 June 30th, 2005 08:04 AM
PHP Form Question IP076 PHP How-To 1 December 8th, 2004 05:12 AM
Login form question Jams30 PHP Databases 1 December 4th, 2003 12:10 PM
form / listbox question nkrukovsky Access VBA 2 November 4th, 2003 09:16 AM





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