Solved

How to fix error 2455

Posted on 2013-06-20
22
932 Views
Last Modified: 2013-07-20
Hi ,
I have attached screenshots of the error i`m facing when click on button in a form (which have main form + a subform (in datasheet view) that have another subform inside it with datasheet view.
I have to mention is that to perform recordset in the 2 subform  i have first to do datasheet expanded to be able to run the recordset for the second subform.
also when i have the error the click debug and step by F8 it works.
i tried to do a sleep (though it was that the process couldn`t found the records but access didn`t expanded the records) it`s doesn`t help.
error1.gif
error1Screen.gif
0
Comment
Question by:drtopserv
  • 15
  • 7
22 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39264274
Hi,

that's really hard to say only having these screenshot. Your code is in "cmdFillInCells" in the Click event - this code should not run if you expand a subdatasheet with the [+] sign. Moreover you should always keep in mind that "RecordsetClone" is a duplicate of the form's recordset which is directly bound to the form like the form's recordset (only the bookmark is independent). If you want to have a real copy of the form's recordset you should use "Recordset.Clone" instead.

But to check where the problem is you should upload your database (a demo database with only the needed forms/code/tables/queries and with some anonymous demo data showing the problem).

Cheers,

Christian
0
 

Author Comment

by:drtopserv
ID: 39264283
0
 

Author Comment

by:drtopserv
ID: 39264305
Well...u have to choose from the combobox as in the screenshots (money-condition:not counted), and amount of 14 for instance.
samplev3.0NotWorkingClean.accdb
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39264403
Hi,

first you need to add "Nz" to your filter string, otherwise it crashes when trying to access an empty field:

Me.sfmList.Form.Filter = "[PackageType] = " & CLng(Nz(Me.cboPackageType, 0)) & " And [CurrencyValue] = " & CLng(Nz(Me.cboCurrencyValue, 0)) & " And [ConditionID]=" & CLng(Nz(Me.cboConditionID.Column(0), 0))

Open in new window


Next is that "rs1" is not used in the procedure, you can also directly use this:

Set rst2 = Me.sfmList.Form!cldsfmList.Form.RecordsetClone

Open in new window


Makes no sense to use "OpenRecordset" as this is used to start a query based on a defined SQL string. RecordsetClone IS an (always) open recordset of the form, like the Recordset.

Then it seems that you try to insert data using recordsets of subforms - why ?
You are in a database, you don't need to do complicate VBA scripts to access these data - simply write UPDATE-queries which directly access the needed data so you don't have troubles with this code (and the result code would be much shorter).

The problem here is as far as I can see: The filter removes any entry from the list and so the subform doesn't exist at this time. I used the debugger - it does also not work if I try to do a single step (would have wondered if this would be the case).

You could of course simply use an "On Error Resume Next" directly before this line and then check with "If Err.Number <> 0 Then..." (don't forget to disable with "On Error Goto 0"). In this case the error would not appear and you can decide in the code what to do if the subform doesn't exist.

As I always say: Think SQL, not VBA. Whenever you can reach your goal with performing a SQL command, do it on this way. You really need recordsets only in very few situations.

BTW: Your filter will only work if you enter data in ANY field of the filterstring (because of the "AND"). I'm not sure if that is what you really want to achieve.

Cheers,

Christian
0
 

Author Comment

by:drtopserv
ID: 39264427
Wow,from what u are asking, i need to change the whole apps for that!.
which is really hard for ME to do:}}
I`ll be so happy if u make maybe the changes to mensioned in the related places,hope maybe this could solve the problem?
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39264451
Hi,

unfortunately I have no time anymore for today, if you can wait until tomorrow I'll see what I can do for you.

Cheers,

Christian
0
 

Author Comment

by:drtopserv
ID: 39264460
wow,perfect bro, yes i`ll be waiting..
plz may a tiny Q, how can i say dsum field in table only if a checkbox is true?
0
 

Author Comment

by:drtopserv
ID: 39264469
btw it checkboxed in a contuios form and still didn`t get the afterupdate.
0
 

Author Comment

by:drtopserv
ID: 39264480
i`ll open new Q in a min
0
 

Author Comment

by:drtopserv
ID: 39264486
ID: 28163442
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39267386
Hi,

sorry, but after working a while with your code I must say that it is nearly impossible to rework this code as it is full of errors, compiler errors and code that makes no sense for me.
It would need hours to rework this code, also because I don't know anything about the background of this database and what you try to achieve with all these buttons - that's far more time than I have.

Some things can be made simpler like updating the seed value with a public function like this:

Public Function UpdateSeed() As Long
    Dim lngSeed As Long
    Dim db As DAO.Database
    Set db = CurrentDb

    lngSeed = Nz(DLookup("SeedNumber", "tblSeed"), -1)

    If lngSeed = 99999 Then
        MsgBox "Document seed has reached limit. Cannot create seed.  Please contact administrator.", vbCritical, "Warning"
        UpdateSeed = -1
    Else
        db.Execute "UPDATE tblSeed SET SeedNumber = SeedNumber + 1"
        UpdateSeed = lngSeed
    End If
End Function

Open in new window


This would return the current seed value, update the seed and return -1 in case of an error.

This example should show you how you can exchange recordset operations with SQL commands and with also much better performance.

But at least I've found the reason why the recordset 2455 error of your initial question didn't work: The subform contains a subform "cldsfmList" and this is not based on a form but on the table directly.

This means: You do not have a form in the subform container - and so you do not have a recordset or a recordsetclone and this is why this command fails.

You can solve that by creating a form for the table tblCellProp as datasheet view or continous form. Then use this form in the subform container "cldsfmList", then this row works. But that does not solve the multiple other problems like for example that the recordset runs in a Null problem in the row "C = rst!CellCapacity / rst!PackageType - Nz(rst!Quantity, 0)" when PackageType is Null.

At least your initial question should be answered now.

Cheers,

Christian
0
 

Author Comment

by:drtopserv
ID: 39268006
well bro, thnx ALOT for your response.
i have tried your suggestion for make a subform exactly in the steps you said for the "cldsfmList"
and still have same error 2455, that as i said before when i run a debug and step (f8) in the code it keep going and work oK.
0
 

Author Comment

by:drtopserv
ID: 39268007
it`s i think an issue of a delay , that i should perform .. which is not that good for waiting 5 sec. for that.
any suggestions/idea?
0
 

Author Comment

by:drtopserv
ID: 39268029
By the way, maybe i`m missing something, i`ll be glad if you send me your fixed sample , I want to try it in my side, my fix not works, i have access 2010
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 39268085
Hi,

you're right, when I started your sample database today the error comes up again. Two further reasons are: The code is not compiled and so doesn't run with optimal speed, moreover Access sometimes creates strange errors if the code is not compiled. You should ALWAYS add "Option Explicit" at the beginning of each module and compile your code until no error appears. I've made that in the attachment and disabled any sub which contains errors (most often because you created these forms by copy/paste and forgot to remove event handlers where the controls doesn't exist anymore).

But the real reason is (which I didn't noticed before) that you used this line directly before the problematic row: Me.sfmList.Form.Refresh

This will reload all the records which are currently displayed in the form and as you also have a subform for each record, also they are refreshed. That takes a little time and you get access to the recordsetclone only after it has finished the refresh. Access works with "Lazy Loading", means: If you load a recordset into a form it will return to the code when the first page is visible on the screen and then load the rest in the background (which is the reason why "RecordCount" will not return the right value if you read it out directly after opening a recordset and you need "MoveLast" to force a fast download of records into the recordset). The refresh is not forcably needed here, even not before you access the RecordsetClone so I moved it after this row and it works. But keep in mind that in long recordsets also the setting of the filter which also comes before this row can take a lot time so maybe the error comes up again later. To avoid this I added a "MoveLast"/"MoveNext" before, so now it should always work.

Cheers,

Christian
samplev3.0NotWorkingClean2.accdb.zip
0
 

Author Comment

by:drtopserv
ID: 39329306
Hi Bitsqueezer ,
Sorry for delay in reply, Firstly thnx alot for your effort to help out, your comment was import to know for me .
I have tried your cleaned sample, but fortunately Still having the same problem with same massege.
try to fill in quantity=14 as example with "not counted" , current value=20
it will give you again same problem :{{
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39331204
Hi,

sorry, but I can't reproduce the error so I'm afraid I don't know what I can do for you now.
0
 

Author Comment

by:drtopserv
ID: 39331235
Oh, didn`t understand, you want to tell me from your side it`s working with no problem?
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39331344
Yes, if we do really the same...
0
 

Author Comment

by:drtopserv
ID: 39331386
oH so weird,are you running access 2010?
anyway, you did your best to help bro:}
you deserve 1000 points:}}}}}
0
 

Author Comment

by:drtopserv
ID: 39341780
BillDenver, thnx alot, YOUR comments helps me to solve the problem,which was to expand all the subforms on open event, then the code works fine.
thnx ALOT.

btw, i have a new Q posted about the same file, but related to deletion recordset, I hope you have a little time to review the Q , (thnx in regard:})
0
 

Author Closing Comment

by:drtopserv
ID: 39341781
thxn alot.:} brilliant! man
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question