Purchase order tracking

[D. M.]

I have been asked to create a PO tracking system for repairs and maintenance
for 116 retail stores. I've already created the Lease Management database
with all of the store information, and plan to link the store information to
the PO tracking.

This database is being created to track actual costs vs. budget for each
store. I've read other posts on this subject, but find that the Invoice from
Northwind doesn't really work for this purpose.

Here's my problem. This database will be separate from the company's
existing accounting system, and our department will be assigned blocks of PO
numbers as we need them. How can I use these numbers to create the PO's in
our database?

Any help would be greatly appreciated. I have been given one week to
complete this.

Thanks again.

 

[D. M.]

I would also note that the PO numbers can only be used once. HELP???
Debbie

 

[Larry Linson]

As we don't know the requirements of your P.O. tracking application, or how
it will be used by multiple stores, etc., it's hard to say if a week is
somewhat inadequate or grossly inadequate. But, given that it was
"edicted", it's a good guess that it is inadequate.

Does the person who gave you the deadline know anything about software /
database development? I have seen "tracking applications" of various kinds
in different languages, some of which took multiple people multiple months
to develop.

You can create a table of P.O. numbers in one of several forms, obtain the
PO number and modify the data so it will not be available again. The
simplest way, perhaps, would be to create a Form into which you can enter
the beginning and ending numbers of the block, and write a separate record
into the "Available PO Number" table. Then, when you use one, delete that
record from the availability table. You could, alternatively, have a table
with records indicating blocks of numbers, and update that -- my
recommendation would be "easiest way" because disk storage is a lot cheaper
than developer time.

Larry Linson
Microsoft Access MVP

 

[D. M.]

Larry,

I'm sorry that I wasn't clearer. Basically, our process now is: We hire
someone to make repairs or do maintenance in a store and we prepare a PO.
The work is done and the invoice is sent to Accounting. There is NO process
in place for tracking costs.

Because the company is in the middle of a restructure, changing the current
processes will have to wait. Until that time we want to create a very simple
database that stores the PO and invoice information. We can then track the
amounts charged to each store. I will create reports showing what work was
done when and by whom by store, by vendor, etc. To clarify, this will only
track the PO's created by our department.

Hope that clears it up a bit.

I have already created the table with the PO Numbers. From there, I don't
understand how the 2 forms work together. Can you please clarify that for me?

Thanks so much for your time.

 

[Larry Linson]

When you create a new PO, you use code to retrieve the "next" PO number,
apply it to the PO, and then delete it from the table of POs. I presume you
are using a Form to enter the PO (there are too many things that can go
wrong using datasheet view).

This code, executed but not thoroughly tested, in the BeforeUpdate event of
the Form, appears to work:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("qryNextPO")
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Me!txtPONo = rs!PONo
rs.Delete
End If
rs.Close
Set rs = Nothing
Set db = Nothing

qryNextPO retrieves the lowest number from tblPOSource, with TopValues in
the Query properties set to 1. It saves that value into the Control txtPONo
on the Form, then Deletes the record from the tblPOSource. Writing the
actual PO is handled automatically by Access when you move off the current
Record or Close the Form.

If your application may concurrently be used by more than one user, you can
precede the read/write statements in this code with an Access "BeginTtans"
and use corresponding "Commit" and "Rollback" -- as explained in Help. If
only one person at a time will be using it, there's no need to force all the
read/write to be done as a unit.

Larry Linson
Microsoft Access MVP

 

[Todos Menos [MSFT]]

dude serious

if you're doing _ANYTHING_ for 116 stores.. then you should do it in
SQL Server

Access MDB has been obsolete for the past decade

SQL Server, like 'Access Data Projects' won the war