Assign next number

[thart21]

I have a table called "AssetNumbers" and will be given a batch of about 10-20 of these numbers at a time from Accounting (they are not in any sequential order and I never know what I will be getting). When the user selects "get new Asset Number" from the menu, I want it to automatically select the next available Asset Number from my table and fill that in on the Add form which will then be used to complete the details for that Asset. This will not be a primary key as there can be many records that use this same Asset Number after it has initially been set up. Once that Asset Number has been used I want it to be available only for updates (via another form). The next time "get new Asset Number" is selected I want the next available number and so on. I also would like some type of warning when I am getting close to the end of available numbers in the table so I can request another set. (not critical though as I will be monitoring it frequently anyway).

Any ideas would be appreciated!

Thanks,

Toni

 

[dcx693]

You could enter these asset numbers into an Access table and pull one out as needed using SQL and DAO or ADO. You can also delete it from the table at the same time.

You can build a check into your form when the remaning number of asset numbers gets below, say 4, so you can be alerted to request more.

 

[tone21]

Finally getting back to this project. Here is what I have so far which isn't working correctly:

table Assets has AssetNumber, DateAssigned
table "AssetNumbers" has DateAssigned, AssetNumber, and additional information that will be appended to it from the RequestAsset form

My goal is to have the user complete the necessary info on the RequestAssetForm, hit "Submit Request" and, if all of the required fields are completed, run my queries, have the AssetNumber automatically filled in on the current RequestAsset form, have a message pop up "Record successfully added, your Asset Number is " " ". vbOK. At the same time, I need it to append all of the information (including the new Asset Number) to my AssetNumbers table and enter the DateAssigned into the Assets table, rendering that Asset Number unavailable for future assigning as a new number.

I would appreciate someone looking at my code and offering suggestions:

To get the next available Asset Number from the Assets table:

"Submit Request" On Click event:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryMinimum"
DoCmd.SetWarnings True

SQL query qryMinimum: The query is actually "opening" here, how do I get it to just run in the background?

SELECT Min(A.AssetNumber)
FROM Assets AS A
WHERE (((A.DateAssigned) Is Null));

Next,

To enter the DateAssigned into the Assets table:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryUpdateDateAssigned"
DoCmd.SetWarnings True

qryUpdateDateAssigned:

UPDATE Assets SET Assets.DateAssigned = Date()
WHERE (((Assets.AssetNumber)=[FORMS]![RequestAsset]![AssetNumber]));


The RequestAsset form is bound to the AssetNumbers table so the data is automatically appended to it, with the exception of the new Asset Number.

How can I get the Asset Number that has been pulled and assigned to this new record to append the the table and show the required message with the new Asset Number?

Thanks in advance for your help!