SQL Question

D

DS

Is this the right Syntex for this? I'm referencing Listbox1. to fill
Listbox2 but it isn't working?
Thanks
DS



Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = Me.List1"
.Requery
End With
End Sub
 
A

Andi Mayer

Is this the right Syntex for this? I'm referencing Listbox1. to fill
Listbox2 but it isn't working?
Thanks
DS



Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = Me.List1"
.Requery
End With
End Sub
Click to expand...

if [Mod Cat] is numeric and Me.List1 is numeric then it's nearly right
"WHERE [Mod Cat] =" Me.List1


otherwise you have to write

"WHERE [Mod Cat] ='" Me.List1&"'"
 
G

George Nicholson

Um, i think you missed the first ampersand in each example:

If numeric: "WHERE [Mod Cat] = " & Me.List1

If non-numeric: "WHERE [Mod Cat] = '" & Me.List1 & "'"

HTH,
--
George Nicholson

Remove 'Junk' from return address.


Andi Mayer said:
Is this the right Syntex for this? I'm referencing Listbox1. to fill
Listbox2 but it isn't working?
Thanks
DS



Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = Me.List1"
.Requery
End With
End Sub
Click to expand...

if [Mod Cat] is numeric and Me.List1 is numeric then it's nearly right
"WHERE [Mod Cat] =" Me.List1


otherwise you have to write

"WHERE [Mod Cat] ='" Me.List1&"'"
Click to expand...
 
A

Andi Mayer

Um, i think you missed the first ampersand in each example:

If numeric: "WHERE [Mod Cat] = " & Me.List1

If non-numeric: "WHERE [Mod Cat] = '" & Me.List1 & "'"

HTH,
Click to expand...

thanks,
was a little bit to fast (the newsreader doesn't turn this red) ;-)
 
D

DS

George said:
Um, i think you missed the first ampersand in each example:

If numeric: "WHERE [Mod Cat] = " & Me.List1

If non-numeric: "WHERE [Mod Cat] = '" & Me.List1 & "'"

HTH,
Click to expand...
Thanks Guys!!! It finally works. Only one more hitch. I need it to
sort by [Product ID]. It's almost doing what it suppose except that
it's not giving me for just the one product. Its giving for all of the
products.

Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = " & Me.List1 & ""
.Requery
End With
End Sub

When I try this I get nothing...

Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = " & Me.List1 & "" &_
"AND [Product ID] = " & Me.List1 & ""
.Requery
End With
End Sub

List1 has 3 Columns [Product ID]#, [Mod Cat]#,[Mod Cat Name]Text.
Its bound on the 2nd field .

Thanks Once Again...Almost there! Then I can sleep!!!!!!!!
DS
 
A

Andi Mayer

Thanks Guys!!! It finally works. Only one more hitch. I need it to
sort by [Product ID]. It's almost doing what it suppose except that
it's not giving me for just the one product. Its giving for all of the
products.
Click to expand...
Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
" WHERE [Mod Cat] = " & Me.List1 & _
" ORDER BY [Product ID]"
.Requery
End With
End Sub


the following will give you records with Mod Cat and Product ID having
the number of Me.List1. I dought that the both fields have the same
number

When I try this I get nothing...

Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = " & Me.List1 & "" &_
"AND [Product ID] = " & Me.List1 & ""
.Requery
End With
End Sub

List1 has 3 Columns [Product ID]#, [Mod Cat]#,[Mod Cat Name]Text.
Its bound on the 2nd field .

Thanks Once Again...Almost there! Then I can sleep!!!!!!!!
DS
Click to expand...
 
D

DS

Andi said:
Thanks Guys!!! It finally works. Only one more hitch. I need it to
sort by [Product ID]. It's almost doing what it suppose except that
it's not giving me for just the one product. Its giving for all of the
products.
Click to expand...

Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
" WHERE [Mod Cat] = " & Me.List1 & _
" ORDER BY [Product ID]"
.Requery
End With
End Sub


the following will give you records with Mod Cat and Product ID having
the number of Me.List1. I dought that the both fields have the same
number


When I try this I get nothing...

Private Sub List1_AfterUpdate()
With Me.List2
.RowSource = _
"SELECT [Mod Name] FROM Mods " & _
"WHERE [Mod Cat] = " & Me.List1 & "" &_
"AND [Product ID] = " & Me.List1 & ""
.Requery
End With
End Sub

List1 has 3 Columns [Product ID]#, [Mod Cat]#,[Mod Cat Name]Text.
Its bound on the 2nd field .

Thanks Once Again...Almost there! Then I can sleep!!!!!!!!
DS
Click to expand...
Click to expand...
List1 has 3 columns... Product ID
Mod Cat ID
Mod Cat Name
Whenever I choose Product ID (Lets say 1) from the first form, form 2
opens and List1 populates with all of the [Mod Cat ID] associated with
the [Product ID] so on the second form, upon choosing Product 1, I would
get this in List1

Product ID Mod Cat ID Mod Cat Name
1 5 Toppings
1 6 Temps

Now, using the SQL that I wrote whenever I click on, lets say toppings
in List2 I get a list of toppings...this works fine. The problem is
that I'm getting Toppings available for both Product 1, 2 etc. I need
to only get the toppings for Product 1. This is my dilemna. Once again
any help is much appreciated.
Thanks
DS
 
A

Andi Mayer

List1 has 3 columns... Product ID
Mod Cat ID
Mod Cat Name
Click to expand...


use the columns like
List1.Column(0) = Product ID
List1.Column(1) = Mod Cat ID

"WHERE [Mod Cat ID] = " & Me.List1.column(1) &_
" AND [Product ID] = " & Me.List1.column(0)
 
D

DS

Andi said:
List1 has 3 columns... Product ID
Mod Cat ID
Mod Cat Name
Click to expand...



use the columns like
List1.Column(0) = Product ID
List1.Column(1) = Mod Cat ID

"WHERE [Mod Cat ID] = " & Me.List1.column(1) &_
" AND [Product ID] = " & Me.List1.column(0)
Click to expand...

Andi...
YOU did it!!!! I would reach out and shake your hand if I could!
Thank You!
Sincerely,
DS
 
Top