Linking two forms together

[Lxmanager]

Hi, please could someone help... It’s probably a very simple answer but I'm missing the point somewhere.



Problem.... I have created a form (Form 1) to enter inspection data, which sends the data to a table. On this form I have a field which I have set up to automatically increment to the next number each time a new record is selected.

I have also created a second form (Form 2) to collect material details and pass this to another table. On form 1 I have a button which when clicked brings up form 2 as a pop up.

What I want is the two forms linked together. I need the incrementing number to appear on both forms and when I click on the new record button on form 1 it resets both forms blank and gives both the same incremented number.

Basically I need it set up as though they are the same form... Sorry if it sounds complicated.

Thanks for your help

[NTC]

Rather than the pop up approach; have you considered making form2 as a subform inset into form 1?

Generically this is probably the normal design approach (though 'normal' like beauty is in the eye of the beholder). When you make a subform the wizard will launch that is going to prompt for the cross referencing field (your autonumber field) - and it will then auto enter that value into the subform as you add records.

So although there is definitely a way with vb that you can force that autonumber up into your pop up; and also force the popup to go to a new record when form1 does - - but you are doing alot of manual coding to establish a technique that comes with the subform approach without any work.....so that is why I first suggest consideration of the subform approach.

[Lxmanager]

I see where yoou are comming from. The original form 1 was quite a large form, probably the size of an A4 piece of paper which ment a lot of scrolling around to enter data. I have trimmed it down to the size of a post card so it is all sean on one screen. The idea of the pop up window was to save more space, the material details are not filled in on every form, thats why I thought of the pop up form so the inputter just activates it when required. If you see my point.

Appart from the auto number there is no other common data on each form, if your suggesting putting it as a subform, i might as well just put the fields back on the main form 1???

I don't really understand the use of sub-forms sorry.

I have attached a word doc showing the forms.

Attachment 1924

[pbaldy]

Have you considered a tab control? It's what I would use if the data is in the same table, but it would let you keep the smaller footprint.

[Lxmanager]

Sorry to act completely stupid, which I am but how do you do that, at the moment the two forms have their own tables but linked together by the Report No field. I separated the Material off the main table to its own because someone said it isnt good to have one big table??

Im sorry but very new to all this

[pbaldy]

Well, I wouldn't say simply "one big table is bad". It depends on the nature of your data, though of course generally speaking proper normalization results in more tables. I just noticed you said that the data wasn't entered on every record, which helps make the case for a related table. In my view, the lower the percentage of records containing the extra data, the greater the case for the related table.

Keeping it a separate table, you could combine our suggestions; put a subform on a tab. The nice part about using a subform is that with the Master/Child links set, it will do all the work of keeping the data in sync. You can do what you're describing with 2 forms, but it would take a little extra work.

[Lxmanager]

Thanks for you replys but the problem is I don't know how to do either method what you have suggested or what I proposed, all I need is all I need is both sets of data to be linked oon the same record and sent to reports. Please advise. and show me how....

[pbaldy]

Did you get this sorted out? If not, what percentage of records will contain the extra data (a guess-timate)?

[Lxmanager]

Hi, not got it sorted yet, i've not been on it this week, im at home fitting a new bathroom. The percentage of material data on the form is difficult to say.what accually happens is this:-

The form is to be used for error tracking on our factory shop floor, there can be three types of fault, Internal, Customer or Supplier. If there is a problem the inspector fills in the relevent details ie hwo is at fault, the part number, description what the fault is and what the action will be, when it is a Supplier fault, then they have to fill in the material details as part of the form, but if its internal or customer they dont. my original design had one table and one form to enter the details, these had 36 fields which I was told was too much for any one table, thats why I thought it best to have a separate table and pop up form for the materials side of it and link them both together, I know how to link the tables but not the forms capturing the auto number for both when new record is selected.