adding new record with id specified by user

[sha7jpm]

ok.. not sure if this is possible (like most of my queries I post!)

I have a dbase where the idea is that the user enters in the batchnumbers to be processed by our data entry team..
i.e 1-10, 14-18

then I am looking to get the dbase to take these numbers and append records to a table with these numbers..

pri ID ID
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 14
12 15
13 16
14 17
15 18

is this possible? I need to have them as individual records because the users will then go back in later and tick a box to say that a certain batch has been keyed....and then again to be verified...

many thanks in advance

John

 

[dcx693]

Yes, this is possible. You can have the user enter the ranges as "1-10", or you can have two fields where the user enters the start and then the end numbers. Doesn't really matter since you can have Access calculate the other for you.

I've done this before in the past, and the easiest way I found was to use VBA code. Have the user enter the data, then run code to enter each number in turn into your table starting at the beginning end of the range and ending at the end.

 

[sha7jpm]

cheers!

would i do this with a recordset?
I can understand the principles behind it.. but am struggling with the code for creating the new records...

assume it would be where you add a new record in the loop till the count = the higher number of the two?

I'll give it a go and post back in the highly likely possibility that it all falls flat!

ta

John

 

[dcx693]

Yes, John. You'd need to use DAO or ADO to open the table as a recordset and then add the entries to it one at a time using the loop.

There are other ways to accomplish this, like using a calculated query, but I think the above is the easiest.