Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Runtime Error 91: DAO connection

I have this code and it is used for when a user types in text into textbox1 and they press space the text is looked up in a database and the ouput is put into textbox2.  I have got this woring in VB6 but i cant get it working in a Word VBA userform, i keep on getting the error runtime error 91 when i press the spacebar.  It highlights the line where it looks for the 'SearchText' field in the database.  Here is the code.


Dim db As DAO.Database
Dim rs As DAO.Recordset

Sub UserForm_Click()
Set db = OpenDatabase("C:\Documents and Settings\cm318\Desktop\TEST.mdb")
Set rs = db.OpenRecordset("tblTEST", dbOpenDynaset)
End Sub

Private Sub CommandButton1_Click()

End Sub


Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Dim Prts() As String
Dim tmpTEXT As String

If KeyAscii = 32 Then
    If TextBox1.Text <> "" Then
        Prts = Split(TextBox1.Text, " ")
            For X = 0 To UBound(Prts)
                rs.FindFirst "[SearchText]='" & Prts(X) & "'"
                    If Not rs.NoMatch Then
                        tmpTEXT = Replace(tmpTEXT, Prts(X), rs.Fields("ReplacementText"))
                    End If
                Next
            TextBox2 = tmpTEXT
        End If
    End If
End Sub
0
caz1805
Asked:
caz1805
  • 6
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is th evalue of Prts(X) when the error occurs?
0
 
caz1805Author Commented:
If i type in hello and then press the spacebar, the value of Prts(X) is 'hello'.
0
 
caz1805Author Commented:
I changed the userform from click to initalize and now i dont recieve a 91 error but the output is not being placed in TextBox2.  I placed a toggle on the replace line and it says that Prts(x) = hello and also that rs.Field("ReplacementText") = hiya which is the replacement.  So it is now accessing the database but not outputting the text, as tmpTEXT has no value.

thanks angel
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
caz1805Author Commented:
Any help would be really appreciated.
0
 
fostejoCommented:
caz1805,

How's about

If KeyAscii = 32 Then
    If TextBox1.Text <> "" Then
        tmpTEXT=""
        Prts = Split(TextBox1.Text, " ")
            For X = 0 To UBound(Prts)
                rs.FindFirst "[SearchText]='" & Prts(X) & "'"
                    If Not rs.NoMatch Then
                        tmpTEXT = tmpTEXT+ rs.Fields("ReplacementText")
                    End If
            Next
    End If
    TextBox2 = tmpTEXT
End If


cheers,
0
 
caz1805Author Commented:
Thats excellent but theres two problems,

- After each replacement there is a tab before the next replacement?

- That replaces every word but if a word is not in the database it doesnt get copied into the second textbox, i need it to copy it even if there isnt a replacement as I have names that dont need replacing, do you know how to do that?

Thanks alot your the best!!!

caz
0
 
fostejoCommented:
caz1805,

Yes; immediately I'd submitted it I spotted the flaws; unfortunately, had to deal with something else, so couldn't alter straight away, sorry.   ;)

If KeyAscii = 32 Then
    If TextBox1.Text <> "" Then
        tmpTEXT=textbox1.text
        Prts = Split(TextBox1.Text, " ")
        For X = 0 To UBound(Prts)
             rs.FindFirst "[SearchText]='" & Prts(X) & "'"
             If Not rs.NoMatch Then
                  tmpTEXT = replace(tmpTEXT,Prts(x),rs.Fields("ReplacementText"))
             End If
        Next
       TextBox2.text = tmpTEXT
    End If
End If


I think that should do the trick (not had chance to try it though!)

Not sure where the tabs are coming from, they must be in the "ReplacementText" field??  However, you could alter the last but two line to TextBox2.text=replace(tmpTEXT,chr(9)," ") to replace any tabs with spaces perhaps (or fix the data source?)

cheers,
0
 
caz1805Author Commented:
Thats excellent, I am going to have a look at the tab problem but I am sure that is a simple problem with the database.  

Just like to say again your the best!!!!!!
0
 
caz1805Author Commented:
i managed to sort the tab problem by making the fields in the database to wrap the text.  Thansk again for the help.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now