Basing items in a combo box on the value of another entry in the t

[Guest]

I know I have read this somewhere, but unfortunately I can't find it again.
I have a combo box that is populated from records in a separate table. I
want to list only those records that match a certain criteria. This part is
working. The problem is that the criteria is based on the value entered in
another combo box on the form. When I am entering new records, the items in
the combo box are based on the last record I entered, not on the current
record that is being entered now. How can I make the form update the value
I'm using for the criteria before it populates my second combo box?
-rg

 

[strive4peace]

Hi rg

since you didnn't specify any data structures, here is an
anaology for you...

limit combobox when you enter, show all when you leave

on the gotFocus event of the Product combobox, assign this:

=SetProductSource(true)

on the lostFocus event of the Product combobox, assign this:

=SetProductSource(false)

put this code behind the subform with the combobox -- and
compile it before testing

'~~~~~~~~~~~
private function SetProductSource(pBooCriteria as boolean)

on error goto Err_proc

dim s as string, mSupplierID as long

s = "SELECT ProductID, Product, PartNumber" _
& " FROM Products "

if pBooCriteria then
if not isnull(me.SupplierID_controlname) then
mSupplierID = nz(me.SupplierID_controlname)
if mSupplierID <> 0 then
s = s & " WHERE (SupplierID =" _
& mSupplierID & ") "
end if
end if
end if

s = s & "ORDER BY Product;"

debug.print s

me.combobox_controlname.RowSource = s
me.combobox_controlname.Requery

exit function

Err_proc:
msgbox err.description,,"ERROR " & err.number & "
SetProductSource"
'press F8 to step through code and fix problem
Stop
Resume
End function

'~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

Crystal,
Thanks for your reply.
I did not specify data structures because what I really want to know is how
to make the selection from one combo box (which inserts the choice into a
field in the current record) update before the code I've written to fill the
next combo box runs. I'm getting the second combo box filled with the
information from the previous record, not the current one. Is what you've
posted the best way to get this info to update before I run the query for the
second combobox?
-rg

 

[strive4peace]

Hi,

I wouldn't say it is the BEST way, but it does work ...

the Requery statement rebuilds the list and the code
replaces the RowSource...

I like to use this because if you have a continuous form,
all the data will show (if you are not IN the combo) even if
they are not within the criteria specified by the current
record since the combo is only filtered while you are in it...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Guest]

This is similar to a question I have...so I'd like to give a little bit more
specific example...

Within a form, I'd like to restrict the choices for a field based on the
value from the previous field.

Field 1 Possible Values: Value1, Value2, Value3, and so on...

Field 2 Possible Values: Value1A, Value1B, Value1C, Value2A, Value2B,
Value3A, Value3B, Value3C, Value3D, and so on...

If Value1 is chosen for Field 1, I want to only see Value1A, Value1B and
Value1C as choices for Field 2.

If Value2 is chosen for Field 1, I want to only see Value2A and Value2B as
choices for Field 2.

And so on...


Thanks!

Lowell

 

[Guest]

I found the place where I read this before, here is the url.
http://office.microsoft.com/en-us/assistance/HA011730581033.aspx
-rg

 

[strive4peace]

Hi Lowell,

I assume, then, that the source for your second combo is a
value list...

on the AfterUpdate (or BeforeUpdate) event of thie first combo:

'~~~~~~~~~~~~~`
dim mValues as string
select case me.firstcombo_controlname
case "firstvalue"
mValues = _
"'value1';'value2';'value3'"
case "secondvalue"
mValues = _
"'value4';'value5';'value6'"
case else
mValues = _
whatever is your default
end select
'clear current value
me.second_combo_controlname = null

'rebuild list
me.second_combo_controlname.rowSource = mValues
me.second_combo_controlname.requery

'~~~~~~~~~~~~~

I have the Value list for the second combo is a text list --
if it is numbers, then remove the single quote delimiters

I have also assumed the first combo return a text value
since it is probably not bound...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*