PO Tracking

[D. M.]

I'm having trouble with the creation of what should be a fairly simple
database.

I work for a company with 120 retail stores. Our department takes care of
property management. We would like to create a small database for tracking
costs associated with purchase orders for maintenance and repairs to stores
which are generated by our department (this is separate from the company
purchase order system). I've already created a Lease Management database,
and I'd like to use the store table in this database.

So far this is what I've created:

Linked to tblstores (PK StoreID)
tblPurchaseOrder (PK POID)
tblPODetails (PK PODetailsID)
tblVendors (PK VendorID)

I'd like to create a form with Store information (Store #, Address), Vendor
Information (Name, Address, phone), Purchase Order Info, and PO Details.

I'd like to have a combo box for both the stores and the vendors (I tried
subforms, but can't seem to get them to work).

This sounded so easy when I "volunteered" to put it together.

Can anyone help?

 

[Larry Daugherty]

Hi ,

It's not clear if you want one combobox or two??? It is also not
clear what you want the result of the combobox selection(s) to be.

The qualified answer to your last question is "Yes, any number of
folks here can help with Access issues". The most time consuming
part of the process is the communications...

I've done any number of applications that use comboboxes to initiate
the completion of data on forms. If you already have your schema
designed and at least partially populated then click on the table on
which the form will be based and click the Autoform Wizard icon. The
wizard will create a form for you. Yu can do that iteratively until
you decide which one is close enough for you to modify to your likes.
Without modifying it yet in any other way, In design mode, View Form
Header and Footer. Expand the Header just a bit to fit in your new
combobox. Make sure that the Wizard are enabled on the toolbox.
Click the combobox icon on the toolbox and draw a combox in the Header
of your somewhat crude form. Answer the wizard's questions and you
should get something close enough to what you want that you can work
with it. Usually you would tell it to go to a particular record. In
run mode, selecting a record in the combobox will cause that record to
appear in your form. There are a host of other things you might be
interested in doing instead but I don't know.....

-
HTH
-
-Larry-

 

[D. M.]

Hi Larry,

Thanks for your reply. I'm sorry I was so unclear. Here's what I'd like to
do:

I'd like to create a form for data input to create PO's. I'd like a combo
box to choose the store location, and a combo box to choose the vendor. I've
tried this in every way I could, but it wouldn't work (using the wizard to
create the combo boxes). I also have a separate table (tblPONos) with a
block of PO numbers which were assigned to our department. Is it possible to
create a combo box to choose the next available PO Number, and remove the
used numbers from the list?

Any help would be appreciated.

Thanks again.

 

[D. M.]

I realized after I posted this that I still didn't clarify. The
relationships are as follows:

tblstores StoreID(PK)--StoreID(FK) tblPurchaseOrders
tblPODetail POID (FK)--POID(PK) tblPurchaseOrders
tblVendors POID (FK)--POID(PK) tblPurchaseOrders

I'm not sure if this is correct.

Hope this clarifies (at least one) of the problems.

Thanks again.

 

[Larry Daugherty]

Bear in mind that there are many ways to get a thing done. I'll just
describe one way for each part of your question.

I'd create a form based on tblStores and call it frmStoresPOs. Use
the Autoform wizard or go from scratch. In the header section I'd put
a combobox, cboFindStore. Let the wizard help you go to the selected
record. That takes care of the first part. Each time you open
cboFindStore and select a store, the form should fill in with that
store's data.

It looks like you have a good handle on the PK/FK paradigm so I'm
going to cop out a bit for the rest of the way: Find your copy of
Northwinds and copy what they have done. Find the controls they use
to call up specific functionality, copy, paste, modify until you get
what you want.

In general, I use the form/subform paradigm until the form gets too
messy. Then I might have something on the subform that, when clicked,
would open a new form in dialog mode in which the current subform
level is the main part of the new form and it has a subform.

I'm not much in favor of putting blank or placeholder records in a
database. You can easily enter PO numbers in advance but then you
need foolproof means of enforcing their use in prescribed order.
Don't the Tight A..e. trust you lower level folk enough follow a
carefully explained mechanism for generating PO numbers? That kind of
thing smacks of "Mother, may I"?!! Theory X management has been out
of vogue so long that most people no longer recall what it was.
That's what it was. Any PO Prefixed with one of your Store
Identifications can't be from any other store.

That will get you going. Post back with questions.

HTH

 

[D. M.]

Thanks Larry, but I've already created each form. Purchaseorder,
sfrmPODetail; sfrmStores; and sfrmVendors. I used the wizard to create combo
boxes on both the stores and Vendors forms. They work great. When I choose
the store, the address shows, when I choose the vendor, their address shows..
until I use them on the Purchase Order form. I'm assuming the problem lies
with the relationships. I just don't know.

Here are the PK/FK relationships again:

tblPurchaseOrder (POID/PK, StoreID/FK)---tblStores (StoreID/PK)
tblPurchaseOrder (POID/PK, VendorID/FK)--tblVendors (VendorID/PK, POID/FK)
tblPurchaseOrder (POID/PK PODetailID/FK)--tblPODetail (PODetailID/PK, POID/FK)

In my thinking (which may definitely be flawed), the Purchase order form is
the main form. Users should be able to choose the store, then the vendor (or
add a new vendor) and enter purchase order information (P.O. Number, Dates,
etc.) and PO details (description, amount, tax, total).

WHAT in the world am I doing wrong? It seems so simple: I just want 2
combo boxes on 1 form, or another alternative to select the store and the
vendor.

This is so frustrating. I created a much more complex database with less
frustration than this!!

Thanks again.... Debbie

 

[D. M.]

Whew! I think I solved MOST of my problem. I downloaded the Order
Management Database template and modified it to suit our purposes. The only
issue remaining is how to apply our PO numbers automatically.

I have a table with the PO numbers RE001000 through RE001500. This database
has a field for manual input of the PO Number.

Any suggestions as to how I could import the next available number when a
new record is created and then delete it from the PONumber table?

Thanks again for your suggestions.
Debbie

 

[John W. Vinson]

I have a table with the PO numbers RE001000 through RE001500. This database
has a field for manual input of the PO Number.

Any suggestions as to how I could import the next available number when a
new record is created and then delete it from the PONumber table?

You can create a Query using the "Unmatched Query Wizard" to return all
records in the PO Number table which do NOT have a match in your
purchase-order table. Use a Combo Box based on this query to select an
available PO number (replace the textbox on the form with this combo). The
combo will only display unused numbers so there's no need to delete them.

John W. Vinson [MVP]