Remove empty values from array

[systemx]

Hi all,

I am brand new to using arrays and think that they may present an a good method to avoid lengthy nested IF statements.

My array contains 10 values, each the contents of a textbox control on a form.

eg arrMVL = Array(Forms!MyForm!txt1.Value, Forms!MyForm!txt2.Value, ...)

I want to:

- Examine the array
- Determine which fields are NULL or ""
- Rebuild the array with only populated fields.
- Determine how many values are in the new array

I will then write this data to my table.

Could anyone provide me with any advice on how to achieve this?

Thank you

Rob

 

[boblarson]

I don't see how that is going to save on coding. It will just be different coding.

Also, just for your knowledge -

You don't need to reference

Forms!MyForm!txt1.Value

using the .Value
as this

Forms!MyForm.txt1

will give you the same result.

 

[Bat17]

I agree with Bob about the usefulness, but here is some code to give you an idea about arrays

Sub tesetit()
Dim aString()
Dim aString2
Dim x
Dim j As Integer

x = Null
aString2 = Array("dd", "FF", "", "RR", x, "£$%")

For Each x In aString2
    If Not x & "" = "" Then
        ReDim Preserve aString(j + 1)
        aString(j) = x
        j = j + 1
    End If
Next
Debug.Print UBound(aString); " Records"
For Each x In aString
  Debug.Print x
Next


End Sub

Peter

 

[systemx]

Thanks Peter & Bob for taking the time to reply. Having now completed the function - I can see that in this particular case the array probably did not make things all that much easier.

For me, it's more about finding new ways to do things so that I am not limited in my approach to problems. I could have used a nested IF statement - but feel this is an ugly way of coding (personal preference).

I have posted my solution below. Whether or not what I wanted to achieve came through in my original post I do not know!

Thanks again both for the advice. Peter - I will tkae a look at your code. Bob...notice I have fixed how I reference controls on other forms

Regards,

Rob

    Dim arrMVL As Variant
    Dim i As Integer
    Dim ADOrs As ADODB.Recordset
    Dim strSQL As String
    Dim lngCusNo As Long
    Dim intChk As Integer
    
    'Declare the array, made up of the textbox control on frmOWAComplete
    arrMVL = Array(Forms!frmOWAComplete.txtMVL1.Value, Forms!frmOWAComplete.txtMVL2.Value, _
    Forms!frmOWAComplete.txtMVL3.Value, Forms!frmOWAComplete.txtMVL4.Value, Forms!frmOWAComplete.txtMVL5.Value, _
    Forms!frmOWAComplete.txtMVL6.Value, Forms!frmOWAComplete.txtMVL7.Value, Forms!frmOWAComplete.txtMVL8.Value, _
    Forms!frmOWAComplete.txtMVL9.Value)
    
    'Select appropriate customer record from tblCustomers
    strSQL = "SELECT * FROM tblCustomers WHERE Customer_No = " & lngCusNo & ";"
    
    'Open the recordset and update the field "MVL"
    Set ADOrs = New ADODB.Recordset
    ADOrs.ActiveConnection = CurrentProject.Connection
    ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic
                
        With ADOrs
            .fields("MVL") = arrMVL(0)
            .Update
            .Close
        End With
        
        Set ADOrs = Nothing
    
    'Integer for referencing array items. arrMVL(0) has already been written to the database
    For i = 1 To 8
                
        'If the array value is not null
        If Not IsNull(arrMVL(i)) Then
        
        'Select appropriate customer record from tblMVL
        strSQL = "SELECT * FROM tblMVL WHERE Customer_No = " & lngCusNo & ";"
        
        'Open the recordset
        Set ADOrs = New ADODB.Recordset
        ADOrs.ActiveConnection = CurrentProject.Connection
        ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic
        
        With ADOrs
            'If customer record does not exist - always the case for arrMDL(1) - create a new record
            If .BOF And .EOF Then
                .AddNew
                .fields("Customer_No") = lngCusNo
                .fields("MVL2") = arrMVL(i)
                'Use intChk to determine the next field to write to
                intChk = 3
                .Update
                .Close
            Else
                'Record exists, update the next empty field
                Dim MyFld As String
                'Name of field is MVL followed by a number (intChk)
                MyFld = "MVL" & intChk
                'Update field from the array
                .fields(MyFld) = arrMVL(i)
                'Increment intChk by one
                intChk = intChk + 1
                .Update
                .Close
            End If
        End With
        
        Set ADOrs = Nothing
        
        End If
    Next i

 

[boblarson]

    'Declare the array, made up of the textbox control on frmOWAComplete
    arrMVL = Array(Forms!frmOWAComplete.txtMVL1.Value, Forms!frmOWAComplete.txtMVL2.Value, _
    Forms!frmOWAComplete.txtMVL3.Value, Forms!frmOWAComplete.txtMVL4.Value, Forms!frmOWAComplete.txtMVL5.Value, _
    Forms!frmOWAComplete.txtMVL6.Value, Forms!frmOWAComplete.txtMVL7.Value, Forms!frmOWAComplete.txtMVL8.Value, _
    Forms!frmOWAComplete.txtMVL9.Value)

Should be this:

    'Declare the array, made up of the textbox control on frmOWAComplete
    arrMVL = Array(Forms!frmOWAComplete.txtMVL1, Forms!frmOWAComplete.txtMVL2, _
    Forms!frmOWAComplete.txtMVL3, Forms!frmOWAComplete.txtMVL4, Forms!frmOWAComplete.txtMVL5, _
    Forms!frmOWAComplete.txtMVL6, Forms!frmOWAComplete.txtMVL7, Forms!frmOWAComplete.txtMVL8, _
    Forms!frmOWAComplete.txtMVL9)

 

[Bat17]

With out the array you could shorten to

Dim i As Integer
    Dim ADOrs As ADODB.Recordset
    Dim strSQL As String
    Dim lngCusNo As Long
    Dim intChk As Integer
    
    
    'Select appropriate customer record from tblCustomers
    strSQL = "SELECT * FROM tblCustomers WHERE Customer_No = " & lngCusNo & ";"
    
    'Open the recordset and update the field "MVL"
    Set ADOrs = New ADODB.Recordset
    ADOrs.ActiveConnection = CurrentProject.Connection
    ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic
                
        With ADOrs
            .Fields("MVL") = Forms!frmOWAComplete.txtMVL1
            .Update
            .Close
        End With
        
        Set ADOrs = Nothing
    
    'Integer for referencing array items. arrMVL(0) has already been written to the database
    For i = 2 To 9
                
        'If the field value is not null
        If Not IsNull(Forms!frmOWAComplete("txtMVL" & i)) Then
        
               'Select appropriate customer record from tblMVL
        strSQL = "SELECT * FROM tblMVL WHERE Customer_No = " & lngCusNo & ";"
                
     'Open the recordset
        Set ADOrs = New ADODB.Recordset
        ADOrs.ActiveConnection = CurrentProject.Connection
        ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic
        
        With ADOrs
            'If customer record does not exist - always the case for arrMDL(1) - create a new record
            If .BOF And .EOF Then
                .AddNew
                .Fields("Customer_No") = lngCusNo
                .Fields("MVL2") = Forms!frmOWAComplete("txtMVL" & i)
                'Use intChk to determine the next field to write to
                intChk = 3
                .Update
                .Close
            Else
                'Record exists, update the next empty field
                Dim MyFld As String
                'Name of field is MVL followed by a number (intChk)
                MyFld = "MVL" & intChk
                'Update field from the array
                .Fields(MyFld) = Forms!frmOWAComplete("txtMVL" & i)
                'Increment intChk by one
                intChk = intChk + 1
                .Update
                .Close
            End If
        End With
        
        Set ADOrs = Nothing
        
        End If
    Next i
End Sub

I dont use ADO but I think that you do not need to keep breaking and setting the connection
In the loop I would try remming out
Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
Set ADOrs = Nothing
and moving the other
Set ADOrs = Nothing
to the end of the routine.

HTH

Peter

 

[boblarson]

Yes, just leave the Set ADOrs = Nothing to the end and you don't have to close it each time if it is using the same recordset. If you want to change the recordset then you can close and reopen it, but you can leave the connection open.

 

[systemx]

Thanks for the advice guys. It's funny, even though I feel like I am relatively competent now in most aspects of Access and VBA - I still have such a tremendous amount to learn.

I appreciate your input and have a great deal of respect for not only your experience - but the collective experience and wealth of knowledge available in this forum.

Cheers,

Rob

 

[boblarson]

... even though I feel like I am relatively competent now in most aspects of Access and VBA - I still have such a tremendous amount to learn.

I think, at least for most of us, we still have a lot to learn as well. I keep finding out things I didn't know as people chime in on the various subjects. I doubt I'll ever get to the point where I "know-it-all" and I hope not as that would be boring.