Populate Table w/ Range of Numbers from Parameter Query 1

[tennisguy]

Hi...

I have a field named RFP_Number in a table named RFP_General_Info. I would like to have something like a parameter query that can prompt a user to enter a range of new RFP numbers (e.g., from 5001 to 5500). I would then like those numbers used to create as many new records in the RFP_General_Info table, populating the RFP_Number field with the numbers retrieved from the parameter prompts. RFP_Number is the primary key field in that table.

Ultimately, other users will use a form designed to create new RFPs (Request for Payment). However, the RFP numbers are controlled by someone else and have to be restricted to a specific range. So, when someone comes onto the form where they fill out the RFP details, the RFP number text box is already using the next available RFP number as created from the above parameter query.

So, I like being able to ask someone via parameter prompts, but I'm not sure how to integrate that with perhaps an append query or code or macro to get this to work.

Any assistance is appreciated.

Thanks,
Michael

 

[Crowley16]

eh, if you really want to create many entries in one go in this way, then you'd need to have some code...

just have a start and end number fields then step through each number between them with a for loop, and in the for loop, put an append query with appropriate parameters...



Procrastinate Now!

 

[tennisguy]

Thanks for the suggestion. Could you provide a code example?

 

[dhookom]

I would create a table "tblNums" with a single long integer field "Num" and values 0,1,2,3,4,5,6,7,8,9 (just 10 records). Then create a query (qcarNumbers) to get all possible numbers from 0 to 99,999:

SELECT [tblNums].[Num]+[tblNums_1].[Num]*10+
[tblNums_2].[Num]*100+[tblNums_3].[Num]*1000+
[tblNums_4].[Num]*10000 AS Numbers
FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2, tblNums AS tblNums_3, tblNums AS tblNums_4
ORDER BY [tblNums].[Num]+[tblNums_1].[Num]*10+
[tblNums_2].[Num]*100+[tblNums_3].[Num]*1000+
[tblNums_4].[Num]*10000;

You can then create an append query using this "cartesian" query with sql like:

PARAMETERS [Enter Start Number] Long, [Enter End Number] Long;
SELECT qcarNumbers.Numbers
FROM qcarNumbers
WHERE (((qcarNumbers.Numbers) Between [Enter Start Number] And [Enter End Number]));

Duane
MS Access MVP
Find out how to get great answers faq219-2884.