Items not showing on forms

[dsomers]

Hello all! I've started working on my redesigned db, and have a problem when I create a new tenant form, when I try to run it to see what it looks like and see if it pulls the data, nothing but the background shows up. I've attached it to this message.

Thanks!
David Somers

 

[dsomers]

I've determined the problem with the items not showing. When I add fields from more than 1 table, the fields do not show. When I add fields from just my tblTenants table, the fields show. Not sure why that is or how I can get it to where when I enter a new Tenant I can put their information in thats stored in other tables as well.

Thanks!
David Somers

 

[Jimused]

You need to create a query using the tables you want the data to go to then make that the RecordSource for your form. Hope this is of some help.

 

[dsomers]

When I created a query called New Tenants Query and selected the fields I wanted from my tables (about 4 different tables), and set that as the record source for my form, it still come up with a blank form. Any ideas?

Thanks!
David Somers

 

[Jimused]

IF the recordset is empty the form will be blank. Check your join properties in your query....they need to be set to select all records from the tblTenant and only those that match for all the other tables. That way, at the least your form should populate with the Tenant name. The way they are set now, they will only return a record if there are related records in every table.

Remember if you are going to use the tenant id as the link for all tables, that id has to be populated to each table whenever a record is created. For example, Tenant #1 is created and then you add rent info and perhaps apartment info. The tenant id has to go to these other tables in order for them to be connected. I usually accomplish this by having a main form with the tenant info on it and subforms for each of the other items. The subforms are linked to the mainform by the tenant id so whenever you add a record say for rent, the id is automatically inserted in that subforms table.

Hope this is all not too confusing...I am in rather a rush this morning.

 

[dsomers]

My recordset is set to my New Tenant Query which is the one I created for the form. As far as the relationships go, I've tried to create the relationships that I think would make sense. However, I don't know if I've got them right. I basically linked each PK ID with the FK ID that corresponds to the PK ID in each table. I guess the part of linking ID of a tenant to the ID of an apartment and building number. I think I'm confusing myself more than anything. But relationships is something that I definitely need help on.

Well lets assume that I go with subforms because it looks like that might be the best way to implement this. I'm guessing I'll need a query created for each of the subforms as well as the main form. And since I'm pretty new to all of this, when I create these queries, do I need to include every field from the table that subform data comes from?

Thanks!
David Somers

 

[dsomers]

Well, I've tried to figure out the relationships in my db. I think I've screwed it up pretty badly. I've attached a copy of my db. Can somebody please take a look and tell me what I'm doing wrong?

Thanks!
David Somers

 

[Jimused]

Unfortunately this is my last day at my current job and I will be out of touch for a week or two.
I would suggest that you perhaps begin a new thread and ask for help setting up your database.
Try to be clear in your description of what it is exactly that you want to do with your database, what it will be used for and hopefully someone with more time can help.
In the meantime, I would read up on relationships (tables) and join properties for queries. Also, take a look at the sample database that comes with Access named Northwind.mdb. There are some good examples of forms/subforms. Yes, for each subform you need a query but that is not too difficult to do.
Finally, try not to be too hard on yourself! If this is your first project, you have picked one that is a bit complex. There is some excellent help on this forum and I am sure you will connect with someone that will get you through. Good luck and my apologies for having to bail out at this time.

 

[dsomers]

That is no problem. I appreciate all the help you have given me. In fact I have been reading up on my relationships, and I've changed my relationships again, but still cannot get it right where the items will show on the form.

I have made a flow chart for the program I'm trying to build. Here is the entire listing:

1. When new tenant is approved, have program automatically fill in and print new tenant forms.

2. On first day of month, post rental rate to tenant ledger. Have place to enter additional rents.

3. As tenant pays rent, post rents to tenants account. Be able to batch a deposit ticket when time to deposit.

4. Be able to print a tenant history at any time.

5. Be able to print a report of all residents whose lease is expiring in next 60 days OR who have not had a rent change in 1 year (still 60 days ahead).

6. Be able to produce a receivables listing at any time (who has paid rent and who hasn't).

7. On 6th day of month, post 5% of rent to anybody that is past due (late fee). Also auto print late notice for those people.

8. On 11th day of month, auto print inhouse eviction notice for those that still haven't paid.

9. On 16th day of month, post $75 to tenant account and auto print eviction proceeding letters for those tenants.

7-9 requires a Mail Merge with Word that I have yet to try to tackle till I have the structure and relationships right with my tables. I have attached the latest version of my db with the relationships that I've messed with and still haven't gotten correct after reading the couple of Access books I have and doing searches here on the forum. If someone could please take a look at this and help me out, I'd really appreciate it.

Thanks!
David Somers