Solved

How to fix error 2455

Posted on 2013-06-20
22
882 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now