cascading delete coding help required

[Graham Mandeno]

Hi Vandy

You're very welcome! I hope you have learned a lot and that your future
with Access goes well!
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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?
--