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

x
?
Solved

Converting a VB form to a VBA userform

Posted on 2006-05-04
7
Medium Priority
?
500 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:caz1805
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:Clever_Bob
ID: 16612527
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
 

Author Comment

by:caz1805
ID: 16612541
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
 

Author Comment

by:caz1805
ID: 16612543
Sorry i meant to also say the error points at the line: -

  rs.FindFirst "[SearchText] = '" & Prts(X) & "'"
0
 

Author Comment

by:caz1805
ID: 16613059
Any help would be great, cheers!
0
 
LVL 7

Accepted Solution

by:
Clever_Bob earned 2000 total points
ID: 16626977
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

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Introduction to Processes

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question