Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 30th, 2007, 09:48 AM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default 1 to 1 relationships

I have 4 tables with 1 to 1 relationships whose primary key is called JobNumber. On the form I currently have you type in the jobnumber on the first table but when I try to save the record it tells me that it cant because another table requires a field.

I think this is because the primary key is the same.

Question how do I make it so that the JobNumber when entered into the form also inserts them into the other tables that have the same primary key?

Reply With Quote
  #2 (permalink)  
Old January 30th, 2007, 09:56 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yikes. How many fields do you have that you need to split them up like this, unless you have confidential information in the subsequent tables?

What is the structure of the tables, and the relationships and referential integrity? Do you want to have one table (the first one) with an autonumber, and the rest with a PK just regular number, and pass the autonumber to those tables?

mmcdonal
Reply With Quote
  #3 (permalink)  
Old January 30th, 2007, 10:17 AM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

Here is the structure for this part of it.

Table 1: Job Summary - purpose to summarize vehicle information and parts that are put on to it.

QuoteNumber-(many side relationship to another table)
JobNumber-Primary Key
CustomerPO
NameSecondPO
SecondPO
GeneralDescription
Date of Completion
Invoice
Payment
Scheduled Date

Table 2 - Vehicle 1-1 all boxes checked on

Jobnumber-Primary (removed the required selection)
Dealer
Agency
VIN
Tracking Method
TrackingNumber
Mileage
Exchange
Exchange2
VINof Exchange

Table #3- Vehicle Details 1-1 all boxes checked on

Jobnumber-Primary (removed the required selection)
Make
Model
Color
Year
Damage
Cab
Chassis
Bedlength
WheelBase
CA
Reardrive
Seats
Seats2
Fuel Capacity
2wdor4wd
VehicleNotes

Table #4- VehicleDetails 1-1 all boxes checked on
Jobnumber-Primary (removed the required selection)
ForPickup
ForDelivery
DatetobeDelivered
DateDelivered
Address
City
State
Postal Code
TagNumber
DeliveryNotes

To answer what exactly I want to do is on one form when I type in a job number it automatically places it in the other tables. I am not using Autonumber on this but eventually i want to automatically generate a Job number but i need to solve this problem first unless you think its going to be easier to automate the creation of JobNumbers first.



Reply With Quote
  #4 (permalink)  
Old January 30th, 2007, 12:04 PM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

Well it looks like i made it work by removing the required property! Am i Going to have problems because of this?

Reply With Quote
  #5 (permalink)  
Old January 30th, 2007, 01:21 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You shouldn't, but you may need to be a little more rigorous with your code and designs.

Is the number passed with code?

mmcdonal
Reply With Quote
  #6 (permalink)  
Old January 30th, 2007, 03:20 PM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

I plan on using the following when the new form is opened:

Docmd.OpenForm "ModifyPricingofaPart",AcNormal
forms!ModifyPricingofaPart!JobNumber = Forms!OldForm!JobNumber

Its most likely obvious to you but its a command button that going to be placed on the Bottom of the first form where "JobNumber" is entered. Currently I do not have anyother code in the pages.

Do you think that the code uptop will suffice?

Reply With Quote
  #7 (permalink)  
Old January 30th, 2007, 03:31 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is what you should have, ommitting the fields not neede for the demo:

A table for the job (lloks like table 1)
A table for the (looks like table 2 and 3)
A table for the delivery (looks like table 4)

This data does not seem normalized, or even denormalized for reporting. What are you doing with this database?

As far as passing parameters to the second form, is this to create a new record, or to select a record?

mmcdonal
Reply With Quote
  #8 (permalink)  
Old January 30th, 2007, 04:17 PM
Friend of Wrox
 
Join Date: Jan 2007
Location: , , .
Posts: 109
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via AIM to scandalous Send a message via MSN to scandalous
Default

I am going to use this database to generate quotes, work orders, Purchase Orders and eventually invoicing.

I am sorry but I am pretty new at this. What do you mean by Normalized?

The outlline I want to use for generating a report is by quote, then by Job which contains all the vehicle informaion, and parts order details.

I am trying to make it so that the user can just get the information into the Database. I have a command button with the code:

Private Sub cmdCreateaQuote_Click()
On Error GoTo Err_cmdCreateaQuote_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "CreateQuote"
    DoCmd.OpenForm "CreateQuote", acNormal, , , acFormAdd

Exit_cmdCreateaQuote_Click:
    Exit Sub

Err_cmdCreateaQuote_Click:
    MsgBox Err.Description
    Resume Exit_cmdCreateaQuote_Click

End Sub

This starts them with the Quote Form. Then at the bottom of every subsequent page I am going to place another Command Button that creates a new record and transfers the previously entered PK into the new Form. I plan on doing this throughout all the forms below.



1-initial quote creation (Tables: Quote, Customer, SalesPerson)

2-initial Job creation (Table: JobSummary)

3-Entering the known Vehicle information (tables:Vehicle, VehicleDetails,Pickup and delivery

4-Adding parts to a Job (Tables: Partsandservices, Order Details)

5-Modifying the prices of a job (Query:Partsandservices, Order Details)

The Only 1 to 1 Relationships I have are the 3 vehicle information tables to the "JobSummary" Table.

Is this not going to Work for generating reports?


Reply With Quote
  #9 (permalink)  
Old January 31st, 2007, 10:25 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I am not sure why you would have a 1 to 1 relationship anywhere for these functions.

Create a table for cars, then a table for jobs, and have it look up cars. Then create a parts table, and have it look up to a junction table for jobs (jobs_parts) which is a many to many relationship, incidentally.

mmcdonal
Reply With Quote
  #10 (permalink)  
Old January 31st, 2007, 10:49 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Make new query
First table take all fields and second table all except primarykey third table same as second and so on.
Make this query as a RowSource for your form. This technique updates all tables with same primary key

-vemaju
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
displaying relationships williamlove Access VBA 1 August 14th, 2008 10:20 AM
Many-to-Many Relationships in O/R Mapping sarosh Classic ASP Databases 0 December 8th, 2006 05:45 AM
relationships - why really? pettrer SQL Server ASP 2 August 31st, 2006 12:59 PM
Table relationships tanjuakio SQL Server 2000 3 July 28th, 2005 09:14 AM
contraints and relationships zouky ADO.NET 11 September 24th, 2004 01:30 PM



All times are GMT -4. The time now is 09:27 AM.


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