Trouble with Forms

[dsomers]

I've set up my tables and created a query that consists of fields from several tables. I then created a form and linked the query to it as the source. Since there are multiple tables, I had Access split the form into a main form and subform. The problem I'm having is I can enter data on the main form, but when I go to the subform to enter the Lease information for my real estate management program it won't let me enter information into fields that come from my tblLease table (Move In Date, Lease Start Date, Lease Expires, Additional Rent, Additional Rent Description, ActiveLease). Also, it will not enter an apartment number from my table tblApartmentInfo. I've attached the db to this post. Could someone please take a look and see where I'm going wrong?

Thanks!
David Somers

 

[Tiro Cupidus]

The Access status bar says it all: the join key for tblLease is not in your recordset. Add DepositID and ApartmentID to your Record Source.

 

[dsomers]

Please forgive me for asking this question because I'm pretty new to Access programming. How do I enter those into the record source?

Thanks!
David Somers

 

[dsomers]

Nevermind, I figured it out. However, there's still a problem. Here's my record source for my subform:

SELECT tblApartmentInfo.ApartmentNumber, tblBuildingInfo.BuildingNumber, tblDeposit.SecDeposit, tblDeposit.PetDeposit, tblLease.MoveInDate, tblLease.LeaseStartDate, tblLease.LeaseExpirationDate, tblLease.AdditionalRent, tblLease.AdditionalRentDescription, tblLease.ActiveLease, tblLease.TenantID, tblLease.DepositID, tblLease.ApartmentID FROM tblDeposit INNER JOIN ((tblBuildingInfo INNER JOIN tblApartmentInfo ON tblBuildingInfo.BuildingID=tblApartmentInfo.BuildingID) INNER JOIN tblLease ON tblApartmentInfo.ApartmentID=tblLease.ApartmentID) ON tblDeposit.DepositID=tblLease.DepositID;

It will now let me enter a Move In Date, Lease Start Date, Lease Expiration Date. But, it will still not let me enter an apartment number (aka Unit). Can somebody help me get this?

Thanks!
David Somers

 

[Tiro Cupidus]

I think you need BuildingID.

 

[dsomers]

Here's what I've put in:

SELECT tblApartmentInfo.ApartmentNumber, tblBuildingInfo.BuildingNumber, tblBuildingInfo.BuildingID, tblDeposit.SecDeposit, tblDeposit.PetDeposit, tblLease.MoveInDate, tblLease.LeaseStartDate, tblLease.LeaseExpirationDate, tblLease.AdditionalRent, tblLease.AdditionalRentDescription, tblLease.ActiveLease, tblLease.TenantID, tblLease.DepositID, tblLease.ApartmentID FROM tblDeposit INNER JOIN ((tblBuildingInfo INNER JOIN tblApartmentInfo ON tblBuildingInfo.BuildingID=tblApartmentInfo.BuildingID) INNER JOIN tblLease ON tblApartmentInfo.ApartmentID=tblLease.ApartmentID) ON tblDeposit.DepositID=tblLease.DepositID;

It still does not let me enter an apartment number on the subform. Any ideas?

Thanks!
David Somers

 

[dsomers]

Nevermind.. I got that fixed I think. I had to add tblApartmentInfo.BuildingID instead of tblBuildingInfo.BuildingID. So you were correct. It will let me input an apartment number now.

However, now when I try to add a new tenant it gives me an error about duplicate information. I have add new record command buttons on both the main form and subforms. I've attached the latest version to this post. If anybody could help me, I'd really appreciate it.

Thanks!
David Somers

 

[dsomers]

Do you all think that if I added DepositID and ApartmentID from tblLease and BuildingID from tblApartmentInfo to my NewTenantQuery that it would correct this error?

Thanks!
David Somers

 

[dsomers]

The error I keep getting is 'You cannot change or add a record because a related record is required in table tblTenants'. Can someone look at my db posted above and advise me where I'm going wrong and what I need to do to fix this?

Thanks!
David Somers

 

[dsomers]

Could it possibly be something wrong with my design? Somebody please let me know.

Thanks!
David Somers

 

[Tiro Cupidus]

It seems it's wanting you to tie the lease information to a tenant (TenantID). When you enter a lease record, it defaults the TenantID field to 0.

 

[dsomers]

I have a relationship set from TenantID (FK) in tblLease to TenantID (PK) in tblTenant. What could it be?

Thanks!
David Somers

 

[Tiro Cupidus]

I think you need to specify a tenant when you enter a lease record.

 

[dsomers]

Is there a way to do that automatically by passing it to the lease information form when I click then button to insert lease information?

Thanks!
David Somers

 

[Tiro Cupidus]

Edit: No, I think I was wrong about this.

The way you have it now, you can't store the Tenant information until you hit the add button, at which time the record goes away. If you go to lease information before you add the record, you can't pass the new ID, and if you go to lease information after you add the record, you've already moved to a new record, so again you can't pass the ID.

What do you think about putting this all on one form? Or a true subform?

 

[Tiro Cupidus]

Why aren't BuildingID and ApartmentID autonumbers?

 

[dsomers]

Those are not autonumbers because I don't need them to be.

I can't put this all on one form because it has multiple tables involved with the query and it would make things more complicated. Unless I'm wrong.

I've made some changes however and not I get an error saying: 'You can't assign a value to this object' when I try to open the Lease Information. I've attached it to this post, can you take a look and see whats going on?

Thanks!
David Somers

 

[Tiro Cupidus]

The line causing your error is:

Me.TenantID = Me.OpenArgs

You might look here for reference, as I don't have much experience with OpenArgs.