NOOB - desperatley needs your help. PURCHASE ORDER DATABASE

[nforce19]

Hello everyone,

I am in desperate need of your help. I was asked by my boss to create a database to create and store purchase orders for our company. We also need a feature to be able to print reports of a certain product so we can see what projects/clients/dates it is associated with. Anyways see below what I have right now. If there is any other info you need feel free to ask.

[nick404]

use a query where you can search product ID, (unless this is autonumber then search product name). you can then turn the query results into a report for printability.

[cbende2]

What do you need help with?

[nforce19]

I dont know where to go from here. I got this far by finding info online. Basically I need my form to:

1. Select a client (by Client name not ID)
2. Select a project (by project name not ID)
3. Enter a work order number
4. Enter a description / Location

I then need my sub form to:

1. Select a product and the unit rate automatically comes up.
2. Be able to enter a quantity for each product
3. I need a calculated total for each product line
4. I need a calculated total at the bottom for all product items.
5. I also want to be able to enter product items not on the list for special cases and notes if necessary.

Thanks

[nick404]

It sounds to me like you want to be able to type in a product and pull up all the fields related to it yes? Create a query and select the tables you will need to use. Drag and drop the fields down that you want to have appear with your results. Where you have ProuctName, type WHERE in the sort area. then in Criteria type [Type Product Name]. Then when you run the query you will be prompted to type the product name. If you do not know all of it type the first few letters and put * after to search any string of characters after the first letters. But if you have multiple products that start with "Th" for example then "Th*" will return anything that begin with Th..

[nick404]

Then afterwards, save the query, then click it once to highlight it and create Report. Then you will have something you can organize and print off based on what you have from the query.

[nforce19]

Thanks for such quick replies! I'm just stepping out of the office for a meeting. Ill test out your suggestions when I get a chance and report back.

Thanks again!

[nick404]

Oh you are looking to make a form?

[nforce19]

Yes, I need to make a form so others in the office can input the data easily. Also I would like to be able to save the PO to a pdf file.

[nick404]

Ok for your client and project selections use a combo box and in properties where it says "row source" select value list and click the three dots to manually enter in what options you want the user to have. OR you could leave it as 'table/query' and click the three dots and pick the table and what field you want the options to come from. So if your drop down (combo box) will be for Client Name then select the table that has that field, then select that field and it will populate the combo box with those options.

For the other fields where the user manually enters in data set some text boxes up and in properties where it says 'control source' just select the table and field that the text box will belong to.

[ssanfu]

I would suggest you work through the tutorials here http://www.rogersaccesslibrary.com/forum/forum46.html
Also see https://www.accessforums.net/access/...tml#post221691

I see that you have named all of your PK fields "ID". This is a poor naming convention.
I would use names like
"Client_ID"
"Project_ID"
"ProdItem_ID"
"ProdTable_ID"

My actual PK field names all end in "_PK" and the FK fields end in "_FK". So
PK field name
"ClientID_PK"
"ProjectID_PK"
"ProdItemID_PK"
"ProdTableID_PK"

The corresponding FK field name would be:
"ClientID_FK"
"ProjectID_FK"
"ProdItemID_FK"
"ProdTableID_FK"

Easier for me to see relationships.

One other suggestion: Do Not use spaces in object names. Not in field names, form names, report names, table names, ....

[nforce19]

Ive tried doing what you said by leaving it as table/query and I get this error message:

[nick404]

Check what your field type is in your tables. Make sure they make sense for what you want entered, like it says text in numeric, etc.

If the field you are putting in the combo box is not unique (i.e. there are multiple duplicates within the table) then you are better off just selecting 'value list' and entering the values in yourself

[nforce19]

Im still getting errors, Is there a basic Purchase order database template out there that can get me started?

[nick404]

Most likely there is. It sounds to me like you just need to get the basics of access down. Access isn't a program you can jump into and learn on the go like excel. Watch a few tutorials and understand the essential concepts of tables, queries, forms, and reports. How and when you use them and figure out exactly what you want your database to do.
Here is one: https://support.office.com/en-in/art...7-d254561f8bce
This one has a plethora of videos on tons of topics, good for beginners. http://www.gcflearnfree.org/access2013
This site I use frequently for all sorts of inquiries; even have it bookmarked. An exquisite resource: http://www.datapigtechnologies.com/AccessMain.htm