VBA CODE for ESC pressing!

[FlavioT]

Hi! I'm having a problem here...

I have assigned a code for each "box" I create in my form.

The problem is that if I don't want to add (like I made a mistake) the user would have to press ESC and then close the form so the Autonumber doesn't have empty values on it.

Which code would emulate the ESC key??
Thanks!

 

[Joe4]

You didn't really say what your code does. I am guessing it is adding new records?

Could you post the code from one of these boxes so that we can see how it works?

 

[FlavioT]

I've changed a little a Function I found here at Mr.Excel..
What is does: Assignes codes for each box I have...


But when I add a new record and don`t want a code assigned for it i'd like to close the form without saving it, you undersant?

the code assignes for a field called COD the value of

{idempresa} - 000.0001 for the first box of this "idempresa"

for the second... {idempresa} - 000.0002
and so on...

What I'd like to have is a button that would CANCEL this reg add...

Thanks!!!

Function GetNextPKValue(IDEmpresa) As String

Dim adoRS As ADODB.Recordset, strSQL As String
Dim strPotentialValue As String, lngSuffix As Long

strPotentialValue = Format(Val(IDEmpresa), "00")


'Search to see if any record exists for the current day
'CHANGE THIS TO MATCH YOUR TABLE/FIELD NAME
strSQL = "SELECT TOP 1 Caixa.cod FROM Caixa WHERE Caixa.cod Like '" & _
strPotentialValue & "%' ORDER BY Caixa.cod DESC;"

Set adoRS = CreateObject("ADODB.RecordSet")

adoRS.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly


If adoRS.BOF And adoRS.EOF Then 'There are no values for this date
GetNextPKValue = strPotentialValue & "-000.0001" 'so it must be record number 1 for the current date
adoRS.Close
Exit Function
End If

ww = Val(Right$(adoRS.Fields(0), 8))
mm = Format(Val(Left$(ww, 3)), "000") & Format(Val(Right$(ww, 4)), "0000") + 1
xx = "-" & Left(mm, 3) & "." & Right(mm, 4)
GetNextPKValue = Left$(strPotentialValue, 2) & xx
adoRS.Close

End Function

 

[Joe4]

Wow, that's some code (I haven't done much with ADO recordsets).

If you are unable to Cancel the record addition, maybe you can create some code that will call a Delete Query to delete the record that was just added.

Anyone else have an idea/suggestion?

 

[bat17]

This is from Help in A97

You can use the Undo method to reset a control or form when its value has been changed. For example, you can use the Undo method to clear a change to a record that contains an invalid entry.

Syntax

object.Undo

The Undo method has the following argument.

Argument Description
object A Form object or a Control object.
Remarks

If the Undo method is applied to a form, all changes to the current record are lost. If the Undo method is applied to a control, only the control itself is affected.
This method must be applied before the form or control is updated. You may want to include this method in a form's BeforeUpdate event or in a control's Change event.
The Undo method offers an alternative to using the SendKeys statement to send the value of the ESC key in an event procedure.

HTH

Peter

 

[Joe4]

Peter,

Thanks for helping out on this one! The UNDO command is good to know. I may be able to implement that in some of the code I use.