Purchase Order form questions

B

BruceM

I have a vendor database that we want to start using to generate and keep
track of purchase orders for purchases from the vendors. To that end I have
made a PO table on the many side of a one-to-many with the Vendor table.
There is also a PO_Details table for the line items, one-to-many from the PO
table. There is a corresponding PO form and PO_Details subform. There is a
command button on the vendor form to open the PO form. The code is as
follows:

strVendor = "VendorID = " & Me.VendorID
DoCmd.OpenForm "frmPO", OpenArgs:=strVendor

In the PO form's Load event:

If Len(Me.OpenArgs) > 0 Then
Dim strArgs As String
strArgs = Me.OpenArgs
DoCmd.ApplyFilter , strArgs
With Me.RecordsetClone
.FindLast strArgs
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
Else ' i.e. if form is opened directly rather than from the
Vendor form
DoCmd.GoToRecord , , acNewRec
End If


Tangential question: I'm not sure defining strArgs has much value here, as
Me.OpenArgs is quite compact, but is there more to it than that (amount of
text in the code) in deciding which to use? The reason for finding the
length of OpenArgs is that the PO form may also be opened directly from the
startup form, in which case there is no OpenArgs, and the form opens to a
new record.

Anyhow, this opens the PO form to the last PO record for the vendor, or to a
new record if the PO is opened directly from the startup form or if there
are no POs for that vendor. However, this is not exactly what I want. If
there are no POs for a particular vendor I would like the PO form to open to
a new record with the vendor information already filled in, along with the
current date and the PO#, which is a sequential primary key value assigned
using DMax + 1 in the field's Default Value. I can handle the problem of
duplicate values in a multi-user environment, but am not worrying about that
yet.

The other idea I am tossing around is that in many cases a new PO is simply
a duplicate of an old one, but with a new date. I would like to know if I
can duplicate a PO record and its related details (line items). I can see
some potential problems with that, and would much prefer a "New PO for this
Vendor button" on the PO form, but the question will arise. In my
experiments so far the difficulty I am having is that I cannot generate a
new PO#, which means there is a duplicate key value error. Also, I would
need to have the date change to the current date.

Another issue has to do with the Details subform. So far I have it so that
a combo box on the subform is used to select items specific to that vendor.
If we need to order an item that hasn't been purchased before I would like
to do that on the fly, or at least as efficiently as is possible. The other
consideration is that prices change. If I have a products table, prices
will inevitably change. Of course I need to keep the price at the time the
order was placed, so if I link to a record in the Products table I think I
will need to store the price in the Details subform's record source
(tblPO_Details). Maybe the best choice is to store all of the values from
the Products table (code, description, and price) in tblPO_Details, since a
new product name or description is not out of the question. That way the
record in the Products table can be edited (to change the price, for
instance), but any old records in tblPO_Details will contain the information
that was in effect at the time the record was created.
 
G

Guest

A veritable novel of a post!!

I'll try and answer what I can in order

BruceM said:
I have a vendor database that we want to start using to generate and keep
track of purchase orders for purchases from the vendors. To that end I have
made a PO table on the many side of a one-to-many with the Vendor table.
There is also a PO_Details table for the line items, one-to-many from the PO
table. There is a corresponding PO form and PO_Details subform. There is a
command button on the vendor form to open the PO form. The code is as
follows:
If you link the PO_Details Table to the Product Table you can use it as a
link table for a many-to-many join saving some of the duplicate redundant
information you mention later.
strVendor = "VendorID = " & Me.VendorID
DoCmd.OpenForm "frmPO", OpenArgs:=strVendor

In the PO form's Load event:

If Len(Me.OpenArgs) > 0 Then
Dim strArgs As String
strArgs = Me.OpenArgs
DoCmd.ApplyFilter , strArgs
With Me.RecordsetClone
.FindLast strArgs
End With
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else 'ie the vendor exists but they have no previous orders
Me.DataEntry=True
DoCmd.GoToRecord , , acNewRec
Me.txtVendorName = strArgs
Me.PO = DMAx("PO", "tblPO")+1
Me.txtDateField = Date()
End If
Else ' i.e. if form is opened directly rather than from the
Vendor form
DoCmd.GoToRecord , , acNewRec
End If


Tangential question: I'm not sure defining strArgs has much value here, as
Me.OpenArgs is quite compact, but is there more to it than that (amount of
text in the code) in deciding which to use? The reason for finding the
length of OpenArgs is that the PO form may also be opened directly from the
startup form, in which case there is no OpenArgs, and the form opens to a
new record.
I always pass the openargs to a Public variable on opening a form,
especially as its not a modal form, so it can cause confusion if a user
backtracks and changes details in the original form.
Anyhow, this opens the PO form to the last PO record for the vendor, or to a
new record if the PO is opened directly from the startup form or if there
are no POs for that vendor. However, this is not exactly what I want. If
there are no POs for a particular vendor I would like the PO form to open to
a new record with the vendor information already filled in, along with the
current date and the PO#, which is a sequential primary key value assigned
using DMax + 1 in the field's Default Value. I can handle the problem of
duplicate values in a multi-user environment, but am not worrying about that
yet.
see coding changes above
The other idea I am tossing around is that in many cases a new PO is simply
a duplicate of an old one, but with a new date. I would like to know if I
can duplicate a PO record and its related details (line items). I can see
some potential problems with that, and would much prefer a "New PO for this
Vendor button" on the PO form, but the question will arise. In my
experiments so far the difficulty I am having is that I cannot generate a
new PO#, which means there is a duplicate key value error. Also, I would
need to have the date change to the current date.

How can you have a duplicate Primary Key Field? and you didn't mention
before the PO number actually includes the data in it.
Another issue has to do with the Details subform. So far I have it so that
a combo box on the subform is used to select items specific to that vendor.
If we need to order an item that hasn't been purchased before I would like
to do that on the fly, or at least as efficiently as is possible. The other
consideration is that prices change. If I have a products table, prices
will inevitably change. Of course I need to keep the price at the time the
order was placed, so if I link to a record in the Products table I think I
will need to store the price in the Details subform's record source
(tblPO_Details). Maybe the best choice is to store all of the values from
the Products table (code, description, and price) in tblPO_Details, since a
new product name or description is not out of the question. That way the
record in the Products table can be edited (to change the price, for
instance), but any old records in tblPO_Details will contain the information
that was in effect at the time the record was created.
No! don't do it! you will be storing much too much duplicate data, just
store the Reference number to the parent Product Table and bring the rest
together in a query. If the price for the product changes can you not just
store the new price for that item in the Product Table?

hope that helps a bit,

TonyT..
 
B

BruceM

And I was trying to keep it as concise as possible. Thanks for the speedy
reply, and for taking the time to wade through this. Replies and follow-up
questions inline.

TonyT said:
A veritable novel of a post!!

I'll try and answer what I can in order


If you link the PO_Details Table to the Product Table you can use it as a
link table for a many-to-many join saving some of the duplicate redundant
information you mention later.

I'm not following you here. Use what as a link table (aka junction table,
if I take your meaning correctly)?
If Not .NoMatch Then
Me.Bookmark = .Bookmark
Else 'ie the vendor exists but they have no previous orders
Me.DataEntry=True
DoCmd.GoToRecord , , acNewRec
Me.txtVendorName = strArgs
Me.PO = DMAx("PO", "tblPO")+1
Me.txtDateField = Date()
End If

I think I see what you're getting at here. I think I would need to define
strArgs before the If statement, though, in order for it to apply in the
Else section. I do want to lock down the records, otherwise people will
edit previous POs (their system with the Excel-based POs currently in use)
rather than creating new ones, but I probably won't use DataEntry = True. I
might arrange it so the PO is editable on the day it was created, or
something like that to allow for same-day corrections.
I always pass the openargs to a Public variable on opening a form,
especially as its not a modal form, so it can cause confusion if a user
backtracks and changes details in the original form.

My question was whether there is an advantage to:
Dim strArgs As String
strArgs = Me.OpenArgs
DoCmd.ApplyFilter , strArgs

as opposed to:
DoCmd.ApplyFilter, Me.OpenArgs

However, I must admit I don't quite follow you about passing OpenArgs to a
public variable. OpenArgs is set in the Click event of the command button
that opens the PO form, and referenced in the form's Load event, which runs
only once. What confusion could be caused by the user backtracking (I assme
you mean to a previous record)?
see coding changes above


How can you have a duplicate Primary Key Field? and you didn't mention
before the PO number actually includes the data in it.

There can't be a duplicate PK field, but that doesn't stop me from initially
duplicating the record. I can duplicate the record, but when I do I also
duplicate the PO#, which is the PK. It is generated by
=Nz(DMax("PO_ID","tblPO"),0)+1 as the default value of the PO_ID field.
When I attempt to navigate away from the record I receive notice of the key
violation.
No! don't do it! you will be storing much too much duplicate data, just
store the Reference number to the parent Product Table and bring the rest
together in a query. If the price for the product changes can you not just
store the new price for that item in the Product Table?

Again, not following you. I need to store the price somewhere, since prices
are apt to change. If a bracket costs $2 when I order it today, and next
week it goes up to $3, how do I assure that the $2 amount remains associated
with this week's PO? Do I store the price in the PO_Detail record (the PO
line item), or do I create a new record in the Products table and mark the
old one as inactive (so that it doesn't show up in the combo box list)?
 
G

Guest

Replies in order again!

BruceM said:
And I was trying to keep it as concise as possible. Thanks for the speedy
reply, and for taking the time to wade through this. Replies and follow-up
questions inline.



I'm not following you here. Use what as a link table (aka junction table,
if I take your meaning correctly)?

You have a Products Table and a PO Table which should be linked, yet each
product can appear on many PO's and each PO can have many Products - a many
to many relationship - If you use the PO_Details Table to store little more
than the ProductID and the PO# and link to both then it will resolve the many
to many problem.
I think I see what you're getting at here. I think I would need to define
strArgs before the If statement, though, in order for it to apply in the
Else section. I do want to lock down the records, otherwise people will
edit previous POs (their system with the Excel-based POs currently in use)
rather than creating new ones, but I probably won't use DataEntry = True. I
might arrange it so the PO is editable on the day it was created, or
something like that to allow for same-day corrections.

Dim lines are best put at the very beginning of a procedure, otherwise it's
all too easy to find they don't work on 1 particular line of code.
Maybe I mis-read what you wanted, but the ammendments I made to your code
allow for the following;
Vendor exists AND has an existing Order(PO) then bookmark finds it and you
can then select the last one.
Vendor Exists but has no previous Orders (the Else is against If Not
..NoMatch) use their Vendor ID (OpenArgs & strArgs values) to open a new Order
for them ready for Products to be added.
Vendor doesn't exist (or form opened from elsewhere that can't provide the
OpenArgs value) go to a new record.
My question was whether there is an advantage to:
Dim strArgs As String
strArgs = Me.OpenArgs
DoCmd.ApplyFilter , strArgs

as opposed to:
DoCmd.ApplyFilter, Me.OpenArgs

However, I must admit I don't quite follow you about passing OpenArgs to a
public variable. OpenArgs is set in the Click event of the command button
that opens the PO form, and referenced in the form's Load event, which runs
only once. What confusion could be caused by the user backtracking (I assme
you mean to a previous record)?

Belt & Braces approach, probably not necessary but my personal preference.
There can't be a duplicate PK field, but that doesn't stop me from initially
duplicating the record. I can duplicate the record, but when I do I also
duplicate the PO#, which is the PK. It is generated by
=Nz(DMax("PO_ID","tblPO"),0)+1 as the default value of the PO_ID field.
When I attempt to navigate away from the record I receive notice of the key
violation.
If you leave the PO_ID field empty, then the table will set it's default
value for you. Are you actually *duplicating* a record, or just showing an
existing record on screen? it doesn't *actually* exist until it's saved in a
table.
Again, not following you. I need to store the price somewhere, since prices
are apt to change. If a bracket costs $2 when I order it today, and next
week it goes up to $3, how do I assure that the $2 amount remains associated
with this week's PO? Do I store the price in the PO_Detail record (the PO
line item), or do I create a new record in the Products table and mark the
old one as inactive (so that it doesn't show up in the combo box list)?
My main reason for saying 'No! don't do it!' was with regards to saving the
description - definitely duplicating data for no good reason a VERY simple
query will link ProductID from tblPO_Detail & Description from ProductTbl.
Always tough to know what prices to store on products - don't store any that
can't be calculated (obvious), but also try to work out which ones will be
redundant, for example;
If selling Price is 150% of cost, your item in stock at cost $2 and you are
about to order more and they have gone up to $3, chaning the *cost* price in
the Product table means in the interim between now and the new stock arriving
the existing stock is sold for $4.50 (150% * $3), which (certainly in my
trade) is standard practise and a good way to maximise profits. For example I
store: Cost Price at time of purchase, Selling Price at time of Sale & Retail
Price. Between the 3 I can show full history of Product over time, average
purchase cost of stock, actual profit etc etc etc, but without duplication of
data. The only work involved is creating the queries to locate last cost
price & average cost of current stock.

TonyT..
 
B

BruceM

Thanks again. Once again, replies inline makes the most sense, I think.

TonyT said:
Replies in order again!



You have a Products Table and a PO Table which should be linked, yet each
product can appear on many PO's and each PO can have many Products - a
many
to many relationship - If you use the PO_Details Table to store little
more
than the ProductID and the PO# and link to both then it will resolve the
many
to many problem.

Ah, I see: PO_Details as the linking table. I somehow wandered elsewhere
and couldn't find my way back. I do think I will need to store the price in
the details table, along with the quantity. More below.
Dim lines are best put at the very beginning of a procedure, otherwise
it's
all too easy to find they don't work on 1 particular line of code.
Maybe I mis-read what you wanted, but the ammendments I made to your code
allow for the following;
Vendor exists AND has an existing Order(PO) then bookmark finds it and you
can then select the last one.
Vendor Exists but has no previous Orders (the Else is against If Not
.NoMatch) use their Vendor ID (OpenArgs & strArgs values) to open a new
Order
for them ready for Products to be added.
Vendor doesn't exist (or form opened from elsewhere that can't provide the
OpenArgs value) go to a new record.

I misread. My original post had an Else that was part of the first If (i.e.
what do do if Len(Me.OpenArgs) = 0, which would occur if the user opens the
PO form directly from the startup form). I looked too quickly at your
suggested code (which I intend to implement in large part, as it resolves a
previously unresolved option), and didn't note that it goes with If Not
..NoMatch. I will keep my Else (against the Len line) because I want the PO
to open to a new record if it is opened directly.
Belt & Braces approach, probably not necessary but my personal preference.
If you leave the PO_ID field empty, then the table will set it's default
value for you. Are you actually *duplicating* a record, or just showing an
existing record on screen? it doesn't *actually* exist until it's saved in
a
table.

I work in a production facitlity that performs value-added services. The
products ordered from a particular supplier are often the same with each
order. In that case I am trying to find out if there is a way to duplicate
the order, including the details (line items), but with a new PO number and
date (the default order date is the current date). I believe that the
answer to the question is that at best it is difficult. I have searched
newsgroup postings, but all I have been able to find about duplicating a
record and its related (subform) records is that it would mean looping
through the subform records or something, but I have not been able to find a
specific way to accomplish that. In any case, I cannot find a way to
duplicate the PO. Maybe I could do it if the PK in the PO table is
autonumber, and the sequential PO number is created during the form's Before
Update event or something like that, so that there is no key violation upon
duplicating the record, but it's not the most important part of the project,
so I will have to set it aside for now.
I know about the reasons records should not be duplicated, but when once a
month the same ten items are ordered from a vendor, there needs to be a
separate PO record. I would like to automate that process to the extent
possible.
My main reason for saying 'No! don't do it!' was with regards to saving
the
description - definitely duplicating data for no good reason a VERY simple
query will link ProductID from tblPO_Detail & Description from ProductTbl.
Always tough to know what prices to store on products - don't store any
that
can't be calculated (obvious), but also try to work out which ones will be
redundant, for example;
If selling Price is 150% of cost, your item in stock at cost $2 and you
are
about to order more and they have gone up to $3, chaning the *cost* price
in
the Product table means in the interim between now and the new stock
arriving
the existing stock is sold for $4.50 (150% * $3), which (certainly in my
trade) is standard practise and a good way to maximise profits. For
example I
store: Cost Price at time of purchase, Selling Price at time of Sale &
Retail
Price. Between the 3 I can show full history of Product over time, average
purchase cost of stock, actual profit etc etc etc, but without duplication
of
data. The only work involved is creating the queries to locate last cost
price & average cost of current stock.

There is no question that the price needs to be flexible. Many purchases
are metals and such, with prices that fluctuate according to some fairly
volatile markets. There is no resale as such. The value-added service is
performed for a contract price. The PO system is for storing (and
eventually sorting, categorizing, etc.) expenditures. I think that the way
to proceed is to have an item in the Products table (ProductID (not shown on
form), Code, Description, Unit, and UnitPrice):
00027 MT225 Metallic powder 50 lb. canister $220.25

I would link ProductID to the PO_Details table, and save the price in
PO_Details; the rest of the details (except ProductID) are in unbound
control. Next month the price is $225.50. The user, noting the old price,
will try to click into the price field on the PO_Details subform, which will
cause a pop-up form (or maybe an input box) to open allowing the price to be
changed. Record 00027 on the Products table will then contain the new price
the vendor is charging. This seems a better approach than adding a new
record to the Products table that duplicates the previous record except for
the Product ID and the price. The price history as such is not important to
us; what we paid is.

I would be interested in hearing what you have to say about these remaining
questions and comments. I appreciate your taking the time to look at this
and offer your thoughts. I have done several database projects, but this is
the first one involving money, and I was having trouble getting my thoughts
organized. Your suggestions make good sense, and have kept me from
wandering off on a tangent.
 
G

Guest

cropped it down, but still in order!!
Thanks again. Once again, replies inline makes the most sense, I think.

my pleasure :p
I misread. My original post had an Else that was part of the first If (i.e.
what do do if Len(Me.OpenArgs) = 0, which would occur if the user opens the
PO form directly from the startup form). I looked too quickly at your
suggested code (which I intend to implement in large part, as it resolves a
previously unresolved option), and didn't note that it goes with If Not
..NoMatch. I will keep my Else (against the Len line) because I want the PO
to open to a new record if it is opened directly.

Yes I thought that would be the case, and could see you knew how to do it,
so left that for you to do with as you saw fit.
I work in a production facitlity that performs value-added services. The
products ordered from a particular supplier are often the same with each
order. In that case I am trying to find out if there is a way to duplicate
the order, including the details (line items), but with a new PO number and
date (the default order date is the current date). I believe that the
answer to the question is that at best it is difficult. I have searched
newsgroup postings, but all I have been able to find about duplicating a
record and its related (subform) records is that it would mean looping
through the subform records or something, but I have not been able to find a
specific way to accomplish that. In any case, I cannot find a way to
duplicate the PO. Maybe I could do it if the PK in the PO table is
autonumber, and the sequential PO number is created during the form's Before
Update event or something like that, so that there is no key violation upon
duplicating the record, but it's not the most important part of the project,
so I will have to set it aside for now.
I know about the reasons records should not be duplicated, but when once a
month the same ten items are ordered from a vendor, there needs to be a
separate PO record. I would like to automate that process to the extent
possible.

Perhaps a listbox showing 'distinct' items from that vendor that can be
mutli-selected, then a cmd button to fire some code to create the PO_ID and
add each selected item to the PO_Detail tbl and then requery the subform or
open another form (or however you display current orders basically).
presumably the Detail table PK is autonumber?

and then the screen went blank - so your posts are copy/pasted without any >'s

There is no question that the price needs to be flexible. Many purchases
are metals and such, with prices that fluctuate according to some fairly
volatile markets. There is no resale as such. The value-added service is
performed for a contract price. The PO system is for storing (and
eventually sorting, categorizing, etc.) expenditures. I think that the way
to proceed is to have an item in the Products table (ProductID (not shown on
form), Code, Description, Unit, and UnitPrice):
00027 MT225 Metallic powder 50 lb. canister $220.25

I would link ProductID to the PO_Details table, and save the price in
PO_Details; the rest of the details (except ProductID) are in unbound
control. Next month the price is $225.50. The user, noting the old price,
will try to click into the price field on the PO_Details subform, which will
cause a pop-up form (or maybe an input box) to open allowing the price to be
changed. Record 00027 on the Products table will then contain the new price
the vendor is charging. This seems a better approach than adding a new
record to the Products table that duplicates the previous record except for
the Product ID and the price. The price history as such is not important to
us; what we paid is.

Your situation differs to what I usually deal with, but I agree, removing a
Product to only replace with the same product with a different price in the
same table is bad practice, and will lose all links to the ProductID in the
PO_Details table, and so any history (should you require it at a later date,
say). My only comment on your proposed method is your life will be easier if
the *most likely* price is available in the Product table, it's always easier
to get info out of the one side of a one to many relationship. Having said
all that, if you don't specifically book the product in - ie via a separate
table, but rather diminish the amount in the detail table, then your method
would seem more logical.

I would be interested in hearing what you have to say about these remaining
questions and comments. I appreciate your taking the time to look at this
and offer your thoughts. I have done several database projects, but this is
the first one involving money, and I was having trouble getting my thoughts
organized. Your suggestions make good sense, and have kept me from
wandering off on a tangent.

Tangents are only any use to you when you are going round the bend!!!

TonyT..
 
B

BruceM

Replies in just this one place this time. I have read about but not used
multi-select list boxes. I have not used them because I have not had a use
for them, but it looks like this could be the time to look into it.
I have been working on this project since last I wrote. The PO table now
has a button to open the Products form; on which the user selects a vendor
(and thus stores the vendor ID) and enters the product information. I will
eventually arrange it so that the Products form opens to a list that is
filtered for the vendor on the PO, with a button to remove the filter if
needed.
The details subform has a combo box that shows only the products for the
current vendor. The way I have accomplished that is to set the combo box
row source in the main form (the PO form) Current event. (i expect the same
system could be used for a list box.) I store the VendorID, ProductID,
Quantity, and Price in the Details subform; other product information is
displayed in unbound text boxes. By the way, when I considered adding a new
Product record for a price change (identical to the current record except
for price) I never intended to get rid of the old record, as it would be, as
you mentioned, part of old POs. That could have meant a lot of product
records for the same product. Price history, if needed, can be obtained by
other means.
One thing I still need to work out is that while it needs to be possible to
change the price via the Products form, changing other information should
not be possible. It probably won't come up that often, since a 1-gallon
bucket and a 5-gallon bucket of the same material will probably have
different product codes, but not every product has a code, and there are
surely some other caveats I have not considered. I'm sure I can lock down
the Product record to the extent necessary, and provide the simplest
possible method for the user to create the new record. In the current
Excel-based system the users tend to open a saved PO, change it as needed,
save the changes (or not), and print it. They should not be expected to
remember that a relational database works quite differently.
Thanks again for your help with this. Your input did a lot to keep me
focused.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top