Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-15-2018, 02:18 PM   #1
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
using ADO recordsource to populate listbox

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

JJSHEP89 is offline   Reply With Quote
Old 11-15-2018, 02:45 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,160
Thanks: 10
Thanked 3,886 Times in 3,829 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: using ADO recordsource to populate listbox

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-15-2018, 02:57 PM   #3
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: using ADO recordsource to populate listbox

Quote:
Originally Posted by pbaldy View Post
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"

JJSHEP89 is offline   Reply With Quote
Old 11-15-2018, 03:03 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,160
Thanks: 10
Thanked 3,886 Times in 3,829 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: using ADO recordsource to populate listbox

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by pbaldy; 11-15-2018 at 03:13 PM. Reason: fix grammar
pbaldy is offline   Reply With Quote
Old 11-15-2018, 03:09 PM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,345
Thanks: 92
Thanked 1,809 Times in 1,684 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: using ADO recordsource to populate listbox

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/...7-7e47f6ea7f83
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders : Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 11-15-2018, 03:51 PM   #6
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: using ADO recordsource to populate listbox

Quote:
Originally Posted by pbaldy View Post
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...

Quote:
Originally Posted by isladogs View Post
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/...7-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
JJSHEP89 is offline   Reply With Quote
Old 11-15-2018, 04:02 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,160
Thanks: 10
Thanked 3,886 Times in 3,829 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: using ADO recordsource to populate listbox

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.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-15-2018, 04:29 PM   #8
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,319
Thanks: 80
Thanked 1,415 Times in 1,335 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: using ADO recordsource to populate listbox

Quote:
Originally Posted by JJSHEP89 View Post
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
Galaxiom is offline   Reply With Quote
Old 11-15-2018, 05:03 PM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,160
Thanks: 10
Thanked 3,886 Times in 3,829 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: using ADO recordsource to populate listbox

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 11-15-2018, 05:24 PM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 666
Thanks: 13
Thanked 158 Times in 156 Posts
MajP will become famous soon enough
Re: using ADO recordsource to populate listbox

I think you may have to add
rst.CursorLocation=adUseClient
MajP is offline   Reply With Quote
Old 11-15-2018, 07:41 PM   #11
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,319
Thanks: 80
Thanked 1,415 Times in 1,335 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: using ADO recordsource to populate listbox

Quote:
Originally Posted by pbaldy View Post
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.
Galaxiom is offline   Reply With Quote
Old 11-16-2018, 04:26 AM   #12
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: using ADO recordsource to populate listbox

I tried this out and got a 438 error "Object doesn't support this property or method"

Quote:
Originally Posted by Galaxiom View Post
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 by JJSHEP89; 11-16-2018 at 04:31 AM.
JJSHEP89 is offline   Reply With Quote
Old 11-16-2018, 07:57 AM   #13
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: using ADO recordsource to populate listbox

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.
JJSHEP89 is offline   Reply With Quote
Old 11-16-2018, 08:13 AM   #14
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 666
Thanks: 13
Thanked 158 Times in 156 Posts
MajP will become famous soon enough
Re: using ADO recordsource to populate listbox

strSQL is a value type (not an object)
rst is a recordset (object)

Use Set when assigning an object.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
JJSHEP89 (11-16-2018)
Old 11-16-2018, 09:21 AM   #15
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,160
Thanks: 10
Thanked 3,886 Times in 3,829 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: using ADO recordsource to populate listbox

Quote:
Originally Posted by JJSHEP89 View Post
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.

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
query for recordsource of listbox keeps changing bobmac- Forms 0 09-27-2011 04:04 PM
Query sort problem (when set as recordsource in listbox) phxbypdx Queries 1 08-16-2010 02:11 PM
Change a Listbox recordsource with the use of Option Buttons spnz Forms 6 05-27-2005 11:18 AM
Listbox recordsource value jrjr Forms 2 05-27-2005 05:19 AM
ListBox not updating form selection when changing listbox recordsource Help.Chris Forms 1 02-22-2002 07:52 AM




All times are GMT -8. The time now is 05:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World