cascading combo boxes, .requery not working

[jsmath22]

Hello,


I am having a lot of problems with the mock database I am creating as a means of educating myself in Access. It is a disc gold ordering db. First, hit the "Place an Order" button and the frm_IntroOrder pops up. Within this form, the first combo box is used to select the Brand of the disc desired. I finally got that working (SELECT DISTINCT) and upon doing that, the next combo box (cmbType) should be filtered to only contain the Types of discs that Brand produces. I have the Row Source

SELECT tbl_Discs.Type FROM tbl_Discs WHERE (((tbl_Discs.Type)=[forms]![frm_IntroOrder].[cmbBrand]));

and the

[code]
Private Sub cmbBrand_AfterUpdate()
Me!cmbType.Requery
End Sub
[\code]
However, all it is showing is the first Type in tbl_Discs.

SELECT DISTINCT tbl_Discs.Brand FROM tbl_Discs;


Thanks for the Help,
Very Beginner

[pbaldy]

Wouldn't the type have to be included in the first combo so the second can reference it?

[pbaldy]

Reverse that; you're comparing type to brand, apples to oranges.

[June7]

Change RowSource for cmbType to:

SELECT DISTINCT tbl_Discs.Type FROM tbl_Discs WHERE (((tbl_Discs.Brand)=[cmbBrand]));

Why is tbl_Discs the RecordSource for Order form instead of tbl_Order? tbl_Order does not have a field for ID of disc ordered. Why save customer info in tbl_Order? Could save just the CustomerID. Saving credit card numbers in database can be bad idea. Are you the ONLY user of this db?

[rpeare]

I can't work with your database, it keeps not saving changes I make and I've run out of patience.

Here's what I can tell you. You're going to have trouble with this database because you do not have it normalized.

For instance, in your tbl_Discs you currently have 3 manufacturers, now what happens if you have 500 records in there and you have Latittude, Latitude and latintude. Whenever you do a search these will not be associated with 1 Manufacturer. What you really should have is a table for each one of your disk properties that are 'standard'

Color, Brand, Type, Distance, Grade, Stability, etc. Any property of the discs that comes down to a 'standard' set of items should be given it's own table.

Second. When you are performing your order, you do NOT want to put in all of those properties over again and further, your orders may consist of several disc type so you will want a primary ORDERS table and a sub table of ORDERDETAIL (for example) where the type of disc the person is order is entered. On that form you are going to want an unbound lookup (you are currently basing your 'orders' table on your tbl_Discs table which I assume is meant to be a list of potential discs someone could buy. That's not what you want.

You'd likely want a main form/subform where the main form is based on your orders and your subform is based on the ORDERDETAIL table. On the subform form you could have your unbound search engine where you could put in any one of the properties in the tbl_Discs table and your list box/combo box would show you any items from tbl_Discs that mach your criteria.

[jsmath22]

Reverse that; you're comparing type to brand, apples to oranges.

Holy macaroni, that was a DUH moment. I knew it had to be something like that. Thank You!!

[jsmath22]

Oops, thank you for spotting that error, it works now!! This is just a mock database, there will be no real card #s in there, just trying to educate myself. However, I would still like to know how do companies save credit card numbers? OK, tbl_Discs is used as the Record source so that the user can pick the disc, which will give the unique id (or primary key, right?) for the disc they want to order. The key will then be entered into tbl_Order (which I just realized is not in there, again oops and thank you for pointing that out). Can you please explain the pitfall there? I did the customer shipping address in the tbl_Order b/c that may change for each order. For example if someone orders a lot of gifts on Amazon, they are all shipped to different addresses, which is not the same as the address of the customer. I'm trying to do the normalization thing, not repeating a customer in tbl_Customer every time they want the order shipped to a different address. Where should it be? Should I have another table called tbl_Shipping or something?
Thank you so much for your help, sorry for all the additional questions, but I'm going to take advantage of your knowledge if you are willing to share :-)

Thank You,
Jessica

[jsmath22]

Change RowSource for cmbType to:

SELECT DISTINCT tbl_Discs.Type FROM tbl_Discs WHERE (((tbl_Discs.Brand)=[cmbBrand]));

Why is tbl_Discs the RecordSource for Order form instead of tbl_Order? tbl_Order does not have a field for ID of disc ordered. Why save customer info in tbl_Order? Could save just the CustomerID. Saving credit card numbers in database can be bad idea. Are you the ONLY user of this db?

Thank You!! It works great now. This is a mock database, no actual credit cards in there. However, for my own education, where do companies store credit card #s? OK, so I use tbl_Discs for the Order form so that I can get the uique id of the disc to enter into tbl_Order (which has now been added, thanks for noticing that as well). What is the potential pitfall there? I have the shipping address in tbl_Order just in case the customer has several different shipping addresses. For instance if someone orders all their gifts to others on Amazon, then all orders would have a different shipping address which would not be the same as the customer's acutal address. How should I do it? Should i have another table, tbl_Shipping? Sorry for all the additional questions but if you are willing to share your knowledge, I'm going to take full advantage :-)

Thank You,
Jessica

[June7]

Order form should have tbl_Orders as RecordSource, tbl_Discs does not need to be in the RecordSource.

Could have an Addresses table. Just depends on how much repeat business you get.

Credit cards in databases is a touchy issue. You can encrypt the field in the table but still at risk. Anyone with access to the database can get around that. We know credit card companies have been hacked. My bank had to replace credit cards after one incident.

I only worked in one business that kept CC info and they were all in a physical rolodex card file. If I remember right, every billing cycle the card number had to be entered for charge to credit card service.