DCount Methods with primary form and sub form

[d9pierce1]

Good morning all,
I have a primary form (frmAccountHolder) and a subform (sFrmAccount) and When I Untick the IsActive checkbox Making it not active, I want to Untick the IsActive checkbox(s) if more than one in the subform which is a continous form.
It may have one or more accounts in it.
I think it is a DCount method but I have tried so many varancies of it and just cannot figure it out.
Here is current code.

Private Sub ChkIsActive_Click()
    If ChkIsActive = True Then
    'If DCount("AccountHolderID", "tblAccount", "IsActive=" & Forms!frmAccountHolder.sFrmAccount.Form!ChkIsActive) > 0 Then
        Forms!frmAccountHolder.sFrmAccount.Form!ChkIsActive = True
        Me.ChkRecordLock = False
    Else
        Forms!frmAccountHolder.sFrmAccount.Form!ChkIsActive = False
        Me.ChkRecordLock = True
    End If
     'End If
    Call Form_Current
End Sub

So, If ChkIsActive = False on primary form, I want to untick all of the ChkIsActive in subform.
I want it to count all the IsActives that have the same AccountHolderId and untick them all.
Current code works with not using the DCount part but will only untick one account in the subform, not all of them.
I would really appriciate some assistance on this please


Thanks
Dave

[Micron]

Isn't this just a matter of running a sql statement or stored query against the table for the subform when the checkbox is changed? Like
If me.AcccountID = 0 then << this pertains to the check box control so I probably should have named it something more intuitive
UPDATE tblSubform SET IsActive = 0 WHERE tblSubform.AccountID = Forms!MainFormName.txtAccountID

then refresh the subform. Could use False instead of zero. You'd use your own object names of course, and delimit text values - I assumed id is a number data type.

[Gicu]

I think Micron's suggestion would work (with some changes as I think the trigger Me.AccountID=0 should be modified to refer to the IsActive field).

But the main point is why have two IsActive fields? This (usually) goes against normalization rules, can't you replace the control source of the IsActive checkbox on the main form with a calculated expression (=IIF(DCount("*","tblAccount","AccountHolderID=" & Me.AccountHolderID)=0,False,True) to avoid the posibility of data integrity issues with two fields.

You can still have code (maybe a button or added to the double click event of the current checkbox) to uncheck all with an Update query (or looping through the subform's recordset as an alternative), but the main form's IsActive should now always be accurate regardless where the changes to the field in the tblAccount are done (this form, other form, query, table, etc.)

Cheers,

[Micron]

I think the trigger Me.AccountID=0 should be modified to refer to the IsActive field

I probably edited my post while you were replying because I noticed I was not clear about that (and maybe still am not). I took it that there really aren't 2 IsActive fields, just 2 controls and they are the Parent/Child controls. However, I don't think that makes sense and the whole post is not clear to me.

Are the accounts listed in the subform? Then what about the main form could be inactive - the member/customer? Then they could be active but one of their accounts may not be, in which case the two controls/fields are not dependent as I interpreted it.

[Gicu]

Then they could be active but one of their accounts may not be, in which case the two controls/fields are not dependent as I interpreted it

Yes, that would still account for an active account holder, but when all their accounts are inactive then the account holder itself becomes inactive.
Just my take....

[d9pierce1]

Hi all and thanks for the info.
The reason I wanted this was I can check an account inactive in my sub form and still have other accounts active but if I uncheck the account holder in the primary form I wanted all the accounts associated with it to be not active. The IsActive control or check box on each the form and subform are to trigger a record lock so no info can be changed. Hope I explained that. I will give some more tries to this.
Thank you all
Dave

[Gicu]

Hi Dave,
What I suggested is in no way hindering your goal here, it just ensures that the status of the account holder is always reflective of the sum of statuses of all their accounts. We don't know your app but I saw cases where users have access to the tblAccounts in different objects, being forms or queries, and changes in there will not trigger updates at the parent level.
You can easily add the update code to the existing checkbox (double-click) or to a button besides it:

Dim sSQl as String
if MsgBox ("Make account inactive?",vbYesNo)=vbNo then Exit Sub
sSQL="UPDATE tblAccount SET IsActive = 0 WHERE tblAccount .AccountHolderID = " & Me.AccountHolderID 
CurrentDB.Execute ssql, dbFailOnError
Me.Refresh

All records in subform should be clear and the main form check-box should be unchecked.
Cheers,

[Micron]

account inactive in my sub form and still have other accounts active

You're using terminology that is subject to interpretation. Maybe if I/we saw a pic or you were explicit in your description of things we would not be guessing - which I think we still are.

I am an investment account holder. I have 3 accounts. Am I the account, or are my holdings defined as 3 accounts? If the latter, then what am I as the holder of 3 accounts? It all depends on the rules of the business. I could be the account and my 3 holdings are defined by their actual investment type names. Or I could be a client and my 3 holdings are "accounts". I still don't think this is clear, but I admit to being a black and white guy (things have specific meanings and when not specific I don't like to guess).

[d9pierce1]

Hi all and again thank you,
I have tried both these methods with the first method It works except I get this Write Conflict?
With second Method the FirstsSQL= I get a debug two few parameters, expected 1
The second sSQL works, The Third and 4th sSQL I get the same Write Conflict?
Posted Coade below and photo of write conflicts

First Method

Private Sub ChkIsActive_Click()
    If ChkIsActive = False Then
    DoCmd.SetWarnings False
        DoCmd.RunSQL "UPDATE tblAccount SET IsActive = 0  WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID
        DoCmd.RunSQL "UPDATE tblAccount SET RecordLock = True  WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID
        DoCmd.RunSQL "UPDATE tblAccountDetail SET RecordLock = True  WHERE tblAccountDetail.AccountHolderID=" & Me.AccountHolderID
        DoCmd.RunSQL "UPDATE tblAccountDetail SET AccountClosed = Now()  WHERE tblAccountDetail.AccountHolderID=" & Me.AccountHolderID
        DoCmd.SetWarnings True
        Me.ChkRecordLock = True
        Me.TxtAccountHolder.SetFocus
    Else
        Me.ChkRecordLock = False
        Me.TxtAccountHolder.SetFocus
    End If
   
    Call Form_Current
End Sub

Second Method

Private Sub ChkIsActive_Click()


 Dim sSQl As String
If ChkIsActive = 0 Then
If MsgBox("Make account inactive?", vbYesNo) = vbNo Then Exit Sub
    sSQl = "UPDATE tblAccount SET IsActive = 0 WHERE tblAccount.AccountHolderID=" & Me.AccountHolderID
    'sSQl = "UPDATE tblAccount SET RecordLock = True  WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID
    'sSQl = "UPDATE tblAccountDetail SET RecordLock = True  WHERE tblAccountDetail.AccountHolderID=" & Me.AccountHolderID
    'sSQl = "UPDATE tblAccountDetail SET AccountClosed = Now()  WHERE tblAccountDetail.AccountHolderID=" & Me.AccountHolderID
CurrentDb.Execute sSQl, dbFailOnError
Me.Refresh
End If
        Me.ChkRecordLock = True
        Me.TxtAccountHolder.SetFocus
  
    Call Form_Current
End Sub

I dont know why this wont work?
Dave

[d9pierce1]

HI all again,
OK, found the error with parameters! My Bad
So, with this I got this to work (Below) just as it should!

Private Sub ChkIsActive_Click()


 Dim sSQl As String
If ChkIsActive = 0 Then
If MsgBox("Make account inactive?", vbYesNo) = vbNo Then Exit Sub
    sSQl = "UPDATE tblAccount SET IsActive = 0 WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID
    'sSQl = "UPDATE tblAccount SET RecordLock = True  WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID
CurrentDb.Execute sSQl, dbFailOnError
Me.Refresh
End If
        Me.ChkRecordLock = True
        Me.TxtAccountHolder.SetFocus
  
    Call Form_Current
End Sub

So, If I add one or both of the (Below) to this, then I get the write conflict and the above two do not work any longer?
Why would that be?

    'sSQl = "UPDATE tblAccountDetail SET RecordLock = True  WHERE tblAccountDetail.AccountHolderID=" & Me.AccountHolderID
    'sSQl = "UPDATE tblAccountDetail SET AccountClosed = Now()  WHERE tblAccountDetail.AccountHolderID=" & Me.AccountHolderID

[Gicu]

Dave,
You are leaving out a bit too many pieces of the puzzle.... Now you want to update two tables, but we don't know how they re related, which form (main\subform) is bound to which. To update multiple fields you simply combine them into one update SQL statement.
In your example I think the first update will update the underlying table and because the subform is also based on that you get the write conflict.

Private Sub ChkIsActive_Click()


Dim sSQl As String
If ChkIsActive = 0 Then
If MsgBox("Make account inactive?", vbYesNo) = vbNo Then Exit Sub
'update tblAccount
sSQl = "UPDATE tblAccount SET IsActive = 0, RecordLock = True WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID    
CurrentDb.Execute sSQl, dbFailOnError
'requery the subform
Forms!frmAccountHolder.sFrmAccount.Form.Requery
'update tblAccountDetail
sSQl = "UPDATE tblAccountDetail SET AccountClosed = Now(), RecordLock = True WHERE tblAccount.AcctHolderID=" & Me.AccountHolderID    
CurrentDb.Execute sSQl, dbFailOnError


Me.Refresh
End If
        Me.ChkRecordLock = True
        Me.TxtAccountHolder.SetFocus
  
    Call Form_Current
End Sub

Cheers,

[d9pierce1]

thank you so much! This works
Youre the best!
Dave