Assign Record Number once submitted

I currently have an autonumber field [RequestNumber] to give a unique identifier to each record in my holiday database - if the number is somewhat random that doesn't matter.

The only problem I get is that one someone starts filling out a sheet the number is assigned as they type, if they don't click submit (which emails the form) it is still stored in whatever state it was left.

Question - is it possible to suppress the autonumber until the submit button has been clicked or if not possible what vba code can i use say in on close to delete a record that has not been submitted??

Thanks for your help

Post your answer or comment

comments powered by Disqus
I working with alot of records and would like to assign file number in the patter 001,002, 003 ... any ideas


I have a web form that is going to feed data into a database I'm working on. What I need to do is figure out how to assign these submitted records to users in the database. I'm guessing running some kind of code 'On Open' makes the most sense. I want it to automatically assign the record to the user who has gone the longest since the last assignment. For example, let's say I have three users: John, Jane, and Joe. John was assigned record #1, Jane #2, and Joe #3. When record number #4 comes it, it should determine that John gets record #4 assigned to him. I hope that makes sense. Any help would be appreciated!

I have a group of records in a Table that I need to assign a number to before appending them to another table.

What's the best way to do this? I was thinking of using a pop-up form but would like to hear of other methods.


Below is the code I wrote after looking at a book. I know, I know, but it's all I have since no Access gurus live nearby. What I'm trying to do is to loop through a recordset of unique pavement sections, then get the overlay/rehab table (rehab_proj_join), of course many projects to one section. Then I want to filter the rehabs/overlays to just that one section to count them and assign an overlay number.

I can't even get the recordset to work. The sectionSQL query works in query-land--I tested it, and this is a very basic SQL here. But when I try to debug, after the rstSections recordset is supposedly open, the immediate window shows 0 as the anssecid (all numbers in the sections table are of course >0). Then it doesn't like the opening of the rehab_proj_join table much either. I'm pulling my hair out here--help! Any advice would be greatly appreciated.

Private Sub overlay_no()
Dim dbsA As Database
Set dbsA = CurrentDb
Dim rstRehab As New ADODB.Recordset
Dim rstSections As New ADODB.Recordset
Dim rehab As String, sectionSQL As String, rehabcount As Integer, counter As Integer, anssecid As Integer

sectionSQL = "select distinct pvmt_analysis_section_id as anssecid from v_pvmt_anlss_sctn_new_with_miles"
Set rstRehab = New ADODB.Recordset
rehab = "rehab_proj_join"

rstSections.Open sectionSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

Do Until rstSections.EOF

rstRehab.Open rehab, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

rstRehab.Filter = "pvmt_analysis_section_id=anssecid"
rehabcount = rstRehab.RecordCount

For counter = 1 To rehabcount

overlay_nmbr = counter
rstRehab.Update overlay_nmbr, counter

Next counter


GoTo Done
Set rstRehab = Nothing
Set rstSections = Nothing

End Sub


i'm working with access and now i have a question if it is possible to save multiple records at once.

i want to save labels. these will be saved in the table when you press the button and i have a seperate button for generating the labels from the table to a pdf file.

now i'm having a problem:

saving and generating the labels works perfect.
now i have a textbox on my form where the user kan submit the amount of labels he wants to save, because sometimes it's possible that one label needs to be printed more than one time on the pdf file.

i've tried some things but i can't seem to make it work

is it possible to do this through a for loop, that the for loop checks the amount that is submitted in the textbox and that he loops until the value is null.

thanx in advance


I have a cumlative DB that exports a file daily based on what was keyed daily..

Therfore, every day I am sending only new records from the DB to a vendor.

I was using autonumber to assign the record number but they now want the record number to restart at 1 for a new days worth of data entry.

So today's file may have record number 1-10 (10 recs) and tomorrow's file would have 11-20 populated om the record number field.

My new record number would have to be 1-10 today and 1-10 tomorrow etc..

Not sure how to do this easily.



im new to the forum so appologies if this is in the wrong place.

I have jsut started to build my database and want to know if there is a way that i can get my database to automatically create a unique record number every time a new one is started.

i know that each record is already unique but i also want a unique ID as a field as well. i want the unique number to be 4 digits long and for the system to autofill this field with a unique number once you start a new record.


Hey there, I'm trying to design an update query that will allow admins to assign a number of records of their choosing for editing to one of a short list of users. 'User' is a drop down box taken from a table and there is an auto number giving each record an Id. If possible, I'd like to have it pop up with a box asking which user to assign records to, then one saying how many records to assign. I've been trying to figure this out for a while now and any help is really really appreciated. thanks!

I am trying to do a check processing function and I get to the point where I need to assign the check number (starting with a check number keyed in by the user)then automatically assigning the next check number to each check record found to have no check number in the query. What I essentially want it to do is start with the first check as number 10001, the next check is 10002 and so on. Is there some way to utilize the Record Number that displays at the bottom of the query to do this - EX. [START_CHECK_NUMBER] + RECNO()-1 ?

This would work but I don't know how to access the record number and use it.

Hello again,

A while back, I asked about the now-removed function of exporting Reports to Excel. I was told that there's no way to do so in Access 2007 and that the best best would be to export the underlying Query.

I did so and I found out that the query was showing the record number of some data pulled in via combo box (which gets data from a separate table) instead of the data itself. Logically, I can understand why that would happen, but is there a way to change that so that the intended data, not the record number, is shown?


2008.....spring.....45.....33.....222.....bookname .....bookauthor.....edition


2008.....spring.....johnson.....aspen publishing.....Intro to Criminology.....bookname.....bookauthor

Once this is sorted out, I can automate the export without a problem.

On my main form I have a list box with our patient names and IDs. Some patients are entered a few times (but they have same patient record number). What I am trying to do is to have a list showing patient name only once and in report (which is accessible from this form) to show all records related to this patients by his patient record number). I would greatly appreciate any suggestions.
Many thanks,

Hello, I have created a database that generates and issues part numbers via a request form. I now need to create a form that will creates 10 - 20 numbers at once for one person. Basically I am using the auto number system with a prefix added for the part numbers. How do you create or add say, 10 records at once? What's the code??

Thanks in advance!


I'd like to display the record numbers and how many total records there are on a certain report. IE 1 of 15, 2 of 15, etc for every record that is displayed on the report. How do I allow this?

Can anyone let me know how to write a macro which checks the gaps in the records numbering and make it sequential. My Idea was that It should check for the missing numbering in that column. Then check for the highest number and change it to lowest Number+1 and go one incrementing the rows in a column.
There may be morw easiest ways to do this. Can anyone let me know how can we do this?

Thank You,

I am looking to create a table in Access with some 90.000 records. Each record is going to have a record number, the maximum being 90.000. As time goes by some of these records may be deleted and therefore the record number is out of use. I would like to create a macro which allows me to list all of the record numbers which are not in use. It was my intention to perform this in Excel, but the limit on records is 64000. The following is the code from the Excel macro, i would like to perform something similar in Access.

Public Sub ListMissingInvNum()
Dim Sh As Worksheet
Dim MinNum, MaxNum, Num As Integer

Sheets.Add before:=ActiveSheet

For Each Sh In Worksheets
If Not Sh.Name = ActiveSheet.Name Then

MinNum = WorksheetFunction.Min(Sh.Columns("A:A"))
MaxNum = WorksheetFunction.Max(Sh.Columns("A:A"))

For Num = MinNum To MaxNum
If WorksheetFunction.CountIf(Sh.Columns("A:A"), Num) = 0 Then
NxRow = Cells(MaxNum, 1).End(xlUp).Row + 1
Cells(NxRow, 1).Value = Sh.Name
Cells(NxRow, 2).Value = Num
End If
Next Num

End If
End Sub

Any help would be greatly appreciated.


So.. I've been trying to create a form that auto-advances a record number.

To do it, I simply put a formula of:
=Year(Date()) & "-" & [ID]
..where [ID] is simply an autonumber for the table I'm using.

The expected result would be: 2009-1, 2009-2, 2009-3, etc.

My issue is that it will populate the field properly on the form, but it won't pass that value to the actual field for use later.

I've tried to make this a default value in the field, but it doesn't populate the field properly - I will end up with simply "2009-" because (I've deduced) at the time the field is auto-populated, the value of [ID] is blank. It doesn't fill that value in until I start to enter data on that particular form.

I'm not a complete MS Access Noob... but not far from it, either.

Is there a simple code to have it pass the string to the field?



I have a problem that i need some help solving.

I have a table called tblCourierCosts, the table consists of the following fields:


What i need to do is for each Run, add the number of boxes and then perform a calculation to work out the cost and place the cost for the whole run in the CourierCost field of the first record of the run.

I am struggling with keeping a note of the record number (so i can write the costs back to the cost field of the 1st record)

Can someone help please

Hi All,

Ok, I'm hoping this is fairly straightforward, but I've been hunting around online and going throguh the inbuilt help files in Access 2003 for about an hour now but simply can't find the answer.

Ok, I have a folder on my PC that contains a set of files whose names all begin with a 5 character code, then a hyphen, then the filename then the extension.

These files are replicated in an Access 2003 table called TblFileData that contains various text fields giving the file's code, name, extension type, and so on.

Now I have a form that contains, amongst a bunch of textboxes which are all bound to the above table for the purpoises of displaying information, a combobox, cbSelectFile.
This combobox is programmed to look at the folder of files in realtime, allowing the user to select any of the files inside. It's contents are generated dynamically via modules to gather data direct from the PC folder; it's values are not populated by the table.

With me?

Ok, not the idea is that the user uses the dropdown box to select a file from the folder. The form then scans the code of the selected file then matches it against it's own table and moves the form to the relevant record for that file. This is achieved by the following code:

	Dim sCriteria As Variant
Me.SelCode.Value = Left(Me.cbSelectFile.Value, 5)
Me.RecNo.Value = DLookup("[ID]", "TblFileData", "[fldCode] ='" & Me.SelCode.Value & "'")
sCriteria = Me.RecNo.Value
If IsNull(Me.RecNo.Value) Then
DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, 1
DoCmd.GoToRecord acDataForm, "frmMyForm", acGoTo, sCriteria

Now this works perfectly. If the user selects a file whose 5 character code [I]does[I] exist in the Access table, the form changes to that record. If the user selects a file and its code does not exist in the table, then the form changes to record 1 which is purposefully kept blank.

This is all great, but I've recently noticed a problem. The column [ID] in my table is the primary key. I had cause recently to delete some rows directly from the table, and now my code throws debug errors when run.

I've investigated and understand why. Basically, I have row/record 1 (which has the [ID] of 1, in my table as being blank, but row/record 2 (which has the [ID] of 4 since I deleted some previous rows) has the data of a file.
When my code runs, it strips the code out of the file, reads the table, finds the code, but is returning the value of the [ID] field, and not the physical record number; so in my case it finds a match and tries to jump to record 4 (which doesn't exist) instead of record 2.

Any ideas how I can fix this? What should really happen to improve stability is the code should run the match, and, if found, jump to the physical record number, and not try to jump to a record that matches the [ID] numebr since these will sometimes fall out alignment.

I have an unbound form in which I need to update two tables (site & Contact) with new records, and create an entry in an index file - I use "INSERT INTO" to update the tables. The two tables are both keyed with an autonumber (they have a many to many relationship).

Can anyone help me with how I get the record numbers of the new records that I added to the 'site' and 'contact' tables, so I can create the index file record?

I have a field in my table that I am trying to increment every new record in the sense like AutoNumber but it has a Letter in the field so it can't be autonumbered! So here is an example of what the record number format in the field looks like


I want it to increment the last 3 numbers every new record so the user doesn't have to input it. I figured implement some sort of code checking the last 3 numbers for what they are in the last record in the database but I forget the code for just picking out the last 3 characters in the string?! And after the check for last record and what the last record in the field contains how would I add 1 to the number if it is a string I keep getting a type mismap from trying to add a number to the string it gets all confused and doesn't know where to add it. Please Help me out!


I'm wanting to know if it's possible to set the value in a control on a form equal to the record number that it is on.
I have a Master form and then a subform on it setup as a columnar form. You know how the records on the subform are number per each record on the master form, like master form record 1 might have 3 records related on the subform. Is it possible to put the value of one of the fields on the subform equal to its record number on the subform?

If this isn't possible how could I count up in the subform and then start over when a new record is added in the master form?

Thanks for the help

There are a number of threads in the forum for a similar question about numbering records in forms and reports but I can't find my answer.

I have a table with a field that is currently blank. I want to fill this field with its corresponding record number. After opening the recordset I have tried the following:

	Do While Not rs.EOF
     RecCnt = RecCnt + 1
     rs!RecNumber = RecCnt

But I get an overflow error because it just keeps repeating the first record in the set to infinity.

If I use "If Not rs.EOF Then.... End If" it updates only the first record and then exits. Why does it not get beyond the first record in the set?

I'm sure there is a very simple way to do this that I am not aware of.

Thanks for the help guys.

Is it possible to change the record numbers displayed at the bottom of a form? For instance, rather than 1-10, make it display 14-23?

I am trying to assign a value to a field using vb. I have already used an auto number for another field, so i definitely have to write the code for this one. I need to assign a number to a field in increments of 1 and the starting number is on a query. Any ideas? The code i have so far is below.

Dim JRNNEXT As Variant

'Loop through unposted journal entries
Do Until rstJournalEntriesToPost.EOF

' Find the next Journal Entry Number
JRNNEXT = DLookup("JRNNEXT", "qry_JENextNumber")

rstJournalEntriesToPost!JRNENTRY = JRNNEXT



Not finding an answer? Try a Google search.