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

Dynamically changing row source of a bound combo box

P: 3
I have a form and combo boxes on it. Depending on what is current record of a form, I need to dynamically (in the code) change the row source of at least one combo box.
Problem is that when I read all controls through cycling Me.Controls - those bound combo boxes are nulls. Which is documented behavior. My question how I change row source of a bound combo box (at least one for the sake of example)in the code (on Form's Current event)
May 9 '14 #1
Share this Question
Share on Google+
5 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
So if you are looping through the controls, that means that you have a variable representing the control. In your code, once it has decided that it is the control that you want to change, then all you have to do is type .RowSource = "SELECT * FROM MyTable" after the variable.
Expand|Select|Wrap|Line Numbers
  1. Dim ctl As Control
  2.  
  3. For Each ctl In Me.Controls
  4.     If ctl.ControlType = acComboBox Then
  5.         ctl.RowSource = "SELECT * FROM MyTable"
  6.     End If
  7. Next ctl
Now you will need more testing than that, but that is the basic method to change the row source while looping through all the controls.
May 9 '14 #2

P: 3
Seth, it is not that easy. When I use this loop
Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Me.Controls
  2.     If ctl.ControlType = acComboBox Then
  3.         ctl.RowSource = "SELECT * FROM MyTable"
  4.     End If
  5. Next ctl
I see that every combobox is not found - because it is equal to null. They simply cannot be caught by this kind of loop
May 9 '14 #3

Seth Schrock
Expert 2.5K+
P: 2,951
A control can't be null, only its value can be null and this loop doesn't work with the value of the control. I work with this type of loop all the time when the data hasn't even been loaded into the form and it works fine. Try this:
Expand|Select|Wrap|Line Numbers
  1. For Each ctl In Me.Controls
  2.     Debug.Print ctl.Name, ctl.ControlType
  3. Next ctl
This will show you all the controls (visible and not visible) on your form in the immediate window (Ctrl + g to view). If you control isn't listed, then we have a different problem. What version of Access are you using?
May 9 '14 #4

P: 3
Will be able to test it tomorrow. Thank you. It looks like working piece. :-)
May 9 '14 #5

NeoPa
Expert Mod 15k+
P: 31,768
[Bessora]
I see that every combobox is not found - because it is equal to null.

This is strange. Clearly that is not the case. What I'm curious about is what did you actually see when you tested this that could lead you to believe that. I can think of nothing. If you can explain what you were thinking when you posted this I think it might help.
May 10 '14 #6

Post your reply

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