Combo Box Lookup in a Subform

[MikeAngelastro]

Hi,

I have a Sales Order form with a Line Item subform. One of the fields in the subform has a combo box to be used to lookup the item's location. Each item can have more than one location and there is an Item Location table. The row source query looks at the item number of the current record and passes a recordset containing only the locations of that item. However, this only works on the first item entered into the line item list. After moving past that record and entering a second item, the second combo box returns a recordset of locations for the first item instead of the second item. It doesn't matter how many new items I add to the list, the options in the combo box are limited to the first item entered after opening the form. Even if I enter other orders, the combo box lists only locations belonging to the first item entered into the first sales order. I am using the following notation in the lookup query:

[Forms]![Sales Order]![SOlineitems].[Form]![Item Number]

What am I doing wrong?

Mike

 

[DES]

Sounds like

You are not requerying the object.

Try going into the OnCurrent event for the form the combo is on and add Me.Requery.

If that doesn't work and you would like I can have a look at it for you. Just trash any confidential data and send it along.

 

[MikeAngelastro]

Hi DES,

I have made a simplified version of the database to replicate the problem. Putting the requery where you suggest causes an error report to be sent to microsoft as soon as I try to open the parent form.

What drives me crazy about this is that running the query that is the combo box row source while the form is open brings up a correct recordset. Running it from the combo box does not.

I will send you this small database to see if you can solve the problem.

Incidentally, I get the same results in Access 97, 2000, and 2002.

Thanks,

Mike

 

[MikeAngelastro]

Hi,

I came up with a solution to this problem though I don't like it. No matter what I did, I couldn't get the combo box to show the right list. So I changed the row source type to "List" and on the Enter event I run a cursor to fill the list with a concatenated string with a ";" following each list item like "Item1;Item2;Item3;....ItemN;".

I think the other way should work because it is a more intuitive solution.

What do you folks think? Anyone have a better solution?

Thanks,

Mike

 

[MikeAngelastro]

Hi,

There is another wrinkle to this issue. Sometimes the value list must show numbers from 1 to 15. However, because the field is text, I have added leading blanks to get them to sort correctly. That is 1, 2, 3, ..., 15 instead of 1, 10, 11, ..., 9. So the table containing the locations has the blanks. When I run the code to fill the combo box, the list variable = " 1; 2; 3; ...;15", which is what I expect, when I assign it to the record source. But the list does not actually contain the blanks and my selection does not contain the blank. So the blanks get lost along the way. Has anyone seen this before? Anyone know a solution?

Thanks again.

Mike

 

[jjturner]

Not that this provides anything in the way of an overall elegant solution, but further to your workaround, the following would keep your leading spaces:

"' 1';' 2';' 3';"

(Single quotes around each space/item # in your string)

OR using RSet function on each item # might also do the trick.

HTH,
John