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"
 '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
6 Years
Discussion Span
Last Post by 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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.