Converting a VB form to a VBA userform

hi everyone,

I have created a program in visual basic 6 which allows the user to type in text to one textbox and when the spacebar is pressed it checks a Microsoft Access database and places the replacement word in another textbox.  This works very well but I would like to emulate this within a VBA Word Userform, I have tried to do this myself but am struggling and i believe this is because the keypress button is not working and I cannot understand why.  Or i have not converted it correctly from VB6 to VBA.

Here is the code from the VBA userform which I have made.

Private db As Database
Private rs As Recordset

Private Sub UserForm1_Load()

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

End Sub

Thanks alot for any help

TextBox1_Change()
End Sub

Private Sub TextBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim Prts() As String
    Dim tmpText As String
   
    If KeyAscii = 32 Or KeyAscii = 46 Then
       
        If TextBox2.Text <> "" Then
        tmpText = TextBox2
            Prts = Split(TextBox2.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
            TextBox1 = tmpText
        End If
    End If
End Sub
caz1805Asked:
Who is Participating?
 
Clever_BobCommented:
I have a couple of ideas:

1) Perhaps I'm being stupid here but you are trying to return the first record in the recordset 'rs' right? I can't see where you have opened your database or run the query?

2) Alternatively, try declaring your database and recordset variables in the same subroutine - that will at least eliminate some other possible problems when testing.


Hope this helps.
0
 
Clever_BobCommented:
Does the TextBox1_Change() function get called at all when you press a key?

Try a msgbox popup to make sure it is getting that far at least.
0
 
caz1805Author Commented:
I have made some changes and know when I press spacebar to fire the keypress i get a 'Run-time error:91 Object variable or With block variable not set'.  I tried it without the find and replace code and just with a message box and it all worked fine.  Here is the new code.

Private db As DAO.Database
Private rs As DAO.Recordset

Private Sub UserForm_Click()

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

End Sub

Private Sub CommandButton1_Click()

End Sub


Private 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
 
caz1805Author Commented:
Sorry i meant to also say the error points at the line: -

  rs.FindFirst "[SearchText] = '" & Prts(X) & "'"
0
 
caz1805Author Commented:
Any help would be great, cheers!
0
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.

All Courses

From novice to tech pro — start learning today.