List Box contents not changing for new records

[tombarth11]

I have searched, but can't find a thread that addresses this problem. I think I'm just overlooking something simple, but I'm stumped at this time. My problem is that I need to provide a list of order numbers that are associated with a piece of equipment. I have a Form (frmEquipment) that is bound to a table (tblEquipment). I have another table (tblOrderNumbers) that contains, among other things, a Foreign Key field (called EqptKey) to tblEquipment and a text field call OrderNumber. What I would like to do is populate a List Box with all of the Order Numbers that are associated with the entry in the tblEquipment table. For the Row Source Type, I have "Table/Query". For the Row Source, I have "SELECT DISTINCT OrderNumber FROM tblOrderNumbers WHERE [EqptKey] = RKey;", where Rkey is the name of the key to tblEquipment. My problem is that I get the same set of values in the List Box for every record (the ones that match the first record in the tblEquipment set). I want to populate the List Box with a different set of values for each record (i.e. the Order Numbers that match only the corresponding piece of equipment). What do I need to change to make it work?

Additional Info: I have the same problem whether I use Single Forms or Continuous Forms, but I really need to use Continuous Forms. I have a separate form that is also bound to tblEquipment in which I use a subform to add/display the Order Numbers. This works fine, but I cannot use a subform on the Form where I'm having the problem because subforms are not allowed with Continuous Forms (found that out the hard way).

Any help would be greatly appreciated. Thanks in advance.

[MMcCarthy]

In the On_Current event of the form you need to requery the list box. Assuming List box is called List1 the

Private Sub Form_Current()
 
    Me!List1.Requery
 
End Sub
 

This should work for either single or continuous forms.

[tombarth11]

Mary,

Thanks for the reply and suggestion. I didn't put the Requery in the Form_Current function originally because I thought it would only work when the record was active. In fact, that is the case. Your solution works fine for Single Form, but I was trying to get the List to populate differently for each record in Continuous Forms mode. Unfortunately, your solution doesn't do this. You still need to activate each record to show the Order Numbers for that particular record. What my users want is to see at a glance what the different Order Numbers are for each piece of equipment. Is it even possible to show different values in a List Box for each record in Continuous Forms mode?

[NeoPa]

Tom,

You may find this (http://bytes.com/topic/access/insigh...s-dont-persist) helpful in understanding the situation better, if not finding an immediate solution.