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

I have a form that has 1 field.  The user enters information (text data), into the field.  Then on the same form is a command button labeled "Add Info".  When the command button is clicked I want to add a record to each of 8 tables.  Each table has an auto-number field and a field named "Client".  

How can I add a new record to each table and have the information entered in the form field to be added to the field, "Client" in each of the tables via VBA code only if the information entered is different than any other record's Client field.  If the data already exists then have a message appear letting the user know that the record already exists.

--Steve
SteveL13Asked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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 ...
0
Anthony BerenguelCommented:
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

Open in new window

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

Dim rst As DAO.Recordset
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony BerenguelCommented:
make sure you've included  dao library reference.

http://www.accessmvp.com/twickerath/articles/adodao.htm
0
SteveL13Author Commented:
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

Open in new window

0
SteveL13Author Commented:
And this like of code is highlighted:

DoCmd.RunSQL sql, dbSeeChanges
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Anthony BerenguelCommented:
I forgot the closing paranthesis! My fault!

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

Open in new window

0
SteveL13Author Commented:
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

Open in new window

0
Anthony BerenguelCommented:
it's because of this line.
Dim TableList(1) As String

Open in new window


Let's change that line to this.
Dim TableList(TableListSize) As String

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
SteveL13Author Commented:
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.
0
PatHartmanCommented:
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.
0
SteveL13Author Commented:
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.
0
SteveL13Author Commented:
Anthony, any thoughts as to why the 1st table isn't getting a record inserted?
0
Anthony BerenguelCommented:
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)?
0
SteveL13Author Commented:
I do not get a message indicating that the record already exists and I know it doesn't because its not there.
0
Anthony BerenguelCommented:
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

Open in new window

0
SteveL13Author Commented:
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?
0
Anthony BerenguelCommented:
assuming the other field is also a text field...
SQL = "INSERT INTO " & TableList(i) & " ([CustomerID],["YOUR_OTHER_FIELD_NAME"]) VALUES ('" & CustomerID & "','" & SOME_STRING_VALUE & "')"

Open in new window

0
SteveL13Author Commented:
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

Open in new window

0
Anthony BerenguelCommented:
[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

Open in new window

0
SteveL13Author Commented:
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.
0
Anthony BerenguelCommented:
so change the name to the field that works :) You will know better than me because I can't see your table ;-)
0
Anthony BerenguelCommented:
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 & "')"

Open in new window

0
Anthony BerenguelCommented:
so the for loop should look like this,

[code]
   For i = 0 To TableListSize - 1

        SQL = Empty
        If DCount("*", TableList(i), "[CustomerID]='" & CustomerID & "'") = 0 Then
	    if i = 0 then
'FIRST TABLE
	        SQL = "INSERT INTO " & TableList(i) & " ([CustomerID],[CompanyName]) VALUES ('" & CustomerID & "','" & CustomerID & "')"
	    else
'NOT FIRST TABLE
		SQL = "INSERT INTO " & TableList(i) & " ([CustomerID]) 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

Open in new window

0

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
SteveL13Author Commented:
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!
0
Anthony BerenguelCommented:
Cool. I'm glad you're squared away for now.

ab
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.