How add records to 8 different tables from data in a form via VBA code

You can insert records like this:

Currentdb.Execute "INSERT INTO Table1(Field1) VALUES('" & Me.YourTextbox & "')"

That assumes Field1 is a Text field. You'd repeat that for each Table, and obviously you'd change table/field/control names to match your own.

I don't know what you mean by "the data already exists", but essentially if you must first check each table to see if the record already exists, you would do something like this:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT * FROM Table1 WHERE Field1='" & Me.YourTextbox & "'")

If Not (rst.EOF and rst.BOF) Then
  '/ the record does not exist, so use the INSERT
End If

Repeat that for each table ...

Try this. You'll have to write the code that references the control that holds the client value and you'll have to populate the array with your table names.  Let me know if you have any questions.

Public Sub Add_Records()
    
    '// get the client name from you form field
    Dim clientName As String
    clientName = "replace this value with a reference to the form control that holds the client value" 'NAME_OF_YOUR_CONTROL.VALUE
  
    Const TableListSize = 8
    Dim TableList(8) As String
    
    '// populate the tableList array with your table names
    TableList(0) = "table 1 name"
    TableList(1) = "table 2 name"
    TableList(2) = "table 3 name"
    TableList(3) = "table 4 name"
    TableList(4) = "table 5 name"
    TableList(5) = "table 6 name"
    TableList(6) = "table 7 name"
    TableList(7) = "table 8 name"
    
    Dim sql As String
    Dim i As Integer
    
    '// Loop through each table and for each table insert the client into the respective table if it doesn't already exist in the table.
    For i = 0 To TableListSize - 1
        sql = Empty
        If DCount("*", TableList(i), "[client]='" & clientName & "'") = 0 Then
            'add the client to the table
            sql = "INSERT INTO " & TableList(i) & " ([Client]) VALUES ('" & clientName & "'"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL sql, dbSeeChanges
            DoCmd.SetWarnings True
        Else
            MsgBox "The client [" & clientName & "] already exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i
    
End Sub

Select allOpen in new window

Scott, when I compile I get an error User defined type not defined on the line...

Dim rst As DAO.Recordset

make sure you've included  dao library reference.

http://www.accessmvp.com/twickerath/articles/adodao.htm

I do have it in there.

Anthony:  When I run your code using just one table as a test (and with some field name changes already, I get an error:

"Syntax error in INSERT INTO statement"


Here's the code as I have it right now:

    '// get the client name from you form field
    Dim CompanyName As String
    CompanyName = Me.txtClientID01 'NAME_OF_YOUR_CONTROL.VALUE
  
    Const TableListSize = 1
    Dim TableList(1) As String
    
    '// populate the tableList array with your table names
    TableList(0) = "Customers"
'    TableList(1) = "table 2 name"
'    TableList(2) = "table 3 name"
'    TableList(3) = "table 4 name"
'    TableList(4) = "table 5 name"
'    TableList(5) = "table 6 name"
'    TableList(6) = "table 7 name"
'    TableList(7) = "table 8 name"
    
    Dim sql As String
    Dim i As Integer
    
    '// Loop through each table and for each table insert the client into the respective table if it doesn't already exist in the table.
    For i = 0 To TableListSize - 1
        sql = Empty
        If DCount("*", TableList(i), "[CompanyName]='" & CompanyName & "'") = 0 Then
            'add the client to the table
            sql = "INSERT INTO " & TableList(i) & " ([CompanyName]) VALUES ('" & CompanyName & "'"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL sql, dbSeeChanges
            DoCmd.SetWarnings True
        Else
            MsgBox "The company name [" & CompanyName & "] already exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i

Select allOpen in new window

And this like of code is highlighted:

DoCmd.RunSQL sql, dbSeeChanges

Note with 2007 and above, DAO is not referenced in the manner discussed in Anthony's link. The reference for DAO in those versions looks like:

Microsoft Office XX Access database engine Object Library

Where "XX" is the version of Office/Access you're using. For example, in my Access 2013, I'm using the 15.0 library.

That said, I'd be very surprised if you do NOT have that reference already in your Access application. It's added by default, and unless you specifically remove it, you should have the reference already. I'd instead think that a different reference is the culprit, so open your VBA Editor, click Tools - References, and make sure none are marked as MISSING.

I forgot the closing paranthesis! My fault!

 sql = "INSERT INTO " & TableList(i) & " ([CompanyName]) VALUES ('" & CompanyName & "')"

Select allOpen in new window

I think that fixed that issue.  Now new issue when running this latest code:

Subscript out of range.  This line gets highlighted:

TableList(2) = "ISPinfo"

Here's the code:

    '// get the client name from you form field
    Dim CustomerID As String
    CustomerID = Me.txtClientID01 'NAME_OF_YOUR_CONTROL.VALUE
  
    Const TableListSize = 10
    Dim TableList(1) As String
    
    '// populate the tableList array with your table names
    TableList(0) = "Customers"
    TableList(1) = "HardwareVendorInfo"
    TableList(2) = "ISPinfo"
    TableList(3) = "LogonInfo"
    TableList(4) = "NADinfo"
    TableList(5) = "NetworkInfo"
    TableList(6) = "RouterInfo"
    TableList(7) = "ServerInfo"
    TableList(8) = "SoftwareVendorInfo"
    TableList(9) = "WorkstationInfo"

    
    Dim SQL As String
    Dim i As Integer
    
    '// Loop through each table and for each table insert the client into the respective table if it doesn't already exist in the table.
    For i = 0 To TableListSize - 1
        SQL = Empty
        If DCount("*", TableList(i), "[CustomerID]='" & CustomerID & "'") = 0 Then
            'add the client to the table
            
            SQL = "INSERT INTO " & TableList(i) & " ([CustomerID]) VALUES ('" & CustomerID & "')"
            
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, dbSeeChanges
            DoCmd.SetWarnings True
        Else
            MsgBox "The customer ID [" & CustomerID & "] already exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i

Select allOpen in new window

it's because of this line.

Dim TableList(1) As String

Select allOpen in new window

Let's change that line to this.

Dim TableList(TableListSize) As String

Select allOpen in new window

You'd only created a single-dimension array with 3 elements here:

Dim TableList(1) As String

But you're adding 10 elements to that string. You need instead to do this:

Dim TableList(9) As String

That will dimension a 10 element single-dimension array.

I would still STRONGLY advise you to determine why you needed to recreate that reference, if that's what you did. That's a default reference, and it should always be checked.

Hmm,

I changed it to

Dim TableList(TableListSize) As String

And now it's working EXCEPT the 1st table isn't getting the new records added.  The rest of them are.

Sit down and take a deep breath.  It sounds like you have a series of 1-1 relationships and are attempting to populate them all at once even if there is no data for them.  Which brings up some questions.
1. Why have a 1-1 relationship at all.  If the data is not optional, why not just add the columns to the main table.
2. Why are you using autonumbers as the PK for the related tables.  If the relationship is 1-1, then the autonumber is the PK of only the "parent" table.  The other tables have only a long integer defined as their PK because that is also the FK to the "parent" table.
3. Are you aware of how Left Joins work?  They can bring back the data from the "parent" table whether or not a related record is found in the "child" table.
4.  If the relationships are actually 1-many, then you do need autonumbers in the "child" tables because Client is only a FK and not also the PK.  And, in this case, you also don't need to create empty records in the related tables.

So the two possible solutions are.
1.  Add all the columns to the same table so you have no "child" tables.  This is correct as long as the relationship is 1-1.
2.  Change your joins to be left joins so it doesn't matter if a row exists in each "child" table.

In no case is it correct to add empty rows to the "child" tables.  There is something wrong with the logic of a table if all columns except the PK and FK are allowed to be blank.

Pat,

I appreciate your comments but this is an inherited database that I can't redesign.  For now I just need to know why it's working EXCEPT the 1st table isn't getting the new records added.  The rest of them are.

Anthony, any thoughts as to why the 1st table isn't getting a record inserted?

When the For loop is in its first iteration does the true path of the If statement execute? Or do you get a message that the value already exists (the false path of the If)?

I do not get a message indicating that the record already exists and I know it doesn't because its not there.

It's hard to know what's going on without seeing your table and how its set. Try commenting out the docmd.Setwarning statements before and after the docmd.runSql statement.  Once you do that and re-run the code it should tell you how many records are going to be inserted. So in our case it should be 1 record per table.

            'DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, dbSeeChanges
            'DoCmd.SetWarnings True

Select allOpen in new window

I think I just figured out why the record isn't being entered in the 1st table.  There is another field that is a required field in that table only.  Even if what was automatically entered was the same vale as the CustomerID field that would be ok.  But how would I make that happen?

assuming the other field is also a text field...

SQL = "INSERT INTO " & TableList(i) & " ([CustomerID],["YOUR_OTHER_FIELD_NAME"]) VALUES ('" & CustomerID & "','" & SOME_STRING_VALUE & "')"

Select allOpen in new window

Almost, but only the 1st table has the extra field that needs to be populated.  It seems that when the loop gets to the 2nd table it complains that it can't find the field, "CompanyName" because it doesn't exist nor does it exist in the rest of the tables..

Here is what I have based on your latest recommendation:

    For i = 0 To TableListSize - 1

        SQL = Empty
        If DCount("*", TableList(i), "[CustomerID]='" & CustomerID & "'") = 0 Then
            SQL = "INSERT INTO " & TableList(i) & " ([CustomerID],[CompanyName]) VALUES ('" & CustomerID & "','" & CustomerID & "')"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, dbSeeChanges
            DoCmd.SetWarnings True
        Else
            MsgBox "The customer ID [" & CustomerID & "] already exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i

Select allOpen in new window

[code]
   For i = 0 To TableListSize - 1

        SQL = Empty
        If DCount("*", TableList(i), "[CustomerID]='" & CustomerID & "'") = 0 Then
	    if i = 0 then
	        SQL = "INSERT INTO " & TableList(i) & " ([CustomerID],[CompanyName]) VALUES ('" & CustomerID & "','" & CustomerID & "')"
	    else
		SQL = "INSERT INTO " & TableList(i) & " ([CompanyName]) VALUES ('" & CustomerID & "')"
	    end if
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQL, dbSeeChanges
            DoCmd.SetWarnings True
        Else
            MsgBox "The customer ID [" & CustomerID & "] already exists in table [" & TableList(i) & "]", vbInformation
        End If
    Next i

Select allOpen in new window

The first table got added as expected.  But when the loop got to the 2nd table it complained that it can't find the field [CompanyName] Which is true because it only exists in the 1st table.

so change the name to the field that works :) You will know better than me because I can't see your table ;-)

fyi:

you'll want to change it in this line. I'm guessing this is the name of the field

SQL = "INSERT INTO " & TableList(i) & " ([CustomerId]) VALUES ('" & CustomerID & "')"

Select allOpen in new window

Thank you so much for the help.  This works perfectly!  Now I need to find a way to delete those records that have been added.  If you have time please watch for a new topic named "How delete records from 10 different tables from data in a form via VBA code".

Again, thank you!

Cool. I'm glad you're squared away for now.

ab