helping in a database problem

[rramico]

hey guys..i'm new here and also new in using ms access.pls if you could help me i will be in great debt to you..i want to solve a problem that sounds like this:


lets say i have 3 shops and same products in all shops and different stock for each product in each shop. i want to create something that will allow me to move a certain quantity of a product from a shop to another.for example:
i want to move 3 books(the same unique code) from shop 1 to shop 2 and automatically subtract that 3 books from shop 1 and add that 3 books in shop 2.i have designed the form with the options and button. it looks like this: http://postimage.org/image/aam3uvxg/ .so when i click that button it should to those operations(subtract from.. and add to..).i also did the macro that allows me to choose from those options i designed there but i don't know what command to write in that macro.
how can i do this?
thanks a lot

[jo15765]

This should work, I am still a beginner at SQL myself, but the syntax looks correct.

This should be the onclick() event of the button that you added to your form:

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO *tableName you are inserting into* (Quantity,  Code) VALUES('" & Me.Quantity & "', '" & Me.Code & "')"

DoCmd.RunSQL "Delete *tableName you are deleting from* (Quantity,Code)  Values ('" & Me.Quantity & "', '" & Me.Code & "')"

DoCmd.SetWarnings True

And make sure you add the table that you are wanting to insert into, and that is assuming that the name of the fields on your form are Quantity, and Code

[Sudha]

Hi,

Could someone help me to solve the below problem urgently please......

I have 2 tables

1. 1st table contains the Pkey(autonumber)
2. The same feild is set as Fkey in the 2nd table

Problem:

I need to get the autnumber from the parent table to child table.

Kindly do the needful.

Thanks & Regards,
Sudha

[jo15765]

lets say the parent table is called table1 and child table is table2

open a query pull in table1 and table2

pull down fkey, and change to an update query

on the update to line type:
[table1].[Pkey]

This is saying to update the field fkey to the value of Pkey in table1

[rramico]

This should work, I am still a beginner at SQL myself, but the syntax looks correct.

This should be the onclick() event of the button that you added to your form:

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO *tableName you are inserting into* (Quantity,  Code) VALUES('" & Me.Quantity & "', '" & Me.Code & "')"

DoCmd.RunSQL "Delete *tableName you are deleting from* (Quantity,Code)  Values ('" & Me.Quantity & "', '" & Me.Code & "')"

DoCmd.SetWarnings True

And make sure you add the table that you are wanting to insert into, and that is assuming that the name of the fields on your form are Quantity, and Code

thanks for the quick answer. i've managed to solve this in a similar way.
i've created an sql update query for adding(http://www.sql-tutorial.net/SQL-UPDATE.asp) the quantity and another for subtracting.and in the macro i gave the command open query, and set the onclick event on running that macro. it works just fine.

[Sudha]

Thank you :-)

lets say the parent table is called table1 and child table is table2

open a query pull in table1 and table2

pull down fkey, and change to an update query

on the update to line type:
[table1].[Pkey]

This is saying to update the field fkey to the value of Pkey in table1