Restricting combo box values

[juliesmomma]

HI!
I have a form called Quote Information where we can quote items for our internal customers. On this form, I have a combo box from which I select a Vendor. I have another combo box that has Products. I have restricted the products field based on the vendor chosen so that I only see active products for the vendor chosen.

My issue is that in the vendors field there is a value called "Multiple". This "vendor" is chosen when they want to do a quote with multiple vendor products on a single quote as opposed to separate quotes. How do I get my products field to show all the active products for all vendors when "Multiple" is chosen?

Any help would be appreciated...
Thanks

 

[lastout]

I'm not quite clear on what the "quotes" are but if you want to show all products when the user selects "Multiple" from the Vendors combobox you can do it using the AfterUpdate property of that combobox. You have to know what the ID value is for the row "Multiple" in your Vendors table. Armed with that, type the following code the AfterUpdate event of VendorsComboBox:

Private Sub VendorsComboBox_AfterUpdate()

If Me.VendorsComboBox = IDnumberOfMultiple Then
me.ProductsComboBox.RowSource = "SELECT * FROM Products;"

End Sub
Hope this helps! lastout (the game's not over till it's over)

 

[lastout]

Oh, and one other thing - I take you have active and inactive products in your Products table. Just add a WHERE clause to the SQL in the Sub, something like WHERE(((ProductsTable.Active)="Yes&quot) lastout (the game's not over till it's over)

 

[juliesmomma]

lastout,
I tried your code in my database but I can't get it to pull any records when I choose "multiple". When you say

"SELECT * FROM Products;"

Is the Products the table it is to refer to? When I try to use my table in the place of Products (PRODUCT TABLE), I get an error message saying that whole SQL statement doesn't exist in my database. I don't know much about SQL statements. I know enough about VB to be dangerous Maybe my syntax just isn't exact.

Thanks for your help!

 

[lastout]

Hmmm, I'm not sure why you're getting that error.

?Do you have something like?:

tblVendors with fields VendorID, VendorName
tblProducts with fields ProductID, Product, VendorID

In relationships: tblProducts.VendorID JOIN tblVendors.VendorID

In your form, say it's called frmVendorsProducts, you have two comboboxes:

cmbVendors and cmbProducts

The ControlSource property of both comboboxes is blank, the RowSourceType of each is set to Table/Query, and the RowSource of each is an SQL statement.

For cmbVendors the RowSource SQL is something like:

SELECT tblVendors.VendorID, tblVendors.VendorName
FROM tblVendors;

For cmbProducts the RowSource SQL is:

SELECT tblProducts.ProductID, tblProducts.Product, tblProducts.VendorID
FROM tblProducts;

Now, let's say the record "Multiple" in your tblVendors has VendorID number 1. If you put the code behind the AfterUpdate event of cmbVendors:

Private Sub cmbVendors_AfterUpdate()

If Me.cmbVendors = 1 Then
me.cmbProducts.RowSource = "SELECT * FROM tblProducts;"
End If

End Sub
that should do it. If it still doesn't work, let me know some more of the particulars about your tables and comboboxes and I might be able to help figure out a different solution.
lastout (the game's not over till it's over)

 

[juliesmomma]

lastout,
I tried this again and I am getting records for the multiple but I can't seem to get it to requery if I change the vendor. It was doing the requery before I added the IF statement. I have tried to give you some particulars about my database below.

**This is the IF statement that I wrote from your direction under the VendorContact_AfterUpdate().

If Me.VendorID = "Multiple" Then
Forms!

!

![PART DESCRIPTION].RowSource = "SELECT * FROM [PRODUCT TABLE] WHERE (([PRODUCT TABLE].Active)=Yes);"
End If


DETAIL OF MY DATABASE:
Main Form: Quote Information Form (Table: Quote Table)
Subform: Quote Details subform (Table: Quote Details)

On the main form I have a combo box named VendorContact(which is pulled from my Contact Table). When a vendor contact is chosen the the VendorID (Vendor Name), address, phone, etc. is populated automatically.

On the subform I have Part Description (which is pulled from my Product Table). The following is the Row Source code for Part Description.

SELECT [PRODUCT TABLE].[PART DESCRIPTION] FROM [PRODUCT TABLE] LEFT JOIN

ON [PRODUCT TABLE].[PART DESCRIPTION]=

.[PART DESCRIPTION] WHERE ((([PRODUCT TABLE].Active)=Yes) And ((

.Vendor) Like Forms!

![VendorID])) ORDER BY [PRODUCT TABLE].[PART DESCRIPTION];

In the VendorContact_AfterUpdate() code, I have written the code to requery the Part Description field when a different vendor is chosen. The statement below is written in this code.
Forms!

!

![PART DESCRIPTION].Requery

Thanks again! You have already been a great help!

 

[lastout]

sweetpea37186,

Yep, you're right. Here's an easy way to solve the problem:

If Me.cmbVendors = "Multiple" Then
Me.cmbProducts.RowSource = "SELECT * FROM tblProducts;"
ElseIf Me.cmbVendors <> &quot;Multiple&quot; Then
Me.cmbProducts.RowSource = &quot;SELECT tblProducts.ProductID, tblProducts.Product, tblProducts.VendorID FROM tblProducts WHERE (((tblProducts.VendorID)=[cmbVendors]));&quot;
End If

Just substitute your table and combobox names where needed.

Let me know if it works for you or not. Good luck!

lastout (the game's not over till it's over)

 

[juliesmomma]

lastout,
YEA! It seems to be working but I have one other little question, in the ElseIF part of the SQL statement, where do I put the part about Active products? I know it goes in the WHERE part of that statement but does it come before, after or in the middle of the WHERE?

You have been a great help! Thanks so much!

 

[juliesmomma]

Hey lastout,
I think I got it to work correctly. I just kept playing with the SQL statement.

Thanks again!