Clear List/Combo Box Row Source when unbound form closes.

[valeryk2000]

1) We are using SQL Server (all tables and queries, e.g. stored procedures) are on the server side - NO LINKED TABLES! All tables and st. procedures run via ADO.
2) On the client side we have forms with unbound controls, populated by recordsets.
3) When the form closes it keeps the values in combo/list boxes as Row Source property.
4) I want to get rid of these sticky stuff and use the following procedure on form_close (or form_deactivate):

===============================
Public Sub ClearAllListComboBox(frm As Form)

Dim controlItem As Control
For Each controlItem In frm
If TypeOf controlItem Is ListBox Or TypeOf controlItem Is ComboBox Then
controlItem.RowSource = ""
End If
Next
End Sub
=======================================

However when I open the form again it retains old data. In design view Row Source still has old Value List.

Ane suggestions?

Thanks

 

[DCrake]

Lets turn this on its head. How do the combos and listboxes get their rowsources from in the first place, assuming there is none when the form opens? and is the rowsource type set as Value List?

David

 

[valeryk2000]

1) We are using ado recordset from sql server stored procedure.
2) we populate lists/combos using Value List

 

[DCrake]

So when the form opens you call some sort of sub routine that populates the rowsource of the combo/list box?

 

[valeryk2000]

Correct. Query

 

[missinglinq]

Sorry, two things don't make sense here.

To begin with, if you're populating the comboboxes/listboxes with a query, as you've just stated, then you're not using Value Lists for your rowsource, you're using Table/Query for the rowsource.

Secondly, if you're populating them when the form opens, as you've also said, how do you expect your original code to clear them?

I think you need to explain a little more clearly exactly what you're trying to do here for us to be able to help you.

 

[valeryk2000]

1) I am not using a query. I am using a recordset. My access database has only forms - no queries, no tables.
2) I have a list on the form with record ids. When I click on the id the rest of lists and combos are populated by related records that are returned by recordsets (using server side stored procedures - like parameter queries)
Does it make it clearer?

 

[missinglinq]

1) I am not using a query

So when the form opens you call some sort of sub routine that populates the rowsource of the combo/list box?

Correct. Query

Then why did you say you were populating them with a query?

Anyway, if the comboboxes are unbound when the form opens, and aren't populated until you choose an ID, they should have nothing in them, whether you have code to 'empty" them out or not.

When you say you "close" the form, are you actually talking about opening the form by clicking on it in the form section of the database window, doing whatever, and then closing the form, returning to the database window? Or are you talking about starting out in Design View for the form, then running the form from there, then closing the form, returning to Design View?

I ask this because one of the quirks of Access is that if you do the latter, going from Design View to Form View back to Design View and back, once again to Form View, unbound controls retain their values. But if you open a form by clicking on it, then close it, and then open it again, the unbound controls are empty.

 

[valeryk2000]

Then why did you say you were populating them with a query?

==> I am using query on the server side (stored procedure). On the client (Access form) I have a recordset.

Anyway, if the comboboxes are unbound when the form opens, and aren't populated until you choose an ID, they should have nothing in them, whether you have code to 'empty" them out or not.

==> Yes

When you say you "close" the form, are you actually talking about opening the form by clicking on it in the form section of the database window, doing whatever, and then closing the form, returning to the database window? Or are you talking about starting out in Design View for the form, then running the form from there, then closing the form, returning to Design View?

==> I close the form explicitly: there is a button Back To Main Menu - and the I close the current form in code and open MainMenu form.

I ask this because one of the quirks of Access is that if you do the latter, going from Design View to Form View back to Design View and back, once again to Form View, unbound controls retain their values. But if you open a form by clicking on it, then close it, and then open it again, the unbound controls are empty.

===> the code that closes the form:

Call ClearAllListComboBox(Me)
Call ClearTextBoxes(Me)
lblPerson.Caption = ""


DoCmd.Close acForm, "frmClinicalReview", acSaveNo
DoCmd.OpenForm "MainMenu"

 

[lewisk]

Simple answer is: Close the form.

When you make a selection on the form, the form dissapears from view, but it is not closed. So, when you reinitiate the action, it reopens (becomes visible) with the same information stored.

In your code, after the value you have selected is processed; add a line of code or a line in your macro to close the form.

 

[valeryk2000]

Well ... the code above does exactly what you suggest - closes current form