Solved accmddeleterecord not working (1 Viewer)

kevnaff

Member
Local time
Today, 22:25
Joined
Mar 25, 2021
Messages
141
Hi All.

I've updated my DB from Office 2010 to 365 and the accmddeleterecord is no longer working.

When a user creates a record for a device, code runs to check whether there is already an outstanding record for the same device. If there is an outstanding job, the user is then prompted whether to abort the current job.

This is done using the following code:

Code:
Dim db As Database
Dim REC As Recordset
Dim Response, Message, Style, Title
Dim TotalRecords As Integer
Dim n As Integer
Dim strJobNo As String
Dim strCodeNo As String
Dim strSql As String
Dim strJobStatus As String

'Disregard Code Number = NONE
strCodeNo = Me![Code No]
If strCodeNo = "NONE" Then GoTo Exit_Code_BeforeUpdate

' Count duplicate records
strSql = "SELECT * From JobsNotDone WHERE [Code No] = " & "'" & Me![Code No] & "'"
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSql, dbOpenSnapshot)
TotalRecords = REC.RecordCount
If TotalRecords = 0 Then REC.Close: GoTo Exit_Code_BeforeUpdate
REC.MoveLast
TotalRecords = REC.RecordCount
strJobNo = REC("Job No")
strJobStatus = REC("Job Status")
REC.Close

HandleDuplicates:
Message = "This Device is already booked on the system as Job No " & strJobNo & "      Job Status is: " & strJobStatus
Title = "Job Duplicate Warning"
Response = MsgBox(Message, vbAbortRetryIgnore, Title)
If Response = vbIgnore Then GoTo Exit_Code_BeforeUpdate
If Response = vbRetry Then GoTo Exit_Retry
RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "AssignmentLedgerEdit", acSaveNo

Exit_Code_BeforeUpdate:
Exit Sub


Exit_Retry:
Code.SetFocus
Code = "NONE"
SendKeys "+{TAB}", False
Exit Sub

When I run the code I get a message stating "Run-time error 2046 - The command or action 'DeleteRecord' isn't available now."

When I debug the code it is only the following part that it is unhappy with:

RunCommand acCmdDeleteRecord


I have read through a few threads and found somebody suggesting the following solution:

CurrentDb.Execute "DELETE FROM table WHERE rowID = " & me.RowID

My table is called Assignment Ledger 2
My RowID is called Job No

Therefore I replaced RunCommand acCmdDeleteRecord with CurrentDb.Execute "DELETE FROM Assignment Ledger 2 WHERE Job No = " & me.Job_No

This then gives me the following message - Run-time error 3131: Syntax error in FROM clause.

Does anybody have any experience with this?

Thanks all
 

Minty

AWF VIP
Local time
Today, 22:25
Joined
Jul 26, 2013
Messages
10,355
You have a lot of code there to handle something that could easily be achieved by a simpler check with a DCount and no need for the recordset.

Your first problem is probably caused by the record not being saved when you run the initial check, you can't delete the record before it is saved. Older versions of access were more tolerant of certain things.

Your second problem with the where clause is because of the spaces in your table and field name;

DELETE FROM [Assignment Ledger 2] WHERE [Job No] = " & me.Job_No

This is why we always tell people not to put spaces in object names...
And this also assumes that Job_No is a number, not text.
 

June7

AWF VIP
Local time
Today, 14:25
Joined
Mar 9, 2014
Messages
5,423
Assignment Ledger and Job No have space so need to surround with [ ] - [Job No].

Strongly advise not to use spaces nor punctuation/special characters in naming convention.

And I see Minty already responded.

Certainly can't delete a record that hasn't been committed to table. Record is committed when: 1) close table/query/form or 2) move to another record or 3) run code to save.

What you should probably do is abort the new record creation. Try Me.Undo
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Jan 23, 2006
Messages
15,364
As others have advised, Access has some restrictions when using field and object names with embedded spaces.
Also, you may find this Order of Events link helpful.
 

kevnaff

Member
Local time
Today, 22:25
Joined
Mar 25, 2021
Messages
141
Hi All.

The code was written more than 20 years ago so that may be why it is so complicated. I have taken the database over in the last year.

What I have decided to do to make the process a little easier to understand for myself, is to do the following:

Create a non-visible command button to delete a record.
Replaced the docmd accmddeleterecord with me.cmd.visible=true
This follows the same path as the old code, but now makes the delete record button visible. The user than has to manually click the delete record command button.

There are probably other ways to do this, but this is on that I can get my head around for now.

Thanks all.
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 18:25
Joined
Apr 27, 2015
Messages
6,286
I have taken the database over in the last year.
Welcome to the "Land o' Suck"! A common tale told on this forum - dont get discouraged! I came here absolutely clueless and now...well, I'm STILL clueless but the only people who know this are people on this forum...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2002
Messages
42,976
The logic of this escapes me. WHY would the record have been saved in the first place if it was a duplicate? You cut off the procedure header so I can't tell if the code is in an event procedure and which one.

The actual solution is to use the BeforeUpdate event of [Code No]. You still need validation code in the form's BeforeUpdate event but this will stop the data entry before the user wastes a lot of time.

Code:
[Code No] If DCount("*", "tablename", "[Code No]= " & "'" & Me.[Code No] & "'") > 0 Then
    Msgbox "Code No already exists and cannot be added again.", vbOKOnly
    Cancel = True
    '' you might want Me.[Code No].Undo but I don't like deleting data.  The user may have just mistyped a single character
    Exit Sub
End If

Proper validation means the bad data never gets saved in the first place and so you don't have to delete it later.

I'm guessing that this database has lots of bad data since the validation does not seem to be in rational places.
 

kevnaff

Member
Local time
Today, 22:25
Joined
Mar 25, 2021
Messages
141
The logic of this escapes me. WHY would the record have been saved in the first place if it was a duplicate? You cut off the procedure header so I can't tell if the code is in an event procedure and which one.

The actual solution is to use the BeforeUpdate event of [Code No]. You still need validation code in the form's BeforeUpdate event but this will stop the data entry before the user wastes a lot of time.

Code:
[Code No] If DCount("*", "tablename", "[Code No]= " & "'" & Me.[Code No] & "'") > 0 Then
    Msgbox "Code No already exists and cannot be added again.", vbOKOnly
    Cancel = True
    '' you might want Me.[Code No].Undo but I don't like deleting data.  The user may have just mistyped a single character
    Exit Sub
End If

Proper validation means the bad data never gets saved in the first place and so you don't have to delete it later.

I'm guessing that this database has lots of bad data since the validation does not seem to be in rational places.

Hi Pat,

We have around 15000 devices in our database, some of which require a yearly service. When a device reaches within 2 weeks of its yearly service, a record (job as we call it) is automatically created with various information. This speeds up the process of filling in the record. Also it acts as a reminder to service the device. If you were to create a record and enter the devices unique Code No, if the device has not yet been serviced, the message would pop up stating that there is already an outstanding job for this device, and ask the user whether they would like to continue with the job, or whether they would like to abort it, this deletes the record. The user would then type the devices unique Code No in to the job checker which would filter to the device's outstanding job.

Hopefully this makes sense. As I've only recently taken over it, I hope to weed out these instances over time.

I will definitely look in to using the before update function. To be honest the user should always use the duplicate job checker tool before adding a record, so there is a tool in place already to stop the data entry, but it's dependent on the user physically doing this.

Thanks
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2002
Messages
42,976
Creating the SECOND record BEFORE checking for a duplicate is the problem. The SECOND record should never have been saved in the first place. You are always in complete control over what gets saved if you use the proper form level events. The requirement to check for a duplicate should not fall to the user. This is very simply done by the application. In fact, not only can you check for duplicates, you can cancel the update and position the form to the existing record so the user can continue with that. Of course since the users have been working with this method for 20 years so they probably think it is normal.

Not sure why you would give the user the option to continue using the duplicate record. If he does, what happens to the original? Does it stay open forever or does someone eventually delete or close it?
 

kevnaff

Member
Local time
Today, 22:25
Joined
Mar 25, 2021
Messages
141
Hi Pat,

I think I may have used the wrong terms to describe what happens. When I say duplicate record I mean, a new record with its own unique job number (Autonumber primary key) but with the same Code No. In theory there should never be 2 records that are not complete, with the same Code No.

A scenario where the user may decide to ignore the auto generated record is the following:

The device's service was due in December 2021. The device has been brought to us in January 2022, because it is faulty, but not for a service. The department who bring the device might insist that they need the device urgently, therefore a service cannot be performed at this time. In this case, the auto generated job, has a job category of planned maintenance. The user can ignore the auto generated job, and create a new record with the category of a routine breakdown. The user could just change the category of the auto generated job, from planned maintenance to routine breakdown, but this doesn't always happen.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:25
Joined
Feb 19, 2002
Messages
42,976
I understand the problem.

The fact is, allowing the second record to save is what is wrong. You should never need to delete that record because you should never have allowed it to be saved with a duplicate Code No. If the duplication is defined by two fields - CodeNo and Category, then check both fields in the dlookup(). It is up to you to prevent bad (duplicate) data from ever being saved, not to delete it later. Because you need two fields, you can't use the CodeNo control's BeforeUpdate event. You will need to use the Form's BeforeUpdate event although you can give the user a pre-warning in the CodNo control's BeforeUpdate event but let him continue and finish the edit later.
 

Users who are viewing this thread

Top Bottom