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?

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

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.

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

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

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?

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

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?

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

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