Subform - Limit: one record's 'Active' field to TRUE

[datalore]

Hi all,

Hope everyone is well!

Quickie for you:

Two Tables: Doctors & Applications, 1 - M relationship. Main Form shows Doctors info, with Subform based on Applications. Simple enough.

In the Applications table, I have an Active field.

What I need to ensure is that JUST BEFORE creating a new application record for THIS doctor in the subform, the user MUST set any existing Active=TRUE fields in other records to FALSE (unticking a checkbox).

I have it working so that creating a new application record has 'Active' set to TRUE (ticked).

I think there is great work being done on this site, and this problem is actually for my wife, but she has no internet access, so I am helping out.

*

*

*

*

*

*

PS: I know it is principle to name related fields with the same name, but I always set up a table field that will be a foreign key field to begin with 'TableName_Code';

i.e. based on the example above, Doctors RegNo is linked to Applications Doctors_RegNo.

The reason is if you start naming fields the same, it is not obvious where tables with multiple links are actually linked to. What are your views on this?

 

[TheAceMan1]

How are ya datalore . . . . .

Try using:

[blue]   Dim SQL as String

   SQL = "Update [purple][b]YourTableName[/b][/purple] Set [Active] = False;"
   DoCmd.RunSQL SQL[/blue]

See Ya! . . . . . .

 

[datalore]

Cheers AceMan, but not what I am looking for exactly...

Say a doctor has an application already entered, and I wish to enter a new one.

As soon as I INSERT a new application record, I still want the previous one displayed (I am using a Datasheet view in the subform), but I want to set the previous application record's Active field to FALSE.

Note that when I create an application record, the Active field defaults to TRUE, hence what I need has to happen on INSERT.

Also, I do not want more than one application record Active at any one time.

 

[TheAceMan1]

datalore . . . . .

Since [purple]Active[/purple] will [blue]always be set to True for a new record[/blue], don't bother setting this in code. In [blue]form design view[/blue], setting the [purple]DefaultValue[/purple] property for Active to True does the same thing!

See Ya! . . . . . .

 

[datalore]

Thanks AceMan...

Solved the problem myself. Here is VBA code I insereted for the subform.

You might wanna paste this into notepad with word wrapping turned off:

Private Sub Update_Active()

Dim SQL As String

SQL1 = "UPDATE Applications " & _
         "SET Applications.[Active] = ""No"" " & _
         "WHERE Applications.Doctor_RegNo = """ & Me.Doctor_RegNo & """"

    
If Me.Active.Value = "Cancel" Then  ' Warn the user that cancelling a record will
                                    ' eventually remove it from view the next time
                                    ' that the doctor's record is displayed.

    response = MsgBox("This record will be removed (i.e. 'hidden') the next time you view this Doctor's application record.", vbCritical, "Cancelling a record!")


ElseIf Me.Active.Value = "Yes" Then ' Update every OTHER Application.Active field to "No".

    DoCmd.SetWarnings False         ' Remove Update warnings.
    
    DoCmd.RunSQL SQL1
 
    DoCmd.SetWarnings True          ' Restore Update warnings.
 
    
End If

End Sub



Private Sub Active_LostFocus()

    If Me.Active.Value = "Yes" Then
        
        Call Update_Active
    
    End If
    

    If IsNull(Me.Category.Value) Or IsNull(Me.Specialty) Or _
        IsNull(Me.CountryQualified) Or IsNull(AcceptanceDate) Then
        
        response = MsgBox("This record needs certain information entered." + vbCrLf + "Please ensure fields are correctly entered.", vbCritical, "Request for information!")
        
        Me.AcceptanceNo.SetFocus
        
    Else
    
        Me.Refresh  ' To handle a 'Save Record' when changing the Applications.Active field
                    ' from "Cancel" to "Yes" (multiple updates invoked)
    End If

    
End Sub



Private Sub Active_AfterUpdate()

    Call Update_Active

End Sub