Code modification

[jaad]

This is a very handy little code that was given to me from a fine expert of this site a while back. It works perfectly, I just want to add something to it:

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?

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 for my Tenants is called [TenantsT]

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

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));

If I missed anything please let me know. Thanks a bunch

[nico5038]

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.

What trouble ?
Guess it's possible to have multiple Workorders for one tenant....

Nic;o)

[jaad]

The reason to have only one Work Order going per tenant at any given time is to keep the administration under control and simple. If it was just the Work Orders it wouldn't be such a big deal but for every Work Order that gets open I have to open a new Purchase Order, new Schedules with different people, New Journals, 4 invoices instead of one from the same contractor, 10 invoices instead of 1 for supplies and so on... Does that make sense to you?

[NeoPa]

A relatively simple way to check would be to use DLookup(). Have you ever come across Domain Aggregate functions before in VBA (DLookup() is one such)?

[jaad]

Is there a spot in that code that show above that would be a good place to put the Dlookup in? Is it better to put it before or after or maybe right in the middle?

[nico5038]

I would take this "to the beginning", by not showing tenants having an order.
So the selection combo's query needs to exclude tenants that are having an order by using a so-called "outer-join" to the orders table. Having such a JOIN (double click the connection line in the query editor and make the tenants table "master") and by testing the OrderID being Null will give you the tenants that are OK.

Getting the idea ?

Nic;o)

[NeoPa]

Is there a spot in that code that show above that would be a good place to put the Dlookup in? Is it better to put it before or after or maybe right in the middle?

That depends on what you want. The DLookup code will check whether the item exists already. Where do you want within your code?

[jaad]

thank you, I will give this a try, I'm more than certain that it will work...

Cheers