Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Listbox Rowsource Public Function Paramaters Type Mismatch? 1

Status
Not open for further replies.
misscrf

misscrf

Technical User
Jun 7, 2004
1,344
US
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:
Code: 
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:

Code: 
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
 
Sort by date Sort by votes
You have:
[tt]Me.lstInv.RowSource = Assigned[red]Asset[/red]Files(Me.Bgroup, Me.AssetID)[/tt]

but your function is called Assigned[red]Media[/red]Files


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
The rowsource property takes a string. It is the name of a query or table. Thus a type mistmatch.
The recordset property can take a recordset.
 
Upvote 0 Downvote
Sorry. I showed you wrong one. I changed it to AssignedMediaFiles and I still get error.

bgroup and assetid are textbox controls on the form. They get populated in the openargs, which are split and assigned as follows:

Code: 
Private Sub Form_Open(Cancel As Integer)
Dim strOpenArgs As Variant
Dim Arg1 As String
Dim Arg2 As String
Dim Arg3 As String
Dim Arg4 As Integer
Dim Arg5 As Integer


If Len(Me.OpenArgs) > 0 Then
    strOpenArgs = Split(Me.OpenArgs, "|")
    Arg1 = strOpenArgs(0)
    Arg2 = strOpenArgs(1)
    Arg3 = strOpenArgs(2)
    Arg4 = strOpenArgs(3)
    Arg5 = strOpenArgs(4)
    Me.AssetSource = Arg3
    Me.sAsset = Arg1
    Me.sourceID = Arg4
    Me.AssetID = Arg5
    Me.Bgroup = DLookup("ProjectGroupKey", "vw_projectgroups", "MediaProject = '" & Left(Arg1, 6) & "'")
Else
    MsgBox "Something Went Wrong! CRAP!"
    Exit Sub
End If

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Thanks, Maj. I changed it to Me.lstInv.Recordset = AssignedAssetFiles(Me.Bgroup, Me.AssetID)

and now I get a run-time error '91': Object variable without block variable not set.

When I try to debug.print just the public function - AssignedAssetFiles(Me.Bgroup, Me.AssetID) I still get that run time error 13 type mismatch :-(



misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Thank you for responding, dhookom. I'm not sure I understand. How would I do that?

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
First any function should have a return type, yours does not. It therefore returns a variant. In fact it is an array
AssignedMediaFiles = objRS.GetRows (get rows returns an array)

So you need a function that either returns a string to set your rowsource property or a function that returns a recordset to set your list's recordset property.

Code: 
Public someFunction(ByVal pkey As String, AID As Integer) as ADODB.Recordset
  ....
  set SomeFunction = someRecordset
end function

Public SomeOtherFunction(ByVal pkey As String, AID As Integer) as String
  dim strSql as string
  ....
  someOtherFunction = strSql
end function

To correct my self the rowsource can be a string that is the name of a query, table, or a properly formatted sql string
The recordset property can take an ADO or DAO recordset.

 
Upvote 0 Downvote
Misscrf,
What about my suggestion don't you understand:
- What's a pass-through query
- What's a saved query
- How to modify the SQL of a saved query
- How to use a saved query as the Row Source of a list box

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I changed the function to the following:
Code: 
Public Function AssignedMediaFiles(ByVal pkey As String, AID As Integer) [red][b] As ADODB.Recordset[/b] [/red]
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
  [red][b] AssignedMediaFiles = objRS[/b][/red]
End If

objRS.Close
Set objRS = Nothing

End Function

I changed the stuff in red, but I still get a type mismatch error. When I go to the immediate window and do debug.print AssignedMediaFiles(me.bgroup, me.assetid) I get that type mismatch

in the function, the pkey (me.bgroup) is a string and the aid (me.assetid) is an integer. When I split the openargs and put them into textbox controls, I set them the same again (string and integer respectively). It looks like the bgroup string is having trouble passing to the function. It never gets the value into it, but I don't understand why. I have written a bunch of functions with parameters that work fine. Why this one???????? lol

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Sorry, Duane. I am not sure I understood what you suggested "...set the SQL property of a saved pass-through query and this query as the Row Source."

am I making 2 queries? a pass-through and another one? Am I converting this dynamic sql to a pass-through? This dynamic sql has the parameters embedded within a concatenation of strings to call the right table.

Thank you

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Sorry. I am researching pass-through queries, but I have no clue how to set up what you are suggesting. I appreciate your advice. Now I just need to understand how to do it.

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
If you would do:

Code: 
With objRS[blue]
    Debug.Print strSQL[/blue]
   .Source = strSQL
   ...

Do you get an SQL that works and returns records?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.
 
Upvote 0 Downvote
A pass-through query has an ODBC Connect str property that probably the same as your objConn.connectionString. The SQL syntax must match the server, not Access. Check faq701-7433 to find code for changing/updating the SQL property.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
I put that there, but right now, I am getting a mismatch type on just the listbox row source section of the on click ([highlight #FCE94F]Me.lstInv.RowSource[/highlight] = AssignedAssetFiles(Me.Bgroup, Me.AssetID))

while it is in debug mode, I can do a debug.print on the AssignedAssetFiles(Me.Bgroup, Me.AssetID) and I get the sql output, but I also get a type mismatch when I hit that too.

The sql that comes out, which has the parameters in them, runs from SQL just fine. When I try to run that sql in an access query, I get a "expects select, append, delete" type of issue, because the sql starts with that xml stuff, so the beginning of the sql is With xyz as (select blah...)

I have spent all day on this. Getting burnt out. Any other thoughts?

misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
This is the sql that I get as is:

Code: 
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].[tbl123ProjectFileList] 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].[tbl123ProjectFileList] 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 = 5 AND t1.AssignedSources IS NOT NULL

I put it in a pass-through and I can view results. Well, that's something lol I just need to set properties and understand how have parameters in the pass-through, and then set it as the row source.


misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Why do you need to set parameters. I see "WHERE a.ID = 5" in the SQL. Did you need to set other filters/parameters? When the "5" changes, just use the code to change the SQL of the saved P-T query.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
This is the strsql. The parameters are in bold red:

Code: 
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" [red][b] & pkey & [/b][/red]"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"[red][b] & pkey & [/b][/red]"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 = " [red][b]& AID & [/b][/red]" AND t1.AssignedSources IS NOT NULL"


misscrf

It is never too late to become what you could have been ~ George Eliot
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
369
dhookom
dhookom
JanaLNelson
  • Locked
  • Question
Filter as you type combo box in Access 2
2 3
Replies
54
Views
756
JanaLNelson
JanaLNelson
DB_Bowes
  • Locked
  • Question
Selection from Listbox to GoTo Record in Bound form Gives Error 2105
Replies
1
Views
111
DB_Bowes
DB_Bowes
Damian3215
  • Locked
  • Question
Recursive - - Function to identify the next higher level
Replies
13
Views
134
Damian3215
Damian3215
DeRochier
  • Locked
  • Question
Subform triggers error: "This expression is typed incorrectly..."
Replies
3
Views
175
DeRochier
DeRochier

Part and Inventory Search

Sponsor

Back
Top