Synchronizing Subforms

I have a form with a combo box (A), a list box (B) and a datasheet view sub
form (C).

The user chooses a value in A, which brings up a list of corresponding
values in B. The user then chooses a value in B which brings up corresponding
values in C. Once the user is done looking at the records he starts the
process over by choosing a new value in A.

When the user chooses a new value in A, I want C to go back to its default
value of no records.

I have successfully done this on a different form where C is a list box
instead of a sub form. In the on change property for A I put in this code:

Me.lstActID_A.RowSource = ""

This resets the list box C back to no records.



Does anyone know how to reset the sub form back to no records?

For a subform's recordset on a form the syntax would be:

Me.SubFormControlName.Form.RecordSource = vbNullString

SubFormControlName is the name of the subform control on your form, not the
name of the form specified in the subform control's Source Object property.

(vbNullString is the same as "" - You actually get better performance using
intrinsic constants than you do literals.)

That would mean, then that you would have to assign the rowsource in the
After Update event of B.

One other thing. Don't use the Change event. It means literally what is
says, so the Change event fires on every keystroke. Use the After Update
event instead.
--
Dave Hargis, Microsoft Access MVP


"djf" wrote:

I have a form with a combo box (A), a list box (B) and a datasheet view sub
form (C).

The user chooses a value in A, which brings up a list of corresponding
values in B. The user then chooses a value in B which brings up corresponding
values in C. Once the user is done looking at the records he starts the
process over by choosing a new value in A.

When the user chooses a new value in A, I want C to go back to its default
value of no records.

I have successfully done this on a different form where C is a list box
instead of a sub form. In the on change property for A I put in this code:

Me.lstActID_A.RowSource = ""

This resets the list box C back to no records.



Does anyone know how to reset the sub form back to no records?

Thanks for the input. The code does work to set the form back to no records.
There is one glitch however. It leaves #Name? in every field instead of
displaying blank fields like it does when I open the form. It also messes up
the function of menu B. Once the subform goes to null with the #Name? in each
field, it will not populate with new values when I make a choice in menu B.
It stays stuck with the #Name? values. Without the new code it works
correctly.

I'll keep playing with it, but so far I can't get it to work quite right.
I'm sure it's operator error, but any insights would be greatly appreciated.

"Klatuu" wrote:

For a subform's recordset on a form the syntax would be:

Me.SubFormControlName.Form.RecordSource = vbNullString

SubFormControlName is the name of the subform control on your form, not the
name of the form specified in the subform control's Source Object property.

(vbNullString is the same as "" - You actually get better performance using
intrinsic constants than you do literals.)

That would mean, then that you would have to assign the rowsource in the
After Update event of B.

One other thing. Don't use the Change event. It means literally what is
says, so the Change event fires on every keystroke. Use the After Update
event instead.
--
Dave Hargis, Microsoft Access MVP


"djf" wrote:

I have a form with a combo box (A), a list box (B) and a datasheet view sub
form (C).

The user chooses a value in A, which brings up a list of corresponding
values in B. The user then chooses a value in B which brings up corresponding
values in C. Once the user is done looking at the records he starts the
process over by choosing a new value in A.

When the user chooses a new value in A, I want C to go back to its default
value of no records.

I have successfully done this on a different form where C is a list box
instead of a sub form. In the on change property for A I put in this code:

Me.lstActID_A.RowSource = ""

This resets the list box C back to no records.



Does anyone know how to reset the sub form back to no records?