By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,442 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Code modification

100+
P: 158
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub OKCmd_Click()
  2. If Len(Nz(Me.TenantID)) > 1 Then
  3.    DoCmd.RunCommand acCmdSaveRecord
  4.   Forms("WOrkorderF").Filter = "WOID=" & Me.WOID
  5.   Forms("WOrkorderF").FilterOn = True
  6.  DoCmd.Close
  7. Else
  8.    MsgBox "Tenant is mandatory, add one or press Cancel"
  9. End If
  10.  
  11. 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.

Expand|Select|Wrap|Line Numbers
  1. SELECT WorkOrderT.WOID, [FirstName] & " " & [LastName] & "     " & [Unit] & "  -  " & [Building] AS Tenant, WorkOrderT.Active, WorkOrderT.WorkOrderDate, WorkOrderT.TenantID, TenantsT.HomePhone, TenantsT.MobilePhone, LocationsT.LocID, BuildingLT.BuildingID
  2. 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
  3. WHERE (((WorkOrderT.Active)=-1));
  4.  
If I missed anything please let me know. Thanks a bunch
Jan 30 '10 #1

✓ answered by 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)

Share this Question
Share on Google+
7 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Jan 31 '10 #2

100+
P: 158
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?
Jan 31 '10 #3

NeoPa
Expert Mod 15k+
P: 31,492
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)?
Jan 31 '10 #4

100+
P: 158
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?
Jan 31 '10 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Jan 31 '10 #6

NeoPa
Expert Mod 15k+
P: 31,492
@jaad
That depends on what you want. The DLookup code will check whether the item exists already. Where do you want within your code?
Feb 1 '10 #7

100+
P: 158
thank you, I will give this a try, I'm more than certain that it will work...

Cheers
Feb 1 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.