Results 1 to 9 of 9

Linking two forms together

  1. #1
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24

    Linking two forms together

    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

  2. #2
    NTC
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    1,503
    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.
    www CahabaData com

  3. #3
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    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.

    Form 1.doc

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,064
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  5. #5
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    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

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,064
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  7. #7
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    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....

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,064
    Did you get this sorted out? If not, what percentage of records will contain the extra data (a guess-timate)?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  9. #9
    Lxmanager is offline Novice
    Windows 7 Access 2007
    Join Date
    Sep 2010
    Posts
    24
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking Forms
    By dalton in forum Forms
    Replies: 1
    Last Post: 05-30-2010, 10:23 PM
  2. Linking Table Data on Forms
    By P5C768 in forum Database Design
    Replies: 12
    Last Post: 03-22-2010, 08:42 AM
  3. linking forms by ID - automatically add ID
    By jlcaviglia-harris in forum Forms
    Replies: 0
    Last Post: 04-17-2009, 02:51 PM
  4. Resource for using linking tables with forms?
    By narayanis in forum Forms
    Replies: 5
    Last Post: 05-18-2008, 03:11 PM
  5. Linking tables and forms
    By vgatell in forum Access
    Replies: 0
    Last Post: 02-10-2007, 11:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums