Append Query Using a Form?

[wraith972]

I am trying to start out with some high level thinking before I get into all the details and would like to get ideas back from good people far more versed with Access than I.

A processing group receives queries from a production database everyday in Excel workbooks. It is essentially their workflow. What they need is a database that stores this information a little better than all of these workbooks so I know Access will do the trick very well. I completely understand how to import the workbooks which I have already completed.

My game plan is laid out as this and is what I was hoping for some feedback on…

• They import the workbooks into Access
• Have a form that pulls the information they need to work
• Take the record and all its information along with who worked it, the day they worked it, if it’s completed or not and some notes via a form and an append query.
o I created another table already that has all the same fields from the query they get daily and then those four new fields.
• Once the record has been appended, delete if from the “temporary” table they imported.

Now, does that sound feasible and the way to go or should I approach it differently? I have been a little stumped trying to create the form that has fields bound to the temporary table and then other fields that are not bound but need to be added to the “completed” table.

Any thoughts are greatly appreciated, as always.

 

[apr pillai]

I think it is feasible. First, for the - who did it part - you need to implement Microsoft Access Security to catch the User Name to record. You need two more fields on the main table to record the editing events. 1) to record the User Name who edited the record, 2) a Date field to record the Date and Time of completion of editing on each record. These fields also must be placed on the Data Editing form and the values can be recorded on the Before_Update() EventProcedure.

Private Sub Form_BeforeUpdate(Cancel As Integer)
  Me![EditedBy] = CurrentUser
  Me![Editedon] = Now()
End Sub

To use the Append Query part and to select the correct records since last editing session, we need to keep a Parameter Table with last edited Date & Time (Maximum Value) updated every time to aide the next session.

Records with Edited Date/Time Values greater than the Parameter Value can be picked using the Append Query to add to the other Table and the same way we can delete those records from the main table as well.