using ADO recordsource to populate listbox (1 Viewer)

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
Let me preface this by saying i am new to the ADO library and all its methods and properties. However, we have moved our backend to a MySQL server and from what i currently understand the ADO library is better to use with ODBC connections than the DAO because it doesn't use the MS Jet engine.....correct me if my thinking is wrong here.


As for the issue,
I have written the following function that takes a user's input from a "Search" textbox on a form and verifies it against data is two separate tables and returns a value based on whichever data matches that in the table..

Quick background on the table structure. Two tables, ToolMatrix & PartMatrix, Tools are used to produced parts, there can be multiple tools that produce the same part & multiple parts produced from the same tool.

The issue that i've run into is when a search returns multiple values. I've been trying to have my function output the results of the recordset to a simple listbox but its not working. I'm currently getting type mismatch errors.

Code:
Public varToolNumber, varPartNumber As Variant

Public Function ValidateEntry(varEntry As Variant) As Variant
Dim varToolNumber As Variant
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

'The ValidateEntry() Function takes an input string from the user and removes the "-16h" section of a job number if input as so. It then verifies that tool number exists _
in the tables and returns that tool number.  If the string was entered as a part number it then verifies the part number exists in the part matrix table and then returns _
the coresponding tool number associated with that part.  If no tool number matches the input string, weather part number, job number, or tool number the function returns a "0".
        
' Section1:::: Determines if entry was input as Job Number
'       Select...Case statement checks to see if the user has entered an invalid entry in the form of a Job number and not a Tool or Part number, the code _
            removes the Job number tag off the end and returns the corrected tool number.
    Select Case Right$(varEntry, 1)
           Case "H", "V", "S"
                If Mid(varEntry, Len(varEntry) - 1, 1) <= 9 And Mid(varEntry, Len(varEntry) - 2, 1) <= 9 And Mid(varEntry, Len(varEntry) - 3, 1) = "-" Then
                            varEntry = Left(varEntry, Len(varEntry) - 4)
                End If
    End Select
    
'Section2:::: Establish ADO Connection for Access & Create Recordset instance
    Set Conn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
        With rst
            Set .ActiveConnection = Conn
                .Source = "SELECT partmatrix.PM_PartNumber AS [Part Number], partmatrix.PM_ToolNumber AS [Tool Number], partmatrix.PM_PartDescription AS Description, " & _
                          "partmatrix.PM_PartStatus AS Status FROM partmatrix WHERE ((partmatrix.PM_PartNumber)= '" & varEntry & "') ORDER BY partmatrix.PM_ToolNumber DESC"
                .LockType = adLockReadOnly
                .CursorType = adOpenStatic
                .Open
        End With
        
'Section2:::: Sets varToolNumber equal to the tool number if entered as tool number or part number
    If Not IsNull(DLookup("TM_ToolNumber", "ToolMatrix", "TM_ToolNumber ='" & varEntry & "'")) Then
        varToolNumber = varEntry
    ElseIf Not IsNull(DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber ='" & varEntry & "'")) Then
        If rst.RecordCount > 1 Then
            DoCmd.OpenForm "PopUp_MultiSelect"  ', acNormal, , "partmatrix.PM_PartNumber = '" & varEntry & "'", acFormReadOnly, acWindowNormal
            Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = rst

        Else
            varToolNumber = DLookup("PM_ToolNumber", "PartMatrix", _
                "PM_PartNumber ='" & varEntry & "'")
        End If
        rst.Close
        Conn.Close
        Set rst = Nothing
    Else: varToolNumber = "0"
    End If
     
    ValidateEntry = varToolNumber
End Function
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
It looks like you're using linked tables, so I doubt ADO/DAO makes any difference. I use DAO against SQL Server linked tables all the time.

Where do you get the error? I'm not sure you can assign the row source to a recordset, but I've never tried. I'd just set the row source to the SQL.
 

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
It looks like you're using linked tables, so I doubt ADO/DAO makes any difference. I use DAO against SQL Server linked tables all the time.

Where do you get the error? I'm not sure you can assign the row source to a recordset, but I've never tried. I'd just set the row source to the SQL.

the error pops up from this line here...

Code:
Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = rst


I copied the SQL over like this and it worked just fine so it has to be something with trying to assign the recordset to the rowsource like you said.

Code:
Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = "SELECT partmatrix.PM_PartNumber AS [Part Number], partmatrix.PM_ToolNumber AS [Tool Number], partmatrix.PM_PartDescription AS Description, " & _
                          "partmatrix.PM_PartStatus AS Status FROM partmatrix WHERE ((partmatrix.PM_PartNumber)= '" & varEntry & "') ORDER BY partmatrix.PM_ToolNumber DESC"
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
I won't say it can't be done, but the property expects a string: a table, query, or SQL, so my gut says it can't. Somebody will probably prove me wrong. ;)

If you use a variable for the recordset SQL, you can use it for this too. I tend to use variables if I'm going to need something more than once.
 
Last edited:

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
I won't say it can't be done, but the property expects a string: a table, query, or SQL, so my gut says it can't. Somebody will probably prove me wrong. ;)

If you use a variable for the recordset SQL, you can use it for this too. I tend to use variables if I'm going to need something more than once.

Here's what i've done to get it to work for me.

Code:
Public Function ValidateEntry(varEntry As Variant) As Variant
Dim varToolNumber As Variant
Dim strSQL As String
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

'The ValidateEntry() Function takes an input string from the user and removes the "-16h" section of a job number if input as so. It then verifies that tool number exists _
in the tables and returns that tool number.  If the string was entered as a part number it then verifies the part number exists in the part matrix table and then returns _
the coresponding tool number associated with that part.  If no tool number matches the input string, weather part number, job number, or tool number the function returns a "0".
        
' Section1:::: Determines if entry was a valid input or as Job Number
'       Select...Case statement checks to see if the user has entered an invalid entry in the form of a Job number and not a Tool or Part number, the code _
            removes the Job number tag off the end and returns the corrected tool number.
    Select Case Right$(varEntry, 1)
           Case "H", "V", "S"
                If Mid(varEntry, Len(varEntry) - 1, 1) <= 9 And Mid(varEntry, Len(varEntry) - 2, 1) <= 9 And Mid(varEntry, Len(varEntry) - 3, 1) = "-" Then
                            varEntry = Left(varEntry, Len(varEntry) - 4)
                End If
    End Select
    
'Section2:::: Establish ADO Connection for Access & Create Recordset instance
    strSQL = "SELECT partmatrix.PM_PartNumber AS [Part Number], partmatrix.PM_ToolNumber AS [Tool Number], partmatrix.PM_PartDescription AS Description, " & _
                          "partmatrix.PM_PartStatus AS Status FROM partmatrix WHERE ((partmatrix.PM_PartNumber)= '" & varEntry & "') ORDER BY partmatrix.PM_ToolNumber DESC"
    Set Conn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
        With rst
            Set .ActiveConnection = Conn
                .Source = strSQL
                .LockType = adLockReadOnly
                .CursorType = adOpenStatic
                .Open
        End With
        
'Section2:::: Sets varToolNumber equal to the tool number if entered as tool number or part number
    If Not IsNull(DLookup("TM_ToolNumber", "ToolMatrix", "TM_ToolNumber ='" & varEntry & "'")) Then
        varToolNumber = varEntry
    ElseIf Not IsNull(DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber ='" & varEntry & "'")) Then
        If rst.RecordCount > 1 Then
            DoCmd.OpenForm "PopUp_MultiSelect"  ', acNormal, , "partmatrix.PM_PartNumber = '" & varEntry & "'", acFormReadOnly, acWindowNormal
            Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = strSQL
        Else
            varToolNumber = DLookup("PM_ToolNumber", "PartMatrix", _
                "PM_PartNumber ='" & varEntry & "'")
        End If
        rst.Close
        Conn.Close
        Set rst = Nothing
    Else: varToolNumber = "0"
    End If
     
    ValidateEntry = varToolNumber
End Function

It seems like overkill now though to establish the recordset just to simply count the number of records returned...

Just to say I agree that it can't be done as a recordset is an object not a string.
This link seems to be further confirmation https://answers.microsoft.com/en-us/msoffice/forum/msoffice_access-msoffice_custom/using-a-recordset-as-the-rowsource-for-a-multi/6e9798b6-58df-4d62-80b7-7e47f6ea7f83


This makes sense, I was thinking of them more as an array than an object... the ADO and DAO libraries are all still pretty new to me, been reading a lot but there's still a ton to learn
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
I'd just use a DCount() to get the count. You could use a variable for its criteria, then use the same variable on the end of an SQL string for the row source.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:12
Joined
Jan 20, 2009
Messages
12,849
the error pops up from this line here...

Code:
Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = rst

I have not read the whole thread but that line would need to be:
Code:
Set Forms("PopUp_MultiSelect").Controls("lstMultipleValues").Recordset = rst
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
Ah, so it can be done, you just need to set the Recordset property rather than the RowSource? Probably won't use it, but good to know.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
8,463
I think you may have to add
rst.CursorLocation=adUseClient
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:12
Joined
Jan 20, 2009
Messages
12,849
Ah, so it can be done, you just need to set the Recordset property rather than the RowSource? Probably won't use it, but good to know.

Setting the Recordset is especially handy when the RowSource can't be created from a query. Using a fabricated ADO Recordset allows anything to be put into the listbox then filter and sort the entries much more efficiently than using a List RowSourceType.
 

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
I tried this out and got a 438 error "Object doesn't support this property or method"

I have not read the whole thread but that line would need to be:
Code:
Set Forms("PopUp_MultiSelect").Controls("lstMultipleValues").Recordset = rst

Can you help me to understand when and why the "Set" statement is needed? I've set variables with and without it, i just don't quite understand the difference.

For example:
This works to populate the listbox
Code:
Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = strSQL
and this doesn't, gives me and object required error
Code:
Set Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = strSQL
 
Last edited:

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
So now that i have the query results posting to the listbox on my form, i've written this to execute on the double click event to assign the selected row to my global variables.

Code:
Private Sub lstMultipleValues_DblClick(Cancel As Integer)
    varToolNumber = Me.lstMultipleValues.Column(1)
    varPartNumber = Me.lstMultipleValues.Value
    DoCmd.Close
    Forms("Toolbook").Requery
End Sub

However, the listbox values keep returning null. Its an unbound form and unbound listbox being populated by the SQL query shown in my earlier posts. the listbox is only single select, 4 columns, and i've tried binding columns 1 & 0, but its still only returning Null. :banghead:
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:12
Joined
May 21, 2018
Messages
8,463
strSQL is a value type (not an object)
rst is a recordset (object)

Use Set when assigning an object.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
the listbox is only single select, 4 columns, and i've tried binding columns 1 & 0, but its still only returning Null.

You're sure Multi Select is set to None? I just did a brief test and that worked as expected.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
I guess I'd also check that the variables aren't declared in two places; you could have a scope problem. Add a debug.print in the code and see if the values are there at that point.
 

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
so the popup form itself containing the listbox is unbound, tried dynaset and snapshot recordset types with no change.

the unbound listbox properties are as follows
Row source = 'Populated from the SQL statement prior
Row Source type = Table / Query
Bound Column = 1
Allow Value List Edits = No
Inherit Value List = Yes
Show only row source values = NO
Enabled = Yes
Locked = No
Multi-select = None
 

JJSHEP89

Registered User.
Local time
Today, 10:12
Joined
Aug 18, 2016
Messages
121
I guess I'd also check that the variables aren't declared in two places; you could have a scope problem. Add a debug.print in the code and see if the values are there at that point.

This may be the culprit, although i'm not 100% clear on how.

I added a breakpoint at the DoCmd line and both variables returned Null wile using the form
Code:
Private Sub lstMultipleValues_DblClick(Cancel As Integer)
    varToolNumber = Me.lstMultipleValues.Column(1)
    varPartNumber = Me.lstMultipleValues.Value
    DoCmd.Close
    Forms("Toolbook").Requery
End Sub

All of my global variables are established within a separate module here:
Code:
Public varToolNumber, varPartNumber, varCompID, varUserID, varAction As Variant
Public strRESQL, gstrReason, strUserName, strPermission As String
Public frmCurrent As Form
'------------------------------------------------------------
' Library Function - ValidateEntry()
'------------------------------------------------------------
Public Function ValidateEntry(varEntry As Variant) As Variant
Dim strSQL As String
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset

'The ValidateEntry() Function takes an input string from the user and removes the "-16h" section of a job number if input as so. It then verifies that tool number exists _
in the tables and returns that tool number.  If the string was entered as a part number it then verifies the part number exists in the part matrix table and then returns _
the corresponding tool number associated with that part.  If no tool number matches the input string, weather part number, job number, or tool number the function returns a "0".
        
' Section1:::: Determines if entry was a valid input or as Job Number
'       Select...Case statement checks to see if the user has entered an invalid entry in the form of a Job number and not a Tool or Part number, the code _
            removes the Job number tag off the end and returns the corrected tool number.
    Select Case Right$(varEntry, 1)
           Case "H", "V", "S"
                If Mid(varEntry, Len(varEntry) - 1, 1) <= 9 And Mid(varEntry, Len(varEntry) - 2, 1) <= 9 And Mid(varEntry, Len(varEntry) - 3, 1) = "-" Then
                            varEntry = Left(varEntry, Len(varEntry) - 4)
                End If
    End Select
    
'Section2:::: Establish ADO Connection for Access & Create Recordset instance
    strSQL = "SELECT partmatrix.PM_PartNumber AS [Part Number], partmatrix.PM_ToolNumber AS [Tool Number], partmatrix.PM_PartDescription AS Description, " & _
                          "partmatrix.PM_PartStatus AS Status FROM partmatrix WHERE ((partmatrix.PM_PartNumber)= '" & varEntry & "') ORDER BY partmatrix.PM_ToolNumber DESC"
    Set Conn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
        With rst
            Set .ActiveConnection = Conn
                .Source = strSQL
                .LockType = adLockReadOnly
                .CursorType = adOpenStatic
                '.CursorLocation = adUseClient
                .Open
        End With
        
'Section2:::: Sets varToolNumber equal to the tool number if entered as tool number or part number
    If Not IsNull(DLookup("TM_ToolNumber", "ToolMatrix", "TM_ToolNumber ='" & varEntry & "'")) Then
        varToolNumber = varEntry
    ElseIf Not IsNull(DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber ='" & varEntry & "'")) Then
        If rst.RecordCount > 1 Then
            DoCmd.OpenForm "PopUp_MultiSelect"
            Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = strSQL
            ValidateEntry = "-1"
            Exit Function
        Else
            varToolNumber = DLookup("PM_ToolNumber", "PartMatrix", _
                "PM_PartNumber ='" & varEntry & "'")
        End If
    Else: varToolNumber = "0"
    End If
    rst.Close
    Conn.Close
    Set rst = Nothing
     
    ValidateEntry = varToolNumber
End Function
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 08:12
Joined
Aug 30, 2003
Messages
36,118
Just for chuckles try this instead of populating variables:

MsgBox Me.lstMultipleValues.Column(1)
MsgBox Me.lstMultipleValues.Value

Can you attach the db here?
 

Users who are viewing this thread

Top Bottom