Subtract number range while keeping track in a table/form - PLEASE HELP!

[corptkm]

I have two tables (one is a query)



Table 1 (query based)

EMP_ID
Prev_Emp_ID
EMP_ID_DDSK

EMP_ID	Prev_Emp_ID	EMP_ID_DDSK
332-123	1	500
332-133	1	501
332-144	0
332-156	1	502
332-654	1	503
332-456	1	504
332-967	0

Table 2

Res_Numbers
Num_Of_Employees

Res_Numbers	Num_Of_Employees
500	10
505 – after 5 numbers are placed	Will be added for next hire

My goal: to place employee numbers into new employee field "EMP_ID_DDSK" (table 1), Numbers will come from (from "Res_Numbers" field (table 2)

Example: If field "Prev_Emp_ID" = false

Get the next number in line from table 2 "Res_Number" and place it in table 1 field "EMP_ID_DDSK".
Example: if "Prev_Emp_ID" = false for 5 new employees. Take the next set of numbers and place into field "EMP_ID_DDSK".

Can anyone give me some ideas on how to do this or at least get me started?
Thanks in advance

[ItsMe]

I am going to go out on a limb and say that DAO is your answer. You could search OpenRecordset or look in the help files for more info on creating recordsets using DAO.

[June7]

Not understanding requirement. Why are you testing for False (0) but all the False records do not have EMP_ID_DDSK value?

If Prev_Emp_ID is a Boolean (Yes/No) field then the values would be -1/0. If it is just a text or number field then don't test for True/False, test for 1/0.

If you want to update EMP_ID_DDSK for existing records, it does appear that VBA code opening recordset and looping through recordset might be required.

[corptkm]

If the value in field "prev_ADSK" in table 1 is 0
then get the next number in line from field "number" in table 2 and apply to field "emp_ID" in table 1

Keep doing this until you reach the last "Prev_ADSK" that = 0

In a nutshell - I want to place the next number in line from one field in a table to another field in another table based on "0" from another field.
Thanks

[June7]

The advice remains the same - VBA code, even though I have no understanding of why you want to do this.