Conditionally Assigning a Value to a Combo Box 1

[Headwinds]

I'm stumped on something that I think should be very simple. I have two combo boxes in a subform. If the user selects a particular value from the first combo box, the second one should have the same value; otherwise, they are independent.

So, on the first combo box, I've written an On Change method that looks like this:

If [Forms]![MyMasterForm]![MySubForm]_
    ![FirstCombo].[Value] = "Bingo" Then
  Set [Forms]![MyMasterForm]![MySubForm]_ 
    ![SecondCombo].[Value] = "Bingo"
End If

When this runs, VB has no trouble evaluating the test expression, but it complains that I haven't specified an object on which it can make the assignment. I've rechecked the names and spellings and can't see why VB can evaluate the test expression but can't figure out what I'm referring to in the Set statement.

Here's what doesn't work:
- Variants of Me (

[Me]![SecondCombo].[Value]

,

[Me]![MySubForm]![SecondCombo].[Value]

, etc.)
- Replacing [Value] with [Text] as the property being set

I'd do this with an AssignValue macro, but I don't know how to run a macro conditionally, and the assignment in SecondCombo only needs to be made for one value of FirstCombo.

Two other kinks:
1. MySubForm is synchronized with MyMasterForm not by Access's LinkMasterFields/LinkChildFields method but by Requerying the Query on which MySubForm is based as the On Click method of a list box in MyMasterForm.
2. The Query on which MySubForm is based queries a Table with Lookup list columns on the two columns are the RowSources for FirstCombo and SecondCombo. The sources of the Lookup lists are Queries of two other tables.

Sorry for these newbie questions, but I'm coming to Access from a Visual FoxPro background.

Thanks for any help,

Jim

 

[RoyVidar]

Hi!

Access events might be a bit different from FoxPro (not that I know anything about FoxPro;-)). The on change event of controls, fires per each letter typed, and when not referring to any property directly, it's the default .Value property that's evaluated. But intil the After Update event has fired, only the .Text property has the "new" values.

First suggestion, move the code to the after update event of the combo.

References - within current form, one uses the Me keyword (without [brackets])

[tt]Me!SecondCombo.Value[/tt]
- or as many do, drop the ".Value", cause it's the default property of controls

So, I think your code could be written like this:

[tt]If Me!FirstCombo.Value = "Bingo" Then
Me!SecondCombo.Value = "Bingo"
End If[/tt]

Set - in VBA is used to instantiate objects, in ordinary assigning, only "="

The [brackets] are only needed (in coding) when the names of the objects contain special characters, spaces... and isn't needed here.

- does this get you any closer to your goals?

Roy-Vidar

 

[Headwinds]

Yes. That works beautifully. Thanks for your instruction.