Issue with table populated from multi-line text box using VBA

[angusdcrouse]

Hello all,

I'm facing an issue that results in my SELECT query not returning all the results I am expecting. This could be due to an issue with my query or tables but I think it's most likely due to my usage of VBA in populating one of my tables hence why I am posting in the VBA section.

In a very simple case have 2 tables:

• [product] - containing all products with fields for productName and productNumber.
• [productSearch] - which contains a list of all productNames the user wants to search for

I then have a SELECT query, [productSearchQuery] that joins these 2 tables and returns the productNumber for all matching items from the productSearch table.

SELECT product.*
FROM productSearch
INNER JOIN product ON productSearch.productSearchName = product.productName;

When I populate the [productSearch] table manually and refresh my query all expected results are returned - all very basic and no problems so far.

I then have a very simple form with a multi line text box and the intention is for the user to use this and enter multiple products to search for on separate lines. There is a button below this text box and the related sub reads clears out any old data from the [productSearch] table and inserts new records for each line of the text box.

Private Sub ButtonSearch_Click()
    Dim lines: lines = Split(Me.TextSearchList, vbLf)

        DoCmd.SetWarnings False
        On Error Resume Next
        DoCmd.RunSQL "DELETE * FROM productSearch"
        On Error GoTo 0
        
        Dim i As Long
        For i = 0 To UBound(lines)
            'MsgBox lines(i)
            DoCmd.RunSQL "INSERT INTO productSearch(productSearchName) VALUES ('" & lines(i) & "');"
        Next
        
       DoCmd.SetWarnings True
End Sub

Whilst the sub is successfully populating my [productSearch] table with records for every line of the text box, when I then run my query only the last record from my [productSearch] table is showing in the results even though all other records appear to be valid matches.

If I manually edit one of the problematic records in [productSearch] to a different value and then edit it back to the correct string the query will then correctly show the record so it appears to be an issue with Private Sub ButtonSearch_Click() and how it is populating the [productSearch] table but I'm not really sure why.

I've tried to search for the issue but unfortunately all the results I find are for people asking how to return only the last record which is obviously what I'm trying to avoid.

Thanks

 

[Minty]

Firstly remove the Set Warnings False, as that will hide any useful error messages you might see during development.

Secondly, you need to see the data set you are creating to see if it matches your expectations.
Add a query and view the results

SELECT * FROM productSearch

after your loop has completed and post the results.

 

[angusdcrouse]

Thanks for the suggestion. Re-enabling warnings didn't offer much insight. Just the warning that the table rows would be deleted and then the 2 warnings that new lines would be added to the table.

SELECT * FROM productSearch shows the expected lines when viewing the query datasheet in Access however copy/pasting the result into notepad shows there were some other characters included.

ID productSearchName
13 "ABC
"
14 DEF

Replacing vbLf with vbCrLf in the split function of the sub seems to solve it:

Private Sub ButtonSearch_Click()
    Dim lines: lines = Split(Me.TextSearchList, vbCrLf)

        DoCmd.SetWarnings False
        On Error Resume Next
        DoCmd.RunSQL "DELETE * FROM productSearch"
        On Error GoTo 0
        
        Dim i As Long
        For i = 0 To UBound(lines)
            'MsgBox lines(i)
            DoCmd.RunSQL "INSERT INTO productSearch(productSearchName) VALUES ('" & lines(i) & "');"
        Next
        
       DoCmd.SetWarnings True
End Sub

 

[theDBguy]

Hi. Welcome to AWF!

I think you could have simplified your approach by just using a subform for entering the search items.

 

[Pat Hartman]

There are easier ways. One is to build an In() clause. What you have chosen is the least efficient method of running an append query for each row. If you really want to populate a table (you don't need to), it would be better to open a recordset and use the .AddNew method. At least that eliminates the overhead of running a separate query for each row.

Another thing is that you should be selecting based on the ID which is numeric rather than a string. It is possible that your list includes hidden characters and that is why typing over a row and retyping the "exact" original value seems to give different results.

I've attached an In() example