Cascaded combos control in a datasheet format subform

[Attos]

Hello all,

I have a form (frmEnvios) with a subform in it (sbfEnvio01).
My suboform contains three comboboxes and one textbox.
The three comboboxes are cascaded.
The first combo contains a list of customers (cbxCust).
The second contains a list purchase orders (cbxPo).
The third contains a list of part numbers (cbxNumpart).

Second and third combos are pulling their values from queries using
parameters taken from the value of the previous combo, respectively.

The subform is shown on datasheet format and is bound to a table
(tblEnvdetalle).

My problem is: when I add more than one record in the subform, the
subsecuent second and third combos, start erasing their contents. They do
that in records different than the active one. I have observed that is due
to the requery method I use on the combobox control, but I cannot remove it
because that is the only way I know of doing what I want.

Is there a way to avoid that? That is, I want the requery to be done upon a
combobox but only on the active record, and not to the whole set of rows
because that refreshes the combobox in all the records, not just on the
active one.

On the other hand, if there is no way of performing a requery on just the
current record, can I then store the values of the combobox for all the other
records then perform the requery, and then recover those values to repopulate
the subform?

Any help would be greatly appreciated.

Attos

 

[strive4peace]

Hi Attros,

here is how I deal with that issue:

rather than putting criteria in the query for the RowSource, do
something like this:

'~~~~~~~~~~~~~~~~~~~~~~
Private Function SQL_combo_controlname(bCrit As Boolean) As Byte

dim strSQL as string

strSQL = "SELECT ... " _
& " FROM ..."

if bCrit then
strSQL = strSQL & " WHERE [fieldname1]= " _
& me.controlname
end if

strSQL = strSQL & " ORDER BY [fieldname2];"

me.combo_controlname.Rowsource = strSQL
me.combo_controlname.Requery
end Function
'~~~~~~~~~~~~~~~~~~~~~~

WHERE
combo_controlname is the Name property of your combo (don't use spaces
or special characters

on the GotFocus event of your combo:
= SQL_combo_controlname(true)

on the LostFocus event of your combo:
= SQL_combo_controlname(false)

the combos in the other rows will be blank ONLY while you are in the
combo on the the you are editing. As soon as you move out of the combo,
all will show again

If you are not comfortable with SQL, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*