Another issue I am having problems with

[RuralGuy]

OK so I should use the first name and last name fields from my customers table in the maint contract form? And no, it is not a form subform. I just want to be able to click on it from the customer profile/work order form/subform and have it open up separately and display the maint contract information (if there is any) or to be able to add a new record to start a maint contract for that customer

You'll need to find a way to supply the Customer ID when you add a new record.

[balderman]

ok but the maint contract form is not a form/subform....it is just a form by itself....

[RuralGuy]

So the ContractID and the CustomerID are the same value? That will work but it is problematic.

[balderman]

I am not even really using the contract ID form for anything I just put it there out of habit because I always feel like every table needs an ID field....perhaps I am wrong....I am trying something though, I added the customer ID field to the maint contract form and I am re-creating the form using the first name and last name fields from the customers table....and I changed the relationship linking the customer table to the maint contract table by customer ID....I am gonna see if that makes a difference and fixes the issue I am having

[RuralGuy]

On your Customer Form, how do you get just that customer's contracts to show up in the SubForm?

[RuralGuy]

You do know you can create a Query the jouns the Customer's table and the Contract table on the Customer ID and the Name Fields will be usable just as if they were in one table.

[balderman]

On your Customer Form, how do you get just that customer's contracts to show up in the SubForm?

On my customer's profile/ work orders form/sub form I created a button at the bottom with Form Operations- Open Form-Selected Maint Contract info form - then selected Open the form and find specific data to display, and then selected Last Name as the matching field to look up data.

[balderman]

You do know you can create a Query the jouns the Customer's table and the Contract table on the Customer ID and the Name Fields will be usable just as if they were in one table.

Right but then what would I do? Create a form from the query? Because I need the form to not only display the Customer's Maint Contract Info (if they have it, not all customer's have a maintenance contract) and to also add a new maintenance contract if they decide they want one....am I explaining this well enough to where it makes sense?

[RuralGuy]

I am not even really using the contract ID form for anything I just put it there out of habit because I always feel like every table needs an ID field....perhaps I am wrong...

It is always a good idea for every table to have a primary key (PK). It is even better if it is an AutoNumber. It creates a special problem when you try to have two tables with the same PK and it is not necessary.

[RuralGuy]

You create a form from a query just like it were a table, it just can do so much more work for you.

[RuralGuy]

On my customer's profile/ work orders form/sub form I created a button at the bottom with Form Operations- Open Form-Selected Maint Contract info form - then selected Open the form and find specific data to display, and then selected Last Name as the matching field to look up data.

I'm sorry but I really do not follow that very well.

[balderman]

I am about to leave work...I will take screenshots and show you what I mean tomorrow.

[RuralGuy]

okay...that works for me.

[ssanfu]

It was either go out and shovel snow or work on a dB..... guess what I chose???

This is the table structures I would use:

Any field with a suffix of "_PK" is an autonumber type field and is the PK.
Any field with a suffix of "_FK" is a Long Integer type field.



Just my $0.02.........................

[balderman]

I have the customer/work orders working perfectly and exactly the way I want them so I do not want to make any changes to those....the Service type, Property type, contract type, etc...those are drop down boxes to select from.....