VBA coding update - IIF value exists... (1 Viewer)

[Valery]

Hi all,

I have some coding that needs updating. I did not write the code and cannot perform the two updates required.

1) At the time the code was written the control text box name was status, same as the field name. I would like to change the control text box name to cboStatus for consistency. I don't know where in the code the name referred to represents the control name vs the field name.

2) The coding looks to ensure that before the form is closed, there must be one (and only one) tenant with a Status "F". The problem here is that the form is closed with a command button that opens another form (a listing). Currently, the code does not let you close the form (which is fine) but it does open the listing... It should prevent any other form from opening and remain on the screen.

If you need any other information, please let me know.

CODE:

Public Function chkStatus() As Boolean
    Dim rs As DAO.Recordset
    Dim iCount As Integer
    Set rs = Me.RecordsetClone
    'Me.Parent.pStatus = True
    With rs
        If Not (.BOF And .EOF) Then
            .MoveFirst
            .FindFirst "[Status] = " & Chr(34) & "F" & Chr(34) & _
                " And [UnitNum] = " & Me.Parent![UnitNum]
            If .NoMatch Then
                MsgBox "You must designate at least one Main Tenant (status F).", _
                        vbInformation + vbOKOnly, _
                        "Tenant Status"
                        chkStatus = True
            End If
            
        End If
        .Close
    End With
    Set rs = Nothing
End Function

Private Sub Status_BeforeUpdate(Cancel As Integer)
    Dim rs As DAO.Recordset
    If Me.Status = "F" Then
        Set rs = Me.RecordsetClone
        With rs
            If Not (.BOF And .EOF) Then
                .FindFirst "[Status] = " & Chr(34) & Me.Status & Chr(34) & _
                " And [UnitNum] = " & Me.Parent![UnitNum]
                If Not .NoMatch Then
                    If ![TenantID] <> Nz(Me.TenantID, 0) Then
                        MsgBox "You already selected a Main Tenant", _
                                vbInformation + vbOKOnly, _
                                "Tenant Status"
                        Me.Status.Undo
                        Cancel = True
                    End If
                End If
            End If
            .Close
        End With
        Set rs = Nothing
    End If
End Sub

 

[CJ_London]

me.status will refer to the control

note if you change the name of the control, it will no longer be linked to Private Sub Status_BeforeUpdate - so don't forget to relink it

 

[Galaxiom]

2)The coding looks to ensure that before the form is closed, there must be one (and only one) tenant with a Status "F".

This entire procedure is a workaround for a serious data structure error. Proper table and relationship design makes it impossible to enter invalid data, even directly into the tables. Developers should never rely on procedures to protect data integrity.

Since there can only be one main tenant, the structure should make it impossible to designate more than one. Thus, MainTenant should not have the scope to be entered multiple times. It should be a field in the Units table that identifies a single record in the Tenants table. However, using the typical PK from the Tenants table (eg: TenantID) would allow a MainTenant to be entered without even being member of the Tenants for that unit. This obviously cannot be allowed either.

The Tenants table will already have a foreign key for the UnitID. Add another field to identify the individual tenant (eg: TenantNum.) Store this in MainTenant. The Tenant record is uniquely identified as the MainTenant by the combination of UnitID and TenantNum. This is a classic case for the use of a Composite Primary Key on the Tenants table. Indeed, AFAIK, setting up the required Referential Integrity can only be done with a Composite Key.

Two separate relationships between the tables are required. Firstly, Unit/Tenant needs a one-many on UnitID so that no Tenant can be entered for a UnitID that does not exist (or a unit deleted that still has tenant records). A second relationship with a 1-1 on Unit.UnitID = Tenant.UnitD and 1-1 on Unit.MainTenant = Tenant-TenantID ensures that the MainTenant must be member of the corresponding Unit's Tenant list.

I have attached a simple example of this structure. Study the Relationships closely. (The form is obviously very crude. eg The TenantNum needs to be generated automatically.)

EDIT: I have realised there is a problem with this strategy not being able to enter either a Unit or a Tenant because of the RI to each other. I am going to see if this can be gotten around using a Transaction. Will report back on this tomorrow.

However my comment about the MainTenant belonging in the Unit table still stands.

 

[Galaxiom]

I'm disappointed that I cannot make a relationship that demands the MainTenant be a member of the Tenants for a Unit. I ran into the same "chicken and egg" problem no matter how I set up Referential Integrity.

In the end I used a full UnitTenant many-to-many relationship which allows the same tenant to occupy multiple units.

Another many-to-many stores the MainTenant. The MainTenant Row Source limits the tenants to those already designated as being in the unit. The combo is Requeried whenever there is a change in the form. See the VBA.

 

[Valery]

Thank you for the response Glaxiom. Very clear to me especially with the explanation and the sample. I understand what you are suggesting. I will give it a lot of thought and try to implement it.