I have a supplier table and a contracts table in my db, one supp can have many contracts. I have set up the relationship as one to many but I am having a slight problem.

My key fields are set to autonumber, heres what I want to do:

I have one form where I add a supplier and a second form where I add a contract. Now I want to have a form that I can add the supp to the contract. I know the relationship automatically links the data but initially I need to tell it what supp has what contract. If I am going about this in a bad way can anyone tell me how I should be doing it?

I have been given an example all in the 1 form with tab controls as a way of doing this but my contracts table contains fields that have large amounts of data sometimes up to 2 pages long so that is whats puting me off that method.

I also notice in my form the autonumber could be bringing up say no 13 and yet when I go to my contracts tble there are only 8 listed so far. Why is this?

All suggestions welcome as Im fairly new to this

Thanks

[This message has been edited by Hayley Baxter (edited 12-20-2001).]

Add the supplier field to the contracts form. A contract MUST have a supplier. Use a combobox for the supplier field that shows the supplier name and stores the supplierID. The combobox wizard will give you options to do this. Use a query as the rowsource of the combo. Just select the SupplierId and the SupplierName and sort by SupplierName.

Don't worry about the gaps in your autonumber values. They will occur from time to time if records are started but not actually saved or if records are deleted. Access generates the autonum value as soon as a single character is typed in a form and if the record is never saved, that number is never actually used.

Ok I did what you suggested but it appears my supplier name is appearing in the combobox more than once(the amount of times for how many contracts they have) When a try to select a supplier from my combobox I get a run time error stating there is no current record?

Any other ideas?

Thanks again

Each supplier must appear only once in the supplier table. Use THAT table as the rowsource for the combo rather than using one where supplier is a foreign key.

I got it sorted but I had to use the foreign key as the supp id because I was getting an error msg the other way stating that it was not in the recordsource. I took the combobox and Im typing the supplier id no in manually. If you know how to solve the prev error msg I would prefer to use your method!

Forgive me for being stupid pat but I have just realised what you mean.

Cheers

No I havent got it it would seem that whatever way I attempt this with a combobox it fails.

I would like to be able to search the combobox select a supplier the id for the supp appears in the box on my form then I want to click add button to link the two together but its not happening!

I think there may be easier better methods of attempting what I am aiming for but as I am new Im really not sure whats best way to do it. Can anyone help me understand exactly what I need to do to overcome these error msgs or give me instructions on an alternative method?

Thanks alot

[This message has been edited by Hayley Baxter (edited 12-21-2001).]