Matt Brown
Registered User.
- Local time
- Today, 11:30
- Joined
- Jun 5, 2000
- Messages
- 120
I am having a problem with a certain part of some VBA.
The code should update the serial number by one when a new record is loaded, after looking to see where abouts the numbering is, i.e. if 0 then next number would be 1, if 6 next number to be inserted would be 7 etc etc...
Here's the code, can anyone see any problems with it, also how can i just test the serial number updating part on its own?
It just doesn't seem to want to create the next number.
SQL = _
" SELECT max(SerialNo)" & _
" FROM Quote " & _
" WHERE CustomerNo = 'Trim(Forms!main!CustomerNo)'" & _
" and Date = " & Format(Now(), "yyyymmdd") & ";"
'Changed MDB
'Serial Number Counter
Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)
If rs1.EOF Then
SerialNo = 1
Else
rs1.MoveFirst
If IsNull(rs1.Fields(0)) Then
SerialNo = 1
Else
SerialNo = rs1.Fields(0) + 1
End If
End If
rs1.Close
cheers
Matt
The code should update the serial number by one when a new record is loaded, after looking to see where abouts the numbering is, i.e. if 0 then next number would be 1, if 6 next number to be inserted would be 7 etc etc...
Here's the code, can anyone see any problems with it, also how can i just test the serial number updating part on its own?
It just doesn't seem to want to create the next number.
SQL = _
" SELECT max(SerialNo)" & _
" FROM Quote " & _
" WHERE CustomerNo = 'Trim(Forms!main!CustomerNo)'" & _
" and Date = " & Format(Now(), "yyyymmdd") & ";"
'Changed MDB
'Serial Number Counter
Set rs1 = db1.OpenRecordset(SQL, dbOpenSnapshot)
If rs1.EOF Then
SerialNo = 1
Else
rs1.MoveFirst
If IsNull(rs1.Fields(0)) Then
SerialNo = 1
Else
SerialNo = rs1.Fields(0) + 1
End If
End If
rs1.Close
cheers
Matt