RowSource uses a query

[Bob Richardson]

My form (datasheet view) has 2 combo boxes. The value picked in ComboA
changes the values that are permitted in ComboB. The RowSource of ComboB is
Q_ClassPick (which of course uses a CombA value). When I first enter the
form, I can choose any row and the comboB values will be correct for that
row. Unfortunately, the Combo B values for OTHER ROWS will also be based on
the ComboA values FOR THE FIRST ROW ENTERED! If I first change a value in
Combo A, before using Combo B, then go to Combo B, it works. Of course using
Combo B on other rows of the datasheet will then be incorrectly limited by
the value in ComboA on the first line accessed.

Is there a Requery or some other method that will solve this problem? Thank
you.

 

[Marshall Barton]

My form (datasheet view) has 2 combo boxes. The value picked in ComboA
changes the values that are permitted in ComboB. The RowSource of ComboB is
Q_ClassPick (which of course uses a CombA value). When I first enter the
form, I can choose any row and the comboB values will be correct for that
row. Unfortunately, the Combo B values for OTHER ROWS will also be based on
the ComboA values FOR THE FIRST ROW ENTERED! If I first change a value in
Combo A, before using Combo B, then go to Combo B, it works. Of course using
Combo B on other rows of the datasheet will then be incorrectly limited by
the value in ComboA on the first line accessed.

Is there a Requery or some other method that will solve this problem? Thank
you.

You need to have:
Me.comboB.Requery
in comboA's AfterUpdate event to synchronize the two combo
boxes when a new value is selected in comboA. You also need
the same line of code in the form's Current event to
synchronize them when you move from one record to another.

While that will make it all functionally correct, some
records other than the current record will not be able to
display the selected item in comboB. This is not a data
problem because all the records have the correct value, it
is strictly a display issue. There is no way to make it
look right in a datasheet form, but there is a complicated
series of techniques that can usually achieve the desired
appearance in a continuous form.

 

[Bob Richardson]

You need to have:
Me.comboB.Requery
in comboA's AfterUpdate event to synchronize the two combo
boxes when a new value is selected in comboA. You also need
the same line of code in the form's Current event to
synchronize them when you move from one record to another.

While that will make it all functionally correct, some
records other than the current record will not be able to
display the selected item in comboB. This is not a data
problem because all the records have the correct value, it
is strictly a display issue. There is no way to make it
look right in a datasheet form, but there is a complicated
series of techniques that can usually achieve the desired
appearance in a continuous form.

Thanks a MILLION Marsh. It works perfectly.

I'm not sure about the problem you discuss in the 2nd paragraph. Are you
saying that the underlying combo list items aren't correct for the other
rows. I can only SEE the item that has been selected in each of the other
rows...which must have come from the correct pick list. I can't see the
other items from that row's combo box until I click on that row. As soon as
I click on a row, that row's combo box gets required (thank you again).

Perhaps you are alluding to what appears in ComboB on the new record "*"
row. Because Combo A has not been selected for a new record, Combo B can't
be determined. What appears in the ComboB cell of the new line record is
#Name? It would be nice to have that Null (blank) but I haven't figured that
out yet.

 

[Marshall Barton]

Thanks a MILLION Marsh. It works perfectly.

I'm not sure about the problem you discuss in the 2nd paragraph. Are you
saying that the underlying combo list items aren't correct for the other
rows. I can only SEE the item that has been selected in each of the other
rows...which must have come from the correct pick list. I can't see the
other items from that row's combo box until I click on that row. As soon as
I click on a row, that row's combo box gets required (thank you again).

Perhaps you are alluding to what appears in ComboB on the new record "*"
row. Because Combo A has not been selected for a new record, Combo B can't
be determined. What appears in the ComboB cell of the new line record is
#Name? It would be nice to have that Null (blank) but I haven't figured that
out yet.

The issue I was referring to is that you can not see some
combo2 values until you make a record current. If that
doesn't bother you, be grateful ;-) Many people get all
excited about it and are willing to go to a lot of trouble
to make it display the value on all of every displayed
record.

I don't see how you can get a #Name error on a new record.
Perhaps there's more going on than we've discussed so far??

 

[Bob Richardson]

The issue I was referring to is that you can not see some
combo2 values until you make a record current. If that
doesn't bother you, be grateful ;-) Many people get all
excited about it and are willing to go to a lot of trouble
to make it display the value on all of every displayed
record.

I don't see how you can get a #Name error on a new record.
Perhaps there's more going on than we've discussed so far??

I found out that my #Name error was being caused by a Default Value
expression. Thanks for the follow up - sorry I didn't get back to you
sooner, but I just discovered the cause of my problems. It's all working
100% now.

 

[chip]

Bob,

I am desperately trying ot get 2 combo boxes to work the way you described
below: "The value picked in ComboA changes the values that are permitted in
ComboB."

Can you describe to me how you did that?

I asked a question on how to do this yesterday and haven't heard a response
yet...

Thanks,

Chip

 

[Marshall Barton]

Check these articles:

http://www.mvps.org/access/forms/frm0028.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;209595

 

[chip]

Marsh,

I'm afraid we're going to have to take it slower for me (sorry)...

I see what these links are saying, but I can't translate them to work for
me...

Part of the problem may be that my form where the combo boxes reside is
based on a table, whereas the Microsoft link directs me to create a form that
is unbound...

Unfortunately, I am modifying a current db so I have to do this under some
constraints...

Could it be that I simply can't do this due to previous development?

Again, I have significant limitations when it comes to sql, etc., so any
"deep programming or coding" will have to be done in baby steps...

Any help you can provide is deeply appreciated...I have been wrestiling with
this for several days and I am running out of time and options...

Chip

Check these articles:

http://www.mvps.org/access/forms/frm0028.htm

http://support.microsoft.com/default.aspx?scid=kb;en-us;209595
--
Marsh
MVP [MS Access]

I am desperately trying ot get 2 combo boxes to work the way you described
below: "The value picked in ComboA changes the values that are permitted in
ComboB."

Can you describe to me how you did that?

 

[Marshall Barton]

I'm not sure how much slower I can go, those articles are
pretty detailed. Perhaps you could ask a specific question
about whatever it is that you don't understand.

The least that I need to guess what you need is both combo
boxes RowSource query's SQL and the combo boxes' bound
column.