How to code an "IF...THEN...ELSE" loop? Is this basic?

[gusbrunston]

I'm trying to "autopopulate" a field with a calculated long integer based on the value entered in the previous control:
(the following is in "pigeon basic"!)

Assume there are already TenantID's 3246001 and 3246002

Field 1: PropertyID = 3246

       TenantID = (PropertyID * 1000) + 1
       
       LOOP
            IF TenantID is a duplicate THEN
                 TenantID = TenantID + 1
            ELSE
                 Exit Loop
            END IF
       REPEAT

Field 2: TenantID = 3246003

Do you think this will work? Can you help me code it, do I use a variable, requery, etc.? (It's a long way from MBasic.)

Thanks a bunch in advance.

Gus Brunston
An old PICKer
padregus@home.com

 

[Broccoli2]

1. Create a Module if you haven't already got one, and 'design' it
2. Type this in:

Function fnSpareTenantID( _
    ByVal PropertyID As Variant) _
    As Variant

    If "" & PropertyID = "" Then _
        Exit Function
    fnSpareTenantID = _
        (PropertyID * 1000) + 1
    While &quot;&quot; & DLookup(&quot;TenantID&quot;, &quot;tenants&quot;, &quot;TenantID=&quot; & fnSpareTenantID) <> &quot;&quot;
        fnSpareTenantID = _
            fnSpareTenantID + 1
    Wend

End Function

(I presumed you have a table called 'tenants' where you're checking for duplicates - change as necessary)
3. In your form design view, set the source for your TenantID control to '

=fnSpareTenantID([PropertyID])

'

Hope this helps,
-Rob

 

[MichaelRed]

Yes, ... but ...

If the [TenantId] is already in a table, just get the largest value from the table and increment it?

Select Max([TenantId] from [MyTbl] where [PropertyId] = 3246

Of course there is some more to it, but in "pigeon basic" it reads O.K. to me.

More being little things like declaration of the db and the recordset thinggy, also getting the various table and field names to be what is in the real database, assigning the query result to a variable, incrementing the variable, doing the 'REAL" SQL syntax ...

but every 'old PICKer' would do this in the German manner (Auto?)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[Broccoli2]

True, but if you step through from 1 you can 'fill in the gaps' (which I suppose may or may not be desired). In terms of speed, it probably depends on how many tenants there are per property...

-Rob

 

[JerryDennison]

My question is why are you doing this? What purpose does this value serve? It may very well be you are doing something that is totally unecessary. I would re-examine how this value fits into the normalization structure of your database.

 

[gusbrunston]

Dear Jerry:
Thanks very much for your question. I was pretty sure that I had thought about this so much that I couldn't put my finger on what was bothering me. My thinking is this:

I have 3 significant tables: Properties, Owners, Tenants.

I thought if, e.g.:

               OwnerID = 0749
            PropertyID = 3246
              TenantID = 3246003

then invoices, reports to owners, collection letters, etc. could be identified with a number displayed like this:

0749-3246-003.

and users would immediately recognize the relationship between the Owner, the Property, and the Tenant (is the third tenant in this property).

There might be more than one TenantID associated with the property--previous tenants, co-tenants, etc.

Your suggestions and advice are quite welcome, and your question is important to me.

Many thanks,
Gus Brunston
An old PICKer
padregus@home.com

 

[JerryDennison]

You should already have a table that identifies and describes the tenant. The tenant should then have it's own ID(this need be nothing more than an autonumber field, the actual value of the PK isn't important so long as it is unique). Because you are using the id in conjunction with the property they are renting you can get the property information without the need of trying to make the ID itself meaningful.

Here's a good reason why you shouldn't try to apply meaning to this number for the Tenant. What happens if they move from one apartment to another? You'll either have to lose the history they have in the current apartment when you change their ID to reflect the move or you'll have to duplicate all their personal information by creating another tenantid for them altogether.

 

[gusbrunston]

Jerry:
Thanks for you comments. The users want to be able to ascertain the current tenant record. I had thought of doing it the way I described earlier...now, I'm thinking that I could introduce a history table containing (among other possible bits of data) information about who leased a property, when, for how long, and when they left, etc. Could be accessed from forms relating to properties and tenants. OK...cogitating...
Thanks again.
Gus Brunston
An old PICKer
padregus@home.com

 

[JerryDennison]

Just because the want to ascertain the current tenant record does not mean you must build a calculated PK. You can still give them all of that and more by simply insuring your data is properly normalized.