Multiple Records From One Form

[ccjjscb]

I am trying to create a form that will add mutiple records to a table. What I need is a form to fill in data in this manner.

Hour A-Temp B-Temp A-Press B-Press

2400 27 89 46 67
0200
0400
0600
etc...
I need the form to look like this with the hours prefilled in. The user would fill in the other columns and each hour would then create a new record. Is this possible and if so what type of form would I use?
Thanks for any help/suggestions you can give
Chris

 

[dhookom]

I would base the form on a temporary, non-normalized table. This might be populated from a crosstab query based append query. The user could then edit the values. After editing, some method of updating the normalized tables would be executed. This might be code with recordsets or update queries or whatever.

Duane
Hook'D on Access
MS Access MVP

 

[ccjjscb]

Thanks for the input, but I am a newer user of Access with limited code skills. Would someone have some type of example they might add?
Again thanks for any help.
Chris

 

[MajP]

One question. Is this done on a daily basis? So when the user enters the form does he choose a day or enter a new day? Is there a seperate day field, or is the Hour field actually a date time field formatted to only show the time value?

 

[ccjjscb]

The form will be for a days use,(24 Hours). Yes, there will be a date, that the user will choose, that will be used for all the 2 hours records, on that one form. Hope this helps.
Thanks
Chris

 

[MajP]

http://www.4shared.com/file/203112489/21c23ae5/PrefilledForm.html

Here is a working demo. This does not take a lot of code, but there are several moving parts which makes explaining this very hard but easy to build.

Type in a date and hit go. There is dummy data for 1/21 - 1/24. If there is data it will go to that day. If there is not data it will add new records (for some reason this does not happen automatically and you have to hit the go button twice and then the new records show).

Look at the table, the queries in detail then I can explain how this works.

 

[ccjjscb]

Thanks very much for the help, it looks like this should do the trick. I looked it over it appears to be just what I was after.
Thanks
Chris

 

[MajP]

Basically here is how it works.
1)The form is based on a query that filters the records to the date in the textbox. The query uses the textbox value as the criteria
2)The go button allows you to navigate to any date in the textbox, by simply requerying the form
3)If you enter a date and hit go, the form requeries, and if there are not any values for that date it runs and Insert query to add a record for each Hour value contained in my tblHours. The insert query takes the date in the textbox and combines it with the time value in tblHours to make a combined date time for each record.

So if the value of 1/21/10 is in the textbox, and my tblHours has a value of 02:00 AM it combines it to make 1/21/10 02:00 AM. It does that for each interval.

 

[ccjjscb]

Nice form, I checked out the code and the structure of the form and query and see somewhat how it works. I think I can change it around to do what I need it to do. Is there a way to make, the form, so that when it reaches the last hour that is on the list it won't try to make a new record? In other words when I tab down past the last entry I can make in the 2200 record, it jumps down to the next row and trys to create a new record, when I want it to stop at the last 2200 entry.
Thanks
Chris

 

[MajP]

Forgot to do that. On the form set the properties
AllowAdditions: False
AllowDeletions: Flase.

 

[ccjjscb]

Thank You very much this was and is a great help. I can opten see what I want to do in Access it's just figuring out how to do it sometimes that is difficult. Thanks again for the great help.
Chris