Need inventory tracking and invoice forms

[jayvan39]

I need some help please.

It has been awhile since I have built a database and am currently trying to build an inventory control database.

The essential idea is track inventory from locations and by orders (invoices) for jobs. Below are tables.

Tblcategories
Category(PK)
tblCustomer
PrimaryPhone (PK) (I don’t think phone is the best for the primary key, any suggestions?)
FirstName
LastName
Company
SecondaryPhone
AddressLine1
AddressLine2
City
StateZip
FileAS

tblEmployee:
Company
LastName
FirstName
Email
JobTitle
HomePhone
MobilePhone (PK) (I don’t think phone is the best for the primary key, any suggestions?)
FaxNumber
AddressLine1
AddressLine2
City
Satae


Zip
FileAS(lastname,firstName)

tblInventory:
Item (PK)
Description
Category (FK)
UnitOfMeasure
Location (FK)
Supplier (FK)
Manufacurer (FK)
Model
ReorderLevel
TargetStockLevel
Discontinued

tblInventoryTransactions:
TransactionID (PK AutoNumer) (internal tracking number for transactions)
InvoiceOrder (this is found on each Invooice)
CreatedDate(from Invoice)
TranscactionType(FK)
Employess(FK)
Item(FK)
QuantityOrdered
LocationTo(FK)
LocationFrom(FK)
Customer(FK) (from Invoice)
UnitCostPerItem (from Invoice)
AMountNet (from Invoice)
QuanityShippedTotal (from Invoice)
TranspertationCost (from Invoice)
Tax (from Invoice)
TotalCost (query fro math)

tblLocation:
Location(PK) (Warehouse, Jobsite, Truck)
tblManufacturer:
Manufacturer (PK)

tblSuppliers:
Supplier (PK)
LastName
FirstName
Website
Email
JobTitle
BuisnessPhone
Address
City
State
Zip

tblTransactionType:
TranasctionType(PK) (out to jobsite; receive New stock; Restock Vehicle; return to shrinkage; shrinkage)
Add/Remove

tblUnitOfMeasure
UnitOfMeasure (PK) (gallon, rioll, bundle etc)

So, what I need is a form that will allow the user to tack the invoice and enater in the order information. This will include multiple items for one Job, and also multiples of one item, plus be able to add all the cost. They the incvoice entry to include the employee that entered the information. They also need it to select if what location it is goingto, a jobsite, wharehouse, or truck.

Then I need form to track all inventory. Help please J

[burrina]

So, what is the problem? You have all of your tables, add what you need to a query and filter as necessary!

[jayvan39]

Okay, wholy crap, that is exactly what I am looking for.

First my problem is that i cant get the subform to work right.

Like the picture above i want the items to be in a subform shown on the bottom, when i do this it just shows a blank box in the forms view

[burrina]

Can you post screenshots or code and a better explanation of the problem? Is the subform linked correctly?

[jayvan39]

I dont really have any yet, every time i go to create it it always wants to put the subform(datsheet view) on top so i delete and start over

[burrina]

Are you entering data in this subform and if so what happens when you do? On top of what? I am not understanding your problem well enough to help! Do you know how to do a print screen and paste into Microsoft Paint?

[jayvan39]

okay i got it to put the subform where i want it, but it doesnt look anything like your, i would love to have the menue and coulumn headers in there too

[burrina]

Again, what view are you using for the subform? I use a continuous form view for mine, here is a screenshot in design mode. Hope this helps.

[jayvan39]

Okay I think i am getting there, thanks

[burrina]

Glad to hear it. Keep plugging away! Good Luck with your project.

[jayvan39]

Okay so for your "Order total" did you write a separate query just for this?

[burrina]

NO. Just use a unbound textbox with something like this. =Nz([txtOrderTotal],0)

[jayvan39]

also my subform is only allowing for one line, how do i have it so i can enter multiple items?

[jayvan39]

nevermind i got it

[burrina]

Are you using it as a continuous form? Otherwise maybe what you want is a listbox instead?