Updating serial no problem

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
 
I just tried :

Dim s As Long

s = DMax("CustomerID", "tblCustomer")

MsgBox s

It returns the highest No in the CustomerID field of tblCustomer.

HTH

Dave
 
Oldsoftboss said:
Why not just use the auto number field of your table ?

The way this is set up, using the autonumber would not really be the best way.

A quote can be created against the same customer on the same day, the serial number will then allow each quote to have a unique ID.

The system has several searches where you can pull out records for the same customer done on the same day but each quote is then numbered 1,2,3 etc etc to give each one a unique reference.

If i use the autonumber within the table it would produce almost unsequential numbers per customer quote per day as users would be creating quotes not for just one customer.

Its a little hard to explain but the autonumber just wont be any use.

Matt
 
Date is a reserved word in Access and shouldn't be used as a field name
and Date = " & Format(Now(), "yyyymmdd") & ";"
should be
and MyDate = " & Format(Date(), "mm/dd/yyyy") & ";"
 
Rich said:
Date is a reserved word in Access and shouldn't be used as a field name
and Date = " & Format(Now(), "yyyymmdd") & ";"
should be
and MyDate = " & Format(Date(), "mm/dd/yyyy") & ";"

Thanks Rich,

Well spotted, but that was a typo on my behalf, it is actually is named as "Dato" which is Danish for Date.
Sorry about that everyone.

Matt
 
Is there any way to echo back an SQL query written in VBA to check/test the result set?
 

Users who are viewing this thread

Back
Top Bottom