access vba requery oddness

0
votes

It's been awhile since i've worked in VBA. I have a bound form. It has two drop down lists. One list is bound the other not (the first ddl is a list of values. The second gets refreshed when the first one changes, using the value of the first to create a query for the second. That value is used as a fk in the table the form is bound too).

Anyway, when the form is first run and uses the default value for ddl 1, if the second combobox is empty, and I try to get the value, it's null, which is what you would expect. But, I have code that runs when ddl1's value changes, to requery ddl2. When it requeries, if the list is empty, and I do combobox1.value, instead of being null, the value is 1. This is confusing, because, since the list is empty, I would think it should be null. What's going on here? Here is what I have:

Combo1 is bound to a table Combo 2 uses this query: 

SELECT tbl_office.id, tbl_office.office_name
FROM tbl_office
WHERE (((tbl_office.otherTable_id)=[Forms]![dlg_addDivision].[Combo1]));

On Combo1 afterUpdate event:

  me.Combo2.requery

So, after combo1 afterUpdate, the above sql gets called. If this produces an empty dataset, and I try to get the value of combo2, even though the list is empty, the value says it's 1

thanks

1
ms-accessvbams-access-2007
When you say "Combo 2 uses this query", how does it use that query? If it's the Row Source query for Combo 2 ... the query which fills the list is restricted by its current value(?) ... that seems wrong. – HansUp
You can determine if there are any values in the comboBox by checking like: If Me.Combo2.ListCount = 0 then ..... But I agree with @HansUp that you can't be using Combo2 in your SQL - I think you mean Combo1. – Wayne G. Dunn
Combo2 uses it as a rowsource. The rowsource Type is Table/Query. Yes, the query I posted has a type. I will fix it. – jason
There's another typo.. when you say "When it requeries, if the list is empty, and I do combobox1.value, instead of being null, the value is 1." don't you really mean to use COMBOBOX2.Value??? If ComboBox 1 is bound, it will probably always have a value... – Wayne G. Dunn
Ah yes, sorry about that. You are correct, when I debug at combo1 Afterupdate I can see combo2.value = 1 – jason

1 Answers

1
votes

The requery requeries the List - not the value. The value stays whatever it is/was before. Its not a bug - its a feature ;-) If you page through datasets that are already filled you wouldn't want them to be changed without user interaction. The List is just a helper for dataEntry (and validation - if you check "only listitems allowed") - but it has nothing to do with your dataset. By setting the value to your first Entry like you proposed: Me.Combo2.Value = Me.Combo2.ItemData(0) you change the dataset intentionally. And that is how it is supposed to happen. Not via changing the list.