dynamic value for list box or option group

[ergo]

Hello,

I'm not sure the title of my thread is self explainatory, I'll better give you an exemple of what I would like to do..

let's consider the following 2 listboxes they are binded to 2 fields in a table

Number:    Item:
1             chair
2             box
              keyboard

now, what I would like Is
if number =2 then when box is selected the value "boxes" is stored

[June7]

Doesn't make sense. There are only 2 items in Number list but 3 items in Item list.

Is Number the PK for record that has Item description? If the Number is saved why would the Item need to be saved? Should not save both.

There is no need for two listboxes.

If you want to use radio buttons with an OptionGroup and you want to save the text instead of number, that requires code.

[ergo]

Thank you June7 I'm sorry If I made no sens .. lets try again

I have a table with several fieds but for the sake of the exemple let say I have one keyfield and 2 fields (fld_A and fld_B)

I use a listbox to store the value "single";"pair" in fld_A and an other listbox to store the value "chair";"box";"whatever" in fld_B
I'm looking for a code that would save me the trouve of having a bigger listbox with value "chair";"chairs";"box";"boxes" etc in fld_B and would depending on the value of fld_a change the selected value with the list box_B from lets say "box" to "boxes"

I hope I make more sens now ?


Ps: I know how to code text instead of number with option groups, I was just saying may be it would be easier to code what I'm aiming at with option groups..

for exemple something in the taste of
on update of option group_2

Select Case fld_a.Value
Case "single"
Select Case og2.Value Case 1
fld_b.Value = "box"
Case 2
fld_b.Value = "chair"

End Select
case else
Select Case og2.Value Case 1
fld_b = "boxes"
Case 2
fld_b.Value = "chairs"



End Select
End Sub

[ergo]

I was writting the above out of my ...
but I tried and it seems to work !

Is there a more elegant way to code this ? could it be done with something else than optiongroup ?




Private Sub Form_Current()


Select Case type.Value
Case "box"
Frame6.Value = "single"
Case "boxes"
Frame6.Value = "pair"
Case "table"
Frame6.Value = "single"
Case "tables"
Frame6.Value = "pair"

Case Else
Frame6.Value = Null
End Select
End Sub


Private Sub Frame6_AfterUpdate()




Select Case Marque.Value
Case "single"
Select Case Frame6.Value
Case 1
type.Value = "box"
Case 2
Itype.Value = "table"

End Select
Case "pair"
Select Case Frame6.Value
Case 1
type.Value = "boxes"
Case 2
type.Value = "tables"

End Select
End Select


End Sub

[June7]

You want to show plural form if some field (Quantity) has a value greater than 1? Unfortunately, forming a plural isn't always just tacking on a 's'. Is this because you want to use the correct form in a narrative? Because if this is just to show on something like an invoice, I wouldn't bother.

Whether or not you save the correct form up front or calculate when required, need some sort of conditional code structure. Forcing user to make that decision with OptionGroup at data entry is one way but if there is a Quantity field, the determination could be automated based on that entry.

[ergo]

Well you got it right it's not about plural it's about narative. the form is used to describe items and then I use a calculated field to make a sentence out of the element of the form.

just need to take a crash course in vba to be able to write some conditional structure a bit more complex

[June7]

So do you know what to do now? The VBA for automatic determination is not much different than what you have for the OptionGroup. But how many different type values are there? A non-VBA approach is to have the single and plural forms in two fields in a table and another field for unique ID as primary key. Save the unique ID value as foreign key. Join the tables. Use expression in textbox:

=IIf[Quantity]>1,[Plural],[Single])

[ergo]

You lost me there.. (I'm new at this) VBA for automatic determination ? what's that ?

I think I understand your non vba approch, I'm not sure I'll be able to set it up up but I'll try

would you advise the non vba over the vba ?

[June7]

Two options for automating with VBA:

1. a sub procedure that is called when the quantity is entered, something user definitely needs to do - no need for additional burden of selecting plural or single, the quantity entered determines

2. a function procedure that is called in the report that constructs the narrative


I recommend the non-vba table approach, especially if there are a LOT of items. Allows more flexibility for adding new items. Don't need to edit code, just add a new record.