cascading delete coding help required

[vandy]

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in TblProjects.
TblTransaction handles the item quantity on hand for each project.

The way the database works a new item or an existing is received or issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong project no
and receives the item under that project. Once the user tries to delete the
line item to link the correct project the system does not allow him to do so
telling him that some transaction values are associated with the record and
the record cannot be deleted.

I think in order to completely delete the line item I have to write a
cascading delete query to delete values in the master and child record
simultaneously.

Any help would be highly appreciated.

 

[Graham Mandeno]

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically deleted.

2. Execute a SQL statement to delete the related records explicitly before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong project.
The best solution then might be to "move" the transaction records so that
they are related to the correct project, so that the data entry does not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError

 

[vandy]

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction record
should i have a additional check box in the form and then let the user input
the WrongPID and CorrectPID. How to call the particular record which needs
correction in a userform to enable correction by the user.

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically deleted.

2. Execute a SQL statement to delete the related records explicitly before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong project.
The best solution then might be to "move" the transaction records so that
they are related to the correct project, so that the data entry does not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in TblProjects.
TblTransaction handles the item quantity on hand for each project.

The way the database works a new item or an existing is received or issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong project
no
and receives the item under that project. Once the user tries to delete
the
line item to link the correct project the system does not allow him to do
so
telling him that some transaction values are associated with the record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to write a
cascading delete query to delete values in the master and child record
simultaneously.

Any help would be highly appreciated.

 

[Graham Mandeno]

I suggest you create a small unbound dialog form to "delete" a project.
Open this form in acDialog mode from a "Delete" button on your main form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all related
records, or move the related records to another project before deleting.

If the "delete" option is selected, then delete the elated transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project from a
combo box that is populated with all the possible destination projects (at
its simplest, this would be all projects EXCEPT the one being deleted).
When a move target has been selected, use the option 3 code to move the
related transaction records, and then delete the main project record.

This is a technique commonly used to merge duplicate records in a database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction
record
should i have a additional check box in the form and then let the user
input
the WrongPID and CorrectPID. How to call the particular record which needs
correction in a userform to enable correction by the user.

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically deleted.

2. Execute a SQL statement to delete the related records explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong project.
The best solution then might be to "move" the transaction records so that
they are related to the correct project, so that the data entry does not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each project.

The way the database works a new item or an existing is received or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong
project
no
and receives the item under that project. Once the user tries to delete
the
line item to link the correct project the system does not allow him to
do
so
telling him that some transaction values are associated with the record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to write a
cascading delete query to delete values in the master and child record
simultaneously.

Any help would be highly appreciated.

 

[vandy]

Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the DELETE_PROJECT form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, , Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd, acDialog
End Sub

The first open form is attempting to pass the PID value from the form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed . When
the user selects the Project no the Project id gets captured in the subform
the Itemno is also in a combo box and when the user clicks on the itemno the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the item
does not belong to project 1880 which has a pid 368 but to 1887 pid 372 than
he goes back selects the item no and and has to go to the combo box to change
the value. how to be able to capture that action ie. the second line in the
trasaction table to be able to change 368 to 372 or to delete the record
completely.

I hope i am not confusing or complicating things .

I suggest you create a small unbound dialog form to "delete" a project.
Open this form in acDialog mode from a "Delete" button on your main form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all related
records, or move the related records to another project before deleting.

If the "delete" option is selected, then delete the elated transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project from a
combo box that is populated with all the possible destination projects (at
its simplest, this would be all projects EXCEPT the one being deleted).
When a move target has been selected, use the option 3 code to move the
related transaction records, and then delete the main project record.

This is a technique commonly used to merge duplicate records in a database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction
record
should i have a additional check box in the form and then let the user
input
the WrongPID and CorrectPID. How to call the particular record which needs
correction in a userform to enable correction by the user.

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically deleted.

2. Execute a SQL statement to delete the related records explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong project.
The best solution then might be to "move" the transaction records so that
they are related to the correct project, so that the data entry does not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each project.

The way the database works a new item or an existing is received or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong
project
no
and receives the item under that project. Once the user tries to delete
the
line item to link the correct project the system does not allow him to
do
so
telling him that some transaction values are associated with the record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to write a
cascading delete query to delete values in the master and child record
simultaneously.

Any help would be highly appreciated.

 

[Graham Mandeno]

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog, OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions to another
PID, I would do it by adding a third option to your dialog form. This
option could show a multi-select listbox of all transactions related to the
PID that was passed to the form. The user could select one or more of them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete project
c) Reassign one or more transactions to another project and DO NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the DELETE_PROJECT form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd, acDialog
End Sub

The first open form is attempting to pass the PID value from the form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed . When
the user selects the Project no the Project id gets captured in the
subform
the Itemno is also in a combo box and when the user clicks on the itemno
the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the item
does not belong to project 1880 which has a pid 368 but to 1887 pid 372
than
he goes back selects the item no and and has to go to the combo box to
change
the value. how to be able to capture that action ie. the second line in
the
trasaction table to be able to change 368 to 372 or to delete the record
completely.

I hope i am not confusing or complicating things .

I suggest you create a small unbound dialog form to "delete" a project.
Open this form in acDialog mode from a "Delete" button on your main form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all related
records, or move the related records to another project before deleting.

If the "delete" option is selected, then delete the elated transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project from a
combo box that is populated with all the possible destination projects
(at
its simplest, this would be all projects EXCEPT the one being deleted).
When a move target has been selected, use the option 3 code to move the
related transaction records, and then delete the main project record.

This is a technique commonly used to merge duplicate records in a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction
record
should i have a additional check box in the form and then let the user
input
the WrongPID and CorrectPID. How to call the particular record which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically
deleted.

2. Execute a SQL statement to delete the related records explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong
project.
The best solution then might be to "move" the transaction records so
that
they are related to the correct project, so that the data entry does
not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each project.

The way the database works a new item or an existing is received or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong
project
no
and receives the item under that project. Once the user tries to
delete
the
line item to link the correct project the system does not allow him
to
do
so
telling him that some transaction values are associated with the
record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to write
a
cascading delete query to delete values in the master and child
record
simultaneously.

Any help would be highly appreciated.

 

[vandy]

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form. I am
trying to open my subform which is final_transubform. How can i ensure that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this code in
the form current event.

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog, OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions to another
PID, I would do it by adding a third option to your dialog form. This
option could show a multi-select listbox of all transactions related to the
PID that was passed to the form. The user could select one or more of them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete project
c) Reassign one or more transactions to another project and DO NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the DELETE_PROJECT form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd, acDialog
End Sub

The first open form is attempting to pass the PID value from the form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed . When
the user selects the Project no the Project id gets captured in the
subform
the Itemno is also in a combo box and when the user clicks on the itemno
the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the item
does not belong to project 1880 which has a pid 368 but to 1887 pid 372
than
he goes back selects the item no and and has to go to the combo box to
change
the value. how to be able to capture that action ie. the second line in
the
trasaction table to be able to change 368 to 372 or to delete the record
completely.

I hope i am not confusing or complicating things .

I suggest you create a small unbound dialog form to "delete" a project.
Open this form in acDialog mode from a "Delete" button on your main form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all related
records, or move the related records to another project before deleting.

If the "delete" option is selected, then delete the elated transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project from a
combo box that is populated with all the possible destination projects
(at
its simplest, this would be all projects EXCEPT the one being deleted).
When a move target has been selected, use the option 3 code to move the
related transaction records, and then delete the main project record.

This is a technique commonly used to merge duplicate records in a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction
record
should i have a additional check box in the form and then let the user
input
the WrongPID and CorrectPID. How to call the particular record which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically
deleted.

2. Execute a SQL statement to delete the related records explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong
project.
The best solution then might be to "move" the transaction records so
that
they are related to the correct project, so that the data entry does
not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each project.

The way the database works a new item or an existing is received or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong
project
no
and receives the item under that project. Once the user tries to
delete
the
line item to link the correct project the system does not allow him
to
do
so
telling him that some transaction values are associated with the
record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to write
a
cascading delete query to delete values in the master and child
record
simultaneously.

Any help would be highly appreciated.

 

[Graham Mandeno]

Hi Vandy

I don't understand... what is final_transubform supposed to do? You say it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead of PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form. I am
trying to open my subform which is final_transubform. How can i ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this code in
the form current event.

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions to
another
PID, I would do it by adding a third option to your dialog form. This
option could show a multi-select listbox of all transactions related to
the
PID that was passed to the form. The user could select one or more of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete project
c) Reassign one or more transactions to another project and DO NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd,
acDialog
End Sub

The first open form is attempting to pass the PID value from the form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type
select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed .
When
the user selects the Project no the Project id gets captured in the
subform
the Itemno is also in a combo box and when the user clicks on the
itemno
the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the
item
does not belong to project 1880 which has a pid 368 but to 1887 pid 372
than
he goes back selects the item no and and has to go to the combo box to
change
the value. how to be able to capture that action ie. the second line in
the
trasaction table to be able to change 368 to 372 or to delete the
record
completely.

I hope i am not confusing or complicating things .


:

I suggest you create a small unbound dialog form to "delete" a
project.
Open this form in acDialog mode from a "Delete" button on your main
form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all related
records, or move the related records to another project before
deleting.

If the "delete" option is selected, then delete the elated transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project from
a
combo box that is populated with all the possible destination projects
(at
its simplest, this would be all projects EXCEPT the one being
deleted).
When a move target has been selected, use the option 3 code to move
the
related transaction records, and then delete the main project record.

This is a technique commonly used to merge duplicate records in a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction
record
should i have a additional check box in the form and then let the
user
input
the WrongPID and CorrectPID. How to call the particular record which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically
deleted.

2. Execute a SQL statement to delete the related records explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong
project.
The best solution then might be to "move" the transaction records
so
that
they are related to the correct project, so that the data entry
does
not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each
project.

The way the database works a new item or an existing is received
or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong
project
no
and receives the item under that project. Once the user tries to
delete
the
line item to link the correct project the system does not allow
him
to
do
so
telling him that some transaction values are associated with the
record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to
write
a
cascading delete query to delete values in the master and child
record
simultaneously.

Any help would be highly appreciated.

 

[vandy]

Hi Graham,

Sorry for the confusion

My main form name = addingfrm

subform = final_transubform

I have attempted passing the PID which is a field in the subform as the 7
parameter .

Everytime the record points only to the first record in the transaction table.

how to open the subform and how can i make it point to the current PID value
which i am selecting.

The subform is bound to the transaction table.

also I have corrected this code to read as follows.
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Everytime I open the main form and select a different item and project no it
the PID that is passed is only the first line item in the transaction table.

thanks for your patience and help.

Hi Vandy

I don't understand... what is final_transubform supposed to do? You say it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead of PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form. I am
trying to open my subform which is final_transubform. How can i ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this code in
the form current event.

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions to
another
PID, I would do it by adding a third option to your dialog form. This
option could show a multi-select listbox of all transactions related to
the
PID that was passed to the form. The user could select one or more of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete project
c) Reassign one or more transactions to another project and DO NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd,
acDialog
End Sub

The first open form is attempting to pass the PID value from the form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type
select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed .
When
the user selects the Project no the Project id gets captured in the
subform
the Itemno is also in a combo box and when the user clicks on the
itemno
the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the
item
does not belong to project 1880 which has a pid 368 but to 1887 pid 372
than
he goes back selects the item no and and has to go to the combo box to
change
the value. how to be able to capture that action ie. the second line in
the
trasaction table to be able to change 368 to 372 or to delete the
record
completely.

I hope i am not confusing or complicating things .


:

I suggest you create a small unbound dialog form to "delete" a
project.
Open this form in acDialog mode from a "Delete" button on your main
form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all related
records, or move the related records to another project before
deleting.

If the "delete" option is selected, then delete the elated transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project from
a
combo box that is populated with all the possible destination projects
(at
its simplest, this would be all projects EXCEPT the one being
deleted).
When a move target has been selected, use the option 3 code to move
the
related transaction records, and then delete the main project record.

This is a technique commonly used to merge duplicate records in a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right transaction
record
should i have a additional check box in the form and then let the
user
input
the WrongPID and CorrectPID. How to call the particular record which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record, all
transaction records with the related PID will be automatically
deleted.

2. Execute a SQL statement to delete the related records explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong
project.
The best solution then might be to "move" the transaction records
so
that
they are related to the correct project, so that the data entry
does
not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each
project.

The way the database works a new item or an existing is received
or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a wrong
project
no
and receives the item under that project. Once the user tries to
delete
the
line item to link the correct project the system does not allow
him
to
do
so
telling him that some transaction values are associated with the
record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to
write
a
cascading delete query to delete values in the master and child
record
simultaneously.

 

[Graham Mandeno]

Hi Vandy

Are you actually *doing* anything with the PID you are passing in parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

I don't understand why you are trying to open the subform - it is already
open, as part of the main form.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Sorry for the confusion

My main form name = addingfrm

subform = final_transubform

I have attempted passing the PID which is a field in the subform as the 7
parameter .

Everytime the record points only to the first record in the transaction
table.

how to open the subform and how can i make it point to the current PID
value
which i am selecting.

The subform is bound to the transaction table.

also I have corrected this code to read as follows.
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Everytime I open the main form and select a different item and project no
it
the PID that is passed is only the first line item in the transaction
table.

thanks for your patience and help.

Hi Vandy

I don't understand... what is final_transubform supposed to do? You say
it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead of PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form. I
am
trying to open my subform which is final_transubform. How can i ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the
first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this code
in
the form current event.

:

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions to
another
PID, I would do it by adding a third option to your dialog form. This
option could show a multi-select listbox of all transactions related
to
the
PID that was passed to the form. The user could select one or more of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the
DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd,
acDialog
End Sub

The first open form is attempting to pass the PID value from the
form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type
select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed .
When
the user selects the Project no the Project id gets captured in the
subform
the Itemno is also in a combo box and when the user clicks on the
itemno
the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the
item
does not belong to project 1880 which has a pid 368 but to 1887 pid
372
than
he goes back selects the item no and and has to go to the combo box
to
change
the value. how to be able to capture that action ie. the second line
in
the
trasaction table to be able to change 368 to 372 or to delete the
record
completely.

I hope i am not confusing or complicating things .


:

I suggest you create a small unbound dialog form to "delete" a
project.
Open this form in acDialog mode from a "Delete" button on your main
form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all
related
records, or move the related records to another project before
deleting.

If the "delete" option is selected, then delete the elated
transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project
from
a
combo box that is populated with all the possible destination
projects
(at
its simplest, this would be all projects EXCEPT the one being
deleted).
When a move target has been selected, use the option 3 code to move
the
related transaction records, and then delete the main project
record.

This is a technique commonly used to merge duplicate records in a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right
transaction
record
should i have a additional check box in the form and then let the
user
input
the WrongPID and CorrectPID. How to call the particular record
which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record,
all
transaction records with the related PID will be automatically
deleted.

2. Execute a SQL statement to delete the related records
explicitly
before
you delete the parent record:
strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

3. You say the user has entered the transaction under the wrong
project.
The best solution then might be to "move" the transaction
records
so
that
they are related to the correct project, so that the data entry
does
not
need to be repeated:
strSQL = "Update tblTransaction set PID=" & CorrectPID _
& " where PID=" & WrongPID
CurrentDb.Execute strSQL, dbFailOnError


--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi All,
I have 3 tables

Tblprojects:
ProjectId
Pno
Pname

TblItems:
ItemID
Itemno
ItemDesc
Uom

TblTransaction:
PID
TranitemID
Location
Units
DateOfTransacation
Type

Tblprojects ~ TblTransaction
ProjectID ~ PID
Referencial Integrity constraint set

TblItems ~ TblTransaction
ItemID ~ TranitemID
Referencial Integrity constraint set

I can create items in TblItems and new project is created in
TblProjects.
TblTransaction handles the item quantity on hand for each
project.

The way the database works a new item or an existing is
received
or
issued
under a project no the QOH value reported.

The problem faced now is when a user by mistake selects a
wrong
project
no
and receives the item under that project. Once the user tries
to
delete
the
line item to link the correct project the system does not
allow
him
to
do
so
telling him that some transaction values are associated with
the
record
and
the record cannot be deleted.

I think in order to completely delete the line item I have to
write
a
cascading delete query to delete values in the master and
child
record
simultaneously.

 

[vandy]

Hi Graham,

I have followed point 1 & 2 and passed the PID to DELETE_PROJECT unbound
form. What is happening here is that only the first line Project Id is
getting passed as open arg to the form.
I want to use the code below to delete the PID and all the related
transaction records from tbltransaction.


strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError


but for that i need to be able to delete the correct PID.


EG;
Tbltransaction.

TransactionID PID Type Qty

235 386 Received 200
236 309 Received 10
237 386 Issued 20
238 312 Recieved 300

What is happening is even say in my main form if the user entered the wrong
Project Id for say TransactionID 238. I need to delete that entry.

if i select 238 TransactionID to delete because of wrong PID entered on my
main form it still selects 235 TransactionID and passes PID = 386 which is
the first line no in my transaction table. how to make it point to the
correct PID for deleting the record.I think i need to refresh the table how
to go about doing that.

thanks

Hi Vandy

Are you actually *doing* anything with the PID you are passing in parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

I don't understand why you are trying to open the subform - it is already
open, as part of the main form.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Sorry for the confusion

My main form name = addingfrm

subform = final_transubform

I have attempted passing the PID which is a field in the subform as the 7
parameter .

Everytime the record points only to the first record in the transaction
table.

how to open the subform and how can i make it point to the current PID
value
which i am selecting.

The subform is bound to the transaction table.

also I have corrected this code to read as follows.
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Everytime I open the main form and select a different item and project no
it
the PID that is passed is only the first line item in the transaction
table.

thanks for your patience and help.

Hi Vandy

I don't understand... what is final_transubform supposed to do? You say
it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead of PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form. I
am
trying to open my subform which is final_transubform. How can i ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the
first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this code
in
the form current event.

:

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions to
another
PID, I would do it by adding a third option to your dialog form. This
option could show a multi-select listbox of all transactions related
to
the
PID that was passed to the form. The user could select one or more of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the
DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , , acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd,
acDialog
End Sub

The first open form is attempting to pass the PID value from the
form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type
select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this code.
in my main form i have a combo box which has all Project no listed .
When
the user selects the Project no the Project id gets captured in the
subform
the Itemno is also in a combo box and when the user clicks on the
itemno
the
TranitemID column is filled in the subform and the TransactionID is
autogenerated.

Say the user realizes that the second line has to be changed and the
item
does not belong to project 1880 which has a pid 368 but to 1887 pid
372
than
he goes back selects the item no and and has to go to the combo box
to
change
the value. how to be able to capture that action ie. the second line
in
the
trasaction table to be able to change 368 to 372 or to delete the
record
completely.

I hope i am not confusing or complicating things .


:

I suggest you create a small unbound dialog form to "delete" a
project.
Open this form in acDialog mode from a "Delete" button on your main
form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all
related
records, or move the related records to another project before
deleting.

If the "delete" option is selected, then delete the elated
transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project
from
a
combo box that is populated with all the possible destination
projects
(at
its simplest, this would be all projects EXCEPT the one being
deleted).
When a move target has been selected, use the option 3 code to move
the
related transaction records, and then delete the main project
record.

This is a technique commonly used to merge duplicate records in a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right
transaction
record
should i have a additional check box in the form and then let the
user
input
the WrongPID and CorrectPID. How to call the particular record
which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation between
TblProjects
and TblTransaction. Then, when you delete the project record,
all
transaction records with the related PID will be automatically
deleted.

2. Execute a SQL statement to delete the related records
explicitly
before

 

[Graham Mandeno]

Hi Vandy

Let's take a step back here. I am making the following assumptions. Please
tell me if each of them is correct or incorrect, and explain as clearly as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

2. The primary key of tblProjects is ProjectID and the related foreign key
in tblTransactions is PID.

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different project.

5. Sometimes a user will add a transaction record to the wrong project, but
the project is a valid one that should not be deleted.

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.

7. You have a main form named "addingfrm" which is bound to tblProjects.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

9. The subform control that contains final_transubform has LinkMasterFields
set to ProjectID and LinkChildFields set to PID.

10. You have a dialog form "DELETE_PROJECT" which is intended to perform one
of the following actions on the project currently displayed in "addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

11. This form (DELETE_PROJECT) is unbound - in other words, its RecordSource
is blank.

12. The dialog form is opened by clicking a button on your *main* form.

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

14. The Open or Load event of the dialog form is retrieving this value from
Me.OpenArgs so it knows which project to perform the action on.

Please confirm or correct all of these assumptions and post back. Don't
worry - we'll get this nailed
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I have followed point 1 & 2 and passed the PID to DELETE_PROJECT unbound
form. What is happening here is that only the first line Project Id is
getting passed as open arg to the form.
I want to use the code below to delete the PID and all the related
transaction records from tbltransaction.


strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError


but for that i need to be able to delete the correct PID.


EG;
Tbltransaction.

TransactionID PID Type Qty

235 386 Received 200
236 309 Received 10
237 386 Issued 20
238 312 Recieved 300

What is happening is even say in my main form if the user entered the
wrong
Project Id for say TransactionID 238. I need to delete that entry.

if i select 238 TransactionID to delete because of wrong PID entered on my
main form it still selects 235 TransactionID and passes PID = 386 which
is
the first line no in my transaction table. how to make it point to the
correct PID for deleting the record.I think i need to refresh the table
how
to go about doing that.

thanks

Hi Vandy

Are you actually *doing* anything with the PID you are passing in
parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

I don't understand why you are trying to open the subform - it is already
open, as part of the main form.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Sorry for the confusion

My main form name = addingfrm

subform = final_transubform

I have attempted passing the PID which is a field in the subform as the
7
parameter .

Everytime the record points only to the first record in the transaction
table.

how to open the subform and how can i make it point to the current PID
value
which i am selecting.

The subform is bound to the transaction table.

also I have corrected this code to read as follows.
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Everytime I open the main form and select a different item and project
no
it
the PID that is passed is only the first line item in the transaction
table.

thanks for your patience and help.


:

Hi Vandy

I don't understand... what is final_transubform supposed to do? You
say
it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead of
PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form.
I
am
trying to open my subform which is final_transubform. How can i
ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the
first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this
code
in
the form current event.

:

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is
blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th
parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions
to
another
PID, I would do it by adding a third option to your dialog form.
This
option could show a multi-select listbox of all transactions
related
to
the
PID that was passed to the form. The user could select one or more
of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO
NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the
DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()
DoCmd.OpenForm "final_transubform", acDialog, , ,
acFormEdit, ,
Me.PID

MsgBox (Me.PID)
DoCmd.OpenForm "DELETE_PROJECT", acNormal, "", "", acFormAdd,
acDialog
End Sub

The first open form is attempting to pass the PID value from the
form.

here is what i dont know how to do.

My trasaction table looks like this:

TransactionID PID TranItemID Loc Units DOT Type
select_tran
206 368 64 H3 100 12-May-08 received
207 368 65 H3 10 12-May-08 issued
208 394 66 H3 50 05-May-08 received
209 372 67 H3 6 05-May-08 issued


right now the pid 368 is getting captured everytime i run this
code.
in my main form i have a combo box which has all Project no
listed .
When
the user selects the Project no the Project id gets captured in
the
subform
the Itemno is also in a combo box and when the user clicks on the
itemno
the
TranitemID column is filled in the subform and the TransactionID
is
autogenerated.

Say the user realizes that the second line has to be changed and
the
item
does not belong to project 1880 which has a pid 368 but to 1887
pid
372
than
he goes back selects the item no and and has to go to the combo
box
to
change
the value. how to be able to capture that action ie. the second
line
in
the
trasaction table to be able to change 368 to 372 or to delete the
record
completely.

I hope i am not confusing or complicating things .


:

I suggest you create a small unbound dialog form to "delete" a
project.
Open this form in acDialog mode from a "Delete" button on your
main
form,
and pass the PID of the project to be deleted.

On this form, offer two options - delete the project and all
related
records, or move the related records to another project before
deleting.

If the "delete" option is selected, then delete the elated
transaction
records (my option 2 below) and then delete the project record.

If the "move" option is selected, have the user select a project
from
a
combo box that is populated with all the possible destination
projects
(at
its simplest, this would be all projects EXCEPT the one being
deleted).
When a move target has been selected, use the option 3 code to
move
the
related transaction records, and then delete the main project
record.

This is a technique commonly used to merge duplicate records in
a
database -
for example, when a customer gets entered twice.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham,
I like the 3 option since there is minimal repeat entry.

Correct me if i am wrong. In order to identify the right
transaction
record
should i have a additional check box in the form and then let
the
user
input
the WrongPID and CorrectPID. How to call the particular record
which
needs
correction in a userform to enable correction by the user.


:

Hi Vandy

You can approach this in three ways:

1. Set the "Cascade Deletes" attribute on the Relation
between
TblProjects
and TblTransaction. Then, when you delete the project
record,
all
transaction records with the related PID will be
automatically
deleted.

2. Execute a SQL statement to delete the related records
explicitly
before

 

[vandy]

Hi Graham,

Let's take a step back here. I am making the following assumptions. Please
tell me if each of them is correct or incorrect, and explain as clearly as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

Three Tables:

tblProjects :
Fields: ProjectID, Pno, Pname

tblItems:
Fields : ItemID,Itemno,ItemDesc,MfgPartno,Supplier,UOM,StockNo

tblTransactions:
Fields :TransacationID,PID,TranItemID,Location,Qty,DOT,
Type

Relation:
One to Many

tblProjects : ProjectID- PK ~ tblTransactions : PID- FK

One to Many
tblItems : ItemID-PK ~ tblTransactions : TranItemID-FK

Constraint : Referential Integrity Constraint.

2. The primary key of tblProjects is ProjectID and the related foreign key
in tblTransactions is PID.
Yes

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.
Yes

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different project.
Yes

5. Sometimes a user will add a transaction record to the wrong project, but
the project is a valid one that should not be deleted.
Yes

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.
Yes

7. You have a main form named "addingfrm" which is bound to tblProjects.

No. it is bound to tbltransactions. Since I have 2 combo boxes on my main
form and tbl transactions has PID field that links to the ProjectID in the
ProjectTable and TranitemID which links to the ItemID of the tblitems.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

No. property sheet of my final_transubform reads

Link Master Fields: cmbpno;cmbitemno

cmbpno has the feilds of tblprojects queried as its row source
cmbitemno: has the feilds of tblitems queried as its row source

Link Child Fields: pid;tranitemid - fields from my tbltransaction

9. The subform control that contains final_transubform has LinkMasterFields
set to ProjectID and LinkChildFields set to PID.
yes

10. You have a dialog form "DELETE_PROJECT" which is intended to perform one
of the following actions on the project currently displayed in "addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

There is a button on the adding form which when clicked opens the
DELETE_PROJECT unbound form in a dialog window.

11. This form (DELETE_PROJECT) is unbound - in other words, its RecordSource
is blank.

Yes

12. The dialog form is opened by clicking a button on your *main* form.

Yes

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

I am confused as to how to code this part !! I have to write a docmd open
form in the button click event.

14. The Open or Load event of the dialog form is retrieving this value from
Me.OpenArgs so it knows which project to perform the action on.

Yes

I really appreciate your patience and you taking the time to go step wise. I
dont think i am following step 7 & 8 because of the 2 combo boxes on my main
form which is referring to 2 associated feilds on my subform.

thanks again.

Hi Vandy

Let's take a step back here. I am making the following assumptions. Please
tell me if each of them is correct or incorrect, and explain as clearly as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

2. The primary key of tblProjects is ProjectID and the related foreign key
in tblTransactions is PID.

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different project.

5. Sometimes a user will add a transaction record to the wrong project, but
the project is a valid one that should not be deleted.

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.

7. You have a main form named "addingfrm" which is bound to tblProjects.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

9. The subform control that contains final_transubform has LinkMasterFields
set to ProjectID and LinkChildFields set to PID.

10. You have a dialog form "DELETE_PROJECT" which is intended to perform one
of the following actions on the project currently displayed in "addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

11. This form (DELETE_PROJECT) is unbound - in other words, its RecordSource
is blank.

12. The dialog form is opened by clicking a button on your *main* form.

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

14. The Open or Load event of the dialog form is retrieving this value from
Me.OpenArgs so it knows which project to perform the action on.

Please confirm or correct all of these assumptions and post back. Don't
worry - we'll get this nailed
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I have followed point 1 & 2 and passed the PID to DELETE_PROJECT unbound
form. What is happening here is that only the first line Project Id is
getting passed as open arg to the form.
I want to use the code below to delete the PID and all the related
transaction records from tbltransaction.


strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError


but for that i need to be able to delete the correct PID.


EG;
Tbltransaction.

TransactionID PID Type Qty

235 386 Received 200
236 309 Received 10
237 386 Issued 20
238 312 Recieved 300

What is happening is even say in my main form if the user entered the
wrong
Project Id for say TransactionID 238. I need to delete that entry.

if i select 238 TransactionID to delete because of wrong PID entered on my
main form it still selects 235 TransactionID and passes PID = 386 which
is
the first line no in my transaction table. how to make it point to the
correct PID for deleting the record.I think i need to refresh the table
how
to go about doing that.

thanks

Hi Vandy

Are you actually *doing* anything with the PID you are passing in
parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

I don't understand why you are trying to open the subform - it is already
open, as part of the main form.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Graham,

Sorry for the confusion

My main form name = addingfrm

subform = final_transubform

I have attempted passing the PID which is a field in the subform as the
7
parameter .

Everytime the record points only to the first record in the transaction
table.

how to open the subform and how can i make it point to the current PID
value
which i am selecting.

The subform is bound to the transaction table.

also I have corrected this code to read as follows.
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Everytime I open the main form and select a different item and project
no
it
the PID that is passed is only the first line item in the transaction
table.

thanks for your patience and help.


:

Hi Vandy

I don't understand... what is final_transubform supposed to do? You
say
it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead of
PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main form.
I
am
trying to open my subform which is final_transubform. How can i
ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID the
first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this
code
in
the form current event.

:

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is
blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th
parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all) transactions
to
another
PID, I would do it by adding a third option to your dialog form.
This
option could show a multi-select listbox of all transactions
related
to
the
PID that was passed to the form. The user could select one or more
of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO
NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the
DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()

 

[Graham Mandeno]

Hi Vandy

OK, so I'm getting a little closer to understanding *what* you have, but I'm
a little at a loss about *why*

If your main form is bound to tblTransactions, I can't see why you have a
subform at all, as it is bound (or so you imply) to the same table.

You say the LinkMasterFields for your subform are: cmbpno;cmbitemno

Are these two combo boxes on your main form? Are they unbound (no
ControlSource)?

If so, then the only record(s) that will display in your subform are
transactions which match BOTH the PID and the ItemID selected in the two
combo boxes. (Is this what you want?)

Also, if cmbpno is unbound, the value selected in that combo box will not
necessarily match the PID of the current record in the main form.
Therefore, if you pass Me.PID to your dialog form you won't necessarily be
passing the same PID as the record in your subform.

Does this make sense?

Let's take another step back. What is the actual purpose of addingfrm? Do
you want to select a project and add related transactions? Or do you want
to add transactions for several different projects one after another?
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as clearly as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

Three Tables:

tblProjects :
Fields: ProjectID, Pno, Pname

tblItems:
Fields : ItemID,Itemno,ItemDesc,MfgPartno,Supplier,UOM,StockNo

tblTransactions:
Fields :TransacationID,PID,TranItemID,Location,Qty,DOT,
Type

Relation:
One to Many

tblProjects : ProjectID- PK ~ tblTransactions : PID- FK

One to Many
tblItems : ItemID-PK ~ tblTransactions : TranItemID-FK

Constraint : Referential Integrity Constraint.

2. The primary key of tblProjects is ProjectID and the related foreign key
in tblTransactions is PID.
Yes

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.
Yes

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different
project.
Yes

5. Sometimes a user will add a transaction record to the wrong project,
but
the project is a valid one that should not be deleted.
Yes

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.
Yes

7. You have a main form named "addingfrm" which is bound to tblProjects.

No. it is bound to tbltransactions. Since I have 2 combo boxes on my main
form and tbl transactions has PID field that links to the ProjectID in the
ProjectTable and TranitemID which links to the ItemID of the tblitems.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

No. property sheet of my final_transubform reads

Link Master Fields: cmbpno;cmbitemno

cmbpno has the feilds of tblprojects queried as its row source
cmbitemno: has the feilds of tblitems queried as its row source

Link Child Fields: pid;tranitemid - fields from my tbltransaction

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.
yes

10. You have a dialog form "DELETE_PROJECT" which is intended to perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

There is a button on the adding form which when clicked opens the
DELETE_PROJECT unbound form in a dialog window.

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

Yes

12. The dialog form is opened by clicking a button on your *main* form.

Yes

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

I am confused as to how to code this part !! I have to write a docmd open
form in the button click event.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Yes

I really appreciate your patience and you taking the time to go step wise.
I
dont think i am following step 7 & 8 because of the 2 combo boxes on my
main
form which is referring to 2 associated feilds on my subform.

thanks again.

Hi Vandy

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as clearly
as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

2. The primary key of tblProjects is ProjectID and the related foreign
key
in tblTransactions is PID.

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different
project.

5. Sometimes a user will add a transaction record to the wrong project,
but
the project is a valid one that should not be deleted.

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.

7. You have a main form named "addingfrm" which is bound to tblProjects.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.

10. You have a dialog form "DELETE_PROJECT" which is intended to perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

12. The dialog form is opened by clicking a button on your *main* form.

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Please confirm or correct all of these assumptions and post back. Don't
worry - we'll get this nailed
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I have followed point 1 & 2 and passed the PID to DELETE_PROJECT
unbound
form. What is happening here is that only the first line Project Id is
getting passed as open arg to the form.
I want to use the code below to delete the PID and all the related
transaction records from tbltransaction.


strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError


but for that i need to be able to delete the correct PID.


EG;
Tbltransaction.

TransactionID PID Type Qty

235 386 Received 200
236 309 Received 10
237 386 Issued 20
238 312 Recieved 300

What is happening is even say in my main form if the user entered the
wrong
Project Id for say TransactionID 238. I need to delete that entry.

if i select 238 TransactionID to delete because of wrong PID entered on
my
main form it still selects 235 TransactionID and passes PID = 386
which
is
the first line no in my transaction table. how to make it point to the
correct PID for deleting the record.I think i need to refresh the table
how
to go about doing that.

thanks






:

Hi Vandy

Are you actually *doing* anything with the PID you are passing in
parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is
blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th
parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

I don't understand why you are trying to open the subform - it is
already
open, as part of the main form.
--


Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Graham,

Sorry for the confusion

My main form name = addingfrm

subform = final_transubform

I have attempted passing the PID which is a field in the subform as
the
7
parameter .

Everytime the record points only to the first record in the
transaction
table.

how to open the subform and how can i make it point to the current
PID
value
which i am selecting.

The subform is bound to the transaction table.

also I have corrected this code to read as follows.
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Everytime I open the main form and select a different item and
project
no
it
the PID that is passed is only the first line item in the
transaction
table.

thanks for your patience and help.


:

Hi Vandy

I don't understand... what is final_transubform supposed to do?
You
say
it
is a subform, but you are attempting to open it as a main form.

Also, why are you passing TransactionID to DELETE_PROJECT instead
of
PID?
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello Graham,


Private Sub Projectbtn_Click()

DoCmd.OpenForm "final_transubform", , , , , acDialog, Me.PID

DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.TransactionID

End Sub

I have this code written behind my Reassign project on my main
form.
I
am
trying to open my subform which is final_transubform. How can i
ensure
that
the current PID is getting passed as an argument.

Right now even if i change the line no to select a different PID
the
first
record in the transaction table is only getting passed.

Is this the way to point to open the subform. Should i write this
code
in
the form current event.

:

Hi Vandy

A few tips that might help:

1. Your DELETE_PROJECT form should be unbound (RecordSource is
blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th
parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

Then, in the Form_Load event procedure for DELETE_PROJECT:

lngPIDtoDelete = Nz(Me.OpenArgs)
If lngPIDtoDelete = 0 Then ... error and close

3. If you want to reassign one or more (but not all)
transactions
to
another
PID, I would do it by adding a third option to your dialog form.
This
option could show a multi-select listbox of all transactions
related
to
the
PID that was passed to the form. The user could select one or
more
of
them
to reassign.

So, to summarise, you would have three options:

a) Delete project AND all related transactions
b) Reassign all transactions to another project then delete
project
c) Reassign one or more transactions to another project and
DO
NOT
delete the project

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Thanks for your reply

I have created a form with 2 option buttons.
form - DELETE_PROJECT
option value 1 - Delete Project
option value 2 - Reassign Project.

I have a delete button on my main form which opens the
DELETE_PROJECT
form
in a modal window.

problem:

i am not very good in coding and so i have just attempted the
following:

Private Sub Projectbtn_Click()

 

[Chuck]

On Fri, 4 Jul 2008 10:11:41 +1200, "Graham Mandeno"

Would the comp.lang.basic.visual newsgroup be a good place to ask this
question?

 

[vandy]

Hi Graham,

Thanks for your time. What you say makes a lot of sense.

My ProjectID combo box is now bound to PID

My ItemID combo box is now bound to TranItemID

I have successfully passed the correct PID to the unbound DELETE PROJECT FORM.

Question
Let's take another step back. What is the actual purpose of addingfrm? Do
you want to select a project and add related transactions? Or do you want
to add transactions for several different projects one after another?

answer
I first select a Project using project combo box and then select the items
using ItemID combo box on my main form.
The subform than displayes the projectID , ItemID and i receive or issue the
item and the record gets updated in my transaction table.

Once i bound both my combo boxes the correct pid got passed in the open
argument.

In my DELETE PROJECT UNBOUND FORM i have the following code written.

Private Sub Form_Load()
lngPIDtoDelete = Nz(Me.OpenArgs)
MsgBox Me.OpenArgs

If lngPIDtoDelete = 0 Then
MsgBox "ERROR"

End If
End Sub

Here the correct PID is passed to the form.

I have an option group
1- delete project
2- reassign project

Private Sub myOptionGroup_Click()

Dim strSQL As String
If myoptiongroup = 1 Then 'delete project
strSQL = "Delete tbltransactions where PID = " & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

Else 'reassign project
MsgBox ("reassign")
End If
End Sub

could you run me through these codes again in order to delete the correct pid.

When i run this code method i get a datamember not found error msg.

thanks a ton for your patience and time. might be i should be getting some
basic code training!!

Hi Vandy

OK, so I'm getting a little closer to understanding *what* you have, but I'm
a little at a loss about *why*

If your main form is bound to tblTransactions, I can't see why you have a
subform at all, as it is bound (or so you imply) to the same table.

You say the LinkMasterFields for your subform are: cmbpno;cmbitemno

Are these two combo boxes on your main form? Are they unbound (no
ControlSource)?

If so, then the only record(s) that will display in your subform are
transactions which match BOTH the PID and the ItemID selected in the two
combo boxes. (Is this what you want?)

Also, if cmbpno is unbound, the value selected in that combo box will not
necessarily match the PID of the current record in the main form.
Therefore, if you pass Me.PID to your dialog form you won't necessarily be
passing the same PID as the record in your subform.

Does this make sense?

Let's take another step back. What is the actual purpose of addingfrm? Do
you want to select a project and add related transactions? Or do you want
to add transactions for several different projects one after another?
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as clearly as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

Three Tables:

tblProjects :
Fields: ProjectID, Pno, Pname

tblItems:
Fields : ItemID,Itemno,ItemDesc,MfgPartno,Supplier,UOM,StockNo

tblTransactions:
Fields :TransacationID,PID,TranItemID,Location,Qty,DOT,
Type

Relation:
One to Many

tblProjects : ProjectID- PK ~ tblTransactions : PID- FK

One to Many
tblItems : ItemID-PK ~ tblTransactions : TranItemID-FK

Constraint : Referential Integrity Constraint.

2. The primary key of tblProjects is ProjectID and the related foreign key
in tblTransactions is PID.
Yes

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.
Yes

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different
project.
Yes

5. Sometimes a user will add a transaction record to the wrong project,
but
the project is a valid one that should not be deleted.
Yes

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.
Yes

7. You have a main form named "addingfrm" which is bound to tblProjects.

No. it is bound to tbltransactions. Since I have 2 combo boxes on my main
form and tbl transactions has PID field that links to the ProjectID in the
ProjectTable and TranitemID which links to the ItemID of the tblitems.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

No. property sheet of my final_transubform reads

Link Master Fields: cmbpno;cmbitemno

cmbpno has the feilds of tblprojects queried as its row source
cmbitemno: has the feilds of tblitems queried as its row source

Link Child Fields: pid;tranitemid - fields from my tbltransaction

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.
yes

10. You have a dialog form "DELETE_PROJECT" which is intended to perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

There is a button on the adding form which when clicked opens the
DELETE_PROJECT unbound form in a dialog window.

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

Yes

12. The dialog form is opened by clicking a button on your *main* form.

Yes

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

I am confused as to how to code this part !! I have to write a docmd open
form in the button click event.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Yes

I really appreciate your patience and you taking the time to go step wise.
I
dont think i am following step 7 & 8 because of the 2 combo boxes on my
main
form which is referring to 2 associated feilds on my subform.

thanks again.

Hi Vandy

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as clearly
as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

2. The primary key of tblProjects is ProjectID and the related foreign
key
in tblTransactions is PID.

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.

4. In such cases you want to delete the entire project but NOT delete the
related transaction records, instead assigning them to a different
project.

5. Sometimes a user will add a transaction record to the wrong project,
but
the project is a valid one that should not be deleted.

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.

7. You have a main form named "addingfrm" which is bound to tblProjects.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.

10. You have a dialog form "DELETE_PROJECT" which is intended to perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

12. The dialog form is opened by clicking a button on your *main* form.

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Please confirm or correct all of these assumptions and post back. Don't
worry - we'll get this nailed
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I have followed point 1 & 2 and passed the PID to DELETE_PROJECT
unbound
form. What is happening here is that only the first line Project Id is
getting passed as open arg to the form.
I want to use the code below to delete the PID and all the related
transaction records from tbltransaction.


strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError


but for that i need to be able to delete the correct PID.


EG;
Tbltransaction.

TransactionID PID Type Qty

235 386 Received 200
236 309 Received 10
237 386 Issued 20
238 312 Recieved 300

What is happening is even say in my main form if the user entered the
wrong
Project Id for say TransactionID 238. I need to delete that entry.

if i select 238 TransactionID to delete because of wrong PID entered on
my
main form it still selects 235 TransactionID and passes PID = 386
which
is
the first line no in my transaction table. how to make it point to the
correct PID for deleting the record.I think i need to refresh the table
how
to go about doing that.

thanks






:

Hi Vandy

Are you actually *doing* anything with the PID you are passing in
parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is
blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th
parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

 

[Graham Mandeno]

Hi Vandy

Sorry - I've been away for the weekend.

If my understanding is correct, binding your combo boxes to PID and
TranItemID is NOT going to work for you. The problem is that when you
select values in these combo boxes to filter your linked subform, you will
actually change the PID and TranItemID in the current record of the main
form.

I suggest that your main form should be unbound, and that you should pass
Me.cmbpno to your Delete_Project form.

One question you have not answered yet: Why do you need a form and subform
bound to the same table?

Do you typically add several transactions at a time for the same project and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?

On the subject of the code in your dialog form, first I would add a command
button "OK" and attach your code to that, not to the click event of the
option group. You will also need a combo box to select the project to
reassign transactions to.

Your variable, lngPIDtoDelete, should be declared at the top of your module:
Option Explicit
Dim lngPIDtoDelete as Long

Then your code for the click event needs to be something like this:

Private Sub cmdOK_Click()
Dim strSQL As String
Select Case MyOptionGroup
Case 1 ' delete
strSQL = "Delete from tblTransactions where PID = " & lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError

Case 2 ' reassign
If IsNull(cmbReassignPID ) Then
MsgBox "Select a project to reassign transactions to"
Exit Sub
End If
strSQL = "Update tblTransactions set PID = " & cmbReassignPID _
& " where PID = " & lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
End Select
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for your time. What you say makes a lot of sense.

My ProjectID combo box is now bound to PID

My ItemID combo box is now bound to TranItemID

I have successfully passed the correct PID to the unbound DELETE PROJECT
FORM.

Question
Let's take another step back. What is the actual purpose of addingfrm?
Do
you want to select a project and add related transactions? Or do you want
to add transactions for several different projects one after another?

answer
I first select a Project using project combo box and then select the items
using ItemID combo box on my main form.
The subform than displayes the projectID , ItemID and i receive or issue
the
item and the record gets updated in my transaction table.

Once i bound both my combo boxes the correct pid got passed in the open
argument.

In my DELETE PROJECT UNBOUND FORM i have the following code written.

Private Sub Form_Load()
lngPIDtoDelete = Nz(Me.OpenArgs)
MsgBox Me.OpenArgs

If lngPIDtoDelete = 0 Then
MsgBox "ERROR"

End If
End Sub

Here the correct PID is passed to the form.

I have an option group
1- delete project
2- reassign project

Private Sub myOptionGroup_Click()

Dim strSQL As String
If myoptiongroup = 1 Then 'delete project
strSQL = "Delete tbltransactions where PID = " & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

Else 'reassign project
MsgBox ("reassign")
End If
End Sub

could you run me through these codes again in order to delete the correct
pid.

When i run this code method i get a datamember not found error msg.

thanks a ton for your patience and time. might be i should be getting some
basic code training!!

Hi Vandy

OK, so I'm getting a little closer to understanding *what* you have, but
I'm
a little at a loss about *why*

If your main form is bound to tblTransactions, I can't see why you have a
subform at all, as it is bound (or so you imply) to the same table.

You say the LinkMasterFields for your subform are: cmbpno;cmbitemno

Are these two combo boxes on your main form? Are they unbound (no
ControlSource)?

If so, then the only record(s) that will display in your subform are
transactions which match BOTH the PID and the ItemID selected in the two
combo boxes. (Is this what you want?)

Also, if cmbpno is unbound, the value selected in that combo box will not
necessarily match the PID of the current record in the main form.
Therefore, if you pass Me.PID to your dialog form you won't necessarily
be
passing the same PID as the record in your subform.

Does this make sense?

Let's take another step back. What is the actual purpose of addingfrm?
Do
you want to select a project and add related transactions? Or do you
want
to add transactions for several different projects one after another?
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as clearly
as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

Three Tables:

tblProjects :
Fields: ProjectID, Pno, Pname

tblItems:
Fields : ItemID,Itemno,ItemDesc,MfgPartno,Supplier,UOM,StockNo

tblTransactions:
Fields :TransacationID,PID,TranItemID,Location,Qty,DOT,
Type

Relation:
One to Many

tblProjects : ProjectID- PK ~ tblTransactions : PID- FK

One to Many
tblItems : ItemID-PK ~ tblTransactions : TranItemID-FK

Constraint : Referential Integrity Constraint.

2. The primary key of tblProjects is ProjectID and the related foreign
key
in tblTransactions is PID.
Yes

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.
Yes

4. In such cases you want to delete the entire project but NOT delete
the
related transaction records, instead assigning them to a different
project.
Yes

5. Sometimes a user will add a transaction record to the wrong project,
but
the project is a valid one that should not be deleted.
Yes

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.
Yes

7. You have a main form named "addingfrm" which is bound to
tblProjects.

No. it is bound to tbltransactions. Since I have 2 combo boxes on my
main
form and tbl transactions has PID field that links to the ProjectID in
the
ProjectTable and TranitemID which links to the ItemID of the tblitems.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

No. property sheet of my final_transubform reads

Link Master Fields: cmbpno;cmbitemno

cmbpno has the feilds of tblprojects queried as its row source
cmbitemno: has the feilds of tblitems queried as its row source

Link Child Fields: pid;tranitemid - fields from my tbltransaction

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.
yes

10. You have a dialog form "DELETE_PROJECT" which is intended to
perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

There is a button on the adding form which when clicked opens the
DELETE_PROJECT unbound form in a dialog window.

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

Yes

12. The dialog form is opened by clicking a button on your *main* form.

Yes

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

I am confused as to how to code this part !! I have to write a docmd
open
form in the button click event.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Yes

I really appreciate your patience and you taking the time to go step
wise.
I
dont think i am following step 7 & 8 because of the 2 combo boxes on my
main
form which is referring to 2 associated feilds on my subform.

thanks again.

:

Hi Vandy

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as
clearly
as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

2. The primary key of tblProjects is ProjectID and the related foreign
key
in tblTransactions is PID.

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.

4. In such cases you want to delete the entire project but NOT delete
the
related transaction records, instead assigning them to a different
project.

5. Sometimes a user will add a transaction record to the wrong
project,
but
the project is a valid one that should not be deleted.

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.

7. You have a main form named "addingfrm" which is bound to
tblProjects.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.

10. You have a dialog form "DELETE_PROJECT" which is intended to
perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then
delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

12. The dialog form is opened by clicking a button on your *main*
form.

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Please confirm or correct all of these assumptions and post back.
Don't
worry - we'll get this nailed
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I have followed point 1 & 2 and passed the PID to DELETE_PROJECT
unbound
form. What is happening here is that only the first line Project Id
is
getting passed as open arg to the form.
I want to use the code below to delete the PID and all the related
transaction records from tbltransaction.


strSQL = "Delete from tblTransaction where PID=" &
Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError


but for that i need to be able to delete the correct PID.


EG;
Tbltransaction.

TransactionID PID Type Qty

235 386 Received 200
236 309 Received 10
237 386 Issued 20
238 312 Recieved 300

What is happening is even say in my main form if the user entered
the
wrong
Project Id for say TransactionID 238. I need to delete that entry.

if i select 238 TransactionID to delete because of wrong PID entered
on
my
main form it still selects 235 TransactionID and passes PID = 386
which
is
the first line no in my transaction table. how to make it point to
the
correct PID for deleting the record.I think i need to refresh the
table
how
to go about doing that.

thanks






:

Hi Vandy

Are you actually *doing* anything with the PID you are passing in
parameter
7? The form you are opening needs to retrieve it from Me.OpenArgs
and
actually *do* something with it.

Did you read my second-to-last message, especially points 1 and 2?

1. Your DELETE_PROJECT form should be unbound (RecordSource is
blank)

2. You can pass the PID to be deleted via OpenArgs (the 7th
parameter
for
OpenForm):

DoCmd.OpenForm "DELETE_PROJECT", , , , , acDialog, Me.PID
or
DoCmd.OpenForm "DELETE_PROJECT", WindowMode:=acDialog,
OpenArgs:=Me.PID

 

[vandy]

Hi Graham,
It works!!!

Do you typically add several transactions at a time for the same project and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?


a)
I add several transaction for the same project but for different items.

eg. Project 1834 can have items A- Z each with serveral transactions.


One question you have not answered yet: Why do you need a form and subform
bound to the same table?

b)
Do you suggest that my main form be unbound since the combo boxes on my main
form are referencing the subform.

I used your example and code and passed Me.cmbpno to my Delete_Project form.

Everything works right now. I did not delete the projectID from the Project
table since all the projectID are preloaded on my form.

Everything works the transactions gets deleted and the reassign action
happens without any problems.
I must take this opportunity to thank you for your stepwise approach,
patience and persistance in helping me solve my problem.
Also it would be helpful if you could clarify on the above 2 to enable me in
my learing process.

Hi Vandy

Sorry - I've been away for the weekend.

If my understanding is correct, binding your combo boxes to PID and
TranItemID is NOT going to work for you. The problem is that when you
select values in these combo boxes to filter your linked subform, you will
actually change the PID and TranItemID in the current record of the main
form.

I suggest that your main form should be unbound, and that you should pass
Me.cmbpno to your Delete_Project form.

One question you have not answered yet: Why do you need a form and subform
bound to the same table?

Do you typically add several transactions at a time for the same project and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?

On the subject of the code in your dialog form, first I would add a command
button "OK" and attach your code to that, not to the click event of the
option group. You will also need a combo box to select the project to
reassign transactions to.

Your variable, lngPIDtoDelete, should be declared at the top of your module:
Option Explicit
Dim lngPIDtoDelete as Long

Then your code for the click event needs to be something like this:

Private Sub cmdOK_Click()
Dim strSQL As String
Select Case MyOptionGroup
Case 1 ' delete
strSQL = "Delete from tblTransactions where PID = " & lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError

Case 2 ' reassign
If IsNull(cmbReassignPID ) Then
MsgBox "Select a project to reassign transactions to"
Exit Sub
End If
strSQL = "Update tblTransactions set PID = " & cmbReassignPID _
& " where PID = " & lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
End Select
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for your time. What you say makes a lot of sense.

My ProjectID combo box is now bound to PID

My ItemID combo box is now bound to TranItemID

I have successfully passed the correct PID to the unbound DELETE PROJECT
FORM.

Question
Let's take another step back. What is the actual purpose of addingfrm?
Do
you want to select a project and add related transactions? Or do you want
to add transactions for several different projects one after another?

answer
I first select a Project using project combo box and then select the items
using ItemID combo box on my main form.
The subform than displayes the projectID , ItemID and i receive or issue
the
item and the record gets updated in my transaction table.

Once i bound both my combo boxes the correct pid got passed in the open
argument.

In my DELETE PROJECT UNBOUND FORM i have the following code written.

Private Sub Form_Load()
lngPIDtoDelete = Nz(Me.OpenArgs)
MsgBox Me.OpenArgs

If lngPIDtoDelete = 0 Then
MsgBox "ERROR"

End If
End Sub

Here the correct PID is passed to the form.

I have an option group
1- delete project
2- reassign project

Private Sub myOptionGroup_Click()

Dim strSQL As String
If myoptiongroup = 1 Then 'delete project
strSQL = "Delete tbltransactions where PID = " & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

Else 'reassign project
MsgBox ("reassign")
End If
End Sub

could you run me through these codes again in order to delete the correct
pid.

When i run this code method i get a datamember not found error msg.

thanks a ton for your patience and time. might be i should be getting some
basic code training!!

Hi Vandy

OK, so I'm getting a little closer to understanding *what* you have, but
I'm
a little at a loss about *why*

If your main form is bound to tblTransactions, I can't see why you have a
subform at all, as it is bound (or so you imply) to the same table.

You say the LinkMasterFields for your subform are: cmbpno;cmbitemno

Are these two combo boxes on your main form? Are they unbound (no
ControlSource)?

If so, then the only record(s) that will display in your subform are
transactions which match BOTH the PID and the ItemID selected in the two
combo boxes. (Is this what you want?)

Also, if cmbpno is unbound, the value selected in that combo box will not
necessarily match the PID of the current record in the main form.
Therefore, if you pass Me.PID to your dialog form you won't necessarily
be
passing the same PID as the record in your subform.

Does this make sense?

Let's take another step back. What is the actual purpose of addingfrm?
Do
you want to select a project and add related transactions? Or do you
want
to add transactions for several different projects one after another?
--


Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Graham,

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as clearly
as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

Three Tables:

tblProjects :
Fields: ProjectID, Pno, Pname

tblItems:
Fields : ItemID,Itemno,ItemDesc,MfgPartno,Supplier,UOM,StockNo

tblTransactions:
Fields :TransacationID,PID,TranItemID,Location,Qty,DOT,
Type

Relation:
One to Many

tblProjects : ProjectID- PK ~ tblTransactions : PID- FK

One to Many
tblItems : ItemID-PK ~ tblTransactions : TranItemID-FK

Constraint : Referential Integrity Constraint.

2. The primary key of tblProjects is ProjectID and the related foreign
key
in tblTransactions is PID.
Yes

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.
Yes

4. In such cases you want to delete the entire project but NOT delete
the
related transaction records, instead assigning them to a different
project.
Yes

5. Sometimes a user will add a transaction record to the wrong project,
but
the project is a valid one that should not be deleted.
Yes

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.
Yes

7. You have a main form named "addingfrm" which is bound to
tblProjects.

No. it is bound to tbltransactions. Since I have 2 combo boxes on my
main
form and tbl transactions has PID field that links to the ProjectID in
the
ProjectTable and TranitemID which links to the ItemID of the tblitems.

8. This form contains a subform "final_transubform" which is bound to
tblTransactions.

No. property sheet of my final_transubform reads

Link Master Fields: cmbpno;cmbitemno

cmbpno has the feilds of tblprojects queried as its row source
cmbitemno: has the feilds of tblitems queried as its row source

Link Child Fields: pid;tranitemid - fields from my tbltransaction

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.
yes

10. You have a dialog form "DELETE_PROJECT" which is intended to
perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then delete
project
c) Reassign one or more transactions to another project and DO NOT
delete the project

There is a button on the adding form which when clicked opens the
DELETE_PROJECT unbound form in a dialog window.

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

Yes

12. The dialog form is opened by clicking a button on your *main* form.

Yes

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

I am confused as to how to code this part !! I have to write a docmd
open
form in the button click event.

14. The Open or Load event of the dialog form is retrieving this value
from
Me.OpenArgs so it knows which project to perform the action on.

Yes

I really appreciate your patience and you taking the time to go step
wise.
I
dont think i am following step 7 & 8 because of the 2 combo boxes on my
main
form which is referring to 2 associated feilds on my subform.

thanks again.

 

[Graham Mandeno]

Hi Vandy

(Is that actually your name? You have never signed off your messages.)

I am SOOOOO glad you've got it working now. It just goes to show that
persistence can pay off

Regarding your clarifying questions:

a) I would take a rather different approach with a single continuous form
bound to tblTransactions, using filters and default values, and no subform.

Across the detail section of this form, place the following controls:
- combo box bound to PID with RowSource based on tblProjects
- combo box bound to TranItemID with RowSource based on tblItems
- textboxes for Location, Qty, DOT, Type
(possibly Location and Type would be best also as combo boxes referring to
reference tables)

I am guessing that TransactionID is an AutoNumber and so doesn't need to be
visible on the form.

Put labels in the form header for your columns.

Now your form should show ALL transactions.

Now, in the header or footer of your form, add two unbound combo boxes:
cboFltrPID with RowSource based on tblProjects
cboFltrItem with RowSource based on tblItems

Use the AfterUpdate events of your combo boxes to (a) filter the form and
(b) set the default value of the corresponding control. For example:

Private Function SetFilterAndDefaults()
Dim sFltr as String
If Len( cboFltrPID ) Then
PID.DefaultValue = cboFltrPID
sFltr = "PID=" & cboFltrPID
Else
PID.DefaultValue = ""
End If
If Len( cboFltrItem ) Then
TranItemID.DefaultValue = cboFltrItem
If Len( sFltr ) then sFltr = sFltr & " and "
sFltr = sFltr & "TranItemID=" & cboFltrItem
Else
TranItemID.DefaultValue = ""
End If
Me.Filter = sFltr
Me.FilterOn = Len( sFltr ) > 0
End Function

Set the AfterUpdate properties of both PID and TranItemID combos to:
=SetFilterAndDefaults()

Now, when you pick a project from cboFltrPID, your form will show only
transactions for that project, and new transactions added will be assigned
to that project by default. Similarly, you can filter on a particular item,
with that item being the default for new transactions.

If you accidentally add a transaction to the wrong project, you can reassign
it simply by selecting a different project in the detail section of the
form.

b) I think I just answered that one above
It sounds to me like you don't actually want to delete projects at all,
just reassign the occasional transaction from one project to another.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
It works!!!

Do you typically add several transactions at a time for the same project
and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?


a)
I add several transaction for the same project but for different items.

eg. Project 1834 can have items A- Z each with serveral transactions.


One question you have not answered yet: Why do you need a form and subform
bound to the same table?

b)
Do you suggest that my main form be unbound since the combo boxes on my
main
form are referencing the subform.

I used your example and code and passed Me.cmbpno to my Delete_Project
form.

Everything works right now. I did not delete the projectID from the
Project
table since all the projectID are preloaded on my form.

Everything works the transactions gets deleted and the reassign action
happens without any problems.
I must take this opportunity to thank you for your stepwise approach,
patience and persistance in helping me solve my problem.
Also it would be helpful if you could clarify on the above 2 to enable me
in
my learing process.

Hi Vandy

Sorry - I've been away for the weekend.

If my understanding is correct, binding your combo boxes to PID and
TranItemID is NOT going to work for you. The problem is that when you
select values in these combo boxes to filter your linked subform, you
will
actually change the PID and TranItemID in the current record of the main
form.

I suggest that your main form should be unbound, and that you should pass
Me.cmbpno to your Delete_Project form.

One question you have not answered yet: Why do you need a form and
subform
bound to the same table?

Do you typically add several transactions at a time for the same project
and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?

On the subject of the code in your dialog form, first I would add a
command
button "OK" and attach your code to that, not to the click event of the
option group. You will also need a combo box to select the project to
reassign transactions to.

Your variable, lngPIDtoDelete, should be declared at the top of your
module:
Option Explicit
Dim lngPIDtoDelete as Long

Then your code for the click event needs to be something like this:

Private Sub cmdOK_Click()
Dim strSQL As String
Select Case MyOptionGroup
Case 1 ' delete
strSQL = "Delete from tblTransactions where PID = " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError

Case 2 ' reassign
If IsNull(cmbReassignPID ) Then
MsgBox "Select a project to reassign transactions to"
Exit Sub
End If
strSQL = "Update tblTransactions set PID = " & cmbReassignPID _
& " where PID = " & lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
End Select
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for your time. What you say makes a lot of sense.

My ProjectID combo box is now bound to PID

My ItemID combo box is now bound to TranItemID

I have successfully passed the correct PID to the unbound DELETE
PROJECT
FORM.

Question
Let's take another step back. What is the actual purpose of addingfrm?
Do
you want to select a project and add related transactions? Or do you
want
to add transactions for several different projects one after another?

answer
I first select a Project using project combo box and then select the
items
using ItemID combo box on my main form.
The subform than displayes the projectID , ItemID and i receive or
issue
the
item and the record gets updated in my transaction table.

Once i bound both my combo boxes the correct pid got passed in the open
argument.

In my DELETE PROJECT UNBOUND FORM i have the following code written.

Private Sub Form_Load()
lngPIDtoDelete = Nz(Me.OpenArgs)
MsgBox Me.OpenArgs

If lngPIDtoDelete = 0 Then
MsgBox "ERROR"

End If
End Sub

Here the correct PID is passed to the form.

I have an option group
1- delete project
2- reassign project

Private Sub myOptionGroup_Click()

Dim strSQL As String
If myoptiongroup = 1 Then 'delete project
strSQL = "Delete tbltransactions where PID = " & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

Else 'reassign project
MsgBox ("reassign")
End If
End Sub

could you run me through these codes again in order to delete the
correct
pid.

When i run this code method i get a datamember not found error msg.

thanks a ton for your patience and time. might be i should be getting
some
basic code training!!


:

Hi Vandy

OK, so I'm getting a little closer to understanding *what* you have,
but
I'm
a little at a loss about *why*

If your main form is bound to tblTransactions, I can't see why you
have a
subform at all, as it is bound (or so you imply) to the same table.

You say the LinkMasterFields for your subform are: cmbpno;cmbitemno

Are these two combo boxes on your main form? Are they unbound (no
ControlSource)?

If so, then the only record(s) that will display in your subform are
transactions which match BOTH the PID and the ItemID selected in the
two
combo boxes. (Is this what you want?)

Also, if cmbpno is unbound, the value selected in that combo box will
not
necessarily match the PID of the current record in the main form.
Therefore, if you pass Me.PID to your dialog form you won't
necessarily
be
passing the same PID as the record in your subform.

Does this make sense?

Let's take another step back. What is the actual purpose of
addingfrm?
Do
you want to select a project and add related transactions? Or do you
want
to add transactions for several different projects one after another?
--


Graham Mandeno [Access MVP]
Auckland, New Zealand


Hi Graham,

Let's take a step back here. I am making the following assumptions.
Please
tell me if each of them is correct or incorrect, and explain as
clearly
as
possible the incorrect ones:

1. You have a one-to-many relationship between tblProjects and
tblTransactions - one project can have many transactions.

Three Tables:

tblProjects :
Fields: ProjectID, Pno, Pname

tblItems:
Fields : ItemID,Itemno,ItemDesc,MfgPartno,Supplier,UOM,StockNo

tblTransactions:
Fields :TransacationID,PID,TranItemID,Location,Qty,DOT,
Type

Relation:
One to Many

tblProjects : ProjectID- PK ~ tblTransactions : PID- FK

One to Many
tblItems : ItemID-PK ~ tblTransactions : TranItemID-FK

Constraint : Referential Integrity Constraint.

2. The primary key of tblProjects is ProjectID and the related
foreign
key
in tblTransactions is PID.
Yes

3. Sometimes a user will mistakenly add a new project and then add
transaction records that relate to it.
Yes

4. In such cases you want to delete the entire project but NOT
delete
the
related transaction records, instead assigning them to a different
project.
Yes

5. Sometimes a user will add a transaction record to the wrong
project,
but
the project is a valid one that should not be deleted.
Yes

6. In such cases, you want to reassign only the incorrectly assigned
transactions to a different project.
Yes

7. You have a main form named "addingfrm" which is bound to
tblProjects.

No. it is bound to tbltransactions. Since I have 2 combo boxes on my
main
form and tbl transactions has PID field that links to the ProjectID
in
the
ProjectTable and TranitemID which links to the ItemID of the
tblitems.

8. This form contains a subform "final_transubform" which is bound
to
tblTransactions.

No. property sheet of my final_transubform reads

Link Master Fields: cmbpno;cmbitemno

cmbpno has the feilds of tblprojects queried as its row source
cmbitemno: has the feilds of tblitems queried as its row source

Link Child Fields: pid;tranitemid - fields from my tbltransaction

9. The subform control that contains final_transubform has
LinkMasterFields
set to ProjectID and LinkChildFields set to PID.
yes

10. You have a dialog form "DELETE_PROJECT" which is intended to
perform
one
of the following actions on the project currently displayed in
"addingfrm":
a) Delete project AND all related transactions
b) Reassign all related transactions to another project then
delete
project
c) Reassign one or more transactions to another project and DO
NOT
delete the project

There is a button on the adding form which when clicked opens the
DELETE_PROJECT unbound form in a dialog window.

11. This form (DELETE_PROJECT) is unbound - in other words, its
RecordSource
is blank.

Yes

12. The dialog form is opened by clicking a button on your *main*
form.

Yes

13. The Click event of the button passes the current ProjectID
(Me.ProjectID) to the form through OpenArgs.

I am confused as to how to code this part !! I have to write a docmd
open
form in the button click event.

14. The Open or Load event of the dialog form is retrieving this
value
from
Me.OpenArgs so it knows which project to perform the action on.

Yes

I really appreciate your patience and you taking the time to go step
wise.
I
dont think i am following step 7 & 8 because of the 2 combo boxes on
my
main
form which is referring to 2 associated feilds on my subform.

thanks again.

 

[vandy]

Hi Graham,

Yes Vandy is my name. Thanks again for your insight and guidance. People
like you do make beginners like me feel we can solve issues and even code!!!
even if it is cutting and pasting!!. Though i have a lot to learn groups like
this give me confidence to move forward and understand the intricacies of
coding!!

Thanks for not giving up on me,even after more than 18 postings!!!


vandy

Hi Vandy

(Is that actually your name? You have never signed off your messages.)

I am SOOOOO glad you've got it working now. It just goes to show that
persistence can pay off

Regarding your clarifying questions:

a) I would take a rather different approach with a single continuous form
bound to tblTransactions, using filters and default values, and no subform.

Across the detail section of this form, place the following controls:
- combo box bound to PID with RowSource based on tblProjects
- combo box bound to TranItemID with RowSource based on tblItems
- textboxes for Location, Qty, DOT, Type
(possibly Location and Type would be best also as combo boxes referring to
reference tables)

I am guessing that TransactionID is an AutoNumber and so doesn't need to be
visible on the form.

Put labels in the form header for your columns.

Now your form should show ALL transactions.

Now, in the header or footer of your form, add two unbound combo boxes:
cboFltrPID with RowSource based on tblProjects
cboFltrItem with RowSource based on tblItems

Use the AfterUpdate events of your combo boxes to (a) filter the form and
(b) set the default value of the corresponding control. For example:

Private Function SetFilterAndDefaults()
Dim sFltr as String
If Len( cboFltrPID ) Then
PID.DefaultValue = cboFltrPID
sFltr = "PID=" & cboFltrPID
Else
PID.DefaultValue = ""
End If
If Len( cboFltrItem ) Then
TranItemID.DefaultValue = cboFltrItem
If Len( sFltr ) then sFltr = sFltr & " and "
sFltr = sFltr & "TranItemID=" & cboFltrItem
Else
TranItemID.DefaultValue = ""
End If
Me.Filter = sFltr
Me.FilterOn = Len( sFltr ) > 0
End Function

Set the AfterUpdate properties of both PID and TranItemID combos to:
=SetFilterAndDefaults()

Now, when you pick a project from cboFltrPID, your form will show only
transactions for that project, and new transactions added will be assigned
to that project by default. Similarly, you can filter on a particular item,
with that item being the default for new transactions.

If you accidentally add a transaction to the wrong project, you can reassign
it simply by selecting a different project in the detail section of the
form.

b) I think I just answered that one above
It sounds to me like you don't actually want to delete projects at all,
just reassign the occasional transaction from one project to another.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,
It works!!!

Do you typically add several transactions at a time for the same project
and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?


a)
I add several transaction for the same project but for different items.

eg. Project 1834 can have items A- Z each with serveral transactions.


One question you have not answered yet: Why do you need a form and subform
bound to the same table?

b)
Do you suggest that my main form be unbound since the combo boxes on my
main
form are referencing the subform.

I used your example and code and passed Me.cmbpno to my Delete_Project
form.

Everything works right now. I did not delete the projectID from the
Project
table since all the projectID are preloaded on my form.

Everything works the transactions gets deleted and the reassign action
happens without any problems.
I must take this opportunity to thank you for your stepwise approach,
patience and persistance in helping me solve my problem.
Also it would be helpful if you could clarify on the above 2 to enable me
in
my learing process.

Hi Vandy

Sorry - I've been away for the weekend.

If my understanding is correct, binding your combo boxes to PID and
TranItemID is NOT going to work for you. The problem is that when you
select values in these combo boxes to filter your linked subform, you
will
actually change the PID and TranItemID in the current record of the main
form.

I suggest that your main form should be unbound, and that you should pass
Me.cmbpno to your Delete_Project form.

One question you have not answered yet: Why do you need a form and
subform
bound to the same table?

Do you typically add several transactions at a time for the same project
and
the same item? Or do you typically add several transactions for the same
project but for different items? Or do both project AND item tend to be
different?

On the subject of the code in your dialog form, first I would add a
command
button "OK" and attach your code to that, not to the click event of the
option group. You will also need a combo box to select the project to
reassign transactions to.

Your variable, lngPIDtoDelete, should be declared at the top of your
module:
Option Explicit
Dim lngPIDtoDelete as Long

Then your code for the click event needs to be something like this:

Private Sub cmdOK_Click()
Dim strSQL As String
Select Case MyOptionGroup
Case 1 ' delete
strSQL = "Delete from tblTransactions where PID = " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError

Case 2 ' reassign
If IsNull(cmbReassignPID ) Then
MsgBox "Select a project to reassign transactions to"
Exit Sub
End If
strSQL = "Update tblTransactions set PID = " & cmbReassignPID _
& " where PID = " & lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
strSQL = "Delete from tblProjects where ProjectID= " &
lngPIDtoDelete
CurrentDb.Execute strSQL, dbFailOnError
End Select
End Sub

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

Thanks for your time. What you say makes a lot of sense.

My ProjectID combo box is now bound to PID

My ItemID combo box is now bound to TranItemID

I have successfully passed the correct PID to the unbound DELETE
PROJECT
FORM.

Question
Let's take another step back. What is the actual purpose of addingfrm?
Do
you want to select a project and add related transactions? Or do you
want
to add transactions for several different projects one after another?

answer
I first select a Project using project combo box and then select the
items
using ItemID combo box on my main form.
The subform than displayes the projectID , ItemID and i receive or
issue
the
item and the record gets updated in my transaction table.

Once i bound both my combo boxes the correct pid got passed in the open
argument.

In my DELETE PROJECT UNBOUND FORM i have the following code written.

Private Sub Form_Load()
lngPIDtoDelete = Nz(Me.OpenArgs)
MsgBox Me.OpenArgs

If lngPIDtoDelete = 0 Then
MsgBox "ERROR"

End If
End Sub

Here the correct PID is passed to the form.

I have an option group
1- delete project
2- reassign project

Private Sub myOptionGroup_Click()

Dim strSQL As String
If myoptiongroup = 1 Then 'delete project
strSQL = "Delete tbltransactions where PID = " & Me.ProjectID
CurrentDb.Execute strSQL, dbFailOnError

Else 'reassign project
MsgBox ("reassign")
End If
End Sub

could you run me through these codes again in order to delete the
correct
pid.

When i run this code method i get a datamember not found error msg.

thanks a ton for your patience and time. might be i should be getting
some
basic code training!!


:

Hi Vandy

OK, so I'm getting a little closer to understanding *what* you have,
but
I'm
a little at a loss about *why*

If your main form is bound to tblTransactions, I can't see why you
have a
subform at all, as it is bound (or so you imply) to the same table.

You say the LinkMasterFields for your subform are: cmbpno;cmbitemno

Are these two combo boxes on your main form? Are they unbound (no
ControlSource)?

If so, then the only record(s) that will display in your subform are
transactions which match BOTH the PID and the ItemID selected in the
two
combo boxes. (Is this what you want?)

Also, if cmbpno is unbound, the value selected in that combo box will
not
necessarily match the PID of the current record in the main form.
Therefore, if you pass Me.PID to your dialog form you won't
necessarily
be
passing the same PID as the record in your subform.

Does this make sense?

Let's take another step back. What is the actual purpose of
addingfrm?
Do
you want to select a project and add related transactions? Or do you
want
to add transactions for several different projects one after another?
--