Create a new record in multiple tables using a form

Hi Experts

I have 8 tables which contain multiple fields in each. There are two fields in each table that are the same - SiteID and SiteName. The SiteID from Table1 is the only primary key.

I would like to have a form where users can create a new record (with SiteID and SiteName only) and when they click a button, a new record will be created in all 8 tables with SiteID and SiteName from this form.

I did some searching and found the code below. I changed the names to match my form, however I can't get it to work.

Can someone please help me with what may be wrong?

Thanks
darls15

Private Sub cmdUpdate_Click()
    Dim db As Database
    Dim rst As Recordset
    Dim Tables(7) As String
    Dim strSql As String
    Dim i As Integer

    Set db = CurrentDb

    Tables(1) = "Table1"
    Tables(2) = "Table2"
    Tables(3) = "Table3"
    Tables(4) = "Table4"
    Tables(5) = "Table5"
    Tables(6) = "Table6"
    Tables(7) = "Table7"
    Tables(8) = "Table8"

    For i = 0 To UBound(Tables) - 1
        strSql = "SELECT a.* FROM " & Tables(i) & "a;"
        Set rst = db.OpenRecordset(strSql, , dbAppendOnly)
        With rst
            .AddNew
            .Fields("SiteID") = txtSiteID.Value
            .Fields("SiteName") = txtSiteName.Value
            .Update
            .Close
        End With
    Next i
    Set db = Nothing
End Sub
darls15Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
With eight identical tables, it seems like you have a serious normalisation problem.
You should work that out before proceeding.

Your code can be reduced to:
Private Sub cmdUpdate_Click()

     Dim db As DAO.Database
     Dim rst As DAO.Recordset
     Dim strSql As String
     Dim i As Integer

     Set db = CurrentDb

     For i = 1 To 8
         strSql = "SELECT a.* FROM Table & CStr(i) & " As a;"
         Set rst = db.OpenRecordset(strSql, , dbAppendOnly)
         With rst
             .AddNew
             .Fields("SiteID").Value = txtSiteID.Value
             .Fields("SiteName").Value = txtSiteName.Value
             .Update
             .Close
         End With
     Next 

     Set rst = Nothing
     Set db = Nothing
End Sub 

Open in new window

/gustav

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
In your code:
For i = 0 To UBound(Tables) - 1

This will process
Table(0) which is not defined,
Table(1),
...
Table(6)

Setting Array values:

 Tables(0) = "Table1"
 Tables(1) = "Table2"
...
  Table(7) = "Table8"

Gustav's idea is clearer matching (1) to Table1, and so on, but you need to modify the array declaration to: Dim Table(8), and ignore Table(0)
Hamed NasrRetired IT ProfessionalCommented:
Adding:
Having 8 tables with same Primary Key, should alert you to think 8 times to improve the design.
Maintenance will be a headache, and the question is an evidence of that, you get tired of repeating ID and Name.

If, for any reason, this info is modified in one or more tables, you will be missing information.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Richard DanekeTrainerCommented:
It is not clear how or when you maintain records in the other tables.  It is important to note that when records are added to subforms, Access will automatically add the key fields.  So, when you add the first record, if you have subforms on this form or other forms where the related records are added, Access will automatically add the needed keys when new records are created.
If you create the 8 records as you suggest, you must handle the normal case that blank related records may exist in 7 tables whenever a form or report is presented.
Jeffrey CoachmanMIS LiasonCommented:
Yes, ...please examine your structure before proceeding with any solution here...

How experienced with database design are you?
Again, in a properly normalized design, there is usually no need to update 8 tables via code.
Even if this were feasible, ...I can see uprating perhaps two tables, ...but 8 ?!

Something is wrong here...
darls15Author Commented:
Hi Everyone

Thanks for responding. Yes, I totally agree with you all. I've inherited this database and it's a mess and, although I'm no expert by any means in database design, I will work on changing this now. This was only meant to be a temporary fix to allow users to keep working as it's causing all sorts of problems with new records being added into one table and not other tables. I was hoping that users could add the SiteID and SiteName automatically via a form and then complete all other details in each table manually from there until it's redesigned.

However, I will now take on all your advice and start a complete rethink to ensure it's designed better before I proceed.

Thank you everyone who contributed. I appreciate everyone's comments and advice as a solution and if it's acceptable, will award points equally to all.

Thanks
darls15
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.