I am have a combo box in which i select a person from the drop down. I then have an unbound list box below it display all the records. I have put in .requery action in the after update and change event and its not working. when i select a new person, it doesnt automatically update. the combo box is the only field on the form. any suggestions?

What is the code you have? Also you say there is a combobox and a listbox on the form, but your last sentence says that the combobox is the only field on the form. Can you clarify?

Absolutly sorry.
The combo box is the only active box on the form that is referenced to a table for data entry.
The list box is a unbound box, displaying the entries in that table based on the main form PK.

Right now as an action under the change event i have
Me.List9.Requery

Stumped...maybe i should pick something other than "on change"?

c

So, the combobox is bound and the listbox is unbound. Is the listbox on the main form or on a subform? Also, what is the rowsource for the listbox? How do you want the listbox entries to change based on the changes in the combobox selection?

both the combo box and the list box are on the subfrom. the row sources are:

combobox:
SELECT tbl_Individual.IndividualID, [last_name] & ", " & [first_name] & "; " & [title] & ", " & [subinstitution] & ", " & [Name] AS Expr1 FROM tbl_Institution INNER JOIN tbl_Individual ON tbl_Institution.InstitutionID=tbl_Individual.InstitutionID;

List box:
SELECT tbl_Proposal_Investigator.IndividualID, [first_name] & " " & [middle_Name] & " " & [last_name] & "; " & [title] & ", " & [name] AS Expr1 FROM tbl_Institution INNER JOIN (tbl_Individual INNER JOIN tbl_Proposal_Investigator ON tbl_Individual.IndividualID=tbl_Proposal_Investigator.IndividualID) ON tbl_Institution.InstitutionID=tbl_Individual.InstitutionID WHERE (((tbl_Proposal_Investigator.ProposalID)=forms!frm_proposal!proposalid));

The list box is just displaying those Individual ID's that are associated with a particular Proposal ID.
The combo box is used to make these associations.
The requery works fine when i attach it to other items, but not at all in the change in list of the combo box.

I hope all that jibberish at the top made sense...i can never make heads or tails when someone posts that....let me know and i will explain it as tables and such.

c

I may be missing something, but the listbox's rowsource does not seem to refer to the selection in the combobox at all. So what is the logic that governs how the listbox contents change when a different selection is made in the combobox? Maybe an example with some real data would be better at explaining what you have in mind?

Ok.

Lets say ten people wrote a paper. Well the main form is the paper, the subform is allowing me to relate these ten authors to the specific paper. So my idea was i select a name from the combo box, then add new entry, select another name and so forth, and as i am selecting these names i wanted the list box to display the people associated with the paper....the list box than has an option where i can delete the record or edit.
My problem is that it is not requering properly. Even if i put it in the afterupdate...the list box wont display the records until i exit and return.

Am i making sense now???

OK, it makes a little more sense. If the combobox and listbox are on a subform, though, you have to use the syntax:

me.subformcontrolname.form.list9.requery

to requery it. Make sure you specify the correct subformcontrolname. It is not the name of the form that is being used as a subform, it is the name of the control on the main form into which the subform has been dropped.

Hummm...so in this case.....the subform sits on a tab sheet. so would i use the tabcontrol name?

c

Tab controls and pages have no influence whatsoever on how you refer to form controls. You must have created a subform control on that tab page and then put the subform inside that control. You need the name of that subform control.

Balaji

That isnt working at all. I tired that exact expression, plus variations of it...including referencing the main form first. I keep getting "cant evaluate" errors.

Any ideas why?

If you can post your db (remove sensitive and/or confidential data, compact and repair, zip it and make sure it is less than 500 kb before trying to attach), I can take a look at it. Tell me which form you are having problems with.

Thanks! Dont know why it is acting fishy. The form in question is frm_proposal. The subform is subfrm_investigator. You will see the combo box and list box there. I removed the actions on the combo box.

OK, I opened subfrm_investigator in design mode and added the line:

me.list9.requery

to the afterupdate() event of individualID (sorry about the confusing references to subform properties posted earlier. They are needed if you are creating the subform inside the main form directly, but if you use an existing form as the subform, then you can code up events internal to that form directly using the me. reference). If the rowsource of list9 is correctly coded up, it should work I think. If it does not work, you might have to run the rowsource as a separate query to figure out whether something is wrong with it, correct it so that it pulls up the right records, and then use the corrected SQL as the rowsource of the listbox.

Ok. Maybe you can help me think this through a bit better. Cause the afterupdate event is not requering/refreshing list box.

You will notice if you go through the tab sheet that i have a few of these listboxs on subforms....what i am trying to do is show the user their particular selections.

What is the best way to get a dynamic (i.e. every record change or edit it will refresh real-time) solution to this.

?

I think the requery is probably working (or at least it should). There may be something wrong with the rowsource that keeps the update from reflecting correctly. If you pull the recordsource out as a standalone query and run it, does it show the records you expect it to show?

Yes...though either have to take out or feed it a variable for the criteria refrencing ProposalID.

I am using the proposalID field on main form as criteria for the listbox rowsource query.

Depending on how you have referenced that field in the SQL, you might require no modifications to the SQL at all (as long as the form in question is open and has data on it). If you are referring to the proposalID as forms!myform.proposalID, it should work in a standalone query also.

The form in question is open and has data.

I reference it as
[forms]![frm_proposal]![proposalid]

I am wondering: what does it take for the afterupdate event to fire.....move out of the field completly...or just change something in it?

c

Move out of the field completely after making some change to it.

Your form reference should work in a standalone query just as well as inside the subform. So try the rowsource as a standalone query and check to see if you are seeing what you should see in the outputs of the query.

Query works fine as a stand alone.

The only field on the subform to move to after the combobox is the list box. that still counts as moving out of the field though right?

stumped!

Yes, the afterupdate should fire. And the requery of the listbox should fire also since it is inside the afterupdate() sub. You can put a call to msgbox() in the sub to make sure the requery is actually being executed (maybe one before and one after the requery line).