By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

List Box contents not changing for new records

P: 5
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.
Dec 17 '08 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
In the On_Current event of the form you need to requery the list box. Assuming List box is called List1 the

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Me!List1.Requery
  4.  
  5. End Sub
  6.  
This should work for either single or continuous forms.
Dec 18 '08 #2

P: 5
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?
Dec 18 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 18 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.