Order entry

[Dave]

I have two tables related as one to many. The first table is
"tblOrders" the many side "tblProducts". I would like the user to
choose from one to many products and after choosing, click a button
that assigns a unique order number in the tblOrders table. Should the
user at any time decide to cancel the whole thing, then that unique
order number would not be used and no record would be created. I want
to avoid autonumber since the order number will reset each month (eg S
(yr)-(month)-(No)).

What is the best way to accomplish this?

Dave

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this. You
can download it for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jeff Boyce]

Dave

A more traditional table design for orders/items would be:

tblOrder

tblItem

trelOrderItem

Your 'real-world' situation may be different, but the model you described
doesn't seem to allow for one order to consist of several items or one item
to show up in several orders. That's a "many-to-many" relationship, and
that's what that third table handles...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dave]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdb" which illustrates how to do this.  You
can download it for free here:http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
  MS Access MVP
  Access Database Samples:www.rogersaccesslibrary.com
  Want answers to your Access questions in your Email?
  Free subscription:
 http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L








- Show quoted text -

Thank you Roger, that is very useful but my problem isn't the
numbering system (thanks to you) it's how to prevent records from
being created until the "submit order" button is clicked.

Dave

 

[Dave]

Dave

A more traditional table design for orders/items would be:

  tblOrder

  tblItem

  trelOrderItem

Your 'real-world' situation may be different, but the model you described
doesn't seem to allow for one order to consist of several items or one item
to show up in several orders.  That's a "many-to-many" relationship, and
that's what that third table handles...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.








- Show quoted text -

tblProducts will actually lookup values from a third table, so in
essence tblProducts is that intermediary table. Isn't it? Or is it?
Crap, now I've got to think.

 

[Jeff Boyce]

Perhaps it's just a matter of naming, then.

If your Products table holds one record for each product/item associated
with an Order, and will have as many rows (per Order) as the order has
products/items, then yes, your tblProducts and my trelOrderItem sound like
they match.

One way to get folks thinking more about this would be to describe the
actual structure of your tables. Here's an example of the ones I mentioned:

tblOrder
OrderID
OrderDate
SalemanID
...

tblItem
ItemID
ItemTitle
ItemDescription
UnitPrice
...

trelOrderItem
OrderItemID
OrderID
ItemID
Quantity
...

(an "OrderItem" table may also hold a "UnitPriceAtOrder", as a way of
ensuring that changes to the unit price in the Item table doesn't 'change
history')

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Dave

A more traditional table design for orders/items would be:

tblOrder

tblItem

trelOrderItem

Your 'real-world' situation may be different, but the model you described
doesn't seem to allow for one order to consist of several items or one
item
to show up in several orders. That's a "many-to-many" relationship, and
that's what that third table handles...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.








- Show quoted text -

tblProducts will actually lookup values from a third table, so in
essence tblProducts is that intermediary table. Isn't it? Or is it?
Crap, now I've got to think.