Listbox Rowsource Public Function Paramaters Type Mismatch? 1

[misscrf]

I have a listbox, which I set the row source on click of a filter type button on a form. The row source I set is a public function, which takes 2 parameters. Originally I tried a stored procedure, but found that you cannot set the row source for a listbox to a stored proc. I converted this to dynamic sql. here is the function:

Public Function AssignedMediaFiles(ByVal pkey As String, AID As Integer)
Dim objConn As ADODB.connection
Dim objRS As ADODB.Recordset
Dim strSQL
Set objConn = CreateObject("ADODB.Connection")
objConn.connectionString = "Driver={SQL Server Native Client 11.0};Server=LIT-SQL;Database=123456Inventories;Trusted_Connection=yes;"
Set objRS = CreateObject("ADODB.Recordset")
objConn.Open
strSQL = "WITH SourceStuff AS (SELECT a.ID AS AID, asd.ID AS ASDID, a.MediaTag, asd.txtSourceTag, fl.id AS FLID, asd.txtSourceTag  AS AssignedSources " & _
"FROM [123456ProjectManagement].[dbo].[tblMediaTrackingNew] a (NOLOCK) " & _
"JOIN [123456ProjectManagement].[dbo].[tblMediaSourceDetail] asd (NOLOCK) ON a.ID = asd.FKMediaTag  " & _
"JOIN [123456ProjectManagement].[dbo].[tblMediaSourceInventory] asi (NOLOCK) ON asd.ID = asi.FKSource  " & _
"JOIN [123456Inventories].[dbo].[tbl" & pkey & "FileList] fl (NOLOCK) ON asi.FKInventory = fl.id)  " & _
"SELECT DISTINCT t1.AID, t1.FLID, STUFF((SELECT DISTINCT '; ' + /*(NULLIF(AssignedSources,''),*/ AssignedSources  " & _
"FROM SourceStuff t2 WHERE t1.FLID = t2.FLID ORDER BY '; ' + AssignedSources FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS AssignedSources  " & _
"FROM SourceStuff t1 (NOLOCK)  " & _
"JOIN [123456Inventories].[dbo].[tbl" & pkey & "FileList] fl (NOLOCK) ON t1.FLID = fl.id  " & _
"JOIN [123456ProjectManagement].[dbo].[tblMediaSourceInventory] asi (NOLOCK) ON fl.id = asi.FKInventory  " & _
"JOIN [123456ProjectManagement].[dbo].[tblMediaSourceDetail] asd (NOLOCK) ON asi.FKSource = asd.ID  " & _
"JOIN [123456ProjectManagement].[dbo].[tblMediaTrackingNew] a (NOLOCK) ON asd.FKMediaTag = a.ID  " & _
"WHERE a.ID = " & AID & " AND t1.AssignedSources IS NOT NULL"

With objRS
   .Source = strSQL
   .ActiveConnection = objConn
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
End With
objRS.Open , , , , adCmdText
If Not objRS.EOF Then
   AssignedMediaFiles = objRS.GetRows
End If

objRS.Close
Set objRS = Nothing

End Function

Here is the on click event of a command button, which should set the row source for the list box:

Private Sub tgAssigned_Click()
Dim strSQL As String

If Me.tgAssigned = -1 Then
    Me.tgAssigned.Caption = "Viewing Assigned"
    Me.lstInv.Visible = True
    [COLOR=#EF2929][b]Me.lstInv.RowSource = AssignedAssetFiles(Me.Bgroup, Me.AssetID)[/b][/color]
Else
    Me.tgAssigned.Caption = "Viewing All"
    Me.lstInv.Visible = False
End If

End Sub

The .rowsource is in bold red. Can anyone see what I am doing wrong? The specific error I get is
"Run-time error ' 13':
Type mismatch"



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Ok, you can simply build the sql exactly the same as your existing code was doing already doing and set the SQL property of your saved P-T. All of your hard work was done before you even posted to this forum.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I get that you are a wiz at this, but I have no idea what you are telling me to do.

"build the sql exactly the same as your existing code was doing"
Do I put the function parameters and the strsql into the query?
Do I put the actual query, with any random parameters integrated?
Where do I allow for parameters?
What do I put in the code for the public function? Am I calling the p-t?

"set the sql property of your saved p-t"
I made a p-t with the built sql and set the properties for the odbc connect str . Is that all you mean?

Sorry, I am very basic as this level, and still trying to learn how it all works.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Your code might look like:

Public Function AssignRowSource(ByVal pkey As String, AID As Integer)
    Dim strSQL as String
    Dim strOldSQL as String
    Dim strPTQueryName as String   [COLOR=#4E9A06]'name of your pass-through query[/color]
    strPTQueryName = "[Your PT Query Name Here]"

    strSQL = "WITH SourceStuff AS (SELECT a.ID AS AID, asd.ID AS ASDID, a.MediaTag, asd.txtSourceTag, fl.id AS FLID, asd.txtSourceTag  AS AssignedSources " & _
    "FROM [123456ProjectManagement].[dbo].[tblMediaTrackingNew] a (NOLOCK) " & _
    "JOIN [123456ProjectManagement].[dbo].[tblMediaSourceDetail] asd (NOLOCK) ON a.ID = asd.FKMediaTag  " & _
    "JOIN [123456ProjectManagement].[dbo].[tblMediaSourceInventory] asi (NOLOCK) ON asd.ID = asi.FKSource  " & _
    "JOIN [123456Inventories].[dbo].[tbl" & pkey & "FileList] fl (NOLOCK) ON asi.FKInventory = fl.id)  " & _
    "SELECT DISTINCT t1.AID, t1.FLID, STUFF((SELECT DISTINCT '; ' + /*(NULLIF(AssignedSources,''),*/ AssignedSources  " & _
    "FROM SourceStuff t2 WHERE t1.FLID = t2.FLID ORDER BY '; ' + AssignedSources FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS AssignedSources  " & _
    "FROM SourceStuff t1 (NOLOCK)  " & _
    "JOIN [123456Inventories].[dbo].[tbl" & pkey & "FileList] fl (NOLOCK) ON t1.FLID = fl.id  " & _
    "JOIN [123456ProjectManagement].[dbo].[tblMediaSourceInventory] asi (NOLOCK) ON fl.id = asi.FKInventory  " & _
    "JOIN [123456ProjectManagement].[dbo].[tblMediaSourceDetail] asd (NOLOCK) ON asi.FKSource = asd.ID  " & _
    "JOIN [123456ProjectManagement].[dbo].[tblMediaTrackingNew] a (NOLOCK) ON asd.FKMediaTag = a.ID  " & _
    "WHERE a.ID = " & AID & " AND t1.AssignedSources IS NOT NULL"

    debug.Print strSQL

    strOldSQL = fChangeSQL(strPTQueryName, strSQL)

End Function

You need to call this function with pkey and AID values to set update the pass-through query. The p-t query is used as the Row Source property of the list box. So your Row Source might look like:

Row Source: SELECT AID, FLID, AssignedSources FROM [Your p-t query name here]

When ever pkey or AID change, you need to call this function sending in the new values.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Thank you so much for taking the time to do this, Duane. I really appreciate it.

What should my p-t query have in it? Does it matter?
Should it be that same sql statement?
How do I put those parameters in p-t query?
Will it allow them?
Do I declare the variables in there too?

Thanks again!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Your very first sentence in this thread was "I have a listbox, which I set the row source on click of a filter type button on a form." The code I suggested would be run as part or all of the click event.

I would set the SQL of the P-T to be similar to any query but maybe not return any records, possibly an AID of -9999.

Your parameter values would come from where ever they were coming from to feed your previous function.

You don't have to declare anything.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

That's what I was just starting to realize. I have been going through your post and other searches for info on pass through queries. I am trying to put it together now. I will let you know how it goes. Fingers crossed!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

what is the fChangeSQL ? I get a debug error on that. I have everything else set up.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

Nevermind. I found you post with that function. It works!!!! Thank you so much!!!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Glad to hear you got it sorted out prior to me heading out on 10 day holiday!

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

hahaha. I had to back track a bit, but only because I needed to expand the sql statement to provide more info and then I need to set it up a few ways, depending on the joins I need.

Thanks again for all your help!

misscrf

It is never too late to become what you could have been ~ George Eliot