Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

How to fix error 2455

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
drtopserv
Asked:
drtopserv
  • 15
  • 7
1 Solution
 
BitsqueezerCommented:
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
 
drtopservAuthor Commented:
0
 
drtopservAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
BitsqueezerCommented:
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
 
drtopservAuthor Commented:
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
 
BitsqueezerCommented:
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
 
drtopservAuthor Commented:
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
 
drtopservAuthor Commented:
btw it checkboxed in a contuios form and still didn`t get the afterupdate.
0
 
drtopservAuthor Commented:
i`ll open new Q in a min
0
 
drtopservAuthor Commented:
ID: 28163442
0
 
BitsqueezerCommented:
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
 
drtopservAuthor Commented:
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
 
drtopservAuthor Commented:
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
 
drtopservAuthor Commented:
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
 
BitsqueezerCommented:
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
 
drtopservAuthor Commented:
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
 
BitsqueezerCommented:
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
 
drtopservAuthor Commented:
Oh, didn`t understand, you want to tell me from your side it`s working with no problem?
0
 
BitsqueezerCommented:
Yes, if we do really the same...
0
 
drtopservAuthor Commented:
oH so weird,are you running access 2010?
anyway, you did your best to help bro:}
you deserve 1000 points:}}}}}
0
 
drtopservAuthor Commented:
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
 
drtopservAuthor Commented:
thxn alot.:} brilliant! man
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 15
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now