none
Auto-fill fields in Access RRS feed

  • Question

  • Hello,

    I have created an Access file (2010 version) that consists of 11 tables. Each of these tables has a variable called "CaseID_Number". The CaseID_Number is the Primary key in all of the tables. Now, I would like to link all the case ID numbers to the one in the first table so that once a number is entered into the case ID field in the first table, all the case ID fields in the other tables will automatically be filled with the same number.

    How do I do that? I am very new to Access so please explain is very simple terms.

    Thank you! 

    Saturday, December 1, 2012 1:36 AM

Answers

  • Hi,

    As this requirement, it cannot be done with table, but we can do it with forms.

    Create a form related to the first table. Then add a button, when you create a new record, then generate a new CaseID_Number, then click the button. In the command_click event, write the code as follow:

    Private Sub Command1_Click()

    Dim db As Database

    Dim rst As Recordset

    Set db = CurrentDb

    Dim Num As Long

    Num = Forms![Table1_Form]![CaseID_Number]

    Set rst = db.OpenRecordset("Table2")

    With rst

        .AddNew

        !CaseID_Number= Num

        .Update  

    End With

    End Sub

    Then refresh Table2, the new CaseID_Number will show in the table.


    Jaynet Zhang
    TechNet Community Support

    Monday, December 3, 2012 9:11 AM
    Moderator

All replies

  • Hi,

    As this requirement, it cannot be done with table, but we can do it with forms.

    Create a form related to the first table. Then add a button, when you create a new record, then generate a new CaseID_Number, then click the button. In the command_click event, write the code as follow:

    Private Sub Command1_Click()

    Dim db As Database

    Dim rst As Recordset

    Set db = CurrentDb

    Dim Num As Long

    Num = Forms![Table1_Form]![CaseID_Number]

    Set rst = db.OpenRecordset("Table2")

    With rst

        .AddNew

        !CaseID_Number= Num

        .Update  

    End With

    End Sub

    Then refresh Table2, the new CaseID_Number will show in the table.


    Jaynet Zhang
    TechNet Community Support

    Monday, December 3, 2012 9:11 AM
    Moderator
  • Thank you so much Jaynet.

    It works! Now, how can I use that for multiple tables? I mean, is there a way to create a button that will copy the CaseID_Number to ten different tables?

    Thanks again!


    Saturday, December 22, 2012 3:17 AM