ARampton
asked on
VBA to dynamically set my form ComboBox RowSource to an existing query
I have a ComboBox that I want to dynamically set its rowsource depending on a value on its form row
I have tried the following but my syntax is wrong somewhere
Private Sub StageListNew_Enter()
StageListNew.RowSource = Query.[Q-StagesCurrentProj ect]
End Sub
I can set a full SQL Query but I want to direct it to an existing Query
I have tried the following but my syntax is wrong somewhere
Private Sub StageListNew_Enter()
StageListNew.RowSource = Query.[Q-StagesCurrentProj
End Sub
I can set a full SQL Query but I want to direct it to an existing Query
...or you could add a "Update/Refresh List" command button near the combobox, and run the code from that, in case this need only be modified at certain times.
JeffCoachman
JeffCoachman
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
^ Good Point...
ASKER
Thanks, seems Access does not like me putting [] around my Query name inside "quotes"
I am trying to keep my combobox showing correct values on all rows in my multi row subform but only showing valid items relating to a another field on the same row when clicked
I have tried using OnEnter event to set my combobox to a restricted query matching another field on my form and OnExit to reset it to an unrestricted list
This almost works but still temporarily stops showing its value in some rows while selecting an item in combobox list on the current row
I find using a normal Query easier while testing when my query criteria links back to a field control on my form
Any ideas on improving this?
I am trying to keep my combobox showing correct values on all rows in my multi row subform but only showing valid items relating to a another field on the same row when clicked
I have tried using OnEnter event to set my combobox to a restricted query matching another field on my form and OnExit to reset it to an unrestricted list
This almost works but still temporarily stops showing its value in some rows while selecting an item in combobox list on the current row
I find using a normal Query easier while testing when my query criteria links back to a field control on my form
Any ideas on improving this?
If I understand your last post, then you want to change the values that show in a combo box (based upon the values in another fied in the same record), in a continuous form. This is not a good idea. As you have seen, when you select a record, those other records that don't have the same value in the "other field" as the select record will display blanks. This is because the value associated with those controls is no longer in the RowSource for that record. The value is still stored properly, but because that value is not in the RowSource, it displays as a NULL (or empty combo box).
Generally, I don't use continuous forms for data entry (at least not in cases where I want a comb box to be based upon another control on the form). In these instances, I use a query as my rowsource, and display the info in a locked textbox instead of a combo. Then, I provide a way to edit the record via a pop-up form, which only works for the current record, and allows the functionality you are looking for.
Generally, I don't use continuous forms for data entry (at least not in cases where I want a comb box to be based upon another control on the form). In these instances, I use a query as my rowsource, and display the info in a locked textbox instead of a combo. Then, I provide a way to edit the record via a pop-up form, which only works for the current record, and allows the functionality you are looking for.
ASKER
fyed I understand some of these issues but apart from this one combobox our form works well as a continuous form
Setting the cmbobox to a row related query OnEnter works quite well (thought not perfect)
Can I have an example of how you use a pop-up form to do the same
Setting the cmbobox to a row related query OnEnter works quite well (thought not perfect)
Can I have an example of how you use a pop-up form to do the same
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
My own solution now works well but little advice offered in this question seemed to guide me to my current method
The normal syntax would be StageListNew.RowSource = "MyQueryName"
If the query refers to the control on your form, then you wouldn't need to reset the rowsource, but just requery it like Me!StageListNew.requery
Also, i wouldn't run the code on the Enter event of the list box, otherwise every time the user selects the control, it will requery unnecessarily. Run it on the OnCurrent event of the form. If it depends on a control that can change on the current row, then also set it to run on the AfterUpdate event of that control.