Get the next voucher number from access column

[mopey12345]

Hi,
Any help appreciated. I have an excel spreadsheet and need to extract the next voucher number from an access table Customers, column called Voucher. Thanks Phil
Sub getmaxvoucher_number()
Dim cn As Object, rs As Object
Dim intColIndex As Integer
Dim DBFullName As String
Dim TargetRange As Range

DBFullName = "X:\MyDocuments\Advance\Advance.mdb"

On Error GoTo End_

Application.ScreenUpdating = False
Set TargetRange = Sheets("Sheet1").Range("A1")

Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"

Set rs = CreateObject("ADODB.Recordset")
rs.Open "SELECT * FROM Customers WHERE Dmax[Voucher],cn, , , adCmdText"

' Write the field names
For intColIndex = 0 To rs.Fields.Count - 1
TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
Next

' Write recordset
TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
Application.ScreenUpdating = True
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
On Error GoTo 0
Exit Sub
End_:
MsgBox "Error Description :" & Err.Description & vbCrLf & _
"Error at line :" & Erl & vbCrLf & _
"Error Number :" & Err.Number
Resume LetsContinue
End If
End Sub

 

[welshgasman]

No code tags?
Generally it would be Dmax() + 1, but how do you update the DB to reflect that this new Voucher number has been used?, so if run again you get the next free voucher number?
See the syntax for Dmax() Application.DMax method (Access)

I would suspect you would use Select Max(Voucher) and then add 1 to it. Still have the update issue?

 

[mopey12345]

No code tags?
Generally it would be Dmax() + 1, but how do you update the DB to reflect that this new Voucher number has been used?, so if run again you get the next free voucher number?
See the syntax for Dmax() Application.DMax method (Access)

I would suspect you would use Select Max(Voucher) and then add 1 to it. Still have the update issue?

Thanks for responding. The excel front end gets the latest number (+1 )from the access table, does the process and then writes back to the Access table a new record with this lastest number. Hope that makes sense. thanks Phil

 

[mopey12345]

Im not sure how to do this but I think I need to open the Access database Customers table from Excel front end (vba) and run a query on the voucher column to get the latest voucher number. Process the case with voucher number +1 and then write back a new record to the Customer table.

 

[welshgasman]

Yes, that is what I was saying? You seem to be saying the same thing?
Not sure what all the fields are for though?

I would Select Voucher from from Customer WHERE as you have it now.
Add 1 to Voucher giving lngVoucher
Then use rs.Edit, assign lngVoucher to Voucher, and then rs.Update

lngVoucher = rs!Voucher + 1
rs.edit
rs!Voucher = lngVoucher
rs.Update

and use the lngVoucher for your next number.
If you need several in a sequence, do the math in Excel and update at the end.

Possible conflict in multi user scenario in that case though?

 

[mopey12345]

Yes, that is what I was saying? You seem to be saying the same thing?
Not sure what all the fields are for though?

I would Select Voucher from from Customer WHERE as you have it now.
Add 1 to Voucher giving lngVoucher
Then use rs.Edit, assign lngVoucher to Voucher, and then rs.Update

lngVoucher = rs!Voucher + 1
rs.edit
rs!Voucher = lngVoucher
rs.Update

and use the lngVoucher for your next number.
If you need several in a sequence, do the math in Excel and update at the end.

Possible conflict in multi user scenario in that case though?

Thanks for replying. Probably a bit out of my depth here, but got this code using the query wizard SQl view,
SELECT Max(Customers.Voucher) AS MaxOfVoucher
FROM Customers
ORDER BY Max(Customers.Voucher);