I've got a continuous form with a bound combo box (suffix)
the rowsource for the cb is "SELECT * FROM
qryQuoteBookingDetailSuffix WHERE quoteId = 34"
which returns one record ('A')

the cb is bound to a field that is used within
qryQuoteBookingDetailSuffix so that my list should only display the
suffixes that haven't yet been choosen

so if I choose that 'A' in the first record of the continous form, I'd
expect the second record on the continuous form to show an empty list
for the cb, but this list still contains the one record ('A')

if I put STOP in the gotFocus() event of the cb, and I run the above
in a query within 'query design' it correctly returns no records

if in the gotFocus() event I do
strsql = suffix.rowsource
set rs = currentdb.openrecordset(strSql)
suffix.rowsource = strsql

rs.eof is true
suffix.Recordset.eof is false
and I still get a list with one record 'A'... why ?

and what can I do with cb's rowsource to show an empty list once 'A'
is selected ?
the best I can do for now, in the form's beforeUpdate event, is try to
detected if 'A' has already been chosen

On Nov 22, 9:39*am, Roger <lesperan... (AT) natpro (DOT) com> wrote:
I don't understand your problem. Are you saying that if you select
something from a combo in a continuous form all records for that
column display the same result?

If that is the issue, change the Combo to a text box. On a dbl-click
event create/call a small form that you would pass the id to filter on
and present a combo and OK/Cancel buttons. This form updates the
column field in the continuous form when closed.
..

On Nov 22, 12:09*pm, Patrick Finucane <patrickfinucan... (AT) gmail (DOT) com>
wrote:
no, after selecting a suffix of 'A' on the first record, everything is
fine
now if I run a query from 'query design' with the statement being used
as the rowsource for the suffix cb it returns nothing, which is
correct because 'A' was selected and is no longer available

but when I dropdown the list of choices for 'suffix' in the second
record, the list still shows 'A' as a valid selection even if I do
suffix.requery within the suffix_gotFocus() event

so I don't understand why the dropdown list isn't empty, and how to
make it so

You need to requery the combo box to get it to reset the rowsource. Do this
in the On Current event of each record:

Me.MyCombobox.Requery

Neil

On Nov 22, 7:20*pm, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
but shouldn't that also work if I do cb.requery in the cb_gotFocus()
event, because it doesn't

On Nov 22, 7:20 pm, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
but shouldn't that also work if I do cb.requery in the cb_gotFocus()
event, because it doesn't
<<

Are you saying it works in the form_current event, but doesn't work in the
cb_gotFocus event? Or that it doesn't work in either event?

On Nov 23, 11:01*am, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
correct, it doesn't work from either event

On Nov 23, 11:01 am, "Neil" <neil.ginsberg+newsgr... (AT) gmail (DOT) com> wrote:
correct, it doesn't work from either event<<

Sorry - I've been away.

If you have me.cb.requery in your form's OnCurrent event (and if you've
confirmed that it's running - press F9 on the line and confirm that the code
stops there when you move to a new record), then I'm not sure what the
problem would be. I assumed it was a requery issue.

In your original post you said the rowsource for the combo box was:

"SELECT * FROM qryQuoteBookingDetailSuffix WHERE quoteId = 34"

which returns record 'A'. You then say:

"the cb is bound to a field that is used within
qryQuoteBookingDetailSuffix so that my list should only display the
suffixes that haven't yet been choosen"

This is the part that's not clear. Why don't you paste the SQL from
qryQuoteBookingDetailSuffix. That would help to see what may or may not be
working here.

Neil