Open form with where condition for TWO criteria (1 Viewer)

KaylaHansa

New member
Local time
Yesterday, 22:25
Joined
Mar 22, 2020
Messages
18
I have non-updateable list created from a union query. It is a list of customers and all the different systems they have (alarm, camera, network, ect) It looks like this:

Customer 1, System A
Customer 1, System B
Customer 2, System A
Customer 2, System B
Customer 2, System C
Ect...

When you click the system, I want a dialog to open with more info about that system. I can make it open to the correct customer, but not to the exact system. For example, if I click on System B for Customer 2, it will open System A for Customer 2, since that is the first in the list. I tried changing it around but now Access asks for a parameter when I click it. This is my code:

Code:
DoCmd.OpenForm "ActivateAlarmDashboard", , , "AlarmID = " & [Forms]![Dashboard].PendingCustomersList.Form.ItemID, , acDialog, [Forms]![Dashboard].PendingCustomersList.Form.ItemID

ItemID is from the union query. I'm assuming it doesn't work because Access can't find the related customer to the system because the union query loses the source tables. Does anyone know a way around this? Thanks in advance! :giggle:
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
is the Union query from different source tables?
you can modify the Union query to include the TableName.
eg:

select ItemID, SystemName, "Table1" As TableName From Table1
Union
select ID, Description, "Table2" From Table2

now you can hide the "New Column" by setting its ColumnWidth=0 in the listbox.

you can check first which table was selected
Code:
DoCmd.OpenForm FormName:="ActivateAlarmDashboard",  _
WindowMode:=acDialog,  _
OpenArgs:= _
[Forms]![Dashboard].PendingCustomersList.Form.ItemID & "/" & [Forms]![Dashboard].PendingCustomersList.Form.NewColumName

add code the form (ActivateAlarmDashboard) Load Event:

Code:
private sub form_load()
dim t as string
dim id as long
id = split(me.openargs,"/")(0)
t = split(me.openargs, "/")(1)

me.recordsource = "select * from [" & t & "] where itemID = " & clng(id) & ";"
end sub
 

KaylaHansa

New member
Local time
Yesterday, 22:25
Joined
Mar 22, 2020
Messages
18
I have a different dialog form for each system type because it systems has it's own specific fields. So I had a if statement set up so that if it was an alarm system, the alarm system dialog would open. If it's a camera system, then a different dialog would open. I added the table to the union query and tried the codes but kept getting an error message...
 

KaylaHansa

New member
Local time
Yesterday, 22:25
Joined
Mar 22, 2020
Messages
18
Each different system is it's own table source, ie AlarmCustomers, CameraCustomers. So each table source would have it's own If line... Which means that access already opens the form to the correct table. It has no problem matching the customer because the customer is selected normally in the query, but since the system types come from different tables they are all selected as "ItemID". So Access can't match the AlarmID and ItemID because the ItemID doesn't have a table source...
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:25
Joined
Oct 29, 2018
Messages
21,358
Each different system is it's own table source, ie AlarmCustomers, CameraCustomers. So each table source would have it's own If line... Which means that access already opens the form to the correct table. It has no problem matching the customer because the customer is selected normally in the query, but since the system types come from different tables they are all selected as "ItemID". So Access can't match the AlarmID and ItemID because the ItemID doesn't have a table source...
Hi. Have you shared your table structure with anyone here yet? If not, you might consider posting your table structure for review. I get the impression it may be a non normalized structure.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:25
Joined
May 7, 2009
Messages
19,169
as i have said, ade a calculated column to yoir query that will identify the tablename.

then use condition (If) statement to determine which table was selected ad which form to open repectively.
 

KaylaHansa

New member
Local time
Yesterday, 22:25
Joined
Mar 22, 2020
Messages
18
Yes, it does open the correct form but not to the specific record on that form. It will just go to the first record in the form. @theDBguy I have and after explaining the design they agreed that it was correct.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:25
Joined
Oct 29, 2018
Messages
21,358
Yes, it does open the correct form but not to the specific record on that form. It will just go to the first record in the form. @theDBguy I have and after explaining the design they agreed that it was correct.
Would you mind posting a link to that discussion? I would like to see that explanation.
 

Cronk

Registered User.
Local time
Today, 16:25
Joined
Jul 4, 2013
Messages
2,770
I wonder about the need for union queries to populate a form, and also tables called AlarmCustomers and CameraCustomers. I would have expected a Customer would have one or more AlarmSystems which in turn would have multiple SystemComponents including cameras and alarms. That's 3 basic tables with no direct link from Customers to cameras or alarms, but related through the AlarmSystem.

Then I'd expect a form with two subforms. The form would be bound to customers. Subform would be bound to Systems and linked to the form by CustomerID, so this subform would show the Systems for the customer selected in the form. SubformB would show the components and be linked to whatever was System was selected in SubformA.

I suspect there is faulty table design.
 

Users who are viewing this thread

Top Bottom