Add Records To Related Table On Continuous Form

[triplee23]

Hi,



I have an issue with my database which I have not been able to find a solution to. I therefore post this question here. I have simplified the issue somewhat. Here is my situation:

I administer the record keeping of the water usage (for each apartment) in my apartment building I am creating a database to keep track of this. The issue I hope to get help with is the data entry of the water usage.

To simplify I have two tables:

tblApartment
ApartmentID
ApartmentName

tblWaterReadings
WaterReadingID
ApartmentID
WaterUsage

The tables are related through ApartmentID.

I wish, through a continuous form list all the individual apartments in tblApartment. The be able to add a new record in tblWaterReadings for each row in the continuous form. So as an example, if I have 5 apartments, the continuous form would have 5 rows and for each row an emtpy text box representing five new records in the tblWaterREadings table. If I enter a value in the text box, a new record is created in the tblWaterReadings table with the correct ApartmentID relation.

I practical terms this is much like an excel sheet. The rows representing the individual apartments and a new column for every new water reading.

I know this can be solved using a simple form for each apartment and a subform for the apartments related waters reading, I was just hoping to do the data entry using ONE form and not having to walk through all the single forms for each apartment.

Hope the above was understandable. Thank in advance for possible solutions.

This my not even be possible?

[ssanfu]

This is how we handle data entry.


A button opens the data entry form, using, in part, this line:

DoCmd.OpenForm "enter hours", , , "[hbh_hours] = -1111"


In the form open event, we have code that creates a recordset of active employee by company and division. The code loops thru the recordset, entering a new record in the hours table for each employee that is active, entering a -1111 (minus quad ones) in the hours field.

When the form loads and displays, it is filtered to only display records with the hours field = -1111. The form record source is a query with the employee table, the hours table and a few other tables.

The actual hours are entered for each employee, various other fields have data entered, calculations are performed and the record is saved.

When the close button is clicked, we run a delete query to delete any records that has a -1111 in the hours field.
The SQL is: "DELETE FROM hour_bank_hour WHERE [hbh_hours] = -1111"


I'm not real happy about adding/deleting so many records each time the form is opened due to bloat, but it ensures no employees are missed.

I threw together an example MDB in A2003 format. Hopefully this will give you some ideas to get moving forward...
Tried to attach it, but the upload function is not working right now. I will upload the example.mdb later