Select Rows from Different Reports

[yaviens]

Hi, I’m working on a DB with multiple tables. My DB consists in a lot of tables, each one for a different element and his specifications (e.g.: transmitter, sensor, relay…).
The user can create loops selecting different elements (All the elements of the same loop have the same Loop ID).

I want that when the loop is finished the user can select one of the loops he has create and see all the elements and specifications he has selected for this loop.
The problem is that when I want to show in a form or report all the elements the user has selected before I can’t select each different element from his table and show in a form or report.

Until now I have this code in a form to select an element from a report and show it, but it doesn’t work very well.

-) List3 is a list box in my form where is the list of elements to select.
-) Boton1 is the button in my form to open the report when the element is selected
-) “Transmitter Specifications” is the report with the list of elements and his specifications.

Option Compare Database
Option Explicit

Private Sub boton1_Click()
On Error GoTo Err_boton1_Click

Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant

'make sure a selection has been made

If Me.List3.ItemsSelected.Count = 0 Then
MsgBox "Must select at least 1 element"
Exit Sub
End If

'add selected values to string
Set ctl = Me.List3
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","

Next varItem

'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)

'open the report, restricted to the selected items
DoCmd.OpenReport "Transmitter Specifications", acPreview, , "EmpID IN(" & strWhere & ")"


Exit_boton1_Click:
Exit Sub

Err_boton1_Click:
MsgBox Err.Description
Resume Exit_boton1_Click
End Sub

Private Sub List3_Click()

End Sub

 

[dhookom]

I'm confused. You want a user to select a loop but there is nothing in your code that suggests "loop". Do you have a report created or is this your primary question?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If it was me I would build a table like this

TblLoops
LoopID
ElementID
Type

to store a loop and all selected elements. The issue is that all elements come from different tables. If you used some type of unique ID across the tables this is less of a problem. But assume you did not. You are going to create a union query anyways so you could concatenate an ID there.

So you would have something like this

Select
"Trans" & ID as ElementID,
TansmitterName as ElementName
From
TableTransmitter
Union
Select
"Resis" & ID as ElementID,
ResistorName as ElementName
From
TableResistors
...


so the union would bring all parts together with concatenated unique IDs. Assume you have Transmitters with ID 1,2,3 and resistor 1,2,3
the union would create
Trans1 Trans2 Trans3 and Resis1 Resis2 Resis3

So in TblLoops I would do the same thing. When I select an item for the loop the data would look like
LoopID ElementID Type
1 2 Trans
1 3 Trans
1 3 Resis

Now I can innerjoin my TblLoops with my Union query On
tblLoops Type & ElementID on union query ElementID