Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

Invoice number

Hello all,

I need your help with a code.

I need to increase by 1 the last number in my accdb access, column Invoice from my VB6 tool.

So if the highest number in column Invoice is1012, when i click on a button, then Text2 will have 1013 as the new invoice number.

oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Base.accdb;"

Thanks for your help
0
Wilder1626
Asked:
Wilder1626
  • 11
  • 8
1 Solution
 
GrahamSkanRetiredCommented:
Run a query that looks for the maximum number
Dim rs as Recordset
rs.Open "SELECT Max(Invoices.Invoice) AS MaxOfInvoice FROM CRO_Births;", oConnect
if not rs.eof then
NextNumber = rs.Fields("Invoice").Value + 1
Endif

rs.close

Open in new window

0
 
Wilder1626Author Commented:
I have a Run-time error 91: Object variable or with block not set

What does it mean?
0
 
Wilder1626Author Commented:
On that part: rs.Open "SELECT Max(Invoices.Invoice) AS MaxOfInvoice FROM Table3;", oConnect
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
GrahamSkanRetiredCommented:
It means that I didn't include a way of instantiating the 'rs' object.

The easiest way to do that is with New:

replace
Dim rs as Recordset

with
Dim rs as New Recordset
0
 
Wilder1626Author Commented:
Here is the code:

But now i have a: Compile error

Invalid use of New Keyword
Private Sub cmdOk_Click()
Dim rs As New Recordset

  
rs.Open "SELECT Max(Invoices.Invoice) AS MaxOfInvoice FROM Table3;", oConnect


If Not rs.EOF Then
NextNumber = rs.Fields("Invoice").Value + 1
End If

rs.Close
End Sub

Open in new window

0
 
GrahamSkanRetiredCommented:
There are several types of recordset. To make sure that we are talking about the same thing, try using:

Dim rs As New ADODB.Recordset
0
 
Wilder1626Author Commented:
Still give me an error on: rs.Open "SELECT Max(Invoices.Invoice) AS MaxOfInvoice FROM Table3;", oConnect

Run time error 3001
0
 
GrahamSkanRetiredCommented:
Is there any error text?
0
 
Wilder1626Author Commented:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
0
 
GrahamSkanRetiredCommented:
Well, I usually add more parameters, but I didn't think they were mandatory.

rs.Open "SELECT Max(Invoices.Invoice) AS MaxOfInvoice FROM Table3;", oConnect, adOpenDynamic, adLockPessimistic, adCmdText

What type of object have you declared oConnect as?
0
 
Wilder1626Author Commented:
oConnect = Empty

Should i add this code somewhere:
oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Jean-Marc\Desktop\Service à la clientel Lab Solution\Base.accdb;"
0
 
GrahamSkanRetiredCommented:
Sorry. You had put that code in your question, so I assumed that you were already using it.

If it isn't there, then yes, but you also need to instantiate it:

Dim oConnect as New ADODB.Connection
oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Jean-Marc\Desktop\Service à la clientel Lab Solution\Base.accdb;"

Note that if had no Dim for it, then you don't had Option Explicit at the top of your module. This is very much advised. You can make VB put it there automatically for all new modules with an Option labelled 'Require variable declaration'.
0
 
Wilder1626Author Commented:
Hello again, Thanks for your help.

Updated code.


Now i have:
Compile error
Invalid use of property
Private Sub cmdOK_Click()
Dim rs As New ADODB.Recordset
Dim oConnect As New ADODB.Connection
  
rs = "SELECT Max(Invoices.Invoice) AS MaxOfInvoice FROM Table3'"

oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Jean-Marc\Desktop\Service à la clientel Lab Solution\Base.accdb;"
If Not rs.EOF Then
NextNumber = rs.Fields("Invoice").Value + 1
End If

rs.Close


End Sub

Open in new window

0
 
Wilder1626Author Commented:
I've tried that also:


But now, i have:

run time error 3265
item cannot be found in the collection corresponding to the requested name or ordina
Private Sub cmdOK_Click()
Dim sSQL1 As String
  Dim rs As ADODB.Recordset
  Set rs = New ADODB.Recordset
  Dim oConnect As ADODB.Connection
  Set oConnect = New ADODB.Connection
  
sSQL1 = "SELECT Max(Facture) AS MaxOfFacture FROM Table3'"

oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Jean-Marc\Desktop\Service à la clientel Lab Solution\Base.accdb;"
rs.Open sSQL1, oConnect
If Not rs.EOF Then
NextNumber = rs.Fields("Facture").Value + 1
End If

rs.Close
End Sub

Open in new window

0
 
Wilder1626Author Commented:
oh yes, that error goes with that part of the code:

NextNumber = rs.Fields("Facture").Value + 1

By the way, i change Invoice with Facture in my db file.
0
 
GrahamSkanRetiredCommented:
Sorry about that. It should be

NextNumber = rs.Fields("MaxOfFacture").Value + 1
0
 
Wilder1626Author Commented:
Good, Now theres no more error.

But now, if let say that i have in the column "Facture" the number 1 and just under, the number 2, if i click on the botton, it should write 3 inthe textbox.

But nothing happend.

Do you know why?

Updated code:

Dim sSQL1 As String
  Dim rs As adodb.Recordset
  Set rs = New adodb.Recordset
  Dim oConnect As adodb.Connection
  Set oConnect = New adodb.Connection
  
sSQL1 = "SELECT Max(Facture) AS MaxOfFacture FROM Table3'"

oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Jean-Marc\Desktop\Service à la clientel Lab Solution\Base.accdb;"
rs.Open sSQL1, oConnect
If Not rs.EOF Then
NextNumber = rs.Fields("MaxOfFacture").Value + 1
End If

rs.Close

Open in new window

0
 
GrahamSkanRetiredCommented:
All the code above does is to set the value of a variable to the next invoice number.
If you want to show the number in a text box, you need something like this:
Dim sSQL1 As String
  Dim rs As adodb.Recordset
  Set rs = New adodb.Recordset
  Dim oConnect As adodb.Connection
  Set oConnect = New adodb.Connection
  
sSQL1 = "SELECT Max(Facture) AS MaxOfFacture FROM Table3'"

oConnect.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source= C:\Users\Jean-Marc\Desktop\Service à la clientel Lab Solution\Base.accdb;"
rs.Open sSQL1, oConnect
If Not rs.EOF Then
   Text1.Text = rs.Fields("MaxOfFacture").Value + 1
End If

rs.Close

Open in new window

0
 
Wilder1626Author Commented:
Thanks a lot for your help.

It work pefectly.

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now