Combo boxes and count with form

[sparrow]

Hi,

I have a table created of clothing ranges and it contains the following columns count, clothing type, colour, size, manfacturer etc. What I need is to create a form so a report can be run to check the various counts for based on user input, they can select eg jumpers, size and colour adn see what is in stock, or choose other combinations. I created a table for clothing type and also colour. Then on the form I used a combo box and just edited the vba to create link the ok button to the user selection so that the query could be run. However the query only returns values for clothing type and not the colour.

Private Sub cmdOK_Click()
DoCmd.OpenQuery "qry1", acViewNormal, acEdit
DoCmd.Close acForm, "qry1"
End Sub​

When I test the query directly it works ok(except it shows blank values as well) but its just the form and programming of the ok button.

Any suggestions would be useful

Thank you

Phil

 

[sparrow]

Would be great if anybody has any ideas??

Has the post been written inefficiently??

 

[Kryst51]

Well, I won't pretend to know the answer, but it might be helpful to others if you post the sql behind your "qry1" that is in your code.

 

[sparrow]

Hi,

Thanks for the reply. attached query.

 

[boblarson]

Hi,

Thanks for the reply. attached query.

That's not the SQL behind the query. You open your query in DESIGN view like you have it there and then go to VIEW > SQL VIEW and then copy and paste THAT here.

 

[sparrow]

Hi,

Thanks for the reply.
Sorry for providing incorrect info. I have changed things slightly. back to testing again using query only now to make sure it is definately working. I suppose my aim is to have a form where I can do count on multiple fields(eg number is shirts, number is shirts is size 10 etc etc), allow the user to use the combo box drop down feature to select the variables. With the query now I need to have variable in each field otherwise its returns nothing. Would be thankful for any help.


SELECT Clothing.[Colour 1], Clothing.[Colour 2], Clothing.Year, Clothing.[Garment type], Clothing.Size, Sum(Clothing.Count) AS SumOfCount
FROM Clothing
GROUP BY Clothing.[Colour 1], Clothing.[Colour 2], Clothing.Year, Clothing.[Garment type], Clothing.Size
HAVING (((Clothing.[Colour 1])=[what colour?]) AND ((Clothing.Year)=[What year?]) AND ((Clothing.Size)=[What Size?]));

 

[sparrow]

Hello,

did you get a chance to look at this??

Thank you