Moving data into a new table once selected from a form

Hi All,

I have a scenario were I am managing an inventory database.Each item stored
is based on a project. Once the project is over and if there are inventory
spares left out , the items should be moved to a table called Rawmaterial to
be used for future project.

I want the user to be able to select the records he wants to move to the
rawmaterial which will delete the entry in the exisiting table and move both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno – project nmber – primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no – primary key
Prno - project number – primary key ( combination of both keys makes the
data unique)
Uom – Unit of measure
Cat_id – Cat id
Stock_no – Item stock no
Nuclear – Nuclear material or not
Selected – select flag

tbltransactions~~Table Name
transactionID – Autonumber
Itemno – Foreign key connecting tblitems
ItemID
DOR – Date of Receipt of item
UnitsReceived - Qty Received
DOI – Date of Issue of item
UnitsUsed – Qty Issued
Location – Location stored
IssuedTo – Issued to person or job

I want to use the select flag in the tblitems table to move the items. Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

Moving records around is generally sign of future problems. Can't you just
'mark' the items used/free ? New projects would then have to be 'trained' to
look only about 'free' items. Alternatively, instead of a check (used/free)
why not a field telling which 'project' has used it, with a NULL (no project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else, assume you
copied the item in the other table and, bang!, the system fails before you
can delete it from the original table. You end up with an inconsistent db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or rolled-back, if
problem occur). You can definitively use the flag in the WHERE clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup). That can
MASSIVELY change your tables. Example, without the WHERE clause, you can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each item
stored
is based on a project. Once the project is over and if there are inventory
spares left out , the items should be moved to a table called Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move to the
rawmaterial which will delete the entry in the exisiting table and move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag

tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOR - Date of Receipt of item
UnitsReceived - Qty Received
DOI - Date of Issue of item
UnitsUsed - Qty Issued
Location - Location stored
IssuedTo - Issued to person or job

I want to use the select flag in the tblitems table to move the items. Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

Hello Michel,

I have come to the end of my desiging and i am left with the step where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works fine. I
am able to sum all similar items used over different projects and append it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key voilation and
0 records due to lock violation and it runs the query but does not delete the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use after
all items have been used up for a project and once the project is completed
and they dont require the item anymore but have to account it as raw material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you just
'mark' the items used/free ? New projects would then have to be 'trained' to
look only about 'free' items. Alternatively, instead of a check (used/free)
why not a field telling which 'project' has used it, with a NULL (no project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else, assume you
copied the item in the other table and, bang!, the system fails before you
can delete it from the original table. You end up with an inconsistent db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or rolled-back, if
problem occur). You can definitively use the flag in the WHERE clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup). That can
MASSIVELY change your tables. Example, without the WHERE clause, you can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each item
stored
is based on a project. Once the project is over and if there are inventory
spares left out , the items should be moved to a table called Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move to the
rawmaterial which will delete the entry in the exisiting table and move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag

tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOR - Date of Receipt of item
UnitsReceived - Qty Received
DOI - Date of Issue of item
UnitsUsed - Qty Issued
Location - Location stored
IssuedTo - Issued to person or job

I want to use the select flag in the tblitems table to move the items. Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

You probably have ENFORCED the foreign constraint: on ItemNo in table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE constraint.

I don't suggest to turn the cascade delete on, but maybe you can SET the
itemNo to NULL, inside the appropriate tblTransactions records, BEFORE
deleting the ItemNo records from tblItems. Sure, since this is crucial and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction only if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to delete /
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works fine.
I
am able to sum all similar items used over different projects and append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key voilation
and
0 records due to lock violation and it runs the query but does not delete
the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you
just
'mark' the items used/free ? New projects would then have to be 'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL (no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else, assume
you
copied the item in the other table and, bang!, the system fails before
you
can delete it from the original table. You end up with an inconsistent
db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or rolled-back,
if
problem occur). You can definitively use the flag in the WHERE clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup). That
can
MASSIVELY change your tables. Example, without the WHERE clause, you can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each item
stored
is based on a project. Once the project is over and if there are
inventory
spares left out , the items should be moved to a table called
Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move to
the
rawmaterial which will delete the entry in the exisiting table and move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag

tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOR - Date of Receipt of item
UnitsReceived - Qty Received
DOI - Date of Issue of item
UnitsUsed - Qty Issued
Location - Location stored
IssuedTo - Issued to person or job

I want to use the select flag in the tblitems table to move the items.
Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

Thanks for replying i can see your point and it is true there is a
referential constraint between items and transcation.

I tried what you told me and it works fine it deletes the record from
tblitems.

but i do not know how to pack them in one single step and roll back if there
is some errors and commit if everything is fine.

I have 3 queries right now.

appendqry:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));



transactionqry: (sets the record in the transaction table to null for items
selected)

UPDATE tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID SET tbltransactions.TranItemID = Null
WHERE (((tblitems.selected)=True));


deleteqry:

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));


how to get it working in one sequence to ensure appending into a new table ,
updating the corresponding rows as null in transaction table to enable data
deletion will happen. Ideally once I set the itemno to null in the
transaction tables and delete it in tblitems, should i not delete it in the
transaction table as well.

Thanks for you time. If you could give me some pointers it would be very
helpful.
I am a newbie as far as coding in VB is concerned.

thanks


"Michel Walsh" wrote:

You probably have ENFORCED the foreign constraint: on ItemNo in table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE constraint.

I don't suggest to turn the cascade delete on, but maybe you can SET the
itemNo to NULL, inside the appropriate tblTransactions records, BEFORE
deleting the ItemNo records from tblItems. Sure, since this is crucial and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction only if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to delete /
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works fine.
I
am able to sum all similar items used over different projects and append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key voilation
and
0 records due to lock violation and it runs the query but does not delete
the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you
just
'mark' the items used/free ? New projects would then have to be 'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL (no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else, assume
you
copied the item in the other table and, bang!, the system fails before
you
can delete it from the original table. You end up with an inconsistent
db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or rolled-back,
if
problem occur). You can definitively use the flag in the WHERE clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup). That
can
MASSIVELY change your tables. Example, without the WHERE clause, you can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each item
stored
is based on a project. Once the project is over and if there are
inventory
spares left out , the items should be moved to a table called
Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move to
the
rawmaterial which will delete the entry in the exisiting table and move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag

tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOR - Date of Receipt of item
UnitsReceived - Qty Received
DOI - Date of Issue of item
UnitsUsed - Qty Issued
Location - Location stored
IssuedTo - Issued to person or job

I want to use the select flag in the tblitems table to move the items.
Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

A possible way is like:

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
On Error Resume Next ' on error, keep going on

ws.BeginTrans 'start the transaction

' do some job, modifying data in tables
ws.Databases(0).Execute "sqlStatementOne"
ws.Databases(0).Execute "sqlStatementTwo"
ws.Databases(0).Execute "sqlStatementThree"

If 0 = Err.Number Then '0= Err.Number, - no error

ws.CommitTrans 'end the transaction, OK

Else
' may decide to also show the Err.Description, could help
' in addition to warn the user that something got wrong
' and no modification had been done

ws.Rollback 'abort the transaction, nothing changed

End If


Note that rolling back the transaction does only avoid what has been done
through the transaction 'pointer'. If you do something to the database
through another mean, it won't be undone, since the (internal) transaction
log won't know anything about it.

Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Thanks for replying i can see your point and it is true there is a
referential constraint between items and transcation.

I tried what you told me and it works fine it deletes the record from
tblitems.

but i do not know how to pack them in one single step and roll back if
there
is some errors and commit if everything is fine.

I have 3 queries right now.

appendqry:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected, QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));



transactionqry: (sets the record in the transaction table to null for
items
selected)

UPDATE tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID SET tbltransactions.TranItemID = Null
WHERE (((tblitems.selected)=True));


deleteqry:

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));


how to get it working in one sequence to ensure appending into a new table
,
updating the corresponding rows as null in transaction table to enable
data
deletion will happen. Ideally once I set the itemno to null in the
transaction tables and delete it in tblitems, should i not delete it in
the
transaction table as well.

Thanks for you time. If you could give me some pointers it would be very
helpful.
I am a newbie as far as coding in VB is concerned.

thanks


"Michel Walsh" wrote:

You probably have ENFORCED the foreign constraint: on ItemNo in table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside
transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE constraint.

I don't suggest to turn the cascade delete on, but maybe you can SET the
itemNo to NULL, inside the appropriate tblTransactions records, BEFORE
deleting the ItemNo records from tblItems. Sure, since this is crucial
and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction only
if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to delete
/
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step where
i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it must
go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works
fine.
I
am able to sum all similar items used over different projects and
append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key
voilation
and
0 records due to lock violation and it runs the query but does not
delete
the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would
be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you
just
'mark' the items used/free ? New projects would then have to be
'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL (no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else, assume
you
copied the item in the other table and, bang!, the system fails before
you
can delete it from the original table. You end up with an inconsistent
db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or
rolled-back,
if
problem occur). You can definitively use the flag in the WHERE clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup).
That
can
MASSIVELY change your tables. Example, without the WHERE clause, you
can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each item
stored
is based on a project. Once the project is over and if there are
inventory
spares left out , the items should be moved to a table called
Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move to
the
rawmaterial which will delete the entry in the exisiting table and
move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys
makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag

tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOR - Date of Receipt of item
UnitsReceived - Qty Received
DOI - Date of Issue of item
UnitsUsed - Qty Issued
Location - Location stored
IssuedTo - Issued to person or job

I want to use the select flag in the tblitems table to move the
items.
Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

Missing the optional flag dbFailOnError


ws.Databases(0).Execute "sqlStatementXXX", dbFailOnError


Vanderghast, Access MVP


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
A possible way is like:

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
On Error Resume Next ' on error, keep going on

ws.BeginTrans 'start the transaction

' do some job, modifying data in tables
ws.Databases(0).Execute "sqlStatementOne"
ws.Databases(0).Execute "sqlStatementTwo"
ws.Databases(0).Execute "sqlStatementThree"

If 0 = Err.Number Then '0= Err.Number, - no error

ws.CommitTrans 'end the transaction, OK

Else
' may decide to also show the Err.Description, could help
' in addition to warn the user that something got wrong
' and no modification had been done

ws.Rollback 'abort the transaction, nothing changed

End If


Note that rolling back the transaction does only avoid what has been done
through the transaction 'pointer'. If you do something to the database
through another mean, it won't be undone, since the (internal) transaction
log won't know anything about it.

Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Thanks for replying i can see your point and it is true there is a
referential constraint between items and transcation.

I tried what you told me and it works fine it deletes the record from
tblitems.

but i do not know how to pack them in one single step and roll back if
there
is some errors and commit if everything is fine.

I have 3 queries right now.

appendqry:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));



transactionqry: (sets the record in the transaction table to null for
items
selected)

UPDATE tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID SET tbltransactions.TranItemID = Null
WHERE (((tblitems.selected)=True));


deleteqry:

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));


how to get it working in one sequence to ensure appending into a new
table ,
updating the corresponding rows as null in transaction table to enable
data
deletion will happen. Ideally once I set the itemno to null in the
transaction tables and delete it in tblitems, should i not delete it in
the
transaction table as well.

Thanks for you time. If you could give me some pointers it would be very
helpful.
I am a newbie as far as coding in VB is concerned.

thanks


"Michel Walsh" wrote:

You probably have ENFORCED the foreign constraint: on ItemNo in table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside
transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE constraint.

I don't suggest to turn the cascade delete on, but maybe you can SET the
itemNo to NULL, inside the appropriate tblTransactions records, BEFORE
deleting the ItemNo records from tblItems. Sure, since this is crucial
and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction only
if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to
delete /
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step
where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it
must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works
fine.
I
am able to sum all similar items used over different projects and
append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key
voilation
and
0 records due to lock violation and it runs the query but does not
delete
the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would
be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you
just
'mark' the items used/free ? New projects would then have to be
'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL (no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else,
assume
you
copied the item in the other table and, bang!, the system fails
before
you
can delete it from the original table. You end up with an
inconsistent
db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or
rolled-back,
if
problem occur). You can definitively use the flag in the WHERE
clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup).
That
can
MASSIVELY change your tables. Example, without the WHERE clause, you
can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each
item
stored
is based on a project. Once the project is over and if there are
inventory
spares left out , the items should be moved to a table called
Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move
to
the
rawmaterial which will delete the entry in the exisiting table and
move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

tblitems~~ Table Name
Itemno - Item no - primary key
Prno - project number - primary key ( combination of both keys
makes
the
data unique)
Uom - Unit of measure
Cat_id - Cat id
Stock_no - Item stock no
Nuclear - Nuclear material or not
Selected - select flag

tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
DOR - Date of Receipt of item
UnitsReceived - Qty Received
DOI - Date of Issue of item
UnitsUsed - Qty Issued
Location - Location stored
IssuedTo - Issued to person or job

I want to use the select flag in the tblitems table to move the
items.
Is
this possible. I do need help in vb as well as access sql query.

thanks in advance.

Hello Michel,

Thanks for all your help. With the help of your code and help from other
answers i have designed the following code:

Private Sub btntransfer_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb


wrk.BeginTrans
strQuery = "qoh_trial"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_nullqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "delete_query"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_deleteqry"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans
strQuery = "delete_query"

Exit_He
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine & _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."

MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

End Sub

Every query works well when i click on the button but how do i prompt the
user with a message warning them about the consequences. If they are sure
than continue but if they are not than none of the queries should work.

One thing that scares me is that all the queris are running without any
warnings like in record deletion and so on. How can a user message and
access warnings be set.

Can this be done. You have been very patient with me and thanks for all your
inputs. One last question does it make sense to flag the records that have
been deleted. My itemid is an autonumber and all the missing numbers are the
records deleted.

thanks


"Michel Walsh" wrote:

Missing the optional flag dbFailOnError


ws.Databases(0).Execute "sqlStatementXXX", dbFailOnError


Vanderghast, Access MVP


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
A possible way is like:

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
On Error Resume Next ' on error, keep going on

ws.BeginTrans 'start the transaction

' do some job, modifying data in tables
ws.Databases(0).Execute "sqlStatementOne"
ws.Databases(0).Execute "sqlStatementTwo"
ws.Databases(0).Execute "sqlStatementThree"

If 0 = Err.Number Then '0= Err.Number, - no error

ws.CommitTrans 'end the transaction, OK

Else
' may decide to also show the Err.Description, could help
' in addition to warn the user that something got wrong
' and no modification had been done

ws.Rollback 'abort the transaction, nothing changed

End If


Note that rolling back the transaction does only avoid what has been done
through the transaction 'pointer'. If you do something to the database
through another mean, it won't be undone, since the (internal) transaction
log won't know anything about it.

Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Thanks for replying i can see your point and it is true there is a
referential constraint between items and transcation.

I tried what you told me and it works fine it deletes the record from
tblitems.

but i do not know how to pack them in one single step and roll back if
there
is some errors and commit if everything is fine.

I have 3 queries right now.

appendqry:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));



transactionqry: (sets the record in the transaction table to null for
items
selected)

UPDATE tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID SET tbltransactions.TranItemID = Null
WHERE (((tblitems.selected)=True));


deleteqry:

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));


how to get it working in one sequence to ensure appending into a new
table ,
updating the corresponding rows as null in transaction table to enable
data
deletion will happen. Ideally once I set the itemno to null in the
transaction tables and delete it in tblitems, should i not delete it in
the
transaction table as well.

Thanks for you time. If you could give me some pointers it would be very
helpful.
I am a newbie as far as coding in VB is concerned.

thanks


"Michel Walsh" wrote:

You probably have ENFORCED the foreign constraint: on ItemNo in table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside
transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE constraint.

I don't suggest to turn the cascade delete on, but maybe you can SET the
itemNo to NULL, inside the appropriate tblTransactions records, BEFORE
deleting the ItemNo records from tblItems. Sure, since this is crucial
and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction only
if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to
delete /
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step
where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it
must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works
fine.
I
am able to sum all similar items used over different projects and
append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key
voilation
and
0 records due to lock violation and it runs the query but does not
delete
the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would
be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you
just
'mark' the items used/free ? New projects would then have to be
'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL (no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else,
assume
you
copied the item in the other table and, bang!, the system fails
before
you
can delete it from the original table. You end up with an
inconsistent
db.
With a transaction (handled through VBA, not through Jet-SQL), you
encapsulate the whole process as ONE step (to be commit, or
rolled-back,
if
problem occur). You can definitively use the flag in the WHERE
clause:


INSERT INTO rawMaterial( list_of_fields_here )
SELECT matching_list_of_fields_here
FROM items
WHERE selected


and


DELETE items.*
FROM items
WHERE selected



Sure, make experimentations on dumb data (or have a recent backup).
That
can
MASSIVELY change your tables. Example, without the WHERE clause, you
can
delete ALL the rows!



Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Hi All,

I have a scenario were I am managing an inventory database.Each
item
stored
is based on a project. Once the project is over and if there are
inventory
spares left out , the items should be moved to a table called
Rawmaterial
to
be used for future project.

I want the user to be able to select the records he wants to move
to
the
rawmaterial which will delete the entry in the exisiting table and
move
both
sturucture and data to the rawmaterial table.

Table Structure
tblprojects~~Table Name
pno - project nmber - primary key
pname- project name

Hello Michel,

Thought I'd let you know that I used the following code to get a warning
message. It works but wanted to know if it is in the right place in the code.

Private Sub btntransfer_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb


If MsgBox("You are about to delete and transfer selected records!!!
WARNING!!", vbYesNo) = vbYes Then

wrk.BeginTrans
strQuery = "qoh_trial"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_nullqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "itemno_deleteqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_deleteqry"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans

Else
Exit_He
End If
MsgBox "Items transferred to Rawmaterial database"
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine & _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."

MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

End Sub


thanks once again for all your help.


"vandy" wrote:

Hello Michel,

Thanks for all your help. With the help of your code and help from other
answers i have designed the following code:

Private Sub btntransfer_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb


wrk.BeginTrans
strQuery = "qoh_trial"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_nullqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "delete_query"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_deleteqry"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans
strQuery = "delete_query"

Exit_He
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine & _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."

MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

End Sub

Every query works well when i click on the button but how do i prompt the
user with a message warning them about the consequences. If they are sure
than continue but if they are not than none of the queries should work.

One thing that scares me is that all the queris are running without any
warnings like in record deletion and so on. How can a user message and
access warnings be set.

Can this be done. You have been very patient with me and thanks for all your
inputs. One last question does it make sense to flag the records that have
been deleted. My itemid is an autonumber and all the missing numbers are the
records deleted.

thanks


"Michel Walsh" wrote:

Missing the optional flag dbFailOnError


ws.Databases(0).Execute "sqlStatementXXX", dbFailOnError


Vanderghast, Access MVP


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
A possible way is like:

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
On Error Resume Next ' on error, keep going on

ws.BeginTrans 'start the transaction

' do some job, modifying data in tables
ws.Databases(0).Execute "sqlStatementOne"
ws.Databases(0).Execute "sqlStatementTwo"
ws.Databases(0).Execute "sqlStatementThree"

If 0 = Err.Number Then '0= Err.Number, - no error

ws.CommitTrans 'end the transaction, OK

Else
' may decide to also show the Err.Description, could help
' in addition to warn the user that something got wrong
' and no modification had been done

ws.Rollback 'abort the transaction, nothing changed

End If


Note that rolling back the transaction does only avoid what has been done
through the transaction 'pointer'. If you do something to the database
through another mean, it won't be undone, since the (internal) transaction
log won't know anything about it.

Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Thanks for replying i can see your point and it is true there is a
referential constraint between items and transcation.

I tried what you told me and it works fine it deletes the record from
tblitems.

but i do not know how to pack them in one single step and roll back if
there
is some errors and commit if everything is fine.

I have 3 queries right now.

appendqry:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));



transactionqry: (sets the record in the transaction table to null for
items
selected)

UPDATE tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID SET tbltransactions.TranItemID = Null
WHERE (((tblitems.selected)=True));


deleteqry:

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));


how to get it working in one sequence to ensure appending into a new
table ,
updating the corresponding rows as null in transaction table to enable
data
deletion will happen. Ideally once I set the itemno to null in the
transaction tables and delete it in tblitems, should i not delete it in
the
transaction table as well.

Thanks for you time. If you could give me some pointers it would be very
helpful.
I am a newbie as far as coding in VB is concerned.

thanks


"Michel Walsh" wrote:

You probably have ENFORCED the foreign constraint: on ItemNo in table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside
transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE constraint.

I don't suggest to turn the cascade delete on, but maybe you can SET the
itemNo to NULL, inside the appropriate tblTransactions records, BEFORE
deleting the ItemNo records from tblItems. Sure, since this is crucial
and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction only
if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to
delete /
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step
where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than it
must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it works
fine.
I
am able to sum all similar items used over different projects and
append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key
voilation
and
0 records due to lock violation and it runs the query but does not
delete
the
selected record.

This step is cruicial since in an append query if i leave the selected
record and someone unchecks and checks it and runs this query i would
be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems. Can't you
just
'mark' the items used/free ? New projects would then have to be
'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL (no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else,
assume
you
copied the item in the other table and, bang!, the system fails
before
you
can delete it from the original table. You end up with an
inconsistent
db.

The following, in Northwind, works fine:



===================================
Public Sub ShowTransactionOne()
Dim ws As DAO.Workspace
Set ws = DBEngine(0)
On Error Resume Next ' on error, keep going on

ws.BeginTrans 'start the transaction

' do some job, modifying data in tables
ws.Databases(0).Execute "UPDATE Employees SET LastName = 'Smith' WHERE
LastName='Davolio'"


If 0 = Err.Number Then '0= Err.Number, - no error

ws.CommitTrans 'end the transaction, OK

Else
MsgBox Err.Description, vbOKOnly, "Some problem occured"

ws.Rollback 'abort the transaction, nothing changed

End If


End Sub

===================================


You can test it by running the subroutine (in the immediate window) and then
open (or refresh an already open view of) table Employee in data view.


Sure, restore the modification once convinced it works.


I don't know what is really going on with your test, but one thing that I
personnaly avoid is to jump into an if statement (that is a compiled time
error under the framework, but even while it is not a compile error, in VBA,
I am no very warm about using it).

===================
....
if ...
Else
Exit_He
End If
....
Err_Handler:
....
Resume Exit_Here ----- your code jump inside the else part of an
if statement!

=========================

Vanderghast, Access MVP



"vandy" wrote in message
...
Hello Michel,

Thought I'd let you know that I used the following code to get a warning
message. It works but wanted to know if it is in the right place in the
code.

Private Sub btntransfer_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb


If MsgBox("You are about to delete and transfer selected records!!!
WARNING!!", vbYesNo) = vbYes Then

wrk.BeginTrans
strQuery = "qoh_trial"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_nullqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "itemno_deleteqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_deleteqry"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans

Else
Exit_He
End If
MsgBox "Items transferred to Rawmaterial database"
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine &
_
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."

MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

End Sub


thanks once again for all your help.


"vandy" wrote:

Hello Michel,

Thanks for all your help. With the help of your code and help from other
answers i have designed the following code:

Private Sub btntransfer_Click()

Dim dbs As DAO.Database
Dim wrk As DAO.Workspace
Dim strQuery As String, strMessage As String

On Error GoTo Err_Handler

Set wrk = DAO.DBEngine.Workspaces(0)
Set dbs = CurrentDb


wrk.BeginTrans
strQuery = "qoh_trial"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_nullqry"
dbs.Execute strQuery, dbFailOnError
strQuery = "delete_query"
dbs.Execute strQuery, dbFailOnError
strQuery = "transaction_deleteqry"
dbs.Execute strQuery, dbFailOnError
wrk.CommitTrans
strQuery = "delete_query"

Exit_He
Exit Sub

Err_Handler:
strMessage = Error & " (" & Err.Number & ")" & vbNewLine & vbNewLine
& _
"(Error in " & strQuery & ")" & _
vbNewLine & vbNewLine & "Transaction rolled back and no tables
updated."

MsgBox strMessage, vbExclamation, "Error"

wrk.Rollback
Resume Exit_Here

End Sub

Every query works well when i click on the button but how do i prompt the
user with a message warning them about the consequences. If they are
sure
than continue but if they are not than none of the queries should work.

One thing that scares me is that all the queris are running without any
warnings like in record deletion and so on. How can a user message and
access warnings be set.

Can this be done. You have been very patient with me and thanks for all
your
inputs. One last question does it make sense to flag the records that
have
been deleted. My itemid is an autonumber and all the missing numbers are
the
records deleted.

thanks


"Michel Walsh" wrote:

Missing the optional flag dbFailOnError


ws.Databases(0).Execute "sqlStatementXXX", dbFailOnError


Vanderghast, Access MVP


"Michel Walsh" vanderghast@VirusAreFunnierThanSpam wrote in message
...
A possible way is like:

Dim ws As DAO.Workspace
Set ws = DBEngine(0)
On Error Resume Next ' on error, keep going on

ws.BeginTrans 'start the transaction

' do some job, modifying data in tables
ws.Databases(0).Execute "sqlStatementOne"
ws.Databases(0).Execute "sqlStatementTwo"
ws.Databases(0).Execute "sqlStatementThree"

If 0 = Err.Number Then '0= Err.Number, - no error

ws.CommitTrans 'end the transaction, OK

Else
' may decide to also show the Err.Description, could help
' in addition to warn the user that something got wrong
' and no modification had been done

ws.Rollback 'abort the transaction, nothing changed

End If


Note that rolling back the transaction does only avoid what has been
done
through the transaction 'pointer'. If you do something to the
database
through another mean, it won't be undone, since the (internal)
transaction
log won't know anything about it.

Hoping it may help,
Vanderghast, Access MVP


"vandy" wrote in message
...
Thanks for replying i can see your point and it is true there is a
referential constraint between items and transcation.

I tried what you told me and it works fine it deletes the record
from
tblitems.

but i do not know how to pack them in one single step and roll back
if
there
is some errors and commit if everything is fine.

I have 3 queries right now.

appendqry:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no, selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));



transactionqry: (sets the record in the transaction table to null
for
items
selected)

UPDATE tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID SET tbltransactions.TranItemID = Null
WHERE (((tblitems.selected)=True));


deleteqry:

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));


how to get it working in one sequence to ensure appending into a new
table ,
updating the corresponding rows as null in transaction table to
enable
data
deletion will happen. Ideally once I set the itemno to null in the
transaction tables and delete it in tblitems, should i not delete it
in
the
transaction table as well.

Thanks for you time. If you could give me some pointers it would be
very
helpful.
I am a newbie as far as coding in VB is concerned.

thanks


"Michel Walsh" wrote:

You probably have ENFORCED the foreign constraint: on ItemNo in
table of
transactions:

============extract from your design===
tbltransactions~~Table Name
transactionID - Autonumber
Itemno - Foreign key connecting tblitems
ItemID
==================================


and so, cannot delete Items.ItemNo if it appears inside
transactions.ItemNo,
with tbltransactions having an enforced NO CASCADE DELETE
constraint.

I don't suggest to turn the cascade delete on, but maybe you can
SET the
itemNo to NULL, inside the appropriate tblTransactions records,
BEFORE
deleting the ItemNo records from tblItems. Sure, since this is
crucial
and
complex, you should 'pack' the whole process inside a single
database-transaction so if any problem occur, you can ROLLBACK the
modifications, while you will commit the said database transaction
only
if
every thing turns fine, without any error.


It would have been more robust if there would have been NO NEED to
delete /
move records all around, imho.



Vanderghast, Access MVP


"vandy" wrote in message
...
Hello Michel,

I have come to the end of my desiging and i am left with the step
where i
have to create another table with Qty on hand values.

Reason: once the item is used up for a project or projects than
it
must go
to a raw material shelf or table in this case.

I am working with the selected check box option now.

I have an append query:

INSERT INTO trailrawmaterial ( Itemno, ItemDesc, Stock_no,
selected,
QOH )
SELECT tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected, Sum([units]*[type]) AS qoh
FROM tblitems INNER JOIN tbltransactions ON tblitems.ItemID =
tbltransactions.TranItemID
GROUP BY tblitems.Itemno, tblitems.ItemDesc, tblitems.Stock_no,
tblitems.selected
HAVING (((tblitems.selected)=True));


trailrawmaterial is my dummy rawmaterial table created and it
works
fine.
I
am able to sum all similar items used over different projects and
append
it
to a feild called QOH in my new table. It works fine.

The problem is deleting the selected record.

DELETE tblitems.*, tblitems.selected
FROM tblitems
WHERE (((tblitems.selected)=True));

Access warning pops ups saying cannot delete record due to key
voilation
and
0 records due to lock violation and it runs the query but does
not
delete
the
selected record.

This step is cruicial since in an append query if i leave the
selected
record and someone unchecks and checks it and runs this query i
would
be
having duplicate values.

Am I in the right direction. Ideally these queries will be put to
use
after
all items have been used up for a project and once the project is
completed
and they dont require the item anymore but have to account it as
raw
material
stock to be used up for future projects.

any help in the right direction would be appreciated.
thanks in advance






"Michel Walsh" wrote:

Moving records around is generally sign of future problems.
Can't you
just
'mark' the items used/free ? New projects would then have to be
'trained'
to
look only about 'free' items. Alternatively, instead of a check
(used/free)
why not a field telling which 'project' has used it, with a NULL
(no
project
has used it yet) when it is free?


Sure, if you have to, you have to, but use a transaction. Else,
assume
you
copied the item in the other table and, bang!, the system fails
before
you
can delete it from the original table. You end up with an
inconsistent
db.