Sending Info into A report

[JayX]

Is there a way to click a button in form view where it will send information into a report view and gets saved there automatically?

[jzwp11]

Access only stores data in tables. Forms allow you to view/edit data in the table, reports allow you to view/print data (in a format you design) from the table. So you cannot save data to a report.

[boblarson]

Okay, first off -

Data gets stored in tables.

Queries display data from tables, usually with some criteria to limit the results returned. They do not store data, but if they are updatable, they can be used to insert, edit, and delete data from tables.

Forms display data (can be bound to tables for storing or just unbound which would mean it isn't stored at all)

Reports display data from tables/queries. They do not store data.

[JayX]

Ok so if you see in this picture this is information I'd like to be able to keep track of in a report view by clicking the submit button, is that possible and if not how should I go about this

[jzwp11]

If you push the data you see in a form to a report, you will get a physical print out of that data. Is that what you are after?

Is the form you show bound to an underlying table?

[JayX]

Yes that is exactly what I am looking for...I am just trying to keep track of inventory within the company shown in the picture above. I need that to be shown in a nice format in a report view, how is that done?

Help would be much appreciated..thank you

[jzwp11]

Well, first you have to create the report which is nothing more than a template. The report must be bound to the same table or query that the form you show is bound.

Now if you print a report without filtering it in any way, it will print a page or pages for each record in the table or query to which it is bound. I will assume that you would want to print a page only for the particular record shown in the form at the time you hit the submit button. If I am correct, then you will need to filter the report to only that record that is currently displayed in the form. That will take a little bit of code behind the submit button to accomplish. So I will let you create the report first. When you have that done, can you post the database (with any sensitive data removed)?

You might want to take a look at this site. It has some very good video tutorials that I think you will find helpful.

[JayX]

I started to put my report together but it won't allow me to open it due to this error 'Type Mismatch in expression'. what is that?

[jzwp11]

A type mismatch error usually means that the data types of two fields do not match. For example, a number datatype field cannot be joined to a text datatype field. The error would usually point to something in table or query on which the report is based, not so much with the report unless you are trying to format a control inappropriately. It is hard to tell for sure without seeing your database. Can you zip and post it (with any sensitive data removed/altered)?

[JayX]

yes, I will do that now give me 10 min please

[JayX]

Here it is...It is an inventory control project I have to work on, please look it over and help me out the best you can.

Much Appreciated.

[jzwp11]

I see that you have your tables named as spreadsheets and looking at the fields in the inventory spreadsheet table, it looks like a spreadsheet. A relational database like Access is completely different from a spreadsheet and requires a different way of organizing data. When you structure a relational database, you have to keep in mind the rules of normalization. This site has an overview of normalization.

There is no point in working on your form issue with the tables you have because if you change the table structure (which is required by the rules of normalization), the forms will have to be reworked anyway.

From the limited information I have from your posts, you are working on an inventory system. It just so happens that you picked one of the more challenging relational database applications.

You will need a product table. This table will only hold essentially of list of products. The fields in that table will be used to hold information about the product such as its name, a product number and maybe a price and some other things that describe the product. I typically have a field in each table that is used to assign a unique number to each record in the table. This field is called the primary key and usually has no significance to the users. I use the autonumber datatype for this field.


tblProducts
-pkProductID primary key, autonumber
-txtProductNo
-txtProductName

pk=primary key, autonumber datatype field
txt=text datatype field
fk=foreign key, must be a long integer number datatype field
dte=date/time datatype field
lng=long integer number datatype field

Typically, you will have sales orders that would pull products from inventory and you would have purchase order to add products to inventory. So, we will need an order table

tblOrders
-pkOrderID primary key autonumber
-dteOrder
-fkOrderTypeID foreign key to tblOrderTypes (see below)
-fkCompanyID foreign key to tblCompanies

tblOrderTypes (this table will have 2 records: Sales, Purchase)
-pkOrderTypeID primary key, autonumber
-txtOrderType

An order (of either type) usually has many products associated with it: one-to-many relationship (1 order: many products). Also, a product can be a part of many orders: another one-to-many relationship (1 product: many orders)

When there are 2 one-to-many relationships between the same two entities (orders and products) you have a many-to-many relationship which is handled with a junction table. In this case, we can call this junction table a transaction table since we are conducting transactions in and out of inventory.

tblTransactions
-pkTransID primary key, autonumber
-fkOrderID foreign key to tblOrders
-fkProductID foreign key to tblProducts
-lngQty (a quantity field)

With the tables I have shown, you have the basic tables needed for an inventory tracking application. As I said earlier, inventory is one of the more challenging relational database applications. You may want to take a look at this site for a different approach. Notice the different table structure that Allen uses in his example.


This site has some good video tutorials on using Access that you might find helpful.

[JayX]

Thanks a lot, the help is greatly appreciated! I am doing this for a project and when I get back to the project I will def. look into all of this.

Thanks again!

[jzwp11]

You're welcome.

[JayX]

So I read all the information you sent me and I only have 2 tables:

One is my inventory spreadsheet that I imported from excel and this contains
pkProduct_ID
Product Description

the other is Employee spreadsheet which contains employee information:
pkEmployee_ID
Employees_Name

I am confused to where I need to go from here and how to set up my relationship correctly in order for the information I need to show up in a report view?