VBA multiple values

[Lyla]

Hi! I am trying to lock a certain field in a form when a specific option is given in another field.
For normal variables this works with the following code:

Private Sub PREG_AfterUpdate()


Select Case Me.PREG
Case "1"
Me.GEST.Enabled = True
Case Else
Me.GEST.Enabled = False
Me.GEST.Value= Null
End Select
End Sub

However, when one of the variables stores multiple values this code doesn't work.

Anyone knows how to fix this?

[CJ_London]

variables cannot store multiple values, only a single one.

So perhaps explain in more detail what you mean - provide some examples

[Lyla]

Thanks! So when you have table and create a "field name" (for example pregnancy = PREG) and the data type is a lookup from another table (numberic) (1 ,2,3). You have the option to "Allow multiple values".
When you try to block a field (so gestation week = GEST) in a form based on the answer given with PREG ( number 2 or 3), then the code I us does not work:

Private Sub PREG_AfterUpdate()
Select Case Me.PREG
Case "1"
Me.GEST.Enabled = True
Case Else
Me.GEST.Enabled = False
Me.GEST.Value= Null
End Select
End Sub

[Minty]

These are known as multivalued fields.
Most developers avoid them like the plague, as they aren't correctly normalized data, but a bit of a kludge to avoid a proper data structure.

More here https://stackoverflow.com/questions/...ds-a-good-idea

[CJ_London]

As Minty says, multivalue fields have their uses but limit what else can be done. Although they display the selected items in a single control, they are not stored that way. They are stored as a list of single values - effectively another table but hidden and not very accessible.

To achieve what you want you would need to iterate through this table/list to see if 1 has been selected and then apply the rest of the code. I'm not sure how you would do that because I don't use them. Here is a link to some code that you might be able to use to see if 1 has been selected http://allenbrowne.com/ser-42.html