Using recordset to set rowsource of multiple controls

Dear Experts,

I would like to set the rowsource of multiple listboxes by using one recordset.
I am not completely sure if this is possible at all. I tried attached code but the function does not return any rows in the listboxes (and does not return an error).
Does anyone have any idea?

Thanks in advance

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
 
Set rs = db.OpenRecordset("SELECT SGH_OrderDetail.Orderdetail_Key, SGH_OrderHead.Meldkamer_Plaats, SGH_Orderdetail.Materieelstring, " _
                    & "SGH_PlanningHead.Planning_Datum, SGH_PlanningHead.Planning_Tijdseenheden, SGH_PlanningHead.Planning_Dagdeel, IIf([Planning_Dagdeel]=" _
                    & "'OCHTEND',[Planning_Tijdseenheden],0) AS O, IIf([Planning_Dagdeel]='MIDDAG',[Planning_Tijdseenheden],0) AS M, IIf([Planning_Dagdeel]=" _
                    & "'GEHELEDAG',[Planning_Tijdseenheden],0) AS G, SGH_PlanningDetail.User_ID, SGH_Planningdetail.Planning_Sequence " _
                    & "FROM ((SGH_OrderHead INNER JOIN SGH_OrderDetail ON SGH_OrderHead.Orderhead_Key = SGH_OrderDetail.Orderhead_Key) INNER JOIN " _
                    & "SGH_PlanningHead ON SGH_OrderDetail.Orderdetail_Key = SGH_PlanningHead.Orderdetail_Key) INNER JOIN SGH_PlanningDetail ON " _
                    & "SGH_PlanningHead.PlanningHeader_Key = SGH_PlanningDetail.PlanningHeader_Key;", dbOpenDynaset, dbSeeChanges)
D1K1.RowSource = "SELECT rs.Orderdetail_Key, rs.Meldkamer_Plaats as Plaats, rs.Materieelstring as Mat, " _
                    & "rs.Planning_Datum, rs.Planning_Tijdseenheden, rs.Planning_Dagdeel, rs.O, rs.M, rs.G, rs.User_ID " _
                    & "FROM rs WHERE rs.Planning_Datum = '" & Me.Date1 & "'and rs.User_ID= " _
                    & "'" & Me.MonteurKolom1 & "' Order By rs.Planning_Sequence ASC;"
D2K1.RowSource = "SELECT rs.Orderdetail_Key, rs.Meldkamer_Plaats as Plaats, rs.Materieelstring as Mat, " _
                    & "rs.Planning_Datum, rs.Planning_Tijdseenheden, rs.Planning_Dagdeel, IIf([Planning_Dagdeel]=" _
                    & "'OCHTEND',[Planning_Tijdseenheden],0) AS O, IIf([Planning_Dagdeel]='MIDDAG',[Planning_Tijdseenheden],0) AS M, IIf([Planning_Dagdeel]=" _
                    & "'GEHELEDAG',[Planning_Tijdseenheden],0) AS G, rs.User_ID " _
                    & "FROM rs WHERE rs.Planning_Datum = '" & Me.Date2 & "'and rs.User_ID = " _
                    & "'" & Me.MonteurKolom1 & "' Order By rs.Planning_Sequence ASC;"
D3K1.RowSource = "SELECT rs.Orderdetail_Key, rs.Meldkamer_Plaats as Plaats, rs.Materieelstring as Mat, " _
                    & "rs.Planning_Datum, rs.Planning_Tijdseenheden, rs.Planning_Dagdeel, IIf([Planning_Dagdeel]=" _
                    & "'OCHTEND',[Planning_Tijdseenheden],0) AS O, IIf([Planning_Dagdeel]='MIDDAG',[Planning_Tijdseenheden],0) AS M, IIf([Planning_Dagdeel]=" _
                    & "'GEHELEDAG',[Planning_Tijdseenheden],0) AS G, rs.User_ID " _
                    & "FROM rs WHERE rs.Planning_Datum= '" & Me.Date3 & "'and rs.User_ID = " _
                    & "'" & Me.MonteurKolom1 & "' Order By rs.Planning_Sequence ASC;"
rs.close
db.close
set rs = nothing
set db = nothing
Exit Function

Open in new window

LVL 1
jrameuwissenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

peter57rCommented:
No you can't. But I don't see why you need to.
Instead of using a recordset, you could create your first query as a saved query and then you can create the rowsources using the equivalent code against the saved query.
0
jrameuwissenAuthor Commented:
Thanks for your reply Peter.

So it doesn't work that way... That's a bummer.
Issue is that I'm using an Access FE and SQL server BE and I don't want the FE to retrieve all data out of these huge tables for each listbox I have to populate.
When I use a saves query, will the FE than pull all data of all tables for each listbox?
0
peter57rCommented:
You can set the rowsource of a listbox to a Sql Server stored procedure with parameters.
The actual doing of that is a bit beyond me, but I know you can do it.
Sample here:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23571814.html
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrameuwissenAuthor Commented:
Mmmmm, did some research..

Wrote my first stored procedure on SQL server...
Now I need to find out how to call the function. For some reason the following does not return any records :

Dim sql As String
sql = "EXEC dbo.GETPLANRECORDS " & Me.MonteurKolom1.Value & ", '" & Me.Date1.Value & "'"
D1K1.RowSource = sql
D1K1.Requery

I quess I do not need quotes for an integer and/or datetime field.......

Anybody any idea how to call the stored procedure?
-- ================================================
-- Stored Procedure voor Planrecords in Planningoverview
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- =============================================
CREATE PROCEDURE GETPLANRECORDS 
	-- Add the parameters for the stored procedure here
	@Monteur Int,
	@Plandatum DateTime
 
AS
 
SELECT     SGH_OrderDetail.Orderdetail_Key, SGH_OrderHead.Meldkamer_Plaats, SGH_OrderDetail.MaterieelString, SGH_PlanningHead.Planning_Datum, 
           SGH_PlanningHead.Planning_Tijdseenheden, SGH_PlanningHead.Planning_Dagdeel, SGH_PlanningHead.Planning_Tijdseenheden AS Expr1, 
           SGH_PlanningDetail.User_ID, SGH_PlanningDetail.Planning_Sequence
FROM       SGH_OrderHead INNER JOIN
           SGH_OrderDetail ON SGH_OrderHead.Orderhead_Key = SGH_OrderDetail.Orderhead_Key INNER JOIN
           SGH_PlanningHead ON SGH_OrderDetail.Orderdetail_Key = SGH_PlanningHead.Orderdetail_Key INNER JOIN
           SGH_PlanningDetail ON SGH_PlanningHead.PlanningHeader_Key = SGH_PlanningDetail.PlanningHeader_Key
WHERE		
		   SGH_PlanningHead.Planning_Datum = @Plandatum AND SGH_PlanningDetail.User_ID = @Monteur
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
END
GO

Open in new window

0
jrameuwissenAuthor Commented:
Peter,

Thanks for pointing me in the right direction. I will open a new question to get this stored procedure going.

Thanks and Regards, Johan
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.