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.
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.