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

 

[Trebor76]

Hi Charles,

Your VBA can much faster if you paste its results in a single command like the following examples I've written:

Option Explicit
Sub FindFilter()
   
    'Needs a header to work reliably or else Genesis 1:1 will always be copied as part of the filter
    Dim wsSrc As Worksheet, wsOutput As Worksheet
    Dim rngAllData As Range, rngFiltrData As Range
    Dim strSearchText As String
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
   
    Set wsSrc = ThisWorkbook.Sheets("Sheet2"): Set wsOutput = ThisWorkbook.Sheets("MAINARES2")
    On Error Resume Next
        wsSrc.ShowAllData: wsOutput.ShowAllData
    On Error GoTo 0
    wsOutput.UsedRange.ClearContents
   
    strSearchText = "last days"
   
    wsSrc.Range("E1:J1103").AutoFilter Field:=1, Criteria1:="*" & strSearchText & "*", Operator:=xlFilterValues
    Set rngFiltrData = wsSrc.Range("E1:J31103").SpecialCells(xlCellTypeVisible)
   
    With wsOutput
        rngFiltrData.Copy Destination:=.Range("B1")
        .Range("H1").Value = Evaluate("SUBTOTAL(3,'" & wsSrc.Name & "'!E1:E31103)")
        .Range("I1").Value = strSearchText
    End With
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub
Sub FindSQL()

    Dim wsSrc As Worksheet, wsOutput As Worksheet
    Dim objConn As Object, objRS As Object
    Dim strSearchText As String, strSQLStmt As String, strConnString As String
   
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
   
    Set wsSrc = ThisWorkbook.Sheets("Sheet2"): Set wsOutput = ThisWorkbook.Sheets("MAINARES2")
    On Error Resume Next
        wsSrc.ShowAllData: wsOutput.ShowAllData
    On Error GoTo 0
    wsOutput.UsedRange.ClearContents

    strSearchText = "last days"
    strSQLStmt = "SELECT * FROM [" & wsSrc.Name & "$E1:J31103] WHERE [F1] Like '%" & strSearchText & "%';"
    strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=NO"";"
   
    Set objConn = CreateObject("ADODB.Connection"): Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open strConnString
   
    With objRS
        .CursorType = 3 'adOpenStatic For a list of these literal values refer http://www.w3schools.com/asp/prop_rs_cursortype.asp
        .Open strSQLStmt, objConn
        If .RecordCount = 0 Then
            MsgBox """" & strSearchText & """ was not found."
        Else
            With wsOutput
                .Range("B1").CopyFromRecordset objRS
                .Range("H1").Value = objRS.RecordCount
                .Range("I1").Value = strSearchText
            End With
        End If
    End With
   
    Set objConn = Nothing: Set objRS = Nothing
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

I think it's worth exploring if Excel can do what you need before embarking on moving to an Access Db. Let me know how it goes.

Regards,

Robert

 

[chazrab]

Hi Charles,

Your VBA can much faster if you paste its results in a single command like the following examples I've written:

Option Explicit
Sub FindFilter()
 
    'Needs a header to work reliably or else Genesis 1:1 will always be copied as part of the filter
    Dim wsSrc As Worksheet, wsOutput As Worksheet
    Dim rngAllData As Range, rngFiltrData As Range
    Dim strSearchText As String
 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    Set wsSrc = ThisWorkbook.Sheets("Sheet2"): Set wsOutput = ThisWorkbook.Sheets("MAINARES2")
    On Error Resume Next
        wsSrc.ShowAllData: wsOutput.ShowAllData
    On Error GoTo 0
    wsOutput.UsedRange.ClearContents
 
    strSearchText = "last days"
 
    wsSrc.Range("E1:J1103").AutoFilter Field:=1, Criteria1:="*" & strSearchText & "*", Operator:=xlFilterValues
    Set rngFiltrData = wsSrc.Range("E1:J31103").SpecialCells(xlCellTypeVisible)
 
    With wsOutput
        rngFiltrData.Copy Destination:=.Range("B1")
        .Range("H1").Value = Evaluate("SUBTOTAL(3,'" & wsSrc.Name & "'!E1:E31103)")
        .Range("I1").Value = strSearchText
    End With
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub
Sub FindSQL()

    Dim wsSrc As Worksheet, wsOutput As Worksheet
    Dim objConn As Object, objRS As Object
    Dim strSearchText As String, strSQLStmt As String, strConnString As String
 
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
 
    Set wsSrc = ThisWorkbook.Sheets("Sheet2"): Set wsOutput = ThisWorkbook.Sheets("MAINARES2")
    On Error Resume Next
        wsSrc.ShowAllData: wsOutput.ShowAllData
    On Error GoTo 0
    wsOutput.UsedRange.ClearContents

    strSearchText = "last days"
    strSQLStmt = "SELECT * FROM [" & wsSrc.Name & "$E1:J31103] WHERE [F1] Like '%" & strSearchText & "%';"
    strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=NO"";"
 
    Set objConn = CreateObject("ADODB.Connection"): Set objRS = CreateObject("ADODB.Recordset")
    objConn.Open strConnString
 
    With objRS
        .CursorType = 3 'adOpenStatic For a list of these literal values refer http://www.w3schools.com/asp/prop_rs_cursortype.asp
        .Open strSQLStmt, objConn
        If .RecordCount = 0 Then
            MsgBox """" & strSearchText & """ was not found."
        Else
            With wsOutput
                .Range("B1").CopyFromRecordset objRS
                .Range("H1").Value = objRS.RecordCount
                .Range("I1").Value = strSearchText
            End With
        End If
    End With
 
    Set objConn = Nothing: Set objRS = Nothing
 
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

I think it's worth exploring if Excel can do what you need before embarking on moving to an Access Db. Let me know how it goes.

Regards,

Robert

Hi Robert - haven't heard from you in a long time - as you may recall you've helped me greatly in the past and I really appreciate it.

Last night, I decided to give up trying to move this Excel app over to Access - until I heard from you early this AM. I have not stuided your code yet - getting ready to now - just wanted to mention where my head is now with this. So here's where I am now:

You spent some time with your reply. So I'm getting ready to study what you sent me. I just wanted to first acknowledge your help reply. Last thing before I get back to you later:

This whole Excel application I've developed is just a very simple matter of displaying large amounts of text in several textboxes, some of which are hidden in an Excel userform using a Listbox Rowsource as the source for displaying the results in textboxes.

I began the process of learning Access 3 weeks ago and discovered its a different world. The Access VBA is not identical to Excel's VBA. So you can't just copy and paste code from Excel to Access and expect it to run - i.e., Access doesn't know what Sheets("MAIN").Range("A1:B600").value means,
or cells(2,colnum).Select, for example.

To add to that, the initial problem I'm having is that no one who knows both Excel and Access well enough has been able to help convert an SQL query into a Table easily. It seems that an Access query result is just a filter - its result is not a table - Changing an SQL Query result to a table with a a few simple lines of code in Access has not been an easy task. - and to me it should be. Why is that so difficult ? - at least for me.

Here's the deal with me now. I've completely developed this application in Excel, meaning that it does everything I want it to. The problem is, as I've previously mentioned in other posts, is that I'm working with a main data sheet of 31,103 rows - that is the number of verses from Gen to Rev - one sheet row being a verse.

Excel's FIND method and other code I've written takes too much time to process code lines searching down that many rows. Several people have suggested redo this whole app in Access using a database(Access Table) instead of an Excel sheet to store that much data. The tradtional cuustomer orders Norltwind database and all the examples by poeple in MrExcel, Stack Overlfow, etc, have that same mindsel and think interms of table normalization, 1st, 2nd and 3rd normal forms, referential integrity etc. I don't need all that. All I'm doing is FINDING all values and displaying the results in a new Sheet (table in Access), and displaying the results in a textbox - I have learned that the Textbox should be a Memo textbox.

Sorry for the long rant. I have new life about moving from Excel to Access only because I heard from you again. As stated, I was ready to give up on learning Access. I will study your code and see if I can get this to work.

cr

 

[Trebor76]

Hi Charles,

Yes I remember you. It's funny - I'm not overly religious but your posts interest me.

I know a little about Access. I have built databases that just store the data and use Excel to report on it and standalone applications. The SQL solution in my second macro could easily be used on an Access table.

I have the 31,103 verses and the above macros ran in less than in a few seconds. I hope they work for you.

Regards,

Robert

 

[Trebor76]

To add to that, the initial problem I'm having is that no one who knows both Excel and Access well enough has been able to help convert an SQL query into a Table easily. It seems that an Access query result is just a filter - its result is not a table - Changing an SQL Query result to a table with a a few simple lines of code in Access has not been an easy task. - and to me it should be. Why is that so difficult ? - at least for me.

So if I have an Access table called tblTheBible and a field (column) called KJV the following query (which I called qmakSearch) will prompt for input and will create or populate a table called tblSearchResults with the records that were found:

SELECT tblTheBible.KJV INTO tblSearchResults
FROM tblTheBible
WHERE tblTheBible.KJV Like "*" & [Enter your search text:] & "*";

Hope that helps.

Robert

 

[chazrab]

Hi Charles,

Yes I remember you. It's funny - I'm not overly religious but your posts interest me.

I know a little about Access. I have built databases that just store the data and use Excel to report on it and standalone applications. The SQL solution in my second macro could easily be used on an Access table.

I have the 31,103 verses and the above macros ran in less than in a few seconds. I hope they work for you.

Regards,

Robert

Hi Robert. So, based on your comments, I'm sticking with Excel. I can display the results of a FIND method with the code I've posted that you have, also in a few seconds - That will have to do. The main problem is that the Excel app stops working every once in a while - 'not responding'. Then I have to use the Task Manger to close Excel and end the operation.

To this point, I keep thinking its having trouble looking with the row size. Then again, I wonder if I should go and spend $2 - 3,000 on the most powerful laptop I can get from BB - with 20 GB or more and an i8 processor - and see if that would make a difference. I just can't afford to have it keep crashing every once in a while. That should not happen. Then again, maybe its the way the code is written - fewer lines of code or an autofilter vs the FIND method.

I don't consider myself religious as the world defines religion. I am a very curious and detailed oriented person. My father was an accountant who loved to read about world history as a hobby. My Mom was an RN who loved helping people. I like to think I inherited the best DNA from each.

Part of my inspiration to learn as much about this life and the next and the ultimate destiny for all creation comes from what architect Bucky Fuller once said about Frank Lloyd Wright - whose homes lend a air of mystery in their design - he said about FLW: "...he was one of those few people who realized that the more we learn in life, the more we realize how little we know" I took that a step further and added both about ourselves and our eternal destination. Time and life are passing way too fast. And one thing I wanted to do before I leave here was to develop a Bible Application that would enable a user to do multiple verse and/or phrase searches simultaneously - like a Bible teacher saying turn to Matthew 24:15, read that, hold your finger there, and turn to Daniel 7:25, read that, hod another finger there or put a bookmark there, and turn to Rev 13.

This application does exactly that with up to 4 different textboxes displaying the results of 4 different searches giving 4 different verses, each being connected to each other and each having its own Listbox Rowsource as its data source to display in each textbox.

Images below show the flexibility of multiple view displays. This a very important concept when doing a detailed Bible study - having the ability to view multiple passages or verses at the same time on one screen view.

One thing that is happening here as I continue to develop this, is that the functional capability to do a serious and detailed study of the Bible built with a software is growing - meaning for one, that more and more connections are being made that reference the same person or event at different points in time. This reveals one thing that may not seem obvious at first to those whose hobby is Bible study is-that the 1) exact same events are repeated
and 2) there is an appointed time for all events to occur.

Our pastor, my 40 year old daughter and several others in life I know, and I have a friendly disagreement about free will and predestination and that the two contradict each other. These are those who follow the tenets of 5 point Calvinism.

No they don't. There is absolutely no contradiction between humanity having the freedom to make independent choices and decisions in this life and the determination of our eternal destiny for this reason:

God have us the freedom to make the choices He already knows we're going to make. No big deal. No PhD in Theology. It's just that simple,


Take the Book of Daniel for instance: Daniel was exiled to Babylon by King Nebuchadnezzar in 609 BC. He was about 16 years old and came from a well to do family in Jerusalem. While there, he experienced visions from the angel Gabriel, God's messenger and other angelic beings that told of the end time even that will come upon this world in the last days. I discovered a connection in a string of verses that point to exactly to a series of identical events.. this stirred my interest to keep going with this. I could go on, but you get this point.

Please feel free to contact me back if you have any questions and/or comments..

I'm staying with Excel and hopefully won't have to spend $3K on a new laptop - yet.

cr

 

[Trebor76]

Hi Charles,

Gee there's a lot of work there. You must have a large number of textboxes which may impact on things.

I've included an image of how I would create the source data tab if I were to build the application in Excel which could be also used as a table in Access (just a suggestion).

Good luck with everything.

Regards,

Robert

 

[Trebor76]

Hi Charles,

I just noticed that my macros are not giving accurate results - the reason being they are finding text within a word ("Amen" was being found in "And God said, Let there be a firmament in the midst of the waters, and let it divide the waters from the waters") or missing the word if it has a character after it like a comma or full stop after it ("Amen" was not being found in "And blessed be His glorious name for ever: and let the whole earth be filled with his glory; Amen, and Amen.").

The following macro and User Defined Function (udf) resolves the issue and it's still very fast (note this would also have be incorporated into an Access Db should you go down that path):

Option Explicit
Sub FindUsingCustomFunction()

    Dim wsSrc As Worksheet, wsOutput As Worksheet
    Dim rngCell As Range, rngResults As Range
    Dim strSearchText As String
    Dim i As Long
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    Set wsSrc = ThisWorkbook.Sheets("Sheet2"): Set wsOutput = ThisWorkbook.Sheets("MAINARES2")
    On Error Resume Next
        wsSrc.ShowAllData: wsOutput.ShowAllData
    On Error GoTo 0
    wsOutput.UsedRange.ClearContents
    strSearchText = "Amen"
    
    For Each rngCell In wsSrc.Range("E1:E31103")
        If CheckForFullString(strSearchText, CStr(rngCell)) = True Then 'https://www.tek-tips.com/viewthread.cfm?qid=68548
            i = i + 1
                If rngResults Is Nothing Then
                    Set rngResults = wsSrc.Range("E" & rngCell.Row & ":J" & rngCell.Row)
                Else
                    Set rngResults = Union(rngResults, wsSrc.Range("E" & rngCell.Row & ":J" & rngCell.Row))
                End If
            End If
    Next rngCell

    If i > 0 Then
        With wsOutput
            rngResults.Copy Destination:=.Range("B1")
            .Range("H1").Value = Format(i, "#,##0")
            .Range("I1").Value = strSearchText
        End With
    Else
        MsgBox "There were no verses found that contained the text """ & strSearchText & """.", vbInformation
    End If
    
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub
Function CheckForFullString(StrIn As String, FieldIn As String) As Boolean

    'Check for the existance of StrIn within FieldIn as a WHOLE word
    'https://www.tek-tips.com/viewthread.cfm?qid=68548

    Dim SubStrPos As Long
    Dim FieldLen As Long
    Dim SubStrLen As Long
    Dim EndChr(1) As String * 1             'Lead/Trail Chrs
    Dim OkFlg As Boolean
    Dim idx As Long

    SubStrLen = Len(StrIn)
    FieldLen = Len(FieldIn)

    SubStrPos = 1               'Init Start Pos for InStr

    'Just check if it exists
    SubStrPos = InStr(SubStrPos, FieldIn, StrIn)

    'Trivial Cases: Not Present.
    If (SubStrPos = 0) Then                 'Pos = 0 ==> Not here
        CheckForFullString = False       'Redundant/ more to SHOW
        Exit Function                       'Go Back
    End If
    
    'We only get here if StrIn is part of FieldIn
    'Trivial Cases: Whole Field
    If (SubStrLen = FieldLen) Then          'The SubStr IS the Field
        CheckForFullString = True
        Exit Function
    End If

    'At least POSSIBLE complexity
    Do While SubStrPos <> 0     'Loop for all occurances of StrIn

        'Make sure the ends are not leftovers
        EndChr(0) = ""
        EndChr(1) = ""

        'Now, we know it is there - SOMEWHERE
        If (SubStrPos = 1) Then                 'SubStr at start
            'Get char following (If any)
            EndChr(1) = Mid(FieldIn, SubStrPos + SubStrLen)
            GoSub ChkEndChr
            If (OkFlg) Then
                'It exists as a "Whole Word", so just say so
                CheckForFullString = True
                Exit Function
             Else
                'BUT, the next char is 'normal' so it's NOT a match
                GoTo NxtPos
            End If
        End If

        If (SubStrPos + SubStrLen - 1 = FieldLen) Then   'SubStr at End
            'Get char Preceeding (If any)
            EndChr(0) = Mid(FieldIn, SubStrPos - 1)
            GoSub ChkEndChr
            If (OkFlg) Then
                CheckForFullString = True
                Exit Function
             Else
                'BUT, the next char is 'normal' so it's NOT a match
                GoTo NxtPos
            End If
        End If

        'Here, only if StrIn is 'buried' in FieldIn
        EndChr(0) = Mid(FieldIn, SubStrPos - 1)
        EndChr(1) = Mid(FieldIn, SubStrPos + SubStrLen)
        GoSub ChkEndChr
        If (OkFlg) Then
           CheckForFullString = True
           Exit Function
         Else
           'BUT, the next char is 'normal' so it's NOT a match
           GoTo NxtPos
        End If

NxtPos:
        'See if there is another instance.
        SubStrPos = InStr(SubStrPos + 1, FieldIn, StrIn)
    Loop

    GoTo NormExit

ChkEndChr:
    OkFlg = True    'Assume the BEST

    For idx = 0 To UBound(EndChr)
        Select Case UCase(EndChr(idx))
            Case "A" To "Z"
                OkFlg = False
                Return
            Case "0" To "9"
                OkFlg = False
                Return
        End Select
    Next idx

    Return          'Must Be O.K?

NormExit:

End Function

Regards,

Robert

 

[chazrab]

Hi Robert - I'm a little late on this - but - your SQL code works! - meaning, the query result was saved to tblSearchResults
I spent part of yesterday and part of today refining your SQL code - I'm still not there but here's what I mean.

The Excel Bible application uses the FIND method with a Do loop to 1) Find all values of a single word, phrase or verse -
addressing what you said above - you're exactly correct - the Excel FIND method pulls all occurrences of a word or phrase - you would know this - because of the way the optional FIND parameters are structured:
xlPart will find any and every occurrence, at the beginning, middle or end of a verse in an Excel.
xlWhole finds whole cell values. If exact text to find is missing one dot or letter, Excel won't pick it up - as it should be.

With Worksheets("Sheet2").Range("E:E311031")
      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)  --> need to replicate in Access VBA
                                           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)--> need to replicate in Access VBA
                                            Else
                                            End If
                              Else
                                 MsgBox "value not found"
                              End If
                     End With

The mechanics of the application is that it
1) for any word or phrase down col E in this case (Range("E:E311031") ), finds all occurrences with the loop,
2)copies each one to its own sheet (MAINARES2 for Textbox1 in this case) (or any existing Acccess table).
3 and displays the rows in its connected Sheet in Textbox1 The point is, I don't have to create a new sheet every time. 4 textboxes all display different FIND results each from its own sheet.

The only way I've been able to accomplish this is by clearing the contents of each respective sheet every time I want to do a new search
with this

Sheets("MAINARES2").UsedRange.ClearContents

The Access code to do this right has to
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

The way I've been able to display the results of a MAINARE2(or any sheet[Table in Access], in Textbox1for example is like this:

Dim n As Long
n = ListBox1.ListIndex
Me.TextBox1.Value = ListBox1.List(n, 3) _
& vbCrLf _
& ListBox1.List(n + 1, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 2, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 3, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 4, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 5, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 6, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 7, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 8, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 9, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 10, 3) _
& vbCrLf _
& vbCrLf + ListBox1.List(n + 11, 3) _
& vbCrLf _

This so simple to do in Excel. To Access, the code, if copied over is like a foreign language.
I think, but am not sure yet, as I am on a slow moving train trying to learn Acees VBA/SQL

that this can be accomplished using vbnewline. But I'm not to the displaying Table1 in Textbox1 yet. That's the very last thing in this

Robert, if you could give me some guidance when you have a chance on adding the correct SQL or Access VBA code to what we have already,
I'd appreciate it. I'm using buttons in trial and error attempts now.

You 've been most helpful, and right now, based on your help with this Access VBA code so far - that of copying a query result to a table, I'm am not giving up on moving this app to Access.

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

Thanks again for all your help. I look forward to hearing from you.

cr

 

[chazrab]

Robert - not sure if reply posted...