Using Summed vb Constants

[dunc723]

Hi,



VBA has some vb constants that can be used in combination and added together instead of declaring each separately. For example, for buttons in a MsgBox statement, I could use vbOKCancel + vbCritical, or I could use 17 (which is the sum of the two constants).

My question: Is there a way to use that in reverse? On a form I have a multi-select drop down with choices A, B, C, D. I would like to assign values of 1, 2, 4, 8 to those. If the user selects B and D, store the value 10 in the field MultChoice. My purpose is to store only the integer instead of the long text string that could result from multiple selections.

Where I'm having a problem is deconstructing that value to the text values for a form or report - when the record opens and reads 10 for MultChoice, it should display "B, D" or some other concatenation of the text strings. The only idea I've come up with so far is converting the integer to a binary string (1010) and looping through the string to check each value, but that seems clunky.

Any suggestions?

Thanks,

Pete

[CJ_London]

think you need a recursive vba function (calls itself), but don't have time to work it our for you. It's been a while since I needed one. I think it would be something like

Public numstring as string

function findnums(v as integer) as integer
dim I as integer


' find max number less than v
i=0
while 2^i<v
    I=I+1
wend

numstring=numstring & ", " & 2^i
while I>0
    findnums(v-2^i)
wend

end function

[ssanfu]

On a form I have a multi-select drop down with choices A, B, C, D.

Any suggestions?

A combo box control on a form does not have a multi-select property.
A list box control DOES have a multi-select property, but does not "drop down".

So it sounds like you have a multi-value field (MVF) in a table.
If this is true, I my suggestion would be to NOT use a MVF. (Most experienced Access programmers do not use MVFs because they are so difficult to work with)

[Micron]

Agree totally with the mvf thing. The constant summing works because the totals represent unique combinations of separate constants. If you don't have numbers such that it is not possible to arrive at a sum in more than one way, then you cannot. If you do, a table of values and their unique sums would be easier to deal with than mapping them out in code. There is a way, using csv in one field, but I think 99.9% of the time it would reflect a poor table design. Why can you not store each numeric value from a list of choices as a separate record, related by some common key field? I think your reasoning would help here, as there might be an easier approach, or an indication that all is not well in the db design.