My event procedure doesn't work like it did before?

T

Tom

I recently had a new server installed and now the event procedure doesn't
work like it did before. What could have effected the procedure?
It actually performs a portion of the routine (creates 6 new numbers) and
then stops.
The Event Procedure is shown below.

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double
Dim db As Database
Dim Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer
Dim x As Integer
'Dim IntI As Integer
Dim IntNoRecords As Integer
Dim dblNewInvNo As Double
Dim dblLastInc As Double

dblInvNo = DMax("[INVNO]", "Inv List")
dblNewInvNo = dblInvNo + 1
Set db = DBEngine.Workspaces(0).Databases(0)
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
Rs.Index = "CUST/CUSTNO/EMPNAME" ' Set current index.

With Rs
'Counting total number of records. This task should be handled in the
'Form_Load event.
.MoveLast
IntMax = .RecordCount
.MoveFirst
'.Edit
'![INVNO] = dblNewInvNo
'.UPDATE

For x = 1 To IntMax
If x = 1 Then
.Edit
![INVNO] = dblNewInvNo
dblLastInc = ![INVNO]
.Update
.MoveNext
Else
.Edit
![INVNO] = dblLastInc + 1
dblLastInc = ![INVNO]
.Update
.MoveNext
End If
Next

End With

db.Close
Me.Refresh

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub
 
O

Ofer

Test this code

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
With Rs

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub
 
T

Tom

Compile error.
You did not properly terminate a With block.
Place an End With statement at the end of the block.


Ofer said:
Test this code

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
With Rs

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub

--
I hope that helped
Good luck


Tom said:
I recently had a new server installed and now the event procedure doesn't
work like it did before. What could have effected the procedure?
It actually performs a portion of the routine (creates 6 new numbers) and
then stops.
The Event Procedure is shown below.

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double
Dim db As Database
Dim Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer
Dim x As Integer
'Dim IntI As Integer
Dim IntNoRecords As Integer
Dim dblNewInvNo As Double
Dim dblLastInc As Double

dblInvNo = DMax("[INVNO]", "Inv List")
dblNewInvNo = dblInvNo + 1
Set db = DBEngine.Workspaces(0).Databases(0)
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
Rs.Index = "CUST/CUSTNO/EMPNAME" ' Set current index.

With Rs
'Counting total number of records. This task should be handled in the
'Form_Load event.
.MoveLast
IntMax = .RecordCount
.MoveFirst
'.Edit
'![INVNO] = dblNewInvNo
'.UPDATE

For x = 1 To IntMax
If x = 1 Then
.Edit
![INVNO] = dblNewInvNo
dblLastInc = ![INVNO]
.Update
.MoveNext
Else
.Edit
![INVNO] = dblLastInc + 1
dblLastInc = ![INVNO]
.Update
.MoveNext
End If
Next

End With

db.Close
Me.Refresh

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub
Click to expand...
Click to expand...
 
O

Ofer

My mistake, replace the "With Rs" in the end with "End If"

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
End If

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub


--
I hope that helped
Good luck


Tom said:
Compile error.
You did not properly terminate a With block.
Place an End With statement at the end of the block.


Ofer said:
Test this code

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
With Rs

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub

--
I hope that helped
Good luck


Tom said:
I recently had a new server installed and now the event procedure doesn't
work like it did before. What could have effected the procedure?
It actually performs a portion of the routine (creates 6 new numbers) and
then stops.
The Event Procedure is shown below.

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double
Dim db As Database
Dim Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer
Dim x As Integer
'Dim IntI As Integer
Dim IntNoRecords As Integer
Dim dblNewInvNo As Double
Dim dblLastInc As Double

dblInvNo = DMax("[INVNO]", "Inv List")
dblNewInvNo = dblInvNo + 1
Set db = DBEngine.Workspaces(0).Databases(0)
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
Rs.Index = "CUST/CUSTNO/EMPNAME" ' Set current index.

With Rs
'Counting total number of records. This task should be handled in the
'Form_Load event.
.MoveLast
IntMax = .RecordCount
.MoveFirst
'.Edit
'![INVNO] = dblNewInvNo
'.UPDATE

For x = 1 To IntMax
If x = 1 Then
.Edit
![INVNO] = dblNewInvNo
dblLastInc = ![INVNO]
.Update
.MoveNext
Else
.Edit
![INVNO] = dblLastInc + 1
dblLastInc = ![INVNO]
.Update
.MoveNext
End If
Next

End With

db.Close
Me.Refresh

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
T

Tom

Thank you "Ofer" it works beautifully, your friend Tom,

I have another problem I have a table in which I place email addresses and
would like the email address to be active and open Outlook from a form when
clicked. How do I do this?


Ofer said:
My mistake, replace the "With Rs" in the end with "End If"

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
End If

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub


--
I hope that helped
Good luck


Tom said:
Compile error.
You did not properly terminate a With block.
Place an End With statement at the end of the block.


Ofer said:
Test this code

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
With Rs

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub

--
I hope that helped
Good luck


:

I recently had a new server installed and now the event procedure doesn't
work like it did before. What could have effected the procedure?
It actually performs a portion of the routine (creates 6 new numbers) and
then stops.
The Event Procedure is shown below.

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double
Dim db As Database
Dim Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer
Dim x As Integer
'Dim IntI As Integer
Dim IntNoRecords As Integer
Dim dblNewInvNo As Double
Dim dblLastInc As Double

dblInvNo = DMax("[INVNO]", "Inv List")
dblNewInvNo = dblInvNo + 1
Set db = DBEngine.Workspaces(0).Databases(0)
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
Rs.Index = "CUST/CUSTNO/EMPNAME" ' Set current index.

With Rs
'Counting total number of records. This task should be handled in the
'Form_Load event.
.MoveLast
IntMax = .RecordCount
.MoveFirst
'.Edit
'![INVNO] = dblNewInvNo
'.UPDATE

For x = 1 To IntMax
If x = 1 Then
.Edit
![INVNO] = dblNewInvNo
dblLastInc = ![INVNO]
.Update
.MoveNext
Else
.Edit
![INVNO] = dblLastInc + 1
dblLastInc = ![INVNO]
.Update
.MoveNext
End If
Next

End With

db.Close
Me.Refresh

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
O

Ofer

Hi Tom
You can use the SendObject command, the true in the end indicate that you
want to
open outlook, the false mean to send mail without openning outlook, True is
the default.

docmd.SendObject acSendNoObject,,,me.AddressFieldName,,,"Subject","Message
Text",True

--
I hope that helped
Good luck


Tom said:
Thank you "Ofer" it works beautifully, your friend Tom,

I have another problem I have a table in which I place email addresses and
would like the email address to be active and open Outlook from a form when
clicked. How do I do this?


Ofer said:
My mistake, replace the "With Rs" in the end with "End If"

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
End If

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub


--
I hope that helped
Good luck


Tom said:
Compile error.
You did not properly terminate a With block.
Place an End With statement at the end of the block.


:

Test this code

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double, db As Database, Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer, x As Integer
Dim IntNoRecords As Integer, dblNewInvNo As Double, dblLastInc As Double

dblNewInvNo = DMax("[INVNO]", "Inv List") + 1
Set db = CodeDB()
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
If Not Rs.EOF Then
While Not Rs.EOF
Rs.Edit
Rs![INVNO] = dblNewInvNo
dblNewInvNo = dblNewInvNo + 1
Rs.Update
Rs.MoveNext
Wend
With Rs

db.Close
Me.Requery

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub

--
I hope that helped
Good luck


:

I recently had a new server installed and now the event procedure doesn't
work like it did before. What could have effected the procedure?
It actually performs a portion of the routine (creates 6 new numbers) and
then stops.
The Event Procedure is shown below.

Private Sub btnInvNo_s_Click()
On Error GoTo Err_btnInvNo_s_Click

'This function generates incremental Invoice Numbers for invoices in
'the table "1WEEK". The first invoice number is the last/maximum invoice
'number in a table called "Inv List"

Dim dblInvNo As Double
Dim db As Database
Dim Rs As Recordset
Dim IntMax As Integer 'holds the number of records in table "1WEEK"
Dim IntRec As Integer
Dim x As Integer
'Dim IntI As Integer
Dim IntNoRecords As Integer
Dim dblNewInvNo As Double
Dim dblLastInc As Double

dblInvNo = DMax("[INVNO]", "Inv List")
dblNewInvNo = dblInvNo + 1
Set db = DBEngine.Workspaces(0).Databases(0)
Set Rs = db.OpenRecordset("1WEEK", DB_OPEN_TABLE)
Rs.Index = "CUST/CUSTNO/EMPNAME" ' Set current index.

With Rs
'Counting total number of records. This task should be handled in the
'Form_Load event.
.MoveLast
IntMax = .RecordCount
.MoveFirst
'.Edit
'![INVNO] = dblNewInvNo
'.UPDATE

For x = 1 To IntMax
If x = 1 Then
.Edit
![INVNO] = dblNewInvNo
dblLastInc = ![INVNO]
.Update
.MoveNext
Else
.Edit
![INVNO] = dblLastInc + 1
dblLastInc = ![INVNO]
.Update
.MoveNext
End If
Next

End With

db.Close
Me.Refresh

Exit_btnInvNo_s_Click:
Exit Sub

Err_btnInvNo_s_Click:
MsgBox Error$
Resume Exit_btnInvNo_s_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Canceling an OnClick Event 3
Combo Box with a event procedure 1
Run-Time Error 3157 - Link SQL Table ODBC- Failed 4
vba code 0
Event procedure not working 2
Need Help 0
Changing/Adding Headers within a Datasheet view 0
Treeview Control Problem 2

Top