App for residents

[nyamongo]

I am trying to set up an app for resident accounting management system. The system is basically to capture the data of all residents in an estate and invoice them automatically every end of the month through email and/or sms. I have created necessary tables and few query which can be modified.

[mike60smart]

Hi
Can you upload your database in zip

[pbaldy]

Is there a question?

[June7]

Reinventing the wheel? Why not use OTS accounting software?

Your requirement is not unusual and has been discussed in numerous forums and threads.

1. build query

2. build report

3. send email with embedded data or report as attachment

Do research, attempt code, and when you have specific issue post question.

[nyamongo]

Hi Mike Smart,

Please use the link below. I was unable to download the zip file here because the zip file was more than recommended size.

https://drive.google.com/file/d/1cLZ...ew?usp=sharing


Alternative would have been to email

Thanks

George

[nyamongo]

Hi June 7,

Thanks for advice. I have checked some of the accounting software but were not to my expectation. I wanted a tailor made to meet my expectation. For example, each resident will have a unique ID assigned to them. Mr Brown William staying in house number 47 will be assigned an Resident ID No. R1032 in the house number MH047 known to be a main house situated at zone 2. Resident ID R1032 might be the owner or a tenant who might move to a different house in the same estate or move out at a later date. This Resident ID R1032 will be tracked in terms of accountability. A new tenant. Mr Shaka Zulu, moving in the same house MH047 will be assigned another number R1172 assuming the previous or last number is R1171. Resident ID will be automatically generated, the same way as the Invoice number, Purchase Order number, etc. The invoice will be automatically be generated to each resident monthly and be send as hard copy, email as pdf file or sms the the month charges and the balances where necessary


Thanks

[mike60smart]

Hi Mike Smart,

Please use the link below. I was unable to download the zip file here because the zip file was more than recommended size.

https://drive.google.com/file/d/1cLZ...ew?usp=sharing


Alternative would have been to email

Thanks

George

Hi George

Unable to access the file via the Link

You can email me

Regards

Mike

[nyamongo]

I have resent and also allowed sharing. Thanks in advance

[mike60smart]

Hi George

You have a few problems.

You should not be using the ResidentID in tblResidentDetails as field which is visible to the User. You have it formatted as R1032

This should be a field on its own in the table possibly named ResidentNr

You are using Lookup fields in your tables which is a NO NO in Access.

[nyamongo]

Hi Mike,

Thanks for advice. I will immediately change that. The ResidentNr field will visible but locked so that no one can do edit on it. It will be generated automatically

[mike60smart]

George
What is the purpose of the ResidentNr?

[nyamongo]

Each and every resident will have this unique number ResidentNr assigned to them. Let say it will be like Social Security Number or passport number.

No.	House No.	ResidentNr	House Type	Zone	Names	MobilePhone	EmailAddress
2	MH002	R1002	Main House	Zone 1	Godfrey Wamalwa
4	MH004	R1004	Main House	Zone 1	Nancy Odero
8	MH008	R1008	Main House	Zone 1	Lydia Kabole
11	MH011	R1011	Main House	Zone 1	Bibian Kasau
14	MH014	R1014	Main House	Zone 1	Caronelius Kinywa
16	MH016	R1016	Main House	Zone 1	Margaret Kerubo
19	MH019	R1019	Main House	Zone 1	George
20	MH020	R1020	Main House	Zone 1	Kiambo Gaitho
21	MH021	R1021	Main House	Zone 1	Irene
210	B012F01	R1210	Block 12	Zone 1	Benedict Mutuku
214	B012F05	R1214	Block 12	Zone 1	Judy
215	B012F06	R1215	Block 12	Zone 1	Alphonse Maghanga
218	B012F09	R1218	Block 12	Zone 1	Eunice Akoth
220	B012F11	R1220	Block 12	Zone 1	Michael Owino
222	B012F13	R1222	Block 12	Zone 1	Joseph Wanyoike
224	B012F15	R1224	Block 12	Zone 1	Norah Wambua
59	MH059	R1059	Main House	Zone 3	Fresha Njeri
60	MH060	R1060	Main House	Zone 3	James Waruingi
66	MH066	R1066	Main House	Zone 4	Bishop Festus
69	MH069	R1069	Main House	Zone 4	Agnes Maina
71	MH071	R1071	Main House	Zone 4	Christopher Mutinda
72	MH072	R1072	Main House	Zone 4	Jimmy Otana
74	MH074	R1074	Main House	Zone 4	Felista John
75	MH075	R1075	Main House	Zone 4	Rev. Ruth Maina

[mike60smart]

Yes that can be done using the following :-

In the Before Update of the Form

Me.ResidentNr = Nz(DMax("ResidentNr","tblResidentDetails"),0) + 1

[nyamongo]

Hi Mike,

I will validate that in the form and keep you updated. Point out more of my weakness so that I improve.

Thanks and be blessed