Run time error 2450 when populating an array, help please.

[mbarbier101]

Hey everyone, I'm new to coding and trying to create a save and load feature for my form. I've been having some trouble with populating a simple array. Somehow, the array will only populate if the related forms are open in design view. Otherwise, i get run time error 2450. :banghead:

Here is what i've written so far.

Public Sub Define_Arrays()

'DEFINING OBJECT ARRAYS

Dim ProductCHK_Aobj(1 To 50) As Object
Dim ProductCOD_Aobj(1 To 50) As Object
Dim ProductUNI_Aobj(1 To 50) As Object
Dim ProductVAL_Aobj(1 To 50) As Object

For i = 1 To 10
Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("chk_p_" & i)
Set ProductCOD_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("cbo_cod_" & i)
Set ProductUNI_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("txt_uni_" & i)
Set ProductVAL_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("txt_PrecioUnitario_" & i)
Next i

For i = 11 To 20
Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_11_20").Controls("chk_p_" & i)
Set ProductCOD_Aobj(i) = Forms("subfrm_Cotizacion_11_20").Controls("cbo_cod_" & i)
Set ProductUNI_Aobj(i) = Forms("subfrm_Cotizacion_11_20").Controls("txt_uni_" & i)
Set ProductVAL_Aobj(i) = Forms("subfrm_Cotizacion_11_20").Controls("txt_PrecioUnitario_" & i)
Next i

For i = 21 To 30
Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_21_30").Controls("chk_p_" & i)
Set ProductCOD_Aobj(i) = Forms("subfrm_Cotizacion_21_30").Controls("cbo_cod_" & i)
Set ProductUNI_Aobj(i) = Forms("subfrm_Cotizacion_21_30").Controls("txt_uni_" & i)
Set ProductVAL_Aobj(i) = Forms("subfrm_Cotizacion_21_30").Controls("txt_PrecioUnitario_" & i)
Next i

For i = 31 To 40
Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_31_40").Controls("chk_p_" & i)
Set ProductCOD_Aobj(i) = Forms("subfrm_Cotizacion_31_40").Controls("cbo_cod_" & i)
Set ProductUNI_Aobj(i) = Forms("subfrm_Cotizacion_31_40").Controls("txt_uni_" & i)
Set ProductVAL_Aobj(i) = Forms("subfrm_Cotizacion_31_40").Controls("txt_PrecioUnitario_" & i)
Next i

For i = 41 To 50
Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_41_50").Controls("chk_p_" & i)
Set ProductCOD_Aobj(i) = Forms("subfrm_Cotizacion_41_50").Controls("cbo_cod_" & i)
Set ProductUNI_Aobj(i) = Forms("subfrm_Cotizacion_41_50").Controls("txt_uni_" & i)
Set ProductVAL_Aobj(i) = Forms("subfrm_Cotizacion_41_50").Controls("txt_PrecioUnitario_" & i)
Next i

'DEFINING VALUE ARRAYS

Dim ProductCHK_Aboo(1 To 50) As Boolean
Dim ProductCOD_Astr(1 To 50) As String
Dim ProductUNI_Aint(1 To 50) As Integer
Dim ProductVAL_Adbl(1 To 50) As Double

For i = 1 To 50
ProductCHK_Aboo(i) = ProductCHK_Aobj(i)
ProductCOD_Astr(i) = ProductCOD_Aobj(i)
ProductUNI_Aint(i) = ProductUNI_Aobj(i)
ProductVAL_Adbl(i) = ProductVAL_Aobj(i)
Next i





End Sub

 

[Ranman256]

There's no reason to use arrays from 1970 when you have tables and collections.

 

[CJ_London]

what is the description of error 2450 - and on what line does it occur?

I'm surprised it even works if the form is in design view, the form needs to be open and populated with data.

 

[mbarbier101]

what is the description of error 2450 - and on what line does it occur?

I'm surprised it even works if the form is in design view, the form needs to be open and populated with data.

It happens inside of the for loops.

 

[CJ_London]

you are not answering my questions - cannot help if you don't give the answers

 

[arnelgp]

I think arrays cannot contain objects. Use collection or dictionary.

 

[mbarbier101]

you are not answering my questions - cannot help if you don't give the answers

Sorry, I'm kind of an amateur noob. Learning by myself and all.

The error reads:


Run-time error '2450'

Microsoft Access cannot find the reference from 'subfrm_Cotizacion_1_10'.

It occurs in the first for loop

 

[mbarbier101]

I think arrays cannot contain objects. Use collection or dictionary.

Ty, will try to learn to use Collections and Dictionary.

 

[MajP]

Not really sure what you are doing, but arrays can hold objects. Here is a simple test

Private Sub Command12_Click()
Dim i As Integer

Dim myControls(1 To 5) As Object
Dim ctrl As Access.Control
For Each ctrl In Me.Controls
i = i + 1
Debug.Print i & " " & ctrl.Name
Set myControls(i) = ctrl
Debug.Print myControls(i).Name & " arr"
Next ctrl
End Sub

With that said, collections are nice since you have the key property. Also that error can happen when you call an object by a bad name. So I doubt it has to do with loading your array and instead it cannot find that object.

 

[CJ_London]

your form needs to be open and in its own window. From it's name it would appear to a subform to another form.

See this link about how to reference objects from elsewhere
http://access.mvps.org/access/forms/frm0031.htm

 

[sonic8]

There's no reason to use arrays from 1970 when you have tables and collections.

Arrays are lean, lightweight and fast.


If you deal with a (more or less) static number of values/objects accessed only by index, there is not much to gain from using a collection instead of an array.



So, actually, there are reasons to use arrays. However, I have to concede, it is rare that these will outweigh the convenience of using collections instead.

 

[The_Doc_Man]

My concern over using arrays here is simple: Why bother? What is the purpose of storing all this stuff in an array anyway? What are you going to do with it when everything has been stored? If this is a mental exercise for learning, then go for it and learn! That's always a good thing. But if this is being done for a business purpose, this does not seem to be an efficient or effective way to proceed. So what is the purpose of all of these gyrations?

If Access exits, any arrays you defined are gone, so it can't be something you do as a matter of data persistence. And your subroutine doesn't do anything with the data extracted from the forms. Once you populate the arrays, nothing else happens before the end of the subroutine. OK, if this is early in the project, maybe you haven't gotten that far yet.

BUT even worse, those arrays aren't persistent within Access either. They are defined dynamically in the body of the subroutine but that doesn't mean they are defined to be permanently global in your Access workspace. They are being defined locally to that subroutine. Just because they are arrays does not mean that they automatically have a longer lifetime. The moment you hit that End Sub that follows your last "Next i", ALL LOCAL ARRAYS (and other local variables) cease to exist. They are anchored to the subroutine's call frame, and that goes "out of scope" the moment you fall through to the End Sub. The "out of scope" arrays become a fragmented part of the "heap" space of your Access instance. You can't easily (or perhaps ever) access a discarded portion of the heap due to a security principle related to "object re-use."

Further, the purpose of all this data manipulation seems pointless. If you are getting data from a bound form, why go through the middle man? Get the data directly from the tables to which everything is bound. Should be easy with a query that appears to only require four fields, particularly since all four fields (even with the same spelling) appear to repeat within the bodies of five separate sub-forms, each of which has forty controls on it. That's kind of dense.

If these four popular fields are NOT bound, then you have a huge amount of data on that form, yet it all seems to be limited in variety. You have what appears to be five sub-forms with at least ten sets of four controls on each sub-form, and the differentiation between controls of the same type is a number tacked onto the name. This is an interesting design choice that implies that SERIOUS normalization issues are present.

You've given us a bunch of code and a symptom but we still don't know what you intend to do and therefore we are all somewhat "shooting in the dark" here. The forum members offer good programming solutions and good design options. (Or at least we try to.)

I don't wish to be harsh here, but unless we can understand the purpose of all of this, we are all seeing issues that don't look like good design options. Therefore we don't understand the reasons for your choices and will thus not be able to help you much.

Now, before you feel like I really had nothing constructive to say, your problem might be easy to fix. The error says it cannot find the object referenced from that particular subform and I'm guessing it occurred in the very first line of the first For loop. You wrote something that I will put on the first line below, and will show what you SHOULD have done on the line beneath it.

Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("chk_p_" & i)

Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("chk_p_" & CStr(i))

The error is that there is no control with the name you gave as "chk_p_" & i, which is equivalent to the more formal "chk_p_" & Chr$(i). That is because in that context, you are using a name string to specify the control (and it is perfectly legal to do that), so when you concatenate something with it, that something also has to be a string. But concatenating the integer i does NOT give you '1', '2', '3', '4', ... - it gives you a control character such as STX, ETX, etc. And obviously, the name does not include a control character. Using CStr(i) converts the number 1 into the digit '1' and THAT will concatenate correctly on the name.

 

[static]

I'm new to coding and trying to create a save and load feature for my form.

Access has great saving and loading functionality already built into forms.

Heed The_Doc_Man's words.

I had a go at cleaning your code. (Untested)

Public Sub Define_Arrays()

    Dim ProductCHK_Aboo(1 To 50) As Boolean
    Dim ProductCOD_Astr(1 To 50) As String
    Dim ProductUNI_Aint(1 To 50) As Integer
    Dim ProductVAL_Adbl(1 To 50) As Double
    
    Dim strfrm As String
    
    For i = 1 To 50
        If i Mod 10 = 1 Then
            strfrm = "subfrm_Cotizacion_" & i & "_" & i + 9
            If Not CurrentProject.AllForms(strfrm).IsLoaded Then
                Err.Raise vbObjectError + 513, , "form " & strfrm & " is not open"
                Exit Sub
            End If
        End If
        
        ProductCHK_Aboo(i) = Forms(strfrm)("chk_p_" & i)
        ProductCOD_Astr(i) = Forms(strfrm)("cbo_cod_ " & i)
        ProductUNI_Aint(i) = Forms(strfrm)("txt_uni_ " & i)
        ProductVAL_Adbl(i) = Forms(strfrm)("txt_Prec ioUnitario_" & i)
    Next

End Sub

 

[The_Doc_Man]

Static, you've got the same problem. Including "i" when you need "Cstr(i)" will cause naming errors.

mbarbier101 - you need to let Access save and load things for you. That was implied in my comments about bound vs. unbound forms. BIND the forms and Access will do all of the work for you.

 

[static]

String & i will always result in the integer being changed to a string.

 

[The_Doc_Man]

Yes, but which string? Chr$(i) and CStr(i) are both strings.

 

[static]

Chr() and cstr() are two completely different things.

If you concatenate any non string datatype onto another string, or assign it to a string variable the value should be converted automatically and the results should be as expected based on Windows default formatting.

This makes no sense to me...

concatenating the integer i does NOT give you '1', '2', '3', '4', ... - it gives you a control character such as STX, ETX, etc.

 

[mbarbier101]

My concern over using arrays here is simple: Why bother? What is the purpose of storing all this stuff in an array anyway? What are you going to do with it when everything has been stored? If this is a mental exercise for learning, then go for it and learn! That's always a good thing. But if this is being done for a business purpose, this does not seem to be an efficient or effective way to proceed. So what is the purpose of all of these gyrations?

If Access exits, any arrays you defined are gone, so it can't be something you do as a matter of data persistence. And your subroutine doesn't do anything with the data extracted from the forms. Once you populate the arrays, nothing else happens before the end of the subroutine. OK, if this is early in the project, maybe you haven't gotten that far yet.

BUT even worse, those arrays aren't persistent within Access either. They are defined dynamically in the body of the subroutine but that doesn't mean they are defined to be permanently global in your Access workspace. They are being defined locally to that subroutine. Just because they are arrays does not mean that they automatically have a longer lifetime. The moment you hit that End Sub that follows your last "Next i", ALL LOCAL ARRAYS (and other local variables) cease to exist. They are anchored to the subroutine's call frame, and that goes "out of scope" the moment you fall through to the End Sub. The "out of scope" arrays become a fragmented part of the "heap" space of your Access instance. You can't easily (or perhaps ever) access a discarded portion of the heap due to a security principle related to "object re-use."

Further, the purpose of all this data manipulation seems pointless. If you are getting data from a bound form, why go through the middle man? Get the data directly from the tables to which everything is bound. Should be easy with a query that appears to only require four fields, particularly since all four fields (even with the same spelling) appear to repeat within the bodies of five separate sub-forms, each of which has forty controls on it. That's kind of dense.

If these four popular fields are NOT bound, then you have a huge amount of data on that form, yet it all seems to be limited in variety. You have what appears to be five sub-forms with at least ten sets of four controls on each sub-form, and the differentiation between controls of the same type is a number tacked onto the name. This is an interesting design choice that implies that SERIOUS normalization issues are present.

You've given us a bunch of code and a symptom but we still don't know what you intend to do and therefore we are all somewhat "shooting in the dark" here. The forum members offer good programming solutions and good design options. (Or at least we try to.)

I don't wish to be harsh here, but unless we can understand the purpose of all of this, we are all seeing issues that don't look like good design options. Therefore we don't understand the reasons for your choices and will thus not be able to help you much.

Now, before you feel like I really had nothing constructive to say, your problem might be easy to fix. The error says it cannot find the object referenced from that particular subform and I'm guessing it occurred in the very first line of the first For loop. You wrote something that I will put on the first line below, and will show what you SHOULD have done on the line beneath it.

Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("chk_p_" & i)

Set ProductCHK_Aobj(i) = Forms("subfrm_Cotizacion_1_10").Controls("chk_p_" & CStr(i))

The error is that there is no control with the name you gave as "chk_p_" & i, which is equivalent to the more formal "chk_p_" & Chr$(i). That is because in that context, you are using a name string to specify the control (and it is perfectly legal to do that), so when you concatenate something with it, that something also has to be a string. But concatenating the integer i does NOT give you '1', '2', '3', '4', ... - it gives you a control character such as STX, ETX, etc. And obviously, the name does not include a control character. Using CStr(i) converts the number 1 into the digit '1' and THAT will concatenate correctly on the name.

Thanks for your imput, I'm quite new in coding and this is the first forum I try to get help from. I've been learning and working alone from the start so I'm not used to providing the proper info. I'll try to elaborate more even though I think your last words might solve my issue.

You see, this form does not communicate with tables and querys that much. Its a form with 5 sub forms that the user can fill to create an offer for a client. So, all the fields that i am trying to extract values and strings from are unbound.

I wanted to create an array to hold the objects related to the details of every product in the offer. Then, I intend to extract the values and strings to save them on a table and then load them from said table so my end user doesn't have to rewrite the whole offer and just modify prices, units to be sold, add a discount, etc. So basically:

1. Set objects in their corresponding arrays

2. Extract the values and strings in objects and add them to a table with the offer code as the ID

3. open another form to select the offer you want to load.

4. click load and all the fields will be automatically filled with the data from the table.

I hope this makes sense to you and if not let me know if you're still interested in helping.

I'll try the tip you gave me and see how it works out, thanks for all the imput.

 

[The_Doc_Man]

You see, this form does not communicate with tables and querys that much. Its a form with 5 sub forms that the user can fill to create an offer for a client. So, all the fields that i am trying to extract values and strings from are unbound.

This is doing things the hard way. Access will automatically save stuff for you if you bind the form to a table or to multiple tables (via sub-forms). You are re-inventing a very complex wheel.

You were planning to somehow save the form content and then presumably would later reload it. Except that this is EXACTLY what Access does with bound forms, even in the case of complex parent/child (or in this case, parent/children) situations. If you don't want to put this information into your main table, make a new table for prospective business and then, when appropriate, use a query to copy data from the prospective-business table to the booked-business table.

If you want to preserve the data elsewhere than Access, look into such things as exporting to a spreadsheet (and you can import from them, too, so there is a bi-directional ability there). But seriously, just use Access features. It will be FAR easier.

Now, if your problem is how to design the process, you can ask that question here, too.

 

[The_Doc_Man]

Time for a retraction, but it means that our OP has a different problem with naming.

I tested using the immediate window on one of my pending projects and damned if "string" & integer didn't automatically convert the integer to the right digits.

The last time I did this, I got a different result, but thinking more about it, I remember I was on another (non-Windows) system a lot just before I retired.

For the record, some versions of BASIC treat "string" & integer as "string" & Chr(integer), but others treat it as "string" & CStr(integer). Obviously, VBA is in the latter category.

But that means that the original 2450 error must have been caused by either bad reference syntax or bad spelling.