Autofill next record in input form.

[TenOc]

Access 2013 Win 7 -64



I have a table [salesmen] with 100 records. Every quarter they are given a new sales goal. I keep a record of the (old and new) sales goals in a table [sales goal]. The sales goal are so different that I cannot use an append query to enter the new goal – I need to enter them manually. I have a form [quarter sales goal] that I use to enter the new goals one salesman at a time. On this form is a combo box that I use to select the next salesman.


Task:

(1) I would like to replace the [quarter sales goal] form which now only updates one salesman at a time, with a data sheet (?) view that allow me to input all the salesman new goals and then once I (and my boss) has approved all the new goals, press a button and update the [sales goal] table all at one time. I have worked with creating a temporary table and then once the goals are approve use an append query to update the real [sales goal] table -- but I do not like this approach. There must be a better way. . . . .


(2) I would like to replace the salesman combo box and have the new data sheet view of [quarter sales goal] auto fill in each of the 100 salesman name – either all at once (when I open the form) or after I complete one salesman goal the form would auto fill the next salesman into the next record so I do not need the use the combo box to look up the next salesman.


I worked with a "do" command without any luck. . . .



Any help or suggestion are welcome. Thanks.

[Rawb]

Without knowing the layout of your Tables, I can't really be specific, but there are two primary methods that come to mind:

1. Create a temporary Table with all the data and, as the Records are approved, move them to the "live" Table.
2. Include both the approved and non-approved Records in your "live" Table. This is usually done by adding a Yes/No Column called something like "Approved".

It sounds like you've already tried the first method and didn't like it, so I'd suggest giving the second method a try.

As for directly populating your Tables with the "dummy" Records, with a normalized Table layout, you should be able to just create an Append Query to do it for you.

INSERT INTO QuarterlySalesGoals (UserID, QuarterStartDate, Approved, SalesGoal)
SELECT Users.UserID, Date(), False, 0 
FROM Users

If you're using a non-normalized Table layout, then you may have a lot of work to do no matter which route you choose.