Combo Box issues

[Mike DeRemus]

Probably a noob question, but here it goes........
What I am trying to do is set up a form, not too much unlike the one in
Northwind, where I can have orders input, but with a few extras features.
For example, I am trying to keep track of payment methods, like cash, check,
etc. I can not figure out the best way to do this. Should I have a separate
table hold the various payment methods like cash and check, or is there an
easier way to do this and just keep track of the information in the orders
table altogether? I have it set up now so that it would read into a combo
box from a PaymentMethod table, and store the values into the Orders table.
I can change the values, and they will be stored correctly in the Orders
table, but when I try to make a new order altogether, it says that required
information is missing. I looked, and nothing seemed to be marked required
in the table design view of PaymentMethod.

That problem is not as concerning to me as my next problem. Here is the big
question, what do you think is the best way to implement a field that will
auto increment, but is not the primary key? In other words, not only do I
want an OrderID, but would also like to have some unique number, preferably
auto generated, that would also identify the record. For instance, like a
WorkOrder number or ProcessOrder number. I know you can not have 2
autonumbers in the same table, so what is the best solution? Or better yet,
the simplest solution? For this, I had originally set up a seperate table
that was called PurchaseOrderTracking, which basically stored the OrderID
and then an autonumber (primarykey). (I know, probably poor design, but I'm
still fairly new so be gentle). I got this number to show up on the orders
form too(called PONumber), and when you do a new Order, it shows that it is
an (autonumber), but will not increment once the order is started like the
OrderID will. Anybody have any ideas, suggestions, or tips for a noob?

This is all done in Access 2000 with VBA. It is an Access application.
Any help would be appreciated.

Mike

 

[Treebeard]

Probably a noob question, but here it goes........
What I am trying to do is set up a form, not too much unlike the one in
Northwind, where I can have orders input, but with a few extras features.
For example, I am trying to keep track of payment methods, like cash, check,
etc. I can not figure out the best way to do this. Should I have a separate
table hold the various payment methods like cash and check, or is there an
easier way to do this and just keep track of the information in the orders
table altogether? I have it set up now so that it would read into a combo
box from a PaymentMethod table, and store the values into the Orders table.
I can change the values, and they will be stored correctly in the Orders
table, but when I try to make a new order altogether, it says that required
information is missing. I looked, and nothing seemed to be marked required
in the table design view of PaymentMethod.

Do you have a default value set in the table somewhere which is an invalid
type compared as the type of the field?

That problem is not as concerning to me as my next problem. Here is the big
question, what do you think is the best way to implement a field that will
auto increment, but is not the primary key? In other words, not only do I
want an OrderID, but would also like to have some unique number, preferably
auto generated, that would also identify the record. For instance, like a
WorkOrder number or ProcessOrder number. I know you can not have 2

Could you list all of your tables and the fields in each table, at least the
first 4 fields. It's not clear what information you are trying to store. Why
do you need the PurchaseOrderTracking table just to store 1 number? Can't
you just store the PO Number in the order table. Whose PO number is this,
the customers or the merchant?

There is no reason to have one table with two autonumbers , it doesn't make
any sense. You should keep these things in seperate tables and link the
fields using the Tools/Relationships menu.


Jack

 

[DungeonAccess]

Thanks for the input Jack,

I got the PaymentMethod problem figured out. Now, I can change method, and
also create a new order so everything is fine. It was the way I had the
relationship set up that screwed me up. Again, a noob goof.

Well, the PO number is going to be for both us and the customer as a way of
tracking Orders. Maybe I should just change the field called OrderID to PO
number. Sounds like that would be easier doesn't it? I mean, the customer
does not care about an OrderID. They are used to PO numbers and such. Then,
it would be an auto number, and I could eliminate the PurchaseOrderTracking
table. Because now that I think about it, it does not make sense to have two
auto numbers. One number(field) alone can identify an Order! Looking back,
that seems like a silly question now. Part of learning I guess.

Oh, and I have too many tables and stuff to list out here. Basically, I am
keeping track of Orders, Customers, Suppliers, Products, Shippers, as well
as some company stuff. I hate to sound negative and say that I am probably
doing stuff wrong, but my feet are not even wrinkled yet because they have
not been in the water long. I originally was going to be doing hardware
programming at this place, but now I am doing this. Kind of a weird
transition! And this was supposed to be VB 6 at first too, and now it is
just VBA in Access 2000. Anyways, I am actually getting stuff to work, it's
just probably not the best practice so any advice is appreciated. Even book
recommendations would be great! I have one that doesn't seem too bad, and I
have actually learned a lot in a short amount of time. But a seasoned vet
would look at my stuff and probably wet himself laughing.

Thanks again,
Mike

 

[Treebeard]

Mike,

A lot of people get confused between "Order Number" and "Purchase Order
Number". A company should maintain both.

When a company sells something it issues an "Order" which has an associated
"Order Number" or "Order ID".

When a company has to buy something, it issues a "Purchase Order" which has
an associated PO Number.

The company doing the selling should maintain the PO Number of the company
doing the buying and visa versa, that is , the company doing the buying
should maintain the Order number of the company doing the selling.

Good Luck,

Jack