SQL Whats Wrong

D

DS

I'm trying to enter this SQL statement after
DoCmd.Open("Mods")

SELECT Mods.[Mod Cat], Mods.[Mod Name], Mods.[Product Id]
FROM Products INNER JOIN ([Mods Cat] INNER JOIN Mods ON [Mods Cat].[Mod
Cat Id] = Mods.[Mod Cat]) ON Products.[Product ID] = Mods.[Product Id]
WHERE (((Mods.[Mod Cat])=[Forms]![Modifiers]![List1]) AND
((Mods.[Product Id])=[Forms]![Orders]![List4]));

And its all coming up Red.
Any Sugestions?
Thanks
DS
 
R

Rick Brandt

DS said:
I'm trying to enter this SQL statement after
DoCmd.Open("Mods")

SELECT Mods.[Mod Cat], Mods.[Mod Name], Mods.[Product Id]
FROM Products INNER JOIN ([Mods Cat] INNER JOIN Mods ON [Mods Cat].[Mod Cat
Id] = Mods.[Mod Cat]) ON Products.[Product ID] = Mods.[Product Id]
WHERE (((Mods.[Mod Cat])=[Forms]![Modifiers]![List1]) AND ((Mods.[Product
Id])=[Forms]![Orders]![List4]));

And its all coming up Red.
Any Sugestions?
Click to expand...

What are you expecting to happen? SQL can't just be typed in a code module like
that.
 
D

DS

Rick said:
I'm trying to enter this SQL statement after
DoCmd.Open("Mods")

SELECT Mods.[Mod Cat], Mods.[Mod Name], Mods.[Product Id]
FROM Products INNER JOIN ([Mods Cat] INNER JOIN Mods ON [Mods Cat].[Mod Cat
Id] = Mods.[Mod Cat]) ON Products.[Product ID] = Mods.[Product Id]
WHERE (((Mods.[Mod Cat])=[Forms]![Modifiers]![List1]) AND ((Mods.[Product
Id])=[Forms]![Orders]![List4]));

And its all coming up Red.
Any Sugestions?
Click to expand...


What are you expecting to happen? SQL can't just be typed in a code module like
that.
Click to expand...
I have a form with a Listbox on it. When I click on it a Second form
opens with 2 Listboxes on it. The first Listbox is populated properly,
just now I got the second to populate except its not filtered. I need
to get that last Listbox to populate according to the Listbox on Form1
and the first Listbox on Form2.
Thanks
DS
 
R

Rick Brandt

DS said:
I have a form with a Listbox on it. When I click on it a Second form
opens with 2 Listboxes on it. The first Listbox is populated
properly, just now I got the second to populate except its not
filtered. I need to get that last Listbox to populate according to
the Listbox on Form1 and the first Listbox on Form2.
Click to expand...

Well then your SQL needs to go into the RowSource property of the second
ListBox. The SQL statement in your OP indicates that you know how to
reference form objects in a query so that is all you have to do. Instead of
a single form reference you need two.
 
S

SirPoonga

You have a Mod Cats table and a Mod Cats field in Mods?

Also, after putting the statement as teh rowsource for listbox2, after
listbox1 is changed/updated you will need to requery the second and set
it to the first item. Something like

Listbox2.Requery
Listbox2 = Listbox2.ItemData(0)
'May have to call Listbox2 change/update event function here
 
D

DS

Rick said:
Well then your SQL needs to go into the RowSource property of the second
ListBox. The SQL statement in your OP indicates that you know how to
reference form objects in a query so that is all you have to do. Instead of
a single form reference you need two.
Click to expand...
So I have to reference the [Product Id] field on the first form
and the [Mod Cat] field on the second form. Because the Modifiers
depend on both of these fields even they are on seperate forms?
Thanks
DS
 
D

DS

SirPoonga said:
You have a Mod Cats table and a Mod Cats field in Mods?

Also, after putting the statement as teh rowsource for listbox2, after
listbox1 is changed/updated you will need to requery the second and set
it to the first item. Something like

Listbox2.Requery
Listbox2 = Listbox2.ItemData(0)
'May have to call Listbox2 change/update event function here
Click to expand...
I have three tables that this is based on. A
"Products" Table
[Product ID] Primary Key
[Product Name]

"Mods" Table
[Mod ID] Primary Key
[Product ID] Primary Key
[Mod Name]
[Mod Cat]

"Mod Cat" Table
[Mod Cat ID] Primary Key
[Mod Cat Name]

The "Products" Table is connected to the "Mod" Table and the "Mod Cat"
Table is also connected to the "Mod" Table.

The First Form called "Orders" has a Listbox "List4" which shows you the
[Product Name]
When you click on the [Product Name] a second Form called "Modern" pops
up. It has 2 Listboxes on it. The first being "List1" the second
"List2". In "List1" all of the [Mod Cat] that belong to that [Product
Name] comes up. So far this works. Now when you go to click on the
[Mod Cat], "List2" is suppose to populate with the [Mod Name] that is
attached to [Product Name]-[Mod Cat]...his has failed to happen. Ive
been able to get the [Mod Name] in "List2" but they are always the same
and never refer back to the [Product Name] and [Mod Cat]...I need only
the [Mod Name] that belong to [Product Name] and [Mod Cat] only. Is
this because they are on seperate forms? Can I refernce 2 different
forms in a Query or an SQL statement? Any elp is appreciated...it's
been 2 weeks trying to figure this out. There doesn't seem to be many
examples of a "Drill Down" using 2 Forms.
Thanks
DS
 
W

weiwei

DS said:
Rick said:
Well then your SQL needs to go into the RowSource property of the second
ListBox. The SQL statement in your OP indicates that you know how to
reference form objects in a query so that is all you have to do. Instead of
a single form reference you need two.
Click to expand...
So I have to reference the [Product Id] field on the first form
and the [Mod Cat] field on the second form. Because the Modifiers
depend on both of these fields even they are on seperate forms?
Thanks
DS
Click to expand...
 
Top