Help Working with user defined types with nulls.(For loop?)

[kballing]

I have a form with about 15 fields, we'll call them Field1,Field2, etc. With the exception of the primary key, all the fields can be null.
I have a type defined, we'll call it a FormStruct, with all it's elements equivelant to the form fields and all are variant types to allow nulls.
i.e.

Type FormStruct
Field1 as variant
Field2 as varient
'etc.
end Type

On a control's AfterUpdate event a variety of tests are done based on control's input.
i.e.

sub control_afterupdate()
if function1(formstruct) then
     goto updateform
elseif function2(formstruct) then
     goto updateform
elseif function3(formstruct) then
     goto updateform
else
     exit sub
end if
 
updateform:
     me.field1 = formstruct.field1
     me.field2 = formstruct.field2
     me.field3 = formstruct.field3
     'etc.
end sub

Each function assigns values to FormStruct.fieldX based on a query or some other function and returns a boolean depending on if the query was successful.

The problem occurs upon reaching the updateform: part and some of the values in formstruct aren't defined depending on which function was or wasn't run. So when I try to assign me.fieldX to formstruct.fieldX I get a zero legth string, or a mismatch error.

How do I get unassigned properties of formstruct to be set to null without too much extra code?
Is there a way to iterate through all the values in formstruct? that way I could make my code a bit shorter.
I'm no VBA pro, but I'm open minded.

 

[SOS]

You would have to handle nulls with the NZ function:

me.field1 = Nz(formstruct.field1)

 

[kballing]

Yeah, sort of, but in this case I would like nulls instead of zero length strings. Also I don't know how to handle values that don't get defined.

e.g. function1 only asigns formstruct.field1 when function2 might assign only formstructl.field5

I only want to update the form to formstruct.fieldX when it has a value, or null if does not.

However, I'm looking over my code again, and I don't like how I've got it set up and am considering other alternatives.

P.S. I forgot to mention, function1, function2, etc. are found in a separate module because they are also referenced in other forms.

 

[SOS]

As far as I know, you can't assign a NULL to an unbound text box which is why you are having a problem. And even bound fields don't always allow you to set them to null.

 

[ChrisO]

I doubt if that is the code you have, it has not proper cased and Field2 as varient is wrong.
Try posting the actual code.

 

[kballing]

@ ChrisO, no that is not actual code, and I realize it is not proper cased

Anyway, the way I was going about this was a total headache. I just made copies of my functions in the same module and directly updated the me.fields directly and avoided the use of a user defined type.

However, I'd still be interested in knowing how to iterate through the values of a custom type. Something to the effect of:

for each field in formstruct
formstruct.field = foo

 

[vbaInet]

However, I'd still be interested in knowing how to iterate through the values of a custom type. Something to the effect of:

for each field in formstruct
formstruct.field = foo

To my knowledge, this isn't possible. A user defined type is an entity in its own right and not a collection (per say). It's a collection of its own if you like. However, to the compiler it would belong to a collection of UDT's, just not accessible by code. Just as you can't iterate through all Boolean types via a collection.

If it did have a collection, how would you loop through if it had an array as one of its elements?

 

[gemma-the-husky]

i suspect there may be a way - as intellisense knows the type members - but no idea what that would be!

 

[vbaInet]

My thought is it is only known and accessible to the compiler. I believe they are workarounds though. If one can loop through all Boolean data types in a given module then we would be on to something.

 

[ChrisO]

Maybe you could use something like this: -

Type FormControl
    Name  As Variant
    Value As Variant
End Type

Type FormStruct
    ControlArray() As FormControl
End Type


Sub Test()
    Dim MyStruct As FormStruct
    Dim intIndex As Integer
    
    ReDim Preserve MyStruct.ControlArray(3)
    
    MyStruct.ControlArray(0).Name = "Fred"
    MyStruct.ControlArray(0).Value = 1234
        
    MyStruct.ControlArray(1).Name = "Sam"
    MyStruct.ControlArray(1).Value = 5678
    
    MyStruct.ControlArray(2).Name = "Tom"
    MyStruct.ControlArray(2).Value = Null
    
    MyStruct.ControlArray(3).Name = "Ted"
    MyStruct.ControlArray(3).Value = "Some Text"
    
    For intIndex = LBound(MyStruct.ControlArray) To UBound(MyStruct.ControlArray)
        MsgBox MyStruct.ControlArray(intIndex).Name & "  " & MyStruct.ControlArray(intIndex).Value
    Next intIndex

End Sub

 

[vbaInet]

Nice ChrisO.

I was thinking the OP was talking about something else, like getting a list of all Types and their elements in a module.

Thanks for clarifying (and the code of course).

 

[ChrisO]

I’m not sure what the OP is talking about but it’s something to play with.

I don’t think it’s possible to get the Names of the members of a UDT but I have seen the size of the members returned. It needs to be done with care because the new 64 bit computers assign different length storage than the 32 bit machines do.

However, if the size of the members is known then it might be possible to index into the UDT based on size offset from the start of the UDT (structure). But I have not done that…yet.

 

[ChrisO]

To assign the UDT members to Controls on a Form we could do: -

For intIndex = LBound(MyStruct.ControlArray) To UBound(MyStruct.ControlArray)
    Me(MyStruct.ControlArray(intIndex).Name) = MyStruct.ControlArray(intIndex).Value
Next intIndex

 

[vbaInet]

I don’t think it’s possible to get the Names of the members of a UDT but I have seen the size of the members returned. It needs to be done with care because the new 64 bit computers assign different length storage than the 32 bit machines do.

Interesting, I didn't know that. Good info.

However, if the size of the members is known then it might be possible to index into the UDT based on size offset from the start of the UDT (structure). But I have not done that…yet.

Makes me wonder if a Dictionary object can hold a reference to a UDT.

 

[kballing]

Thanks all, some great ideas and examples. My questions were definitely answered.