Populating subforms each year automatically

[Shiftlock]

Hello Access forum users,

I'm new here and I have come to you with a technical obstacle i'm facing in the hope you guys will be able to assist me.

first off let met start by saying hi and saying that access isn't my usual work environment. I've been asked to expand on a currently existing system used for real-estate management. up untill now no issues but have now come across something that i have no clue on how to tackle.

here goes.

In the system i have the following relevant tables:

Customers
used to keep track of the owners of appartments
Buildings
used to keep track of general information where the appartments are housed in
appartments
general info specific to each appartment
rent
info partaining to the current occupant (that rents) of the appartment with general rent related info
management
info used by managers at the real-estate office to keep track of yearly information

just to give you a general view on how the current flow is. a building contains multiple appartments. a customer can have one or more appartments that (s)he is the owner of. on each of these appartments multiple records in the rent table can exist each represting a rent period by a certain individual (these are just stored in the rent table) the start and end date of the rental period are also filled here. also on each appartment assigned to a customer we have the management which offers information that can span multiple rent records.

the way this is accessed is as followed: there is a main form with customer info. in that i have a subform showing each appartment for the customer. on that subform there are 2 buttons.
1 leads to rents which opens a new form that shows the rent specific details so multiple records can exist per appartment. another button will lead to the management form and here comes my problem.

on the buildings table there is a date that represents the start when that appartment can be rented out. this is a yearly repeating element fixed on a 12 month interval. so it's like a calendar year just with a differnt start date. (if this "rentyear" starts for example on november 12th 2009 then it will end on november 11th 2010.

now for the part where i'm clueless:
on the management form which is specific for one appartment i would need a subform or grid that shows me each rentyear and the ability to add some additional info here. also I would need to let the lines be created automatically and keep track if multiple rentees are involved so that multiple lines can be written.

example:
building A has a rentyear startdate of 10/06/08

in case there would be one rentee up untill now i need 3 lines to be shown:

1 - 01/06/08 - 31/05/09 - rentee - "random info 1" - "random info 2" ...
2 - 01/06/09 - 31/05/10 - rentee - "random info 1" - "random info 2" ...
3 - 01/06/10 - 31/05/11 - rentee - "random info 1" - "random info 2" ...

(3 lines since we passed 01/06)

this can become even trickier if muliple rentees are involved. let's say the appartment was rented out to 2 ppl in this timespan. the first from 10/06/08 untill 12/12/09 and then the next one.

in this case i would need to see:

1 - 01/06/08 - 31/05/09 - rentee1 - "random info 1" - "random info 2" ...
2 - 01/06/09 - 31/05/10 - rentee1 - "random info 1" - "random info 2" ...
2 - 01/06/09 - 31/05/10 - rentee2 - "random info 1" - "random info 2" ...
3 - 01/06/10 - 31/05/11 - rentee2 - "random info 1" - "random info 2" ...

so this is the issue. I know it's quite a bit of info but i'm truely hoping you guys can help or at least point me in the correct direction.

Greetz

 

[jzwp22]

Welcome to AWF!

Before we get to your main issue, we need to understand how the tables you mentioned are related to one another. We will also need some detail on what is in each table. Could you provide the pertinent field names in each table?

I would guess that if an apartment can have many renters over time, you need a table like this:

tblAptRenters
-pkAptRenterID primary key, autonumber
-fkAptID foreign key to your apartment table
-fkRenterID foreign key to your renter table?
-dteRentalStart (start date when this person rents this apt.)

 

[Shiftlock]

thanks for the welcome jzwp22.

just to give you a quick sketch we got customers those are linked to a sale (customerid in sales table) a sale relates to an appartment (appid in sales) which is linked to a building (buildingid in apps).
A "rent" can only happen on a sold appartment (salesid in rents table). in the rent table we store the info of the rentee directly since this is only unique per appartment and no extra info is extrapolated from this. last but not least management is linked to a sale aswell.

with that said i'm proud to announce that i have solved this issue with some code. the only thing is that a button has to be pushed on the management form in order to refresh the info.

this is the code for those interested (you'll have to get passed the dutch table names though)




Option Compare Database
Option Explicit

Private Sub Command83_Click()
'On Error Resume Next

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim RSBeheer As DAO.Recordset
Dim DateStart As Date
Dim DateEnd As Date
Dim appid As Long
Dim Huurder As String
Dim AfrekeningVan As Date
Dim AfrekeningTot As Date
Dim Sql As String
Dim verkid As Long

appid = Me.appid
verkid = Me.verkid


Set DB = CurrentDb

'instellen van initiële start en stop van eerste boekjaar van het gebouw
DateStart = Me.gebEerstBoekjaar.Value

'herhaallus instellen voor elk jaar tussen start van gebouw en nu
Do While DateStart < Now 'Or (Year(DateEnd) = Year(Now))
'----- Bereken einddatum
DateEnd = DateAdd("d", -1, DateAdd("yyyy", 1, DateStart))

'----- lees verhuurlijnen voor huidige boekperiode
Sql = "SELECT verhuurbegindatum, verhuureinde, verhuurHuurderNaam" _
& " FROM verhuringen" _
& " WHERE verhuurverkid= " & verkid _
& " AND (Format$(verhuureinde,""yyyymmdd"") >= """ & Format$(DateStart, "yyyymmdd") & """ OR verhuureinde is Null)" _
& " AND Format$(verhuurbegindatum,""yyyymmdd"") <= """ & Format$(DateEnd, "yyyymmdd") & """"
Set RS = DB.OpenRecordset(Sql)
If RS.EOF = False Then RS.MoveFirst
If RS.EOF = True Then
'---- Creeer lege beheerlijn
CreateBeheerlijn verkid, DateStart, DateEnd
Else
While RS.EOF = False
UpdateBeheerlijn verkid, DateStart, DateEnd, RS ' RS("verhuurbegindatum"), RS("verhuureinde"), RS("verhuurHuurderNaam")
RS.MoveNext
Wend
End If

'----- Bereken volgende startdatum
DateStart = DateAdd("yyyy", 1, DateStart)
Loop


Me.Beheerlijnen.Requery

End Sub

Private Sub CreateBeheerlijn(verkid As Long, DateStart As Date, DateEnd As Date)

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim Sql As String

Set DB = CurrentDb

Sql = "Select * from Beheer" _
& " where verkid=" & verkid _
& " And format$(StartBoekjaar,""yyyymmdd"") = """ & Format$(DateStart, "yyyymmdd") & """" _
& " And format$(EindBoekjaar,""yyyymmdd"") = """ & Format$(DateEnd, "yyyymmdd") & """" _
& " And afrekeningVan is Null" _
& " And afrekeningTot is Null"
Set RS = DB.OpenRecordset(Sql)
If RS.EOF = False Then RS.MoveFirst
If RS.EOF = True Then
' beheerlijn bestaat niet
RS.AddNew
RS("verkid") = verkid
RS("StartBoekjaar") = DateStart
RS("EindBoekjaar") = DateEnd
RS.Update
End If

End Sub

Private Sub UpdateBeheerlijn(verkid As Long, DateStart As Date, DateEnd As Date _
, ByRef RSx As DAO.Recordset) ' _
, verhuurbegindatum As Date _
, verhuureinde As Date _
, verhuurHuurderNaam As String)

Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim Sql As String

Set DB = CurrentDb

'Bestaat beheerlijn voor verhuring ?
Sql = "Select * from Beheer" _
& " where verkId=" & verkid _
& " And format$(StartBoekjaar,""yyyymmdd"") = """ & Format$(DateStart, "yyyymmdd") & """" _
& " And format$(EindBoekjaar,""yyyymmdd"") = """ & Format$(DateEnd, "yyyymmdd") & """" _
& " And format$(AfrekeningVan,""yyyymmdd"") = """ & Format$(IIf(RSx("verhuurbegindatum") < DateStart, DateStart, RSx("verhuurbegindatum")), "yyyymmdd") & """"
Set RS = DB.OpenRecordset(Sql)
If RS.EOF = False Then RS.MoveFirst
If RS.EOF = False Then
'Ja : update indien verhuureinde not null
If Not IsNull(RSx("verhuureinde")) Then ' ??????????????
' --- update AfrekeningTot
RS.Edit
RS("AfrekeningTot") = RSx("verhuureinde")
RS.Update
End If
Else
'geen beheerlijn gevonden
'Zoek lege beheerlijn
Sql = "Select * from Beheer" _
& " where verkId=" & verkid _
& " And format$(StartBoekjaar,""yyyymmdd"") = """ & Format$(DateStart, "yyyymmdd") & """" _
& " And format$(EindBoekjaar,""yyyymmdd"") = """ & Format$(DateEnd, "yyyymmdd") & """" _
& " And AfrekeningVan is null"
Set RS = DB.OpenRecordset(Sql)
If RS.EOF = False Then RS.MoveFirst
If RS.EOF = False Then
'gevonden : update
RS.Edit
' RS("AfrekeningVan") = verhuurbegindatum
' RS("AfrekeningTot") = verhuureinde
' RS("Huurder") = verhuurHuurderNaam
RS("AfrekeningVan") = IIf(RSx("verhuurbegindatum") < DateStart, DateStart, RSx("verhuurbegindatum"))
RS("AfrekeningTot") = IIf(RSx("verhuureinde") > DateStart And RSx("verhuureinde") < DateEnd, RSx("verhuureinde"), DateEnd)
RS("Huurder") = RSx("verhuurHuurderNaam")
RS.Update
Else
'niet gevonden : creatie beheerlijn
RS.AddNew
RS("verkid") = verkid
RS("StartBoekjaar") = DateStart
RS("EindBoekjaar") = DateEnd
' RS("AfrekeningVan") = verhuurbegindatum
' RS("AfrekeningTot") = verhuureinde
' RS("Huurder") = verhuurHuurderNaam
RS("AfrekeningVan") = IIf(RSx("verhuurbegindatum") < DateStart, DateStart, RSx("verhuurbegindatum"))
RS("AfrekeningTot") = IIf(RSx("verhuureinde") > DateStart And RSx("verhuureinde") < DateEnd, RSx("verhuureinde"), DateEnd)
RS("Huurder") = RSx("verhuurHuurderNaam")
RS.Update
End If
End If

End Sub