Troubles troubles troubles

[arobbo]

hello this might be a really easy problem but its one thats got me stumped

problem 1

basically i'm trying to insert a new record using the primary key from another form (not that it is a foriegn key in the table that i am attempting to add data too) when i use a button to open a new form the only way i could carry forward the value i wanted was to have a text box with the value [forms]![bladebladebla]![primary key i want to use] but that won't then add that to the table when it autmatically saves the record so i used an insert query actioned by a button... of course then when you try to exit the form access tries to save it and can't ... it has been saved but gives loads of nasty error messages .... please help

problem 2

related to problem one, is a way i've tried to overcome problem one by simply having the user select the value from a drop down box etc etc bit of a wimp out but should work very well .... however when navigating to the form to add a new record i want a button that will "open form in add new record state" something which you can do from the switchboard but not from a button within a form .... any ideas

All the best

hope my problems aren't too trivial for a bit of assistance feel a bit silly asking for help over such a small problem but its driving me up the wall !!!!

cheers

Andy

 

[The_Doc_Man]

We need to cut through the haze here.

basically i'm trying to insert a new record using the primary key from another form

If the new record and its destination table use the same field (regardless of field names) as the primary key, and if the PK of the new record is already in another record in that table, you can't do this. (And if the records are not the same, you also have an underlying conceptual flaw, because in that case you have data differentiated by something that isn't a key. This violates normalization rules.) Using a form or a query - or directly opening the table and hand-entering the values - won't make a difference.

however when navigating to the form to add a new record i want a button that will "open form in add new record state"

You might try one of the options in the DoCmd.OpenForm method, which can provide parameters for input and for the mode in which the form is opened.

 

[neileg]

Just to follow on from DocMan's comments

(not that it is a foriegn key in the table that i am attempting to add data too)

What is it then? If you are saving this value as part of the record in the second table, then how is it not a foreign key?

In any case, these things are often better handled with a form/subform set up than doing it the hard way in code.

 

[arobbo]

thanks for advice

think i may have got a bit muddled with my keys .... basically my tables are such that i use a manufacturers_ID (Man_ID) to tie together three other tables which have information about rebates, discounts and settlements..... i add a new rebate using the open form in add mode select the manufacturer from a drop list of manufacters and then save the record ... that works fine but i would like two buttons on this form to say add a corresponding settlement and discount record for this manufacturer open up the settlement form with the manufacturer ID already inputted fill in the rest of the information and then save it.....

as i couldn't get that working well enough i decided to settle with a button that would open the settlement form in add mode select the manufacturer again from a drop down list and save etc ....

does that clarify my question ... was a bit trunkated before

 

[neileg]

Why don't you put all these fields on one form, or a form/subform setup? Is there a real need to pop up a new form?

Assuming Man_ID is the primary key in your main table, it becomes a foreign key when you hold this in a subsidiary table.