Array Merge runs into a "Only user-defined type defined in public..." error

[p4willi]

I've defined two arrays in a Module called PubVars

Public Type LinesRec 
    CORRECT As Integer
    QUESTION As String
    PROC As Integer
    PAY As Integer
End Type

Public Lines_Array() As LinesRec

Public Type HeaderRec 
    CORRECT As Integer
    QUESTION As String
    PROC As Integer
    PAY As Integer
End Type

Public Hdr_Array() As HeaderRec

I'm trying to merge the two arrays in my main form using the ArrayMerge sub in the manner below:

 ArrayMerge Hdr_Array, Lines_Array

and run into this error "Only user-defined type defined in public object modules can be coerced to or from a variant.....".

How best can I merge these arrays?

This sub I got off the web and am posting for reference below:

Public Sub ArrayMerge(SourceArray As Variant, _
  DestArray As Variant, Optional KillSource As Boolean = False)

'MERGES TWO ARRAYS
'SourceArray is appended to end of DestArray
'If KillSource is set to true, then SourceArray
'is erased following the merge

'EXAMPLE

'Dim i(2) As Integer
'Dim j() As Integer
'Dim iCtr As Integer
'
'ReDim j(2) As Integer
'i(0) = 4
'i(1) = 5
'i(2) = 6
'j(0) = 1
'j(1) = 2
'j(2) = 3
'ArrayMerge i, j
'
'For iCtr = 0 To UBound(j)
' Debug.Print j(iCtr) 'Outputs 1 2 3 4 5 6
'Next
   
Dim l As Long, lngPos As Long, lngTemp As Long
Dim lngUboundSource As Long
Dim lngLBoundSource As Long
Dim lngUboundDest As Long


If (Not IsArray(SourceArray)) Or (Not IsArray(DestArray)) _
  Then Exit Sub

lngLBoundSource = LBound(SourceArray)
lngUboundSource = UBound(SourceArray)
lngUboundDest = UBound(DestArray)
lngTemp = lngUboundSource - lngLBoundSource + 1

lngPos = UBound(DestArray) + 1

ReDim Preserve DestArray(LBound(DestArray) To _
    UBound(DestArray) + lngTemp)

For l = lngUboundDest To lngPos Step -1
   DestArray(l + lngTemp) = DestArray(l)
Next

lngUboundSource = lngPos + lngTemp - 1

For l = lngPos To lngUboundSource
   DestArray(l) = SourceArray(l - lngPos)
Next

If KillSource = True Then Erase SourceArray
End Sub

[Guido Geurs]

I hope this will help: see attachment.

PS: no need to define two times the same TYPE for different array's.

[p4willi]

I hope this will help: see attachment.

PS: no need to define two times the same TYPE for different array's.

This might be of great help to me. Thanks for it. But having received and extracted the zip file containing 4 files:
Form1.frm,
Project1.vbp,
Project1.vbw
Pubvars.bas

how do I open and run this on ms access 2007 to see how it works?

Please help with this.

Thanks

[Guido Geurs]

Sorry, but I did not know this was for Access and I have no experience with Access!.
My example is in VB6 and these are VB6 files!
Next are the codes.
I hope You can deduce the mechanism from it and bring it in practice in Your program.

the module "Pubvars" =

Public Type LinesRec
    CORRECT As Integer
    QUESTION As String
    PROC As Integer
    PAY As Integer
End Type
  
Public Lines_Array() As LinesRec
  
Public Hdr_Array() As LinesRec
  
Public Dest_Array() As LinesRec

the form:

Private Sub Command1_Click()
'populate the arrays
Dim i As Integer
   ReDim Lines_Array(0)
   For i = 1 To 10
      ReDim Preserve Lines_Array(UBound(Lines_Array) + 1)
      With Lines_Array(UBound(Lines_Array))
         .CORRECT = i
         .PAY = i * 10
         .PROC = i * 100
         .QUESTION = "LQ" & i
      End With
   Next
   Text1.Text = ""
   For i = LBound(Lines_Array) To UBound(Lines_Array)
      With Lines_Array(i)
         Text1.Text = Text1.Text & _
            .CORRECT & " # " & _
            .PAY & " # " & _
            .PROC & " # " & _
            .QUESTION & vbCrLf
      End With
   Next
   ReDim Hdr_Array(0)
   For i = 1 To 10
      ReDim Preserve Hdr_Array(UBound(Hdr_Array) + 1)
      With Hdr_Array(UBound(Hdr_Array))
         .CORRECT = i
         .PAY = i * 100
         .PROC = i * 1000
         .QUESTION = "HQ" & i
      End With
   Next
   Text2.Text = ""
   For i = LBound(Hdr_Array) To UBound(Hdr_Array)
      With Hdr_Array(i)
         Text2.Text = Text2.Text & _
            .CORRECT & " # " & _
            .PAY & " # " & _
            .PROC & " # " & _
            .QUESTION & vbCrLf
      End With
   Next
End Sub

Private Sub Command2_Click()
Dim i As Integer
   ReDim dest_array(0)
   For i = LBound(Lines_Array) To UBound(Lines_Array)
      ReDim Preserve dest_array(UBound(dest_array) + 1)
      With Lines_Array(i)
         dest_array(UBound(dest_array)).CORRECT = .CORRECT
         dest_array(UBound(dest_array)).PAY = .PAY
         dest_array(UBound(dest_array)).PROC = .PROC
         dest_array(UBound(dest_array)).QUESTION = .QUESTION
      End With
   Next
   For i = LBound(Hdr_Array) To UBound(Hdr_Array)
      ReDim Preserve dest_array(UBound(dest_array) + 1)
      With Hdr_Array(i)
         dest_array(UBound(dest_array)).CORRECT = .CORRECT
         dest_array(UBound(dest_array)).PAY = .PAY
         dest_array(UBound(dest_array)).PROC = .PROC
         dest_array(UBound(dest_array)).QUESTION = .QUESTION
      End With
   Next
   Text3.Text = ""
   For i = LBound(dest_array) To UBound(dest_array)
      With dest_array(i)
         Text3.Text = Text3.Text & _
            .CORRECT & " # " & _
            .PAY & " # " & _
            .PROC & " # " & _
            .QUESTION & vbCrLf
      End With
   Next
End Sub

How it works:

with command1 (populate):
- populate 2 test arrays with numbers and a text.
- dumps the array into a Textbox for verification.

with command2 ( merge):
- merges the 2 test arrays into a 3e array (Dest_array)
- dumps the dest_array into a textbox as verification.

I have attached an .EXE of the program !

PS: You can read the code of the module (.bas) and the program (.frm) with a text editor (notepad or wordpad)

[p4willi]

Ggeu,
Thank you for sending me the exe. It helped seeing it run and I think I will be able to incorporate it into my Access program.
This will help me tremendously in merging my arrays before inserting into the table - thanks.

Now, since Lines_Array and Hdr_Array use the same piece of code to do the same operation in both Command1_Click & 2 , how does one pass just the array's names one after another to reuse the code to do the same operation over and over again. This will come in handy for me as I may potentially have many arrays defined doing the same type of operation again and again.

Also, how would I finally erase (destroy) the array after completing the save operation to start afresh.

Thanks

[Guido Geurs]

It's possible to reuse the same command lines by using the statement "With...End With".

normally the command lines are:

         Text1.Text = Text1.Text & _
            Lines_Array(i).CORRECT & " # " & _
            Lines_Array(i).PAY & " # " & _
            Lines_Array(i).PROC & " # " & _
            Lines_Array(i).QUESTION & vbCrLf

by just using "With...End With" , all the names of the identity can be deleted.

so the code can be reused for different identities like:

   For i = LBound(Lines_Array) To UBound(Lines_Array)
      With Lines_Array(i)
         Text1.Text = Text1.Text & _
            .CORRECT & " # " & _
            .PAY & " # " & _
            .PROC & " # " & _
            .QUESTION & vbCrLf
      End With
   Next

It's even possible to centralize the code in a function (see attachment) and call the function with the vars needed like array_name and textbox_name.

To clear a array: use=

Erase Dest_Array

[p4willi]

Ggeu,
Brilliant.... and thanks... this will help me immensely.