?
Solved

Using recordset to set rowsource of multiple controls

Posted on 2008-11-10
5
Medium Priority
?
401 Views
Last Modified: 2013-11-27
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
Comment
Question by:jrameuwissen
  • 3
  • 2
5 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22922861
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
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22923288
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
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 22923646
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
 
LVL 1

Author Comment

by:jrameuwissen
ID: 22924259
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
 
LVL 1

Author Closing Comment

by:jrameuwissen
ID: 31515060
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question