Someone was generous enough to provide me with this coding/module which works great! However, the database structure has changed somewhat and I need something added to the function and do not have a clue how to do this.
Code:
Public Function fnUpdateMailListField()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "Update tblTenants Set [MailList] = True Where [MailList] = False And " & _
"Val(fnAge([DOB]) & '') > 16;"
Set db = Nothing
End Function
Need to add NOT to change the MailList field (as per the function) IF the tblTenancy table [EndDate] field is not null.
or, if it is easier, the reverse:
Make the change to the MailList field (as per the Function) ONLY if the tblTenancy table [EndDate] field is null.
Shoot! It is two different tables. I guess that is a big problem? Please see JPEG for relationships. I just changed ALL of the relationships and added the tenancy table based on strong, experienced advice from one of the super moderators.
I suggest trying to make the query you need in the query builder, save it and then run it with DoCmd.Openquery. I think you might this easier than you think. Just select Query Design from the CREATE Tab. Add the tables you need for this which looks like tblTenents and tblTenancy. Since you have a relationship between these tables they will already be joined for you. Click Update in the ribbon. Put in MailList for a field. Put True in Update To and put False in the Criteria. Select the other fields you need for criteria and add the criteria you want. For adding something like Val(fnAge([DOB]) I suggest right click in one of the field boxes and click build so you can develop the expression with the expression builder.
If you run into problems upload a screen shot of the query and we help you along,
It could if you wanted it to. You could run it in your main form;'s on load event or put the code to run it in a module and call in from the Autoexec.
Oh and a tip on making this query. You can see what records a update query will be updating by changing to data sheet view in the query builder. This doesn't do the updates so it's good for checking to see if you got the criteria right.
I suggest building this step by step adding criteria one at a time and looking at the result in the data sheet view to see if you are on track. Note you might have to add some extra temporary fields to see something.
After much thought to both ways... I will add a field to the Tenant table that will indicate the tenant is no longer active, so he has an EndDate in the other table...
But I have made a copy of your advice Sneuberg - may work very well for other things I had in mind... TY
After much thought to both ways... I will add a field to the Tenant table that will indicate the tenant is no longer active, so he has an EndDate in the other table..
If you are going to put a field in tblTenant like for example a Yes/No field named Active that the user will have to check/uncheck after looking in the tblTenancy EndDate, then I would say this is a very bad idea. Why require the user to determine this manually when you can automate it. It's value isn't even difficult to calculate being in a form something like:
Code:
DCount("*", "tblTenancy", "tblTenancy.EndDate Is Null AND tenantID = " & Me.tenantID) > 0
If you want this more complicated structure then you will need to create the queries need to support it. In my opinion I don't think the addition of the tenancy table was a good idea. I was watching the thread when MarkK suggested it and was going to chime in but you said
WOW - love it! I did have a system in place for keeping all the info for the Unit - like who lived there last year, last month... I had it working with MoveInDate and MoveOutDate. Especially that many of them move to another Unit with more bedrooms vs moving out completely of the COOP. ....
If you only liked it I might have said something but since you loved it I let it slide. But now I think I will say my piece.
I think this structure sucks. What tenancy table does is allow the silly conditions like an tenant being a occupant in two or more units at the same time. Normally this could be avoided through a composite index but since this involves the start and end dates you will need complicated code you check for this.
When you didn't have this tenancy table you could still move a tenant from one unit to another. You could keep them in the database after they moved out just by clearing the move in and out dates. The only thing you didn't have was a history of moves. I believe the better way to have that is to have a table related just to the unit with that information. Call it tblUnitHistory for example. This would be populated by code when the events occurred that you want to record. For example you could have a button on a form named Moved Out when pressed would present a pop up where you could select a unit, select tenants from that unit, specify the condition of the unit, etc and code would update the tblTenant records and add whatever information you wanted in the history table. You could do something similar for moves within the complex.
If you keep this tenancy table you need to include it in nearly every query/report you create for this system. While that wouldn't bother me if it were my project it seems that it has already caused problems for you. I suggest dumping the tenancy table.
If you need help writing any code for this or for that matter having code written I'm willing to help. Send me a PM when you create a new thread.
It is so strange that you said this now. I have been redoing all the reports for the last 2 days - and, yes, have had to add the Tenancy table everywhere and find myself always confused as to why it has to be there... I just figured Mark is right and I am learning to do it the "right" way. But it is cumbersome, a lot - I will say that.
What I previously had were simply two continuous forms - opened by the main menu with command buttons - one with current tenants, the other with previous tenants. I used the MoveIn, MoveOut dates... to query the records.
In that continuous form, you press a command button to open a form that contains ALL of one tenant AND unit data. The tenant's name, phone numbers and so on were in a continous subform. As soon as the user would enter a moveout date to that tenant, the entire line would gray out.
Also the sort puts the current tenants always at the top of the dropdown list, with Members first and then Occupants.
What is extremely difficult right now is that I am spending more time learning than doing! I am doing this for free so my bonus in doing this was the learning experience and the gift of giving. So, I love it but I get very discouraged as it feels the database is getting nowhere...
I am very glad to stepped in to say your "piece" as you put it. I do need all the help I can get. As an experienced programmer maybe you can tell me when one stops re-visiting and restructuring the foundation of the database?
I want to provide my brother-in-law with the best I can do but when you don't know what that is and you are presented with different views by experts, it is very difficult and confusing.
Trying to apply the change to the module - created a field within the same table.
Code:
Public Function fnUpdateMailListField()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "Update tblTenants Set [MailList] = True Where [MailList] = False And " & _
"Val(fnAge([DOB]) & '') > 16 AND [Deactivated] = N"
Set db = Nothing
End Function
You had at the end: AND EndDate is Null"
This one is a bit different, need to = N (for No).
Can you help? It returns an error. Too few paramaters. Expected 1.
If the field is Yes/No, you want
"Val(fnAge([DOB]) & '') > 16 AND [Deactivated] = False"
If Text
"Val(fnAge([DOB]) & '') > 16 AND [Deactivated] = 'N'"
If you only liked it - I honestly thought it "looked" so professional and logical - had never built this way before and was VERY appreciative someone taking the time to explain and offer it this way- I had no idea what I was getting into - still don't but willing to learn - I might have said something but since you loved it I let it slide. But now I think I will say my piece.
I think this structure sucks. I would not use that strong a word but I am having issues but I attribute that to my lack of understanding and experience What tenancy table does is allow the silly conditions like an tenant being a occupant in two or more units at the same time. Normally this could be avoided through a composite index would not know how to do that but can research itbut since this involves the start and end dates you will need complicated code you check for this.
When you didn't have this tenancy table you could still move a tenant from one unit to another. I did not want to "move him" - the plan was when a tenant moves within the COOP, the record is treated the same way as if he had moved elsewhere - there is a movein and a moveout date - a new record is added to the unit where he has now moved... and so is a new movein date. This is logical as all the paperwork is also redone: a new lease, a new rent fee, etc. This was not a problem for me. You could keep them in the database after they moved out just by clearing the move in and out dates. The only thing you didn't have was a history of moves. Actually, I would - for each unit, you would clearly see in a report MoveinDate with tenants and MoveOutDate and so on
I believe the better way to have that is to have a table related just to the unit with that information. Call it tblUnitHistory for example. I honestly don't see the point
This would be populated by code when the events occurred that you want to record. For example you could have a button on a form named Moved Out when pressed would present a pop up where you could select a unit, select tenants from that unit, specify the condition of the unit, etc and code would update the tblTenant records and add whatever information you wanted in the history table. You could do something similar for moves within the complex.
If you keep this tenancy table you need to include it in nearly every query/report you create for this system. While that wouldn't bother me if it were my project it seems that it has already caused problems for you. I suggest dumping the tenancy table.
If you need help writing any code for this or for that matter having code written I'm willing to help. God Bless! I know I am right now!Send me a PM when you create a new thread.
For what it's worth I have a bachelors in computer science but my actually experience with databases is limited to about a dozen or so small system. Obviously that doesn't stop me from sharing my opinion. I figure it's probably not much worse than the opinion of the other uncredentialed experts who contributed to this site.
maybe you can tell me when one stops re-visiting and restructuring the foundation of the database?
Maybe when you pass the point of no return. To quote Bill Foster (Micheal Douglas) in the movie Falling Down
Do you know what that is, Beth? That's the point in a journey where it's longer to go back to the beginning. It's like when those astronauts got in trouble. I don't know, somebody messed up, and they had to get them back to Earth. But they had passed the point of no return
Without know what the future hold for the database, I think it's difficult to know where that point is in database development. In anycase you are certainly the one best informed to make that call
PBaldy, thank you but this is not a "yes/no" field. It is a TEXT field where the user is limited to a "Y" or an "N" (the default value).
FYI (just a note): there are four fields - deactivate, deactivate date, deactivate reason(s) and deactivate by... I want this treated clearly, with least possible error on the part of the user. A yes/no field is too volatile and may be clicked by mistake more easily... Furthermore, when the user enters a "Y", he is prompted to fill out the other fields (date, reasons and initials).
Trying to apply the change to the module - created a field within the same table.
Code:
Public Function fnUpdateMailListField()
Dim db As DAO.Database
Set db = CurrentDb
db.Execute "Update tblTenants Set [MailList] = True Where [MailList] = False And " & _
"Val(fnAge([DOB]) & '') > 16 AND [Deactivated] = N"
Set db = Nothing
End Function
You had at the end: AND EndDate is Null"
This one is a bit different, need to = N (for No).
Can you help? It returns an error. Too few paramaters. Expected 1.
If the field is Yes/No, you want
"Val(fnAge([DOB]) & '') > 16 AND [Deactivated] = False"
If Text
"Val(fnAge([DOB]) & '') > 16 AND [Deactivated] = 'N'"
I believe the better way to have that is to have a table related just to the unit with that information. Call it tblUnitHistory for example. I honestly don't see the point
This would have been an alternative to the tenancy table and would have had the same information but not have been related to tenants table. I guess you are sticky with the tenancy table?
No not sticky with the Tenancy table although hours were put in to redo all the queries, reports and adjust the data... I don't see the need for a History table because - WITHOUT the Tenancy table I can still pull the information using what I previously had, the MoveIn and MoveOut dates within the Tenant table. That would be going back to what I had before... which I now forget what problems or challenges it was causing, lol ! But the unit history was not a problem.
I remember now... it was producing labels! 1 label per unit with ALL of the tenants' names on it.
