I have the following tables in my db
tb1 Suppliers
tbl2 Contracts
tbl3 Project Type

I am able to add contracts at the moment by typing the supp id field into a text box then the contractID is automatically genereted (autonumber)

I want to make this more user friendly I want to select a supp from my combo box supp1, supp2 the I want to select project type also from a combo box project1, project2. I thought this would work by creating a combo box and getting this to look up the values either in a table or a query however I tried this but access is assuming I want to add a supplier here when I am trying to add a contract and assign it to that supp so therefore it will not let me add the record in this way. I know it can be done but I don't know how. I don't want to keep it as a text box this is more prone to errors users cannot remember every supp id.

Can anyone help
Many thanks
Hayley

Hayley, Do you want to pull up the supplier details after selecting from the supplier combo box? then have option 'add new contract' Y or N - if yes then add new contract ID? or am I off the track completely
Col

I don't want the supp details to populate. I have a form where I add a contract so for example I want to add contract 1.

combo box supp 1,2,3

I would like to select a supp from my combo to say this contract contract1 that I am currently adding belongs to supp? chosen from my combo box. At the moment I have a text box where i enter the suppid 1,2,3 works perfectly, all I'm saying is it's impossible to remember these suppids I would rather just select from a drop down list with the supp name field, id field is hidden in my combo and for access to recognise the supp id and assign this contract to the supplier I chose. It seems that because I am adding this contract in add mode access thinks I am trying to add a supplier. Does this make sense?

If you have an alternative way which is user friendly I would also be interested??

Thanks
Hayley

Hayley, If you have the contract form open so that you can fill in the details and it's generated a new contract No., I can't at the moment see why a combo box looking up suppliers details doesn't work. In the contract table presumably you have fields for Supplier Name, ID etc. therefore if the combo box is looking at the supplier table it should add whatever details you have specified. It's never that simple though is it ! - Col

Just checking something else, Do I need text boxes on my Add a contract form for the supp ie name, add etc for this to work because at the moment I have a separate form to add my suppliers details.

Hayley, Presumably you've got two tables. One with the suppliers detail and the other with the contract details. If you go into the contract form there is a combo box saying "Supplier". If you select supplier 10 (say) Fred Bloggs Co. you can add the 10 to the contract table and have the name etc. come up as a caption in a label box. That way if "Fred Bloggs" becomes "Frederick Bloggs & Son" it doesn't matter because it's still ID 10. You only need the suppliers ID No. to be posted to the contract table. - Col

Yes that's understood and that's what I have but it doesn't work with a combo box only a text box.

I have the combo box looking up my supp table, the fields in my combo are suppid and suppname. Everything else on this form is stored in my contracts table which I have the form set up based on a qry, qrycontractdetails.


Thanks

Hayley, Silly question - why is your contract form based on a query and not linked directly to the contract table? I'm not helping much am I !!

Queries allow you to select from more than one table, in this case I have fields from another 2 tables(I know I never said that in my above post) but I have only created these tables recently - they used to be part of my contracts table until I changed the design a little(see my post in the gerneral forum if you want to know why the sudden change) I wouldn't have thought this would have caused a problem anyway because they were all part of the same table originally and really the query is based upon the table so it doesn't really make alot of difference...correct me if I am wrong