Adding Records To Tables?

[Guest]

Hi

Is it possible to add records to a table (eg Stock) then have a button on a
table which adds the records just entered to another table (eg Incoming
Transactions)?

Thanks for all your help,
Louisa

 

[Guest]

You don't have buttons on tables, buttons are on forms. If you are allowing
users to enter data directly into tables, you are setting yourself up for
disaster. Users should never be allowed to enter directly into tables. All
data entry should be done using forms. The forms should also be constructed
to prevent the user form entering incorrect or incomplete data.

What you want to do is possilbe, but it will take a little VBA code. What
is your experience with Access and VBA?

 

[Guest]

Im experienced in Access but done very little VBA.
Please could you help me out with some code for:-
I am trying to create an inventory application in Access 2007.

I have created the following tables:-
- Stock table (where information is entered and stored on each shoe
when it
comes in)
- Warehouse table (where stock is kept, if transferring from one shop
to another)
- Shop table (eg. Nelson, where information is stored on each shoe)
- Incoming Transactions table (where information is stored on
incoming
transactions eg, new shoes come in)
- Outgoing Transactions (where information is stored on outgoing
transactions
eg, selling a pair of shoes).
- Sales table (where information is stored when we sell a shoe)

After I enter the shoe information into the stock table when we have
recieved an order. I would like to press a button to add the records to that
stock table and add/update the records to the incoming transactions table and
the shop/warehouse table, (to whatever location its going to ).

When we have sold a shoe, I would like to enter the details into the sales
table. When I press a button, the records will be added to the sales table
and the outgoing transaction table. Then in turn the quantity of stock will
be updated from where we have sold the shoe, (eg Nelson or Warehouse table).

When we transfer a shoe, it goes from a shop to the warehouse on a credit
note then it is invoiced to another shop. When this happens I would like the
relevant shops stock to be updated and the appropriate transaction table.

I need your precious advice on how to automatically update the stock?
Will I need to create update queries for each situation? Or create a macro
which contains an update query and VBA?

I hope my explanation is clear to you.
Please do not hesitate to ask me any questions if you need to clarify before
being able to answer my question.

Thanks for all your help,
Louisa Holt.

 

[Guest]

I would rethink my database design. If the inventory is to be shared among
all shops and the common warehouse, You should really have only one
inventory table with a field that shows the location of the inventory. This
will make everything else in your system much easier to accomplish.
Otherwise, you will find you have to have mutiple queries and a lot of code
when it comes to inventory look ups, reporting, etc.
A typical shoe inventory table will have
Vendor (Mfgr)
Style
Size
Color
Qty
Location
Shelf/Bin

etc.

 

[Guest]

However, I would like to be able to see a history of all transactions. For
example, to see what date the shoes went into a shop, and how many they've
sold over a certain amount of time or up to the present day.
Will this be possible with one inventory table?

Thank you very much for your help, Louisa

 

[Guest]

Yes, it is certainly possible. What you want, and again is very common in
Inventory systems, is a Transaction table. Each time you receive, transfer,
return, sell, or scap an item, you create a transaction record that shows
what happened to the inventory. That will give you full life cycle history
of your inventory. You just need to structure your table so that it contains
all the data you want to track for a transaction.

You form should then use the Transaction table as its record source. Now,
we are right back where we started. Once the transaction is completed, you
may need to update the Inventory table if it involves a change in quantities
or cost.

That you can do it the form's After Update event. You could use an Update
query to update the information in the Inventory table.

 

[Guest]

Am I right in saying that you create a transaction form which will add those
details to the transaction table. I have done this, but it comes up with an
error saying - "You cannot add or change a record because a related record is
required in table Stock.
Then on the transaction form you need a button that runs an update query,
which will update quantites or cost in the stock table.
Am I also right in saying that the transaction is recorded before any queries?

Thank you very much, Louisa

 

[Guest]

--
Dave Hargis, Microsoft Access MVP

Am I right in saying that you create a transaction form which will add those
details to the transaction table.

Yes

I have done this, but it comes up with an

error saying - "You cannot add or change a record because a related record is
required in table Stock.

The inventory table does not need to be linked to the transaction table in
this form. I would suggest a combo box to look up the inventory item code
and description.

 

[Guest]

Sorry, you're being a great help, but Im still struggling with some aspects.

I have created a transaction form that adds the records to the transaction
table. But unfortunately it doesn't add the stock id and quantity. Is this
because I have a one to many relationship from stock table to transaction
table?

Also on the transaction form I have a button that is supposed to run an
update query, but that is not working either. Is the following query correct?
- I am updating the fields from tblStock to the appropriate fields in
tblTransactions.

You have also mentioned in an earlier post that you can use an update query
(to update the information in the stock table) in the form's After Update
event. To do this do I write the following SQL:-
UPDATE tblStock
SET Size = (SELECT Size
FROM tblTransactions)

Thank you very much for all your help, sorry if being a pain,
Thanks again Louisa

 

[Guest]

The SQL appears to be correct, but why are you updating the size? The size
in the Inventory table should never change. Isn't it the quantity you want
to update?

 

[Guest]

Please help, where do I write the following SQL exactly?
UPDATE tblStock
SET Quantity = (SELECT Quantity
FROM tblTransactions)

 

[Guest]

There is a problem here. You would not want to be replacing the value in the
quantity field. You would either add or subtract the transaction quantity
from the on hand quantity in the inventory table. It is not uncommon for
both ins and outs to be in the same field, but outs are usually negative
numbers and ins are postitive numbers.

Another thing. If you are transfering, you will either need logic to modify
the quantity correctly for both sides of the transfer or more typically, it
would actually be two transactions.

As to your code, rather than expect a user will remember to update the
inventory with a command button, you may want to do it in the form's after
update event.

That brings to mind another point. Once a transaction has been saved, it
should not be allowed to be edited again.