• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

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

0
jrameuwissen
Asked:
jrameuwissen
  • 3
  • 2
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now