Solved

VBA to dynamically set my form ComboBox RowSource to an existing query

Posted on 2010-11-15
10
9,240 Views
Last Modified: 2013-11-28
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-StagesCurrentProject]
End Sub

I can set a full SQL Query but I want to direct it to an existing Query
0
Comment
Question by:ARampton
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 3

Expert Comment

by:Billy_London
Comment Utility
what is the control name whose value affects the rowsource? Is this refered to in the query as a filter in the query you are trying to use?
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
...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
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
Comment Utility
Why do you need to do this?  I could understand doing it if you were basing it the value of another control on a particular record, but that is not what you have indicated by your example (although it sounds like that is what you want).  Generally, when I want the RowSource of a combo box to be based upon the value of another control on the form, I create the query for that control to look something like:

SELECT ID, Field1 FROM yourTable WHERE [Field2] = Forms!yourFormName!OtherControlName

If you do this, then all you need to do is requery the control, and you would normally do this in the Form_Current event, and in the AfterUpdate event of the control on which the query is based (OtherControlName)

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
^ Good Point...
0
 

Author Comment

by:ARampton
Comment Utility
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?

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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.
0
 

Author Comment

by:ARampton
Comment Utility
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
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 500 total points
Comment Utility
What I do is add a button to the forms footer, and use that button to open a small form (you could base it on the continuous form, but set its Default View to "Single Form" something like:

Private Sub cmd_EditRecord

    docmd.OpenForm "frmSingleForm",,,"[ID] = " & me.ID, acFormEdit,acDialog
    me.refresh

End Sub

Use of acDialog will prevent your users from doing anything else before they close the popup form, and the subsequent line (me.refresh) will update the recordset of your continuous form with the changes you made when editing the record in Single Form view.

   
0
 

Assisted Solution

by:ARampton
ARampton earned 0 total points
Comment Utility
I have found a good solution to using a ComboBox (whose values vary depending on another filed on the same row) in a continuous subform

I have first set the ComboBox row rource to full query that lists all possible values so all rows show with their correct values when the form opens

When Entering or clicking the ComboBox I reset its row source to a query whose criteria is linked the matching field on the current row so the combo box only shows valid choices for this row

When Exiting then ComboBox reset the row source back to the full query

If left other rows may not display their ComboBox values

To avoid the other rows appearing to loose their ComboBox values while the list is active I place a TextBox bound to the same control and locked exactly under the ComboBox

Set the ComboBox background to clear so the TextBox shows through whne it has no matching value to display

This is tricky to get just right but seem to work really well
0
 

Author Closing Comment

by:ARampton
Comment Utility
My own solution now works well but little advice offered in this question seemed to guide me to my current method
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now