Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: automatic update of fields from prior entries

    I am a novice with Access and was assigned a project to create a database for work. My issue is that my coworkers want to be able to see a certain identification number across the board but only enter it once. In this case, this identification number is called "batch number." I have made it the primary key of the first table and a foreign key in following tables, however, I can't figure out how to make it automatically transfer from one table to another without them having to do enter it or choose it from a list.

    Any suggestions?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A common setup is to use a form/subform, and let the master/child links handle it (if you use the wizard, it will ask about linking fields).
    Paul

  3. #3
    Join Date
    Jan 2012
    Posts
    6
    I was hoping for maybe a vba code so I wouldn't have to redo all of my forms. My coworkers informed me that they wanted this After I created everything.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Hard to say without knowing your setup. Generally you can copy a value from one form to another with:

    Forms.TargetFormName.TextboxName = Forms!SourceFormName.TextboxName
    Paul

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Is the data going to be entered in these multiple Forms at different times? If so, your statement "I have made it the primary key of the first table and a foreign key in following tables..." really reinforces what Paul has said; you're talking about a one-to-many relationship and using a Main Form/Subform is the standard way of implementing this.

    When your users need to enter data into any Table for a given 'batch number,' they go to that batch number's Record on the Main Form and Access automatically pulls up the corresponding Records on the Subforms for that batch number, either for review or foe entering new Records.

    Unless you do it this way, I'm afraid your users are really going to have either enter it manually, which is not really wise, for this type of data, or select it from a Combobox, which is a far better idea, as it will at least prevent the entering of a non-existent number. Stop and think about it; short of a neuro-link interface, ala Clint Eastwood in "Firefox," how else will Access know what batch number the user is thinking about?

    As for your concern about redoing all your Forms, it shouldn't really be all that big a deal. You could add a Tabbed Control to your Main Form and place each Subform on its own page, using the Forms you already have as the basis for the Subforms.

    If all of the Records for a given batch number are going to be entered at one time, i.e. enter the batch number data in the first Form/Table, move to the second Form/Table and enter data for that batch number, and so on, never having to enter data for a given number again, then you can do this by opening each subsequent Form carrying the batch number forward, using OpenArgs to do do. If this is the case, let us know and we'll walk you thru that scenario.

    Linq ;0)>
    Last edited by Missinglinq; 01-21-12 at 00:47.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Jan 2012
    Posts
    6
    They will only be entering the data on the first form, so the open argument idea would be wonderful!

    How do I go about this?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •