This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?

[chazrab]

Excel's VBA FIND method easily finds multiple records and copies them to another sheet. However, for me, trying to use this same code to do the
same thing with Acess's VBA code and a table is a different story. I know there is a difference between a sheet and an Access db table. And I know
won't recognize some oof the following code lines
I'm sure there has to be many cases where an Excel sheet gets so large that it facilitates porting the Excel application to Access's database based application

Such is my case. The main data Sheet has 31.103 rows and Excel stops responding at times. This is unacceptable for me. It has to work right every time.
Please take a look at this FIND method. Granted, it's long, but I' d really appreciate somone's help in moving this to Access.
I am not having any luck with trying to do the same thing with an Access Query. (The SQL copy Query to Table solution causes multiple conflicts and seems inefficient)

CORRECT EXCEL VBA FIND CODE
Private Sub cmdFIND_Click()
Sheets("MAINARES2").UsedRange.ClearContents
Dim lastrow, lastrow2 As Integer, X As String, c As Range, rw As Long, firstAddress As Variant, rowno As Variant, RownoA As Variant
X = MAINWINDOW2.TextBox11.Value
With Worksheets("Sheet2").Range("E1:E31103")
Set c = .FIND(X, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False)
If Not c Is Nothing Then
rw = 1
firstAddress = c.Address
Do
Worksheets("Sheet2").Select
c.Select
Range(Cells(c.Row, 2), Cells(c.Row, 7)).Copy Destination:=Sheets("MAINARES2").Range("B" & rw)
rw = rw + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
lastrow = Sheets("MAINARES2").Range("B" & rows.count).End(xlUp).Row
If lastrow = 1 Then
Range(Cells(c.Row + 7, 2), Cells(c.Row, 7)).Copy Destination:=Sheets("MAINARES2").Range("B" & rw)
Else
End If
Else
MsgBox "value not found"
End If
End With
rowno = Sheets("MAINARES2").Range("B2").End(xlDown).Row
Sheets("MAINARES2").Range("H1").Value = rowno 'total rows found in search
Sheets("MAINARES2").Range("I1").Value = X 'value to find, i.e.,, "last days"
End Sub

I apoligize for a lengthy post - I don't like them - but I cannot explain my dilemma and have people understand with less words and code.
If anyone with both Excel and Access  build experience could help me, I'd really appreciate it vey much.

cr

 

[chazrab]

Robert - after your post above, I got curious and typed "amen" w/out cap A. It found 'amen' in firmament in Gen 1:6 - but get this - it took 10 seconds!
The screen turned white for about 2-3 secs and the results displayed. My code gave 331 occurrences from Gen to Rev of 'amen'.

But I am greatly bothered by the time of 10 seconds. How long did it to give your result an dhow many occurrences did you get - 331 like me?
I have 16 GB and an i7 processor. Granted, that the Excel machine is searching down 31,103 rows, but still, it should not take 10 seconds. This is way too long.
cr

 

[Trebor76]

Robert - after your post above, I got curious and typed "amen" w/out cap A. It found 'amen' in firmament in Gen 1:6 - but get this - it took 10 seconds!
The screen turned white for about 2-3 secs and the results displayed. My code gave 331 occurrences from Gen to Rev of 'amen'.

But I am greatly bothered by the time of 10 seconds. How long did it to give your result an dhow many occurrences did you get - 331 like me?
I have 16 GB and an i7 processor. Granted, that the Excel machine is searching down 31,103 rows, but still, it should not take 10 seconds. This is way too long.

Using the User Defined Function from post eight it took about one second to produce 72 verses (this number reconciles here). I dare you have a lot more going in your workbook than mine does though.

Sorry for the long reply. I feel like this 5 step process above is not really that difficult to do in Access
1) have an already existing Table1
2) Empty Table1
3) Do the query
4) copy the query results to Table1
5) display Table1 in Textbox1

Ensure the qmakSeach query I put the SQL for in post five is on your Db and then assign this code to the form button (mine was called "cmdTestCode") you want to populate the text box with its results:

Private Sub cmdTestCode_Click()

    Dim rs As Recordset
    Dim db As Database
    Dim strSQL As String

    With DoCmd
        .SetWarnings False
        .OpenQuery "qmakSearch" 'qmakSearch is a make table query that must exist on the Db
        .SetWarnings True
    End With
  
    If DCount("*", "tblSearchResults") > 0 Then
        Me.Controls("txtMatchedVerses").Value = "" 'Clear - Note txtMatchedVerses is the name of the text box thta must exist on the form to receive the results of the SQL query.
        strSQL = "SELECT * FROM tblSearchResults;"
        Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
        rs.MoveFirst
        Do Until rs.EOF
            Me.Controls("txtMatchedVerses").Value = IIf(Len(Me.Controls("txtMatchedVerses").Value) = 0, rs.Fields(0).Value, Me.Controls("txtMatchedVerses").Value & vbNewLine & rs.Fields(0).Value)
            rs.MoveNext
        Loop
        Set rs = Nothing: Set db = Nothing
        MsgBox "There were " & DCount("*", "tblSearchResults") & " records found.", vbInformation
    Else
        MsgBox "There were no records found.", vbExclamation
    End If

End Sub

Hope that helps.

Robert

 

[chazrab]

2:14 PM Tues 2/20/24:
Hi Robert - just getting to this now - been a busy day, but wanted again to acknowledge your help and say thanks again.
Give me time to study your code and work it in to the Access code in the form I have now.
Will be back in touch.

Thx again, cr

 

[chazrab]

Robert..This works exactly as it does in the Excel app.
Each Textbox in the Access app will have a button with this code, changing only the code references to the query names and Textbox names. . Some will be hidden and can be made visible by setting TextboX.visible =true and TextboxX.bringToFront.

I think I can take it from here. Thanks again for all your help. You spent a lot of time helping me get to this point. I really appreciate it.
cr

 

[chazrab]

Robert - I hope you don't mind with one more question - that little "Enter parameter box - can that be replaced with a textbox directly
on the form where the cmdTestCode(Go) button gets its search value? A user can type anything in the textbox on the form and will
display all values in the large textbox.

Thanks again for all your help.

cr

 

[Trebor76]

Hi Charles,

Glad to hear we've made good progress

The following is the SQL for my query qmakSearch that now use a text box called "txtSearchCriteria" on a form called "frmSearchEngine" (change to your names) to create a recordset:

SELECT tblTheBible.KJV INTO tblSearchResults
FROM tblTheBible
WHERE tblTheBible.KJV Like "*" & [Forms]![frmSearchEngine]![txtSearchCriteria] & "*";

Regards,

Robert

 

[chazrab]

HI Robert - now working on refining the application. I wrote this code in a button to expand the now populated textbox txtMatchedVerses for easier reading and return it back to its normal width with a Dblclick event in the textbox:
original width = 5.125" in Properties setting.

Private Sub cmdXpandtxtMatches_Click()
Me.txtMatchedVerses.Width = 10.2743
Me.txtMatchedVerses.Visible = True
'Textbox2.Visible = False
End Sub

Private Sub cmdXpandtxtMatches_DblClick(Cancel As Integer)
txtMatchedVerses.Width = 5.125
Textbox2.Visible = True
End Sub

When I click the button and the code runs, txtMatchedVerses disappears even when I set the visible property to true.
Has to be something simple.   
Can you help ? 
cr

 

[Trebor76]

Hi Charles,

As per this link:

The default unit of measure in VBA is twips. One twip is 1/1440 of an inch.

So this should do the job:

Private Sub txtMatchedVerses_DblClick(Cancel As Integer)

    'https://www.tek-tips.com/viewthread.cfm?qid=677946
    'The default unit of measure in VBA is twips (you can change it with code).
    'One twip is 1/1440 of an inch.
    
    Controls("txtMatchedVerses").Width = 1440 * 5.125

End Sub

Note the change won't save after you close the form as only changes made in design mode do (refer here for a possible (clunky) solution) but this may be what you want as the original width setting will remain.

Did you resolve the FIND issue with the data using Access?

Regards,

Robert

 

[chazrab]

Hi Robert. I put the expand code in a button on the form:

Private Sub cmdXpandtxtMatches_Click()
 Controls("txtMatchedVerses").Width = 1440 * 10.274
 Textbox2.Visible = False
End Sub

And the code for returning to normal size in the textbox Dbllick event:

Private Sub txtMatchedVerses_DblClick(Cancel As Integer)
txtMatchedVerses.Width = 7380
End Sub

I closed Access and reopened it. Everything works great. I had no idea what a twip unit was.

Coding FIND in Access to work correctly was too much of a bear. I was able to get the same results as FIND does in Excel with SQL statements and a new MakeTable query combined with your code earlier:

Dim rs As Recordset
    Dim db As Database
    Dim strSQL As String
    With DoCmd
        .SetWarnings False
        .OpenQuery "qmakSearch" 'qmakSearch is a make table query that must exist on the Db
        .SetWarnings True
    End With
    If DCount("*", "tblSearchResults") > 0 Then
       Me.Controls("txtMatchedVerses").Value = "" 'Clear - Note txtMatchedVerses is the name of the text box thta must exist on the form to receive the results of the SQL query.
        strSQL = "SELECT * FROM tblSearchResults;"
        Set db = CurrentDb: Set rs = db.OpenRecordset(strSQL)
        rs.MoveFirst
        Do Until rs.EOF
            Me.Controls("txtMatchedVerses").Value = IIf(Len(Me.Controls("txtMatchedVerses").Value) = 0, rs.Fields(0).Value, Me.Controls("txtMatchedVerses").Value & vbNewLine & rs.Fields(0).Value)
            rs.MoveNext
        Loop
        Set rs = Nothing: Set db = Nothing
        MsgBox "There were " & DCount("*", "tblSearchResults") & " records found.", vbInformation
    Else
        MsgBox "There were no records found.", vbExclamation
    End If

SQL:

SELECT tblTheBible.KJV INTO tblSearchResults
FROM tblTheBible
WHERE tblTheBible.KJV Like "*" & [Forms]![SEARCHF]![txtSearchCriteria] & "*";

The way I see this now, this has to be done for each textbox on the form to be populated. If then or Case is = statements I'm thinking
may work for ALL textboxes just by changing a few items.

In my mind, the absolute best way to achieve this with less lines of code is this needing only 1 input textbox for 4 populated large textboxes just by changing Query --->to table --> to specific textbox on the form to be populated references.
A challenge, but well worth doing for the result it will give. Working on this now.

I'm picking up Access pretty fast now. A few lines of SQL does the same thing as an Excel FIND method. BTW - running this app in tests in Access now is giving me instantaneous results - way faster than Excel. Maybe because its an Access table and not a sheet...

Take care, and many thanks again for all your help.

cr

 

[Trebor76]

Hi Charles,

I had no idea what a twip unit was

Neither did I to be honest

Coding FIND in Access to work correctly was too much of a bear

One idea I had was to create a recordset using LIKE to reduce the number of records from 31,102 down to say maybe a few hundred and then loop through each of its records (rows) checking word by word and if any matched our search word the code would add it to the tblSearchResults table and to the lbxSearchResults listbox on the form. I've included a snapshot of the form to give you an idea. This required the verse data to contain only words and spaces and no other characters like numbers, quotes, full stops, commas etc and so took a bit of formatting.
Using this method I was able to reconcile each of the 13 words from here based on KJV of The Bible. If there is more than one word to search for, I just used INSTR.
This did require a lot of VBA though.

Regards,

Robert