Automatic Update of ID's While Form is Filtered 2

[lastout]

I have a database of tenants for various buildings. When a user needs to add a new tenant to a building they have to select the exact building from a combobox then that building (using its ID number as a global variable, intProjectID) opens the "Add new tenants" form filtered for just that building. That's the background to my question. I actually have three related questions.

First question: Right now I have it set up so that when the user changes to a new record in the filtered form, a new tenantID is added (autonumber) and the building ID is automatically (using Form_OnCurrent) assigned as intProjectID. This works but what happens is that EVERY TIME I move from one record to another in the filtered recordset, the Building ID is assigned over and over again. Like I said it works but I can't help but consider that sort of dumb. Is there any other way to do this?

Second question: Every building has a more or less fixed set of apartments. The tenants can change but apt's generally do not. For this reason, tenants and apartments are in two different tables. When the user goes into the "Add new tenant form" they have have to assign the tenant to a specific apt. The two tables are brought together by a simple query (don't want for a variety of reasons to use a subform here) and the recordset is updateable in the query. When I go to the form though, I can't add the apt when I add the tenant. I have to move to another record and then move back to the tenant I just added and ONLY then can I add the apt. I don't know why this is. Any clues?

Third question: Because of the two table structure behind the query that is the recordsource for the form, I need another filter. I think. The first filter is the one in question 1, for the building itself. Apartments are in a separate table from buildings. The apartments table and the tenants table both contain the building ID. When a user adds a new tenant they not only need the building ID to be automatically assigned to the filter intProjectID but also the building ID in the apartment table. I hope I'm making sense here. I can manage to get the question 1 building ID to work but not the one adding a building ID to a new apartment in the apartment table. AHHH, sorry I should've said earlier, we don't always have the full list of every last apt in a building at the start. We build that as we go along.

If you can answer one question, all questions, or only part of questions above, PLEASE do. Any help is VERY much appreciated.

 

[Edski]

From what I understand tenants and buildings should not be related directly. A tenant can own many apartments (1 to many) and a building can contain many apartments (1 to many). Can you post your relationships diagram? That could be the underlying problem.

For question one, did you consider using the BeforeInsert Event instead of OnCurrent?

Ed

 

[Eupher]

Another option for question 1 is to assign the building ID conditionally, i.e. when the form moves to a new record:

If Me.NewRecord Then
     'code to assign Building ID
     Else
          'nothing
End If

Ken S.

 

[lastout]

Edski and Eupher, Thanks so much! Two perfect tickets.

Edski, I'm not sure how to post the relationship diagram on the forum. Can you tell me how to do it?

I've been thinking and tinkering more around question 2 and wonder if there are two autonumbers (primary keys) from two different tables in a query, is there any order or precedence implied for one to be created before the other?

Thanks again!

 

[Edski]

use TGML. The Instructions are below 'Your Reply' window.

img

http://www.yoursite.com/image.gif

This image actually exists!

You'll need to upload the image to your website.

For question 2, I suspect the reason it's not letting you assign an apt to a newly added tenant is that the new tenant record has not yet been saved. Try Me.Refreshing the form before assigning an apt. (Moving back and forth from one record to another has the same effect).

Difficult to comment further without looking at your Relationships and the underlying query you're using. Also, do old tenants (tenants that have left an apt) stay in the database or get overwritten/deleted?