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?
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)
.Fields("SiteID") = txtSiteID.Value
.Fields("SiteName") = txtSiteName.Value
Set db = Nothing