Add combo box to sub form

J

jules

I wonder if anyone can help. I have used the inventory control template for
my database but have run into a problem with the purchase order/p.o sub form.
I have added category and colour tables linked to the products table to make
it easier to select items when ordering. i therefore would like to add
something to the purchase order sub form so i can select a category and it
limits the product selection. I have tried adding cascading combo boxes -
category followed by product. I have managed the category drop down but cant
get it to populate the product list. Any help would be greatly appreciated.
Perhaps there is an easier way of going about this altogether?? Thanks.
 
B

buced3846

Jules -

To populate the second combo box you need to add some code to th
AfterUpdate Event of the first combo box.

I use:

Private Sub ProductName_AfterUpdate()
Dim strFilter As String
Dim Category As String
Dim cbosql As String
Dim cbosql1 As String

On Error GoTo Err_ProductName_AfterUpdate
If Not IsNull(Me.ProductName.Column(3)) Then
Category = Me.ProductName.Column(3)
cbosql = "SELECT * FROM CompanyColors WHERE supplierid = "
Category
cbosql1 = "SELECT * FROM CompanySizes WHERE supplierid = "
Category
'MsgBox cbosql
With Me.Color
.RowSource = cbosql
.ColumnCount = 4
.ColumnHeads = False
.ColumnWidths = "0cm; 0cm; 2.5cm; 2.5cm"
.Requery
End With
With Me.Size
.RowSource = cbosql1
.ColumnCount = 4
.ColumnHeads = False
.ColumnWidths = "0cm; 0cm; 2.5cm; 2.5cm"
.Requery
End With
End If
' Evaluate filter before it's passed to DLookup function.
strFilter = "ProductID = " & Me!ProductID

' Look up product's unit price and assign it to UnitPrice control.
Me!UnitPrice = DLookup("UnitPrice", "Products", strFilter)

Exit_ProductName_AfterUpdate:
Exit Sub

Err_ProductName_AfterUpdate:
MsgBox Err.Description
Resume Exit_ProductName_AfterUpdate

End Sub

Where I need to lookup colors and sizes in dropdowns that are dependen
on the supplier (column(3)) of the product (column(1)) selected in th
first dropdown.

Bruce Davis
 
J

jules

Thank you so much for replying. I'll have a go at that this evening. I've
havent attempted a D Lookup yet - it never occurred to me to do it that way.
I hope you dont mind if I post back if i get into trouble?? Can I just ask
a quick question regarding this - the queries i have practiced with before
involving combos i did through the expression builder. without wishing to
sound completely dumb, why am i just typing this sql into the after update?
and also, what does the Dim strFilter stand for? I've seen it on quite a few
posts. Or perhaps you can direct me to a site where all this code malarky is
explained!

Thanks once again for your reply.
Best wishes
Jules
 
B

buced3846

Jules,

While not claiming to be anything close to an expert, I am gettin
pretty good a searching multiple forums.

The DLookup I use to find the default price of the Product selected i
the first combo box, to populate the "Unit Price" control on the form
yet allow the user to type over it if it is not what they want to use
and then save it back to the table.

The reason I use the code is to (in effect) create a new combobo
eachtime the productID changes. The DIM strFilter is code to DIMENSIO
(define a private variable) with the name strFilter as a STRING typ
variable. The name strFilter is part of what most folks cal
"NORMALIZATION", the str stands for "string"... so that anyone wh
reads the code will be able to understand what the intent was. Rathe
like using tbl as the prefix for all table names, cbo as the prefix fo
all combo names, etc...

Just keep searching and reading up, there is plenty of good info ou
there. One I use all the time is http://www.utteraccess.com/ ... O
course you may post or PM if you get stuck, that's what these forum
are for.

Bruce Davis
 
J

jules

Hi Bruce. I dont wish to outstay my welcome but i have just one final
question for you - I was typing your code in and i realised that I dont need
to query the products by size - just by category and colour so, with regards
to your code, would i just not type in the Select * from CompanyColours bit?
Is the "CompanyColours" your name for your colour table? PS I will take you
up on your suggestion of utter access.
Best wishes
Jules
 
Top