Records "disappear" from datashee subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using a combo box based on another combo box ("cascading"?) in my
subform. I'm viewing the subform as a datasheet so that there can be mulitple
records associated (it is an invoice structure for products). I have followed
the directions to set up the 2nd combo box based on the 1st, and that works
just fine. However, when I scroll to the next record on the main form, the
data "disappears" from the screen. It's still there, stored in the main
table, but it's no longer showing up on the screen. I'm sure I need to write
some code to requery, but I've tried everything and I just don't know enough
about it to make it work.

To summarize, my combo boxes are working fine, but they are not storing the
data on the screen when I change records.
 
JAS,

On a continuous form, the RowSource for a combo box has only one value at
any given time, it cannot have a separate value for each record. If, after
changing the RowSource, there exist values in other records that are not in
the new RowSource list, Access can't display their value, so they seem to
disappear! They are, however, still intact in the underlying table.

The only workaround to this problem that I'm aware of, other than using a
single record form is to use a combo box on a popup dialog form to select the
value, and then write the selection to a bound textbox on the main form in
the combo box' AfterUpdate event.

‘ Textbox on main form’s On Enter event
DoCmd.OpenForm _
FormName:="YourDialogForm", _
View:=acNormal, _
WindowMode:=acDialog
' Code resumes here after the dialog form closes. Set the focus to the
next field.
Me!YourNextField.SetFocus

' Dialog form AfterUpdate event
Forms!YourMainForm!YourTextBox = Me!YourComboBox
' Close the dialog form
DoCmd.Close

Hope that helps.

Sprinks
 
I have a similar problem on a form.

I've used this...

cboSite.RowSource = "Select Locations.[Site] From Locations Where
Locations.[SubDivision] = '" & Me!cboSubDiv & "';"

....in the after update event to populate my second combo with
[Site]locations which fall under the [SubDivision] selected in the first
combo.

When I move to input data ito anther record the previous value remains in
the second combo.

And

When I close the form and reopen it, the Sub division is selected, but the
second combo s blank, even though the data is recorded in the table.

I've made a textbox to sit above it and display the Site field, but users
are getting all sorts of error messages about "field cannot be blank" when
the try to click in the cascading combo.

I hope you find a solution to your problem JAS1924, it sounds quite advanced.
 
Back
Top