none
Inserting and Deleting Records via Word VBA into Access Data-Base RRS feed

  • Question

  • Hello Everybody,

    I was folowwing Greg Maxey Tutorial and i can't get to work. I would like to obtain the infomation that has been loaded into the UserForm and record it into the a data-base. But at the same time to be pupulated in the corrent ActiveDocument template.

    Sub CallEnterData()
    Dim oFrm As EnterData
    Dim oVars As Word.Variables
    Dim pStr As String
    Dim oRng As Word.Range
    Set oVars = ActiveDocument.Variables
    Set oFrm = New EnterData
    'Everything down is for the Data-Base
    'This section requires reference MS Active Data Object 2.8 Library
    Dim oPath As String
    Dim FileArray() As String
    Dim oFileName As String
    Dim i As Long
    Dim vConnection As New ADODB.Connection
    Dim vRecordSet As New ADODB.Recordset
    Dim myDoc As Word.Document
    Dim FiletoKill As String
    With oFrm
        .Show
        If .boolProceed Then
            'Provide connection string for data using Jet Provider for Access Data-Base
            vConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                                            & "Data Source=C:\Testing Macros\Tally Data.accdb;"
            vConnection.Open
            vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
            'Retrieve the data
            vConnection.Execute "DELETE * FROM MyTable"
            For i = 1 To UBound(FileArray)
            vRecordSet.AddNew
                oVars("varName").Value = .TextBox2.Text
                oVars("varName1").Value = .TextBox2.Text
                oVars("varLastName").Value = .TextBox4.Text
                oVars("varLastName1").Value = .TextBox4.Text
                oVars("varStreetAddress").Value = .TextBox10.Text
                oVars("varCity").Value = .TextBox11.Text
                If .ComboBox2.Value <> "" Then
                    oVars("varState").Value = .ComboBox2.Text
                Else: oVars("varState").Value = "Unknow"
                End If
                oVars("varZipCode").Value = .TextBox12.Text
                oVars("varDOI").Value = .TextBox16.Text
                If .ComboBox3.Value <> "" Then
                    oVars("varMember").Value = .ComboBox3.Text
                Else: oVars("varMember").Value = "Unknow"
                End If
                Select Case True
                    Case .OptionButton1
                        pStr = "Mr. " & .TextBox4.Text
                    Case .OptionButton2
                        pStr = "Ms. " & .TextBox4.Text
                End Select
                oVars("varSalutation").Value = pStr
                If .TextBox2.Text <> "" Then
                    vRecordSet("FirstName") = .TextBox2.Text
                If .TextBox3.Text <> "" Then
                    vRecordSet("MI") = .TextBox3.Text
                If .TextBox4.Text <> "" Then
                    vRecordSet("LastName") = .TextBox4.Text
                If .TextBox5.Text <> "" Then
                    vRecordSet("StreetAddress") = .TextBox5.Text
                If .ComboBox1.Text <> "" Then
                    vRecordSet("State") = .ComboBox1.Text
                If .TextBox7.Text <> "" Then
                    vRecordSet("ZipCode") = .TextBox7.Text
                If .TextBox8.Text <> "" Then
                    vRecordSet("Cell#") = .TextBox8.Text
                If .TextBox9.Text <> "" Then
                    vRecordSet("Home#") = .TextBox9.Text
                If .TextBox10.Text <> "" Then
                    vRecordSet("MailingStreet") = .TextBox10.Text
                If .TextBox11.Text <> "" Then
                    vRecordSet("MailingCity") = .TextBox11.Text
                If .ComboBox2.Text <> "" Then
                    vRecordSet("MailingState") = .ComboBox2.Text
                If .TextBox12.Text <> "" Then
                    vRecordSet("MailingZipCode") = .TextBox12.Text
                If .TextBox13.Text <> "" Then
                    vRecordSet("Qiss#") = .TextBox13.Text
                If .TextBox14.Text <> "" Then
                    vRecordSet("Home#") = .TextBox14.Text
                If .ComboBox3.Text <> "" Then
                    vRecordSet("Member") = .ComboBox3.Text
                If .TextBox15.Text <> "" Then
                    vRecordSet("OWCP#") = .TextBox15.Text
                If .TextBox16.Text <> "" Then
                    vRecordSet("DOI") = .TextBox16.Text
                If .ComboBox4.Text <> "" Then
                    vRecordSet("BodyPart1") = .ComboBox4.Text
                If .ComboBox5.Text <> "" Then
                    vRecordSet("BodyPart2") = .ComboBox5.Text
                If .ComboBox6.Text <> "" Then
                    vRecordSet("BodyPart3") = .ComboBox6.Text
                If .ComboBox7.Text <> "" Then
                    vRecordSet("Atty") = .ComboBox7.Text
                myUpdateFields
        Else
            MsgBox "Form cancelled by user"
        End If
    vRecordSet.Update
    vRecordSet.Close
    vConnection.Close
    Application.ScreenUpdating = True
    Unload oFrm
    Set oFrm = Nothing
    Set oVars = Nothing
    Set oRng = Nothing
    Set vRecordSet = Nothing
    Set vConnection = Nothing
    End Sub

    When i try to run the code I get the the following error:

    Compile Error:

    Block If without End If

    Please Help. Thanks.


    Thanks for everything, all your help is well appreciated
    Thursday, April 21, 2011 1:53 PM

Answers

  • Hi scorpdevil

    You mean this:

    Else: oVars("varName").Value = " "

    If xxx Then
       code here
    Else
       code here
    End If

    So delete the colon, then press ENTER...


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by scorpdevil Saturday, April 23, 2011 1:54 AM
    Friday, April 22, 2011 7:16 AM
    Moderator
  • Hello,

    If you mean you want to add a new reckord to the table each time, you are almost there. Comment the following line:
    vConnection.Execute "DELETE * FROM MyTable"

    That line clears the table from all existing rows. You must also put back the field names in the statements like "...vRecordSet("") = .ComboBox4.Text"

    I would also recommend some error handling around DB actions.

    After the line " vConnection.Open" you should check if you got an error or not. If you failed to connect to the database all further code using the DB must be skipped. You should also check for any errors after writing data to the table. Add error handling after this line too: "vRecordSet.Update"

    You can use code like this to check for errors in AdoDB

        If vConnection.Errors.Count > 0 Then
            For Each uVal In vConnection.Errors
                sDBerror = sDBError & uVal & vbCr
            Next
            MsgBox sExtra
        End If
       

    Regards,
    Lars-Eric

    • Marked as answer by scorpdevil Saturday, April 23, 2011 1:54 AM
    Friday, April 22, 2011 10:01 AM
  • Hello again,

    You should rearrange the code a little bit, it's not the best logic in it right now.

    You should move the following lines:
    Dim vConnection As New ADODB.Connection
    Dim vRecordSet As New ADODB.Recordset

    So they appear right after the following line:
     If .boolProceed Then ....

    Then you should move the following lines:
    vRecordSet.Update
    vRecordSet.Close
    vConnection.Close

    Put them between these two lines in the end of the If statement (before the Else line):
               myUpdateFields
            Else

    If you press the cancel button there is no reason (according to the code) to make a connection to the database nor create a RecordSet. You should make it a habit to not make a connection to a database if you don't need to. Creating connections to databases are often resource intensive operations.

    On the other hand, I would not mix DB handling with other operations in the same Sub/Method/Function. That makes error handling harder and the code harder to maintain.

    Regards,
    Lars-Eric

    • Marked as answer by scorpdevil Saturday, April 23, 2011 5:21 PM
    Saturday, April 23, 2011 4:11 PM

All replies

  • Hi scorpdevil

    The error message is a bit misleading. What it should say is that you're missing the closing "End" of some kind of block. But it's not necessarily an IF block.

    A quick scan of your code and I see you have an opening "With" but no "End With" at the end of that block? Try sticking that in, probably after your last "End If"?


    Cindy Meister, VSTO/Word MVP
    Thursday, April 21, 2011 4:04 PM
    Moderator
  • Dear Cindy,

    Now i'm getting End With without With.

    I know i'm doing something wrong.


    Thanks for everything, all your help is well appreciated
    Thursday, April 21, 2011 4:10 PM
  • Hi scorpdevil

    you need to go through your code carefully and make sure every block has matching start-end pairs. If there's lots of nesting - which your code has, you need to be very careful. For example, I see no Next to end the For block.

    You must also make sure that nothing like this happens:

    With
      If THen
    End With
      End If

    This is one reason why we always use indents for each nested "block" and code within a block - it's easier to spot the logical error.

    It's also why most VB-programmers never use the colon (Else : in your code)

    And it's why most don't use

    If something Then _[underscore]
       Do that

    (At least, I assume that this is what that long list of Ifs is doing in your code, although I don't see an underscore after Then)


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by scorpdevil Friday, April 22, 2011 1:12 AM
    • Unmarked as answer by scorpdevil Friday, April 22, 2011 1:12 AM
    Thursday, April 21, 2011 4:38 PM
    Moderator
  • Hello,

    I can see you have 21 If statements with no End If.

    You have all the lines that starts with "If .TextBox<x>.Text <> "" " that do not have any "End If"

    Just a tip, instead of using the syntax vRecordset("fieldname") you can write vRecordset!fieldnamn. vRecordset("fieldname")  is a short for vRecordset.Fields("fieldname") but vRecordset!fieldname works as well. That is a feature of AdoDB Recordset and works independent of databas you connect to.

    Hope that helped,
    Lars-Eric

    Thursday, April 21, 2011 5:02 PM
  • ;-)

    You guys are the best!....i made the following changes.

    Now, I Have a little problem, how do i make to use the next row instead of re-wrinting on the same row?

    Sub CallEnterData()
    Dim oFrm As EnterData
    Dim oVars As Word.Variables
    Dim pStr As String
    Set oVars = ActiveDocument.Variables
    Set oFrm = New EnterData
    'Everything down is for the Data-Base
    'This section requires reference MS Active Data Object 2.8 Library
    Dim oPath As String
    Dim FileArray() As String
    Dim oFileName As String
    Dim i As Long
    Dim vConnection As New ADODB.Connection
    Dim vRecordSet As New ADODB.Recordset
    Dim myDoc As Word.Document
    With oFrm
        .Show
        If .boolProceed Then
            'Provide connection string for data using Jet Provider for Access Data-Base
            vConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" _
                                            & "Data Source=C:\Testing Macros\Tally Data.accdb;"
            vConnection.Open
            vRecordSet.Open "MyTable", vConnection, adOpenKeyset, adLockOptimistic
            'Retrieve the data
            vConnection.Execute "DELETE * FROM MyTable"
            vRecordSet.AddNew
                If .TextBox2.Text <> "" Then
                    oVars("varName").Value = .TextBox2.Text
                Else: oVars("varName").Value = " "
                End If
                If .TextBox2.Text <> "" Then
                    oVars("varName1").Value = .TextBox2.Text
                Else: oVars("varName1").Value = " "
                End If
                If .TextBox4.Text <> "" Then
                    oVars("varLastName").Value = .TextBox4.Text
                Else: oVars("varLastName").Value = " "
                End If
                If .TextBox4.Text <> "" Then
                    oVars("varLastName1").Value = .TextBox4.Text
                Else: oVars("varLastName1").Value = " "
                End If
                If .TextBox10.Text <> "" Then
                    oVars("varStreetAddress").Value = .TextBox10.Text
                Else: oVars("varStreetAddress").Value = " "
                End If
                If .TextBox11.Text <> "" Then
                    oVars("varCity").Value = .TextBox11.Text
                Else: oVars("varCity").Value = " "
                End If
                If .ComboBox2.Value <> "" Then
                    oVars("varState").Value = .ComboBox2.Text
                Else: oVars("varState").Value = " "
                End If
                If .TextBox12.Text <> "" Then
                    oVars("varZipCode").Value = .TextBox12.Text
                Else: oVars("varZipCode").Value = " "
                End If
                If .TextBox16.Text <> "" Then
                    oVars("varDOI").Value = .TextBox16.Text
                Else: oVars("varDOI").Value = " "
                End If
                If .ComboBox3.Value <> "" Then
                    oVars("varMember").Value = .ComboBox3.Text
                Else: oVars("varMember").Value = " "
                End If
                If .TextBox14.Text <> "" Then
                    oVars("varClaim").Value = .TextBox14.Text
                Else: oVars("varClaim").Value = " "
                End If
                If .TextBox13.Text <> "" Then
                    oVars("varQiss").Value = .TextBox13.Text
                Else: oVars("varQiss").Value = " "
                End If
                If .TextBox2.Text <> "" Then
                    oVars("varName").Value = .TextBox2.Text
                Else: oVars("varName").Value = " "
                End If
                Select Case True
                    Case .OptionButton1
                        pStr = "Mr. " & .TextBox4.Text
                    Case .OptionButton2
                        pStr = "Ms. " & .TextBox4.Text
                End Select
                oVars("varSalutation").Value = pStr
                If .TextBox2.Text <> "" Then _
                    vRecordSet("") = .TextBox2.Text
                If .TextBox3.Text <> "" Then _
                    vRecordSet("") = .TextBox3.Text
                If .TextBox4.Text <> "" Then _
                    vRecordSet("") = .TextBox4.Text
                If .TextBox5.Text <> "" Then _
                    vRecordSet("") = .TextBox5.Text
                If .ComboBox1.Text <> "" Then _
                    vRecordSet("") = .ComboBox1.Text
                If .TextBox7.Text <> "" Then _
                    vRecordSet("") = .TextBox7.Text
                If .TextBox8.Text <> "" Then _
                    vRecordSet("") = .TextBox8.Text
                If .TextBox9.Text <> "" Then _
                    vRecordSet("") = .TextBox9.Text
                If .TextBox10.Text <> "" Then _
                    vRecordSet("") = .TextBox10.Text
                If .TextBox11.Text <> "" Then _
                    vRecordSet("") = .TextBox11.Text
                If .ComboBox2.Text <> "" Then _
                    vRecordSet("") = .ComboBox2.Text
                If .TextBox12.Text <> "" Then _
                    vRecordSet("") = .TextBox12.Text
                If .TextBox13.Text <> "" Then _
                    vRecordSet("") = .TextBox13.Text
                If .TextBox14.Text <> "" Then _
                    vRecordSet("") = .TextBox14.Text
                If .ComboBox3.Text <> "" Then
                    vRecordSet("") = .ComboBox3.Text
                If .TextBox15.Text <> "" Then _
                    vRecordSet("") = .TextBox15.Text
                If .TextBox16.Text <> "" Then _
                    vRecordSet("") = .TextBox16.Text
                If .ComboBox4.Text <> "" Then _
                    vRecordSet("") = .ComboBox4.Text
                If .ComboBox5.Text <> "" Then _
                    vRecordSet("") = .ComboBox5.Text
                If .ComboBox6.Text <> "" Then _
                    vRecordSet("") = .ComboBox6.Text
                If .ComboBox7.Text <> "" Then _
                    vRecordSet("") = .ComboBox7.Text
                myUpdateFields
            Else
                MsgBox "Form cancelled by user"
            End If
    End If
    End With
    vRecordSet.Update
    vRecordSet.Close
    vConnection.Close
    Application.ScreenUpdating = True
    Unload oFrm
    Set oFrm = Nothing
    Set oVars = Nothing
    End Sub

    Sub myUpdateFields()
    Dim pRange As Word.Range
    Dim iLink As Long
    iLink = ActiveDocument.Sections(1).Headers(1).Range.StoryType
    For Each pRange In ActiveDocument.StoryRanges
        Do
            pRange.Fields.Update
            Set pRange = pRange.NextStoryRange
        Loop Until pRange Is Nothing
    Next
    End Sub

    Sub Create_Reset_Variables()
    With ActiveDocument.Variables
        .Item(" Enter the Variable bookmark here").Value = " "
        .Item("").Value = " "
        .Item("").Value = " "
        .Item("").Value = " "
        .Item("").Value = " "
        .Item("").Value = " "
        .Item("varState").Value = " "
        .Item("varZipCode").Value = " "
        .Item("varDOI").Value = " "
        .Item("varMember").Value = " "
        .Item("varClaim").Value = " "
        .Item("varQiss").Value = " "
        .Item("varSalutation").Value = " "
        .Item("varUser").Value = " "
        .Item("varAdjuster").Value = " "
    End With
    myUpdateFields
    End Sub
    Sub AutoNew()
    Create_Reset_Variables
    CallEnterData
    End Sub


    Thanks for everything, all your help is well appreciated
    Thursday, April 21, 2011 9:40 PM
  • Hi scorpdevil

    You mean this:

    Else: oVars("varName").Value = " "

    If xxx Then
       code here
    Else
       code here
    End If

    So delete the colon, then press ENTER...


    Cindy Meister, VSTO/Word MVP
    • Marked as answer by scorpdevil Saturday, April 23, 2011 1:54 AM
    Friday, April 22, 2011 7:16 AM
    Moderator
  • Hello,

    If you mean you want to add a new reckord to the table each time, you are almost there. Comment the following line:
    vConnection.Execute "DELETE * FROM MyTable"

    That line clears the table from all existing rows. You must also put back the field names in the statements like "...vRecordSet("") = .ComboBox4.Text"

    I would also recommend some error handling around DB actions.

    After the line " vConnection.Open" you should check if you got an error or not. If you failed to connect to the database all further code using the DB must be skipped. You should also check for any errors after writing data to the table. Add error handling after this line too: "vRecordSet.Update"

    You can use code like this to check for errors in AdoDB

        If vConnection.Errors.Count > 0 Then
            For Each uVal In vConnection.Errors
                sDBerror = sDBError & uVal & vbCr
            Next
            MsgBox sExtra
        End If
       

    Regards,
    Lars-Eric

    • Marked as answer by scorpdevil Saturday, April 23, 2011 1:54 AM
    Friday, April 22, 2011 10:01 AM
  • Dear Cindy,

    I did it as you recomended and it works.

    Thanks


    Thanks for everything, all your help is well appreciated
    Friday, April 22, 2011 12:56 PM
  • Hi Larsg_Sweden

    I did the vRecordSet.Fields("") =

    works fine

    Also, i comment out the vConnection.Execute" DELETE * FROM MyTable" and it does adds new record!

    Thanks a lot.

    Now the Error matter is not working for me.

    I get the following error: (uVal)

    Compile error:

    Variable not defined

    Plase help.


    Thanks for everything, all your help is well appreciated
    Friday, April 22, 2011 1:04 PM
  • Hi,

    Sorry about that, put a Dim statement at the top of the Sub where you have the other Dim statements

    Dim uVal as Variant

    Regards,
    Lars-Eric

    Friday, April 22, 2011 1:46 PM
  • Hi Larsg_sweden,

    I just have a final Quetion.

    In my form i have a cancel button. When there is no data present on any of my textboxes and you click cancel i get the following error.

    Run-time "3704:

    Operation is not allowed when the object is closed.

    then i click debug then it points to "vRecordSet.Update"

    Do i fix this by a error handling?


    Thanks for everything, all your help is well appreciated
    Saturday, April 23, 2011 1:56 PM
  • Hello again,

    You should rearrange the code a little bit, it's not the best logic in it right now.

    You should move the following lines:
    Dim vConnection As New ADODB.Connection
    Dim vRecordSet As New ADODB.Recordset

    So they appear right after the following line:
     If .boolProceed Then ....

    Then you should move the following lines:
    vRecordSet.Update
    vRecordSet.Close
    vConnection.Close

    Put them between these two lines in the end of the If statement (before the Else line):
               myUpdateFields
            Else

    If you press the cancel button there is no reason (according to the code) to make a connection to the database nor create a RecordSet. You should make it a habit to not make a connection to a database if you don't need to. Creating connections to databases are often resource intensive operations.

    On the other hand, I would not mix DB handling with other operations in the same Sub/Method/Function. That makes error handling harder and the code harder to maintain.

    Regards,
    Lars-Eric

    • Marked as answer by scorpdevil Saturday, April 23, 2011 5:21 PM
    Saturday, April 23, 2011 4:11 PM
  • My Friend,

    Thanks a lot.  I really DO!

    I guess this is what you gain with experience.....which a have NONE. :)

    Thanks a lot


    Thanks for everything, all your help is well appreciated
    Saturday, April 23, 2011 5:21 PM