Change RowSource with VBA but don't save form 1

[wvandenberg]

I have a combobox on a form. Depending on the other information that is entered into other controls on the form, I use VBA to change the RowSource of the combobox. I don't want to save this new RowSource when the user moves to another record or closes the form. I would like to revert back to the original RowSource of the combobox.

What form event procedure should I use to

1. Test if the RowSource has been changed
2. Reset the combobox to the original RowSource?

Thanks in advance.

 

[RuralGuy]

Close the form with the following code:
DoCmd.Close acForm, Me.Name, acSaveNo
...the acSaveNo refers to changes to the form and not to changes to underlying records. Changes to the records are *always* saved unless you take extrodinary steps.


HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

How are ya wvandenberg . . .

Just a note of caution here!

I hope the combobox is [blue]unbound[/blue] because if its bound you could run into trouble. For example, a selection from one rowsource [blue]doesn't exist in the alternate rowsource[/blue]. This circumvents itself in prior selections seeming to disappear! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[wvandenberg]

Thanks RG, I'll use that code in the OnClose event.

TheAceMan1, yes, the combobox is unbound and the default rowsource will contain the selection from the alternate rowsource.

Should I use the AfterInsert event to test if the combo rowsource was changed? Also, to change the rowsource back to the default rowsource? Is there a better event to use?

 

[RuralGuy]

Hi wvandenberg,
You are not going to want to put that code in the close event. Instead, just put it behind a Command button for closing the form.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[TheAceMan1]

wvandenberg . . .

Apparently I didn't read you post origination very well.

[blue]I don't want to save this new RowSource when the user moves to another record or closes the form. I would like to revert back to the original RowSource of the combobox.[/blue]

Whenever you open the form the [blue]rowsource[/blue] of the combo [blue]always starts with the default[/blue] (that which you set in design view). It doesn't matter how many times you change it during run time.

To circumvent this perform the following (you substitute proper names in [purple]purple[/purple]):
[ol][li] Add a private variable to the declaration section of the forms code module. At the top of the module it should look like:

[blue]Option Compare Database
Option Explicit

Private oldRowSource As String[/blue]

[/li]
[li]Next copy/paste the following to the forms [blue]On Load[/blue] event:

[blue]   oldRowSource = Me![purple][b]ComboboxName[/b][/purple].RowSource[/blue]

[/li]
[li]Next copy/paste the following to the forms [blue]OnCurrent[/blue] event:

[blue]   If Me![purple][b]ComboboxName[/b][/purple].RowSource <> oldRowSource Then
      Me![purple][b]ComboboxName[/b][/purple].RowSource = oldRowSource
   End If[/blue]

[/li][/ol]
As you move from record to record the old rowsource is restored. No worry if you close the form . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[wvandenberg]

Thanks TheAceMan1. Worked like a charm!