Problem with automated reserve ID

[ata_tszx]

Hi guys, i have a small problem...
The coding works well but it have logic error, let say i have 5 reservation and the database will contain RSV001-RSV005. If i delete RSV002 from the database, and wanted to create a new reservation. The system will automatically will generate RSV005 although there is already a RSV005 in the database, because it count the number of record in the database and not the current reserve id. So how do i overcome this? Please help!!

Call QuickReservation

With QReservation


If .RecordCount = 0 Then    'If there are no records in the table
            
strCode = "RSV0001"
        
Else
             
 'Calculating the number of records and storing in a variable
iNumofRecords = .RecordCount
iNumofRecords = iNumofRecords + 1 'incrementing the number by 1
            
If iNumofRecords < 10 Then
strCode = "RSV000" & iNumofRecords
ElseIf iNumofRecords < 100 Then
strCode = "RSV00" & iNumofRecords
ElseIf iNumofRecords < 1000 Then
strCode = "RSV0" & iNumofRecords
ElseIf iNumofRecords < 10000 Then
strCode = "RSV" & iNumofRecords
End If
            
End If
        
.Requery    'Requerying the Table
.AddNew     'Adding a new recordset
        
End With
    
q_id.Text = strCode

[AndreRet]

Do not use the record count for adding a new record ID. Try the following -

'First move your recordset to the last record, then get the ID Number, Then...
Dim strSubstr As String
Dim xStr As Integer
strSubstr = Right$(strCode, 3)     ' strSubstr = "000", RSV removed
xStr = strSubstr +1
strCode = "RSV" & xStr

Now add the strcode to the designated record. The 3 above can be changed to the number of integers on the right after RSV. You can change this maybe in your iNumOfRecords If Then Statement.