Cascading Combo

[YNWA]

Hi,

Can anyone help me with this. Usually ok doing these but having a problem with this one.

using the Northwind DB and I have the Customer Order Form.

I have inserted a combo called cboSupplierID which lists Suppliers. I then have a combo called cboProductID which lists Product Names.

When I select my supplier, I want the product combo to filter to those items related to that supplier.

My products come from the Products table which has the fields for SupplierID and ProduceName in.

my supplier combo comes from a supplier table.

Any ideas? Thanks.

 

[Alansidman]

here's a solution for you. Look at Pat's solution in the thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=219997

 

[YNWA]

Hi, could people take a look at the OrderLineItems form for me.

When I enter a Company, I want the Products drop down to only show those products relating to that companyu.

Products list can be found in table Products.

The drop downs are a little mssed up now showing ID's instead of names but thats the easy fix.

Anyone?

 

[YNWA]

I see people are viewing the db, any luck?

 

[YNWA]

I see people are viewing the db, any luck?

 

[GT_engineer]

I opened the file and I don't know where you are looking at. Which forms/tabs do I need to click on, to get to these combo boxes?

 

[YNWA]

I opened the file and I don't know where you are looking at. Which forms/tabs do I need to click on, to get to these combo boxes?

Use the forms in the Nav bar and open OrderLineItems.

 

[GT_engineer]

your Product combo box has the following source:

SELECT ProductList.[ID]
FROM ProductList
WHERE (((ProductList.[SupplierID])='1'));

change it to

SELECT ProductList.[ID]
FROM ProductList
WHERE (((ProductList.[SupplierID])=[cboSupplierID]));

try this.

Also for Supplier Combo box, After Update, create a macro that refreshes the product query

Private Sub cboSupplierID_AfterUpdate()
Me.cboProductID.Requery
End Sub

 

[YNWA]

your Product combo box has the following source:

SELECT ProductList.[ID]
FROM ProductList
WHERE (((ProductList.[SupplierID])='1'));

change it to

SELECT ProductList.[ID]
FROM ProductList
WHERE (((ProductList.[SupplierID])=[cboSupplierID]));

try this.

Also for Supplier Combo box, After Update, create a macro that refreshes the product query

Private Sub cboSupplierID_AfterUpdate()
Me.cboProductID.Requery
End Sub

Boss mate thanks. Sure it works now. Will test tonight/tomorrow and feed back results.

 

[YNWA]

your Product combo box has the following source:

SELECT ProductList.[ID]
FROM ProductList
WHERE (((ProductList.[SupplierID])='1'));

change it to

SELECT ProductList.[ID]
FROM ProductList
WHERE (((ProductList.[SupplierID])=[cboSupplierID]));

try this.

Also for Supplier Combo box, After Update, create a macro that refreshes the product query

Private Sub cboSupplierID_AfterUpdate()
Me.cboProductID.Requery
End Sub

Last problem, I have my Supplier combo bringing back the SupplierID's, however I can't seem to get the Supplier name which the query brings back. Query is ProductList.

Fiddled around but can't seem to get it?

 

[GT_engineer]

I noticed that, it's becuase you have a combo box built into the table/query. I have not seen this before, so I don't know how or why you did it like that.

You should make your Supplier Query source to the location where the table/query is getting their combo box from.

Or don't have a combo box built into your table/query

 

[YNWA]

I noticed that, it's becuase you have a combo box built into the table/query. I have not seen this before, so I don't know how or why you did it like that.

You should make your Supplier Query source to the location where the table/query is getting their combo box from.

Or don't have a combo box built into your table/query

I agree 100% but its Microsoft sample (northwind) so not sure why MS would do that either.

But I dont want to change all the lookups incase the forms, fields etc.. fail.

Annoying.

 

[GT_engineer]

your database is a bit of a mess, I highly recommend you don't put combo boxes into tables and forms, but anyways I think i fixed it.

1.) Fixed Table OrderDetails, field SupplierID. Filled in missing values and replaced numbers with names via combo box.
2.) Changed the source of OrderLineItems, Supplier combo box to [Suppliers]
3.) Changed the source of OrderLineItems, Product combo box to [Products]
4.) Added Query to Product combo box (cboProductID) to:

SELECT Products.ID, Products.ProductName
FROM Products
WHERE (((Products.SupplierID)=[cboSupplierID]));

5.) Modified After Update Event of cboSupplierID to:
Private Sub cboSupplierID_AfterUpdate()
Me.cboProductID.Requery
End Sub

Please let me know if the attached works, I had problems compressing it

 

[YNWA]

your database is a bit of a mess, I highly recommend you don't put combo boxes into tables and forms, but anyways I think i fixed it.

1.) Fixed Table OrderDetails, field SupplierID. Filled in missing values and replaced numbers with names via combo box.
2.) Changed the source of OrderLineItems, Supplier combo box to [Suppliers]
3.) Changed the source of OrderLineItems, Product combo box to [Products]
4.) Added Query to Product combo box (cboProductID) to:

SELECT Products.ID, Products.ProductName
FROM Products
WHERE (((Products.SupplierID)=[cboSupplierID]));

5.) Modified After Update Event of cboSupplierID to:
Private Sub cboSupplierID_AfterUpdate()
Me.cboProductID.Requery
End Sub

Please let me know if the attached works, I had problems compressing it

Hi, yes i think it works , thank you very much.

1 problem I have found. For the OrderLineItems form, if I select Supplier products filter fine. But if I enter a new row below and select Supplier, it clears the Product from previous entry?

Any ideas??

 

[GT_engineer]

sorry, i just noticed that now. You are correct, that's because the form is continous view, so when the combo box updates they all update.

I'll have to play with it later, but I think an Or statement will work. The row source will either be the query or from the same source with no query.

 

[YNWA]

Cheers. Think its ok now.

However I would like to know if there is a way I can remove the feature that doesnt allow me to edit invoices after they have been submitted?

Reason I need to edit is sometimes if people cant pay, we may offer a discount, so the invoice will need editing to reflect the new amount?

Anyone?

 

[YNWA]

Issue an invoice in the db attached few posts back.

 

[GT_engineer]

When you submit the invoice, I'm guessing you have a button or a check box that tells the database its submitted and edits are no longer allowed.

Me.AllowAdditions = False
Me.AllowDeletions = False
Me.AllowEdits = False

You can create a command button that will unlock your form.

button_Click()
     If Msgbox(" Do you want to unlock the form?", vbOKCancel, "") = vbOK Then
          Me.AllowAdditions = True
          Me.AllowDeletions = True
          Me.AllowEdits = True
     End If

 

[YNWA]

Cheers mate I will give it a go.

 

[YNWA]

When you submit the invoice, I'm guessing you have a button or a check box that tells the database its submitted and edits are no longer allowed.

Me.AllowAdditions = False
Me.AllowDeletions = False
Me.AllowEdits = False

You can create a command button that will unlock your form.

button_Click()
     If Msgbox(" Do you want to unlock the form?", vbOKCancel, "") = vbOK Then
          Me.AllowAdditions = True
          Me.AllowDeletions = True
          Me.AllowEdits = True
     End If

Cheers but not sure what to do here.

If I get properties for Invoice Order button on the OrderDetail form. There is a Marco attached. Not sure which part of this needs to be edited?

There is a SetOrderState line within that, any ideas if I just removed that would it be ok?

I have created the button and it asks do I want to unlock but for some reason I can change the invoice amounts but cant Update the totals or save the new entry. Error message saying I cant change details once invoiced.

Further looking in design view and in the footer are IsInvoiced check box that control sources to the table of a calculated data type.

In over my head here I think.

Any ideas?