error message 2501 the runcommand action was cancelled

A

all21

dear all,
i'm sorry for my english.........
i have a form that contains several text boxes, 1 combo box, + 2 buttons....
the buttuons are : add & exit.....
after i enter the data from my form, i click the add button and it says like
the title:
error message 2501 the runcommand action was cancelled.

here the code behind that button :
Private Sub save_Click()
On Error GoTo Err_save_Click
If Me.NoPembayaran.Value <> "" Then
DoCmd.SetWarnings False
If Me.totalbyr.Value - Me.totalbeli.Value >= 0 Then
DoCmd.RunSQL "UPDATE TBLPembelianHeader SET TBLPembelianHeader.bayar
= True WHERE (((TBLPembelianHeader.NoTransaksi)='" & Me.No_Pembelian & "'));"
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Else
MsgBox "Nomor Pembayaran Tidak Boleh Kosong!", vbInformation + vbOKOnly,
"Perhatian"
Me.NoPembayaran.SetFocus
End If

Exit_save_Click:
Exit Sub

Err_save_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in save_Click"
Resume Exit_save_Click
End Sub

i've try to change the code like this :
Private Sub save_Click()
On Error GoTo Err_save_Click
If Me.NoPembayaran.Value <> "" Then
DoCmd.SetWarnings False
If Me.totalbyr.Value - Me.totalbeli.Value >= 0 Then
DoCmd.RunSQL "UPDATE TBLPembelianHeader SET TBLPembelianHeader.bayar
= True WHERE (((TBLPembelianHeader.NoTransaksi)='" & Me.No_Pembelian & "'));"
End If
'DoCmd.RunCommand acCmdSaveRecord <=== i marked the save record line as
comment
DoCmd.GoToRecord , , acNewRec
Else
MsgBox "Nomor Pembayaran Tidak Boleh Kosong!", vbInformation + vbOKOnly,
"Perhatian"
Me.NoPembayaran.SetFocus
End If

Exit_save_Click:
Exit Sub

Err_save_Click:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in save_Click"
Resume Exit_save_Click
End Sub

but when i click the save button, i still get error message:
error 2105 : you can't go to the specified record

help me please.......................
 
S

strive4peace

save the record FIRST ... but only if it needs to be saved...

to test to see if a record needs to be saved and then save it if it does:

if me.dirty then me.dirty = false

~~~~~~~~~~~~~~~~~~~

"dirty" is a form property that gets set to TRUE if a record has changes
that need to be saved

if me.dirty then
is the same thing as
if me.dirty=True then

me.dirty = false
is just another way to save a record


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
A

all21 via AccessMonster.com

@strive4peace
thanks for u'r response.....
but, can u give me an example to enter the code and whee to put the code in
my form...
 
A

all21 via AccessMonster.com

@strive4peace
thanks a lot sir......................
i'll try it & give a report here
 
A

all21 via AccessMonster.com

dear mis......
i've try your trick in the document
and i applied it in my code so my code became like this:
Private Sub save_click()
On Error GoTo Err_save_Click
If Me.Dirty = True Then
Me.Dirty = False
If Me.NoPembayaran.Value <> "" Then
DoCmd.RunCommand acCmdSaveRecord
If Me.totalbyr.Value - Me.TotalBeli.Value >= 0 Then
DoCmd.RunSQL "UPDATE TBLPembelianHeader SET TBLPembelianHeader.bayar
= True WHERE (((TBLPembelianHeader.NoTransaksi)='" & Me.No_Pembelian & "'));"
End If
DoCmd.SetWarnings False
'DoCmd.RunCommand acCmdSaveRecord
DoCmd.GoToRecord , , acNewRec
Me.Text29.Value = ""
Else
MsgBox "Nomor Pembayaran Tidak Boleh Kosong!", vbInformation + vbOKOnly,
"Perhatian"
Me.NoPembayaran.SetFocus
End If
End If
Exit_save_Click:
Exit Sub

Err_save_Click:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_save_Click
End Sub

but when i click the save button, i got message like this :
http://advenet.com/photos/all21/picture1522.aspx

when i click yes, it show this message :
http://advenet.com/photos/all21/picture1521.aspx

the rcord was saved, bot wont go to new record until i close form & reopen it
where i do wrong???
 
A

all21 via AccessMonster.com

dear miss...
how if i change my concept to sql.....
means : in form, i only use unbound textbox, and if i click savebutton, it
runs any sql statement like this : insert into tbpelunasanutang...............
............... (idon't know what to write here :))
can u help me with the statement ?
this is the structure of my dtabase :
1. tblpembelianheader :
|Field Name |Data Type |Description |
|Notransaksi |Text | |
|Status |Text |Pembelian atau Retur |
|Tanggal |Date/Time |Tanggal Pembelian |
|NoVendor |Text | |
|Note |Text | |
|TotalQTY |Number |Total Pembelian |
|TotalBeli |Number | |
|byr |Yes/No |Bayar Cash Aau Tidak |

2. tbpelunasanutang :
|Field Name |Data Type |Description |
|NoPembayaran |Text | |
|Tanggalbyr |Date/Time | |
|NoTransaksi |Text | |
|KodeSupplier |Text | |
|NamaSupplier |Text | |
|tangaltrx |Date/Time | |
|TotalQTY |Number |Total Pembelian |
|TotalUtang |Number | |
|TotalByr |Number | |

3. qryutang :
SELECT TBLPembelianHeader.NoTransaksi, TBLPembelianHeader.Tanggal,
TBLPembelianHeader.NoVendor, TBLVendor.NamaPerusahaan, TBLPembelianHeader.
TotalQty, TBLPembelianHeader.TotalBeli, TBLPembelianHeader.bayar,
TBLPembelianHeader.Status
FROM TBLVendor INNER JOIN TBLPembelianHeader ON TBLVendor.NoVendor =
TBLPembelianHeader.NoVendor
WHERE (((TBLPembelianHeader.bayar)<>True) AND ((TBLPembelianHeader.Status)
<>"Retur"));

sorry if i make u so busy (& sorry with my englis too)
 
S

strive4peace

Hi all21 (what is your name?)
If Me.NoPembayaran.Value <> "" then
Click to expand...

do you mean to test for the control not being filled out? If so, you
can use:

If IsNull(Me.NoPembayaran) then

~~

anyway, checks to validate the data should be on the form BeforeUpdate event

to validate a record and prevent it from being saved, put code in the
form BeforeUpdate event

'----------------- make sure all required data is filled out

'make sure SomeControlName is filled out
If IsNull(me.SomeControlName) then

'if it is not filled out, then move the focus to that control
me.SomeControlName.setFocus

'give the user a message
msgbox "You must enter Some Data",,"Missing Data"

'if this is a combobox, drop the list for them
me.SomeControlName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure the first Date is filled out
If IsNull(me.[Date1]) then
me.[Date1].setFocus
msgbox "You must enter the first Date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is filled out
If IsNull(me.[Date2]) then
me.[Date2].setFocus
msgbox "You must enter the second date",,"Missing Data"
Cancel = true
exit sub
end if

'make sure the second Date is >= Date1
If me.[Date2] < me.[Date1] then
me.[Date2].setFocus

msgbox "The second date, " & me.Date2 _
& " must be >= the first date, " _
& me.[Date1],,"Invalid Data"

Cancel = true

'IF you want to undo the entries to the record
'Me.Undo

'IF you want to undo the entries to the field
'Me.controlname.Undo
Cancel = true
exit sub
end if

'~~~~~~~~~~~~~~~~~~~~

instead of
DoCmd.RunSQL strSQL

you should use:
CurrentDb.Execute strSQL

advantages of CurrentDb.Execute over DoCmd.RunSQL

1. it is faster!
2. you do not have to turn Warnings or Echo off (Execute goes directly
to Jet, so it doesn't trigger warnings or ask user to confirm)

from Help:

Quote:
The Execute method is valid only for action queries. If you use
Execute with another type of query, an error occurs. Because an action
query doesn't return any records, Execute doesn't return a Recordset.
(Executing an SQL pass-through query in an ODBCDirect workspace will not
return an error if a Recordset isn't returned.)

Use the RecordsAffected property of the Connection, Database, or
QueryDef object to determine the number of records affected by the most
recent Execute method. For example, RecordsAffected contains the number
of records deleted, updated, or inserted when executing an action query.
When you use the Execute method to run a query, the RecordsAffected
property of the QueryDef object is set to the number of records affected.

In a Microsoft Jet workspace, if you provide a syntactically
correct SQL statement and have the appropriate permissions, the Execute
method won't fail — even if not a single row can be modified or deleted.
Therefore, always use the dbFailOnError option when using the Execute
method to run an update or delete query. This option generates a
run-time error and rolls back all successful changes if any of the
records affected are locked and can't be updated or deleted.

In earlier versions of the Microsoft Jet Database Engine, SQL
statements were automatically embedded in implicit transactions. If part
of a statement executed with dbFailOnError failed, the entire statement
would be rolled back. To improve performance, these implicit
transactions were removed starting with version 3.5. If you are updating
older DAO code, be sure to consider using explicit transactions around
Execute statements.

For best performance in a Microsoft Jet workspace, especially in a
multiuser environment, nest the Execute method inside a transaction. Use
the BeginTrans method on the current Workspace object, then use the
Execute method, and complete the transaction by using the CommitTrans
method on the Workspace. This saves changes on disk and frees any locks
placed while the query is running.

In an ODBCDirect workspace, if you include the optional dbRunAsync
constant, the query runs asynchronously. To determine whether an
asynchronous query is still executing, check the value of the
StillExecuting property on the object from which the Execute method was
called. To terminate execution of an asynchronous Execute method call,
use the Cancel method.

For ODBCDirect, use single quotes to signify strings or an error
will be returned.

~~~~~~~~~~
on the code for your 'Save' button

if, after you say "Me.Dirty = False"

you can text to see if the record was actually saved with

If Me.Dirty = True Then

(again)


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
S

strive4peace

What is the structure for TBLVendor

You gave tbpelunasanutang but the query does not (appear to) use it

Make sure your field names are spelled right! Your SQL has '
TBLPembelianHeader.bayar' but your data structure shows '
TBLPembelianHeader.byr'


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
A

all21 via AccessMonster.com

dear mis Crystal
thanks a lot for your advace....
but, in this :
~~~~~~~~~~
on the code for your 'Save' button

if, after you say "Me.Dirty = False"

you can text to see if the record was actually saved with

If Me.Dirty = True Then
Click to expand...

can u giveme me an example ??????
coz i realy bad in english....
for other example from u, i can understand it. but this one i can't
understand
can u please give me example???
thank you so much

Ps: my name is Umar
 
S

strive4peace

Hi Umar,

"can u giveme me an example ?"

more important that you understand the logic

if me.dirty = true
then there are changes that have not been saved

if, after you TRY to save it and it is still not saved, then it is, of
course, not saved <smile> -- this means that the record failed the
validation and you should not proceed as if everything is ok



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
A

all21 via AccessMonster.com

dear miss crystal....
thanks...............
i understand now the logic.
but, my case is, the record wont go to new record.........
it saved now, but it wont go to a new record.....
what the solution for it????
thanks before........
and i'm sorry if i make u so busy...........
 
S

strive4peace

Hi Umar,

"it wont go to a new record"

is the form AllowAdditions property set to true?

are you using a query for the RecordSource? If so, what is the SQL for
the query?
(from the query design --> View, SQL)

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 
A

all21 via AccessMonster.com

strive4peace said:
is the form AllowAdditions property set to true?
Click to expand...
Yes The form AllowAdditions property is set true
are you using a query for the RecordSource? If so, what is the SQL for
the query?
(from the query design --> View, SQL)
Click to expand...
No i only use a table for the RecordSource the table name is tbpelunasanutang
the structure is:
|Field Name |Data Type |
|NoPembayaran |Text |
|Tanggalbyr |Date/Time |
|Notransaksi |Text |
|KodeSupplier |Text |
|NamaSupplier |Text |
|Tanggaltrx |Date/Time |
|TotalQty |Number |
|TotalUtang |Number |
|TotalByr |Number |
 
S

strive4peace

Hi Umar,

maybe the problem is not that it won't go to a new record, but you can't
get off the one you are on...

go to the table design

If you have any fields set to REQUIRED = Yes, make it No

if you have foreign keys in the table, make sure there is no
DefaultValue (Access sets a default value of zero (0) for all numeric
fields)

if any fields are set up with 'comobox' as the Display Control*, set it
to textbox

* Lookup tab of the properties on the bottom of the screen

The Evils of Lookup Fields in Tables
http://www.mvps.org/access/lookupfields.htm

check each property that is set in the table design for each field --
perhaps there is a problem. Is the record you are editing being saved?

did you read Access Basics?


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
:) have an awesome day :)
*
 

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

Need error help 4
Error 2501 - The OpenForm action was cancelled 2
The RunCommand action was cancelled(Run-time error '2501') 1
RunCommand action was canceled 3
Filtered records 3
SendObject Action Message 1
Error 2046 Save Record isn't Avail now 0
The OpenForm action was canceled 4

Top