appending data to a queries....

[sjl]

I have a database with a main Form which houses 3 tabs (or pages); each tab has 2 subforms (for a total of 6 subforms).

There are updates (dates, names, etc) which need to be added over time. The boss here wants the 10+ study managers to key any updates into a spreadsheet or Access form. After these data points are reviewed she wants this data uploaded to the data base objects (i.e. the queries/tables).

The only way I can think to do it in Access is:

• make a data-entry Form (in Access), based on a copy of the current query (subform)
• once data is keyed into the above Form, (and reviewed), run an Apend query to apend the data to the original query(subform).

Am I on the right track? Any pitfalls or pointers I should be aware of.

Thanks
sjl

 

[dkinley]

Throwing this out there for consideration ...

Why not bypass the whole append process? Let's say you use a form to key in the data. Include an extra field in the table for it's approval status. On the form you could have a protected control (based on user priviledges) that can change this field to 'approved' or 'denied'.

In this manner, you can do queries on all the records that are either approved, denied or yet to be reviewed (blank). The ability to parse these based on the criteria of one field eliminates the need for an append query or any overhead needed with that sort of implementation.

-dK

 

[sjl]

thanks dk.

I have a passworded data base. In order for them to key directly into the database (via a form), they would have to have full data permissions--which I don't know if the powers that be will allow.

This is my scenario: In one subform I have 8 variables. If 5 fields are already filled in, and three are not, the record needs to show in the live database...but, if the whole record is denied (b/c not yet full) then the old data would not show either, right? Would there be a way around this?

My other quandry (for either of the above scenario, or for doing an append query) is how can I get a form to be able to write to a passworded database--unless one is already in the database (and have at least update date user permissions). When I set up a test Form and an Append Query...it would not allow the appending back to the main database. Does anyone know how to give an outside database/query the rights to append to a pass-worded database?

thanks
sjl

 

[dkinley]

I have a passworded data base. In order for them to key directly into the database (via a form), they would have to have full data permissions--which I don't know if the powers that be will allow./quote]

Since the type of security that is implemented is unknown to me, then I would see if the have different levels of security in the application to perhaps to allow a single form view/entry and if they would allow that.

This is my scenario: In one subform I have 8 variables. If 5 fields are already filled in, and three are not, the record needs to show in the live database...but, if the whole record is denied (b/c not yet full) then the old data would not show either, right? Would there be a way around this?

You use the phrase 'variables'. If you mean fields, then these can be checked via a query (is not null). If you develop the query then it can show or not show depending on the result. In this instance, the incompletes and completes are still in the same table akin to using a status type field I described in the first post.

My other quandry (for either of the above scenario, or for doing an append query) is how can I get a form to be able to write to a passworded database--unless one is already in the database (and have at least update date user permissions). When I set up a test Form and an Append Query...it would not allow the appending back to the main database. Does anyone know how to give an outside database/query the rights to append to a pass-worded database?

To this extent I do not know how to connect and/or append to a pass-worded database.

Perhaps someone else can jump in and help you in this.

-dK