Invoices

M

McC

I'm making a database for a book store and I have created the invoice...
However, when I want to add a new record, i.e. make a new invoice for a
different customer, the old one gets lost, I have no idea how to make this
work
 
R

Rick B

Is the form bound to your table? If you look at the table are the records
there? If so, perhaps you have the "data entry" property set to "true" on
your form. That property will not let you pull up old records, only add new
ones. If you have the "data entry" property turned on, turn it off.
 
M

McC

What exactly do you mean by the form being bound to the table?
'Data Entry' is off. I can add new data fine, like i put in the ID and all
the rest of the information comes up it's just when I make a new invoice...
it's lost... as I said before.

So how would I bind the form to the table... I've probably done it.. I just
don't know that I have.
 
R

Rick B

Look at the "properties" for your form and see what is in "data source". It
should be the name of your table or query.

You did not say if the data is indeed getting stored in your table. Go open
the table and see if the data is there.
 
M

McC

I'm going to seem like such an idiot now but... the data is stored in the
query when it's run but it's just a list of all the books sold... It doesn't
say who bought it or when it was bought etc.
 
R

Rick B

Okay step back a bit here. Data is not stored in a query, it is stored in a
table.

The first thing you do in a database is build your tables. The tables are
where all your data is stored. The next step is to build queries, forms,
and reports. These are the items that allow you to view, modify, and add
data to the tables.

Typically, a form would be based off of the table (bound to it) if it were
simply used to enter data in the table. If you open the form in design-view
and look at the "data source" you'd see the table name listed there.

If the form is also used to view data, it will often be bound to a query.
The query can help filter out some records. For example, if your table has
a field in it called "inactive", you may want to create a query that will
exclude all the inactive records. Binding your form to this query would
cause the inactive records to be excluded from the form as well.

Now, back to my question...
1) In your form, what is the data source; what do you see in that field in
the properties box?
2) When you open the table (double-click it) do you see the "missing"
records? In other words, are the records that you think have vanished
actually in the table?
 
M

McC

Oh, and the form is linked...

McC said:
I'm going to seem like such an idiot now but... the data is stored in the
query when it's run but it's just a list of all the books sold... It doesn't
say who bought it or when it was bought etc.
Click to expand...
 
M

McC

The IDs are all in a table but the rest of the information isn't... That's
what I used the query for, to link the ID to the list of all the books.

Which form do you mean? The subform where I input the data or the main form
which I use to have like customer details and the invoice number etc.?
 
B

Brian Bastl

McC,

how do you have your relevant tables set up?

Very basically, you should have 4 tables:
Customers
Products
Orders
Order Details

Orders table should contain things like:
OrderID - primary key
OrderDate
CustomerID - foreign key to Customer table

Order Details should contain things like:
OrderDetailsID - primary key
OrderID - foreign key to Orders table
ProductID
Quantity
PriceEach

You'd use the Orders table as the recordsource for the main form, and the
Order Details table as the recordsource for the subform, linking them by
OrderID.

HTH,
Brian
 
L

Lynn Trapp

A foreign key is a field in a table that identifies how one table is related
to another table. For example, if you have a table for Orders and another
one for Order Details, the foreign key would identify which record in the
Orders table each of the records in the Order Details table are related to.
Typically, the foreign key will store the value of the Primary Key from it's
"parent" table, although it could conceivably link to another unique
candidate key. Look at the table layout below.

Orders
OrderID -- Primary Key
OrderDate
-- Other fields related to the Order

OrderDetails
OrderDetailID -- Primary Key
OrderID -- Foreign Key linked to Primary Key of Orders table.
-- Other fields related to Order Details

The Orders table might contain the following

OrderID OrderDate SomeOtherField
1 4/24/06 BlahBlah
2 4/25/06 MoreBlahBlah

The OrderDetails Table might contain the following
OrderDetailsID OrderID OtherField
1 1 LaLaLa
2 1 LaLaLa
3 2 LaLaLa

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
Top