AutoSum Purchase Order costs | Autofill cell with next number | Auto Enter Date on new record

[K.McL]

Hello,

I'm new to access but I've undertaken the creation of a Purchase Order database and while I've completed one access class so far, there is a lot I don't know and am eager to learn.
I've got some questions that need assistance - I've been through a hundred or so pages of threads but was unable to find answers.

Question 1.
PO Number:
It needs to auto generate for each new record. moving in a sequential manner, i.e. 16-TEST-1, 16-TEST-2, 16-TEST-3...
With every record a number needs to show up in the cell. I don't know how to make a cell auto populate let alone how to make it change for each new record.


Question 2.
Order Date:
This is a similar problem. I've entered =NOW() to make today's date show up in the cell like I wanted it to auto fill in the current date.
How do I make this number not change after the creation of a Purchase order? Is there a different formula that I should use?
My goal for the order date is for it to auto fill with today's date at the creation of a record and have the date stay the same whether I view the record tomorrow or next year.
Question 3.
Vendor:
There are two check boxes to mark if the vendor needed is internal or external.
I wanted the Vendor combo box to populate the data based on the selection of the check box. If an employee selected External for the vendor list to populate only External Vendors.
I don't know how to do this and I'm sure it's something super simple that I can't figure out.
Question 4.
New Vendor:
I need to have an email sent to me with the details of new vendors that my employees fill out. I've created the form and a hyperlink to my email address. Is there a way for Access to auto attach a screen shot or something of the record the employee alters?
Question 5.
Vendor Code:
I need this cell to auto fill from the data on the Vendor table - based on the selection of the Vendor in the drop down menu from question 3
Question 6.
Purchase Order total:
I need a grand total of all the costs associated with each purchase order at the bottom of the page and am unable to convert the instructions in my workbook to work for what I need.
Question 7.
Find record by Purchase Order number
I need to have buttons to search for records based on a number of variables. IF I can figure out or be told how to make a search button to locate records based on PO numbers I can make it work for other searches.


Thank you for your time, I will provide anything you need to help me.
I'm just at a loss and I learned more from my textbook than I did my professor - she's even more of a beginner at access than I am.

Thanks again!
K.McL

[K.McL]

I'm not sure what happened but my Vendor table combobox used to show the vendors I have in the list and now it's not displaying any of the two vendors I've added.
Also,
I have a section of the Purchase Order subform where the employees can select from a drop down menu to fill out the accounting code. What I'd intended to have was for each record in the subform to have a unique code for each. When I select the code on the first record it fills it in for any other record within the Purchase Order. And changing the second records code changes what's selected on the first.

And at the bottom of the form there is a spot for Office use only - a place where I track the invoice # and invoice date. This has altered somehow and I am unable to fill in the information there.

Does anyone have any suggestions?
Thanks!

[rpeare]

Question 1. Do not use the PO number as your primary key, create a table like:

tblPO
PO_ID  PO_Number ---> other PO related fields
1      16-TEST-1
2      16-TEST-2
3      17-TEST-1
4      17-TEST-2

Assuming this is your format you can get the maximum sequential value by doing something like dmax("[PO_Number]", "tblPO", "[PO_Number] like '" & right(datepart("yyyy", date()),2) & "-TEST-*")

This assumes the text portion of the PO number is always the same if it's not, for instance the PO Number contains the customer ID, then you'd have to modify the lookup.

The other alternative is to do something like

tblPO
PO_ID  PO_Date  PO_Customer  PO_Sequence

Then you could construct the PO number based on the three relevant fields (the year of the PO_Date, the customer number and the sequence) and all you'd have to do is increment the sequence number for that customer for that year using a similar dmax function.

Question 2. Don't use a control source of =Date()

Instead have vb code that populates the field whenever you go to create a new PO

if isnull(me.PO_Date) then
    PO_Date = date()
endif

Question 3. You're looking at a cascading combo box there are tons of examples out there

Let's say your first combo box is called cboVendorType
Let's say your second combo box is called cboVendor

In the ON EXIT (or similar property) of cboVendorType you would have code like

cboVendor.requery

And the query driving cboVendor would be something like

SELECT * FROM tblVendor WHERE [VendorType_ID] = [forms]![Your_Form_Name]![cboVendorType]

Question 4.

You don't need an email to do this, you can create an audit log where every time a change is made it appends the information to a table. The table that was changed, the field that was changed, what it was changed from, what it was changed to, who made the change and when. I've always found this easier to do with unbound forms rather than bound forms though so if you're new I doubt you're using unbound forms.

Another option would be to place the requested changes in a secondary table and, pending your approval apply the change to the 'main' table. This would require a different front end for you and your users (a split database with 2 different front ends probably)

Question 5.

Again assuming your vendor combo box is named cboVendor let's say your vendor table is this:

tblVendor
V_ID  V_Name   V_Number  V_Type
1     Vendor A  V-001-1   I
2     Vendor C  X-002-5   E
3     Vendor Z  B-002-2   I

The query driving your cboVendor would be

SELECT V_ID, V_Name, V_Number FROM tblVendor WHERE V_Type = 'I'

for internal vendors for instance

Then on your form, because you do not need to store the vendor number (you can look it up through queries) you can have an unbound field with a CONTROL SOURCE of

=cboVendor.column(2)

Whenever you select a new vendor it would be updated with the Vendor number.

Question 6.

MAKE SURE YOU ARE USING A NORMALIZED TABLE STRUCTURE

i.e.

tblPO
PO_ID  PO_Number ---> other PO related fields
1      16-TEST-1
2      16-TEST-2
3      17-TEST-1
4      17-TEST-2

tblItem
IT_ID  IT_Desc  ----> other item related fields
1      Item XYZ
2      Item ABC
3      Item QRS

tblPODetail
POD_ID  PO_ID  Item_ID  PO_Qty ----> other detail related fields
1       2      3        5
2       2      2        10
3       2      1        20
4       4      1        30

If you use a normalized structure summing the value of items on a PO is simple.

Question 7. If you are using a BOUND form (a bound form is a form connected to a table or query)

In your form header put in an UNBOUND TEXT BOX name the text box fldSearch

Make the query driving your form have a criteria in the PO_Number field of

like "*" & forms!YourFormName!fldSearch & "*"

this will find any po where any part of the PO_Number can be entered
OR

like forms!YourFormName!fldSearch & "*"

this will find any po where the START of the PO_Number matches what the user entered.

If you are using unbound forms it's a different process but I don't think you're doing that, I can tell you how if you are.

[K.McL]

Thank you for your reply - I am still really new and will have a few follow up questions.

Q1. I've actually got my PO Table set up like you suggested - it's got many many fields in the table but the Primary ID is not the same as the PO# - I didn't think that would be a good idea since I'm trying to capture many records to be associated with the same PO#.
The code you gave as an example, does that go in the PO# Field on the form?
Q2. I've placed that into VBA and will try to figure out how to make it work.
Q3. I'm confused by this but I think you meant that I create a combo box to create the drop down of External and Internal selection and create a query to the Vendor combo box to search for all Vendor's that related to the selection made in the previous combo box. Is this correct?
Q4. How would I create an audit table? Just like a regular table but how would it track exactly what was done and by who?
Q5. I'll try what you said for Q3 - If I can get it to work for Q3 then I should be able to make it work for Q5.
Q6. I don't think I am. It seems that in your description of tbl's that you have very few fields in each tbl. I've got a ton of fields in the tbl that tracks the information for the Purchase Orders. I don't think that's the way I should be doing it.
Q7. I don't know the difference in Bound v. Unbound. I'll have to do some research on this to figure out what I am using v. what I should be using.

Thank you for your help!

[rpeare]

Q1. Doesn't matter where you put it, you can populate the field on a 'new' record, just be aware that if you are using BOUND forms (forms that are tied to a query or table) if you populate a field you may get some errors when navigating into/out of the form if you also have required elements. It is very easy to create null records (records that only have the primary key populated) using bound forms. Personally I would only populate the date after the PO number has been populated that way you're fairly certain it's going to be saved anyway, so for instance put the code in the ON EXIT property of the PO Number field but add condition

if isnull(PO_Number)

Else
    ' Put the code to populate the date here
Endif

Q3. Yes that's what I said, it's called a cascading combo box. If you are not storing the vendor type on your PO the 'type' combo box must be an unbound combo box

Q4. I wouldn't attempt an audit table unless you are making the leap to unbound forms, I find bound forms much harder to work with when creating audit records and perhaps someone can give you better guidance here because I simply do not use bound forms. As for your original question, no, there's no easy way to take a screen shot and email it and the screen shot would not tell you what was changed, it would only show you what the record looked like (if you take it before) or what it looks like now (if you take it after changes, when it would be pretty useless anyway since it would match what's in your database)

Q6. that's why I put things like

-----> other <table> related fields

to indicate there are other fields in the table that aren't really of concern in answering the question. My goal in designing a database is to store as little information as possible, look for articles about data normalization, if you're new to database design I can pretty much guarantee you are going to struggle with normalization and learning about it early will save a lot of aggravation.

Q7. I defined what a bound form and an unbound form were :P

Bound forms are tied directly to your data (queries or tables) so that when you make a change in a field it's permanent

unbound forms rely on button clicks or events to alter data in tables, I find this method safer, but for neophytes it's a bit cumbersome to get through.