Append Query Using a Form?

wraith972

Board Regular
Joined
Mar 6, 2007
Messages
104
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

apr pillai

Board Regular
Joined
Aug 12, 2008
Messages
236
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.

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,087
Members
414,501
Latest member
mdhaumyu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top