This code helps me pick out a Tenant from a table based on its TenantID. A thing that happens is if there is already a work order for a tenant with the same TenantID reference then I get into trouble.
Is there a way to look and see if first there is a Work Order that is already active for this TenantID?
Expand|Select|Wrap|Line Numbers
- Private Sub OKCmd_Click()
- If Len(Nz(Me.TenantID)) > 1 Then
- DoCmd.RunCommand acCmdSaveRecord
- Forms("WOrkorderF").Filter = "WOID=" & Me.WOID
- Forms("WOrkorderF").FilterOn = True
- DoCmd.Close
- Else
- MsgBox "Tenant is mandatory, add one or press Cancel"
- End If
- End Sub
-The table where I store my Work Orders is Called [WorkOrderT]
The form used to filter a tenant is called [WOrkorderF] which get its value from a query called [WOSourceQ] with this code in it.
Expand|Select|Wrap|Line Numbers
- SELECT WorkOrderT.WOID, [FirstName] & " " & [LastName] & " " & [Unit] & " - " & [Building] AS Tenant, WorkOrderT.Active, WorkOrderT.WorkOrderDate, WorkOrderT.TenantID, TenantsT.HomePhone, TenantsT.MobilePhone, LocationsT.LocID, BuildingLT.BuildingID
- FROM LocationsT RIGHT JOIN (BuildingLT RIGHT JOIN (TenantsT RIGHT JOIN WorkOrderT ON TenantsT.TenantID=WorkOrderT.TenantID) ON BuildingLT.BuildingID=TenantsT.BuildingID) ON LocationsT.LocID=TenantsT.LocID
- WHERE (((WorkOrderT.Active)=-1));