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

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.

This article has been dead for over six months. Start a new discussion instead.