Add multiple records from a Continuous Form

[gixerp]

Hi, Although I have built a couple of extremely basic Databases for work, I am still, very much a beginner.

I have looked for ages for a solution to the problem I am currently stuck on, with no real success.

A small part of my DB uses 2 tables, 1 Query, a single form, and a Continuous form.
Table 'Assets' has the Fields - Asset, Code, Site (it has other fields but are not relevant for this problem).
This table holds all the information relating to each Asset at each of our sites.

Table 'Hours' has the Fields - Asset, Code, Site, Hours
This table needs to be added to each week so we have a weekly history of all the hours each assets has done.

I have a single form where the User selects a site and enters the date (once a week) when the hours were recorded.
The Site and the date are held as a TempVariables.

Once the site is selected the Continuous Form opens, listing all the assets on that particular site.
The form gets the data from the Query 'SiteAssets'. This Query has the 3 Fields from the Asset Table, and the Hours Field from the Hours Table.

The User sees a list of all the assets at their particular site. Next to the Asset is an empty field (Hours) where the User enters each Assets machine hours.
Once completed I need them to select a 'Save' or 'Cancel' Button.

This is far as I have managed to get with no problem. . . what I cant figure out is. . . how to save all the new records to the 'Hours' Table once the Save button has been selected.?



I hope I have explained it clearly enough. In my head it should be a fairly simple thing to do. . . but I just cant seem to work it out

I have mainly used Macros up till now and my knowledge of VBA is extremely limited. . . a few very basic bits but that's about it, unfortunately I don't seem to be able to learn it quick enough just yet

Thanks in advance for any help

Pete

[ranman256]

If you are editing directly on the table, then no save button is needed, since save is automatic.
if you edit from an unbound form, then the save btn would execute aa append query on the main data table.

this can be done thru macros.

[gixerp]

Thanks for the reply ranman. . . its best our users don't have any direct access to any tables

I have never used append queries before. . . and after having a quick read up, it looks like it could be the way to go. . .
I will investigate further and give it a try.

Thanks for you help

[Missinglinq]

You cannot have a Continuous View, Unbound Form! An Unbound Form actually has no Records, only Controls to enter data. With a Bound Form, as with a Table, as ranman256 mentioned, leaving a given Record automatically saves that Record.

Using Unbound Forms really does away with the basic function of Access, which is to facilitate RAD (Rapid Application Development) and should only be attempted by very experienced Access developers, and then only when/if a legitimate purpose requires it, and most situations don’t!

Several developers I know, experienced in Visual Basic database development and Access development, estimate that development, using Unbound Forms, by highly experienced developers, takes two to three times as long, using Unbound Forms, as it does when using Access and Bound Forms. That’s because with Bound Forms the Access Gnomes do the vast majority of the heavy lifting; with Unbound Forms the developer has to write code for everything...even the most mundane tasks!

The only workaround, for this kind of thing, would be using a Form that is Bound to a temporary Table, and then appending the Records to the permanent Table...and after twenty plus years of developing Access databases, that involves more programming than I'd care to do, for this reason!

If you insist on using Unbound Forms, you'd be far better off using a straight VB or C++ front end with a SQL Server or Oracle back end.

• You can create an EXE file which gives total protection to your code/design
  
• You can distribute the db to PCs without a copy of Access being on board
  
• Your data security is far, far better than anything you can do in Access

Linq ;0)>

[gixerp]

Thanks Linq . . . All that seems far too advanced for me, to be honest I just want to keep it as simple as possible.

This DB doesn't need to be that in-depth or sophisticated. It is purely to just keep a weekly record of machine hours so we can see how many hours a machine has done during various timescales and at what site they were at (assets change sites from time to time).

It would be simple enough to do for me if I got the user (there will only be 2 or 3 users) to do 1 asset at a time i.e. get first asset from 'Assets' table, enter hours, create new record in 'Hours' Table and save as new record once the Saved button has been pressed. et next record from 'Assets' Table etc etc. . .
But the Users will soon get fed up doing one at a time. . . They want a list where they can do it all in one go.

I don't want to update current records in the 'Hours' Table. . . I need to create New ones every week.

I haven't had time to try anything regarding append queries just yet. . . (I'm only at a computer for about an hour a day).

Thanks