Solved: Using a query with a variable + SourceObject

[Touni102]

Hello all,

The problem i've got now is that I'm trying to change the SourceObject of a panel in my form so it displays a query of mine (in a combo box update event). The problem is my query takes in a variable, and so I would like that variable to be chosen by the current value of the combo box. I don't know how to reference my created query in vba, nor how to pass the value of the combo box to the query.

My query looks like this:
SELECT * FROM [Computer Software] WHERE ProgramName=Pick;

So when I run that query, I'm able to type <Pick> in a textbox to run the query desired...

Any ideas?

[CreganTur]

To reference the value of a form object for a query, you have to refer to it like this:
forms!FormName!ObjectName
Where FormName is the actual name of the form, and ObjectName is the name of the object whose value you want.

You will need to define this as a parameter (in query design view click Query -> Parameters). The parameter's name needs to be the same forms!FormName!ObjectName string used in the query, and select the data type.

HTH:thumb

[Touni102]

The query works perfectly now! but what vba code would I need to write in the update event handler for the combo box in order to update the query on the form?

[CreganTur]

Try:Me.Requery

[Touni102]

I don't know how to use it...

I'm not sure what Me refers to here, but i'm guessing it is a reference to the object that the code is on... In that case wouldn't that refer to the Combo Box? How do I tell the Query on the form to Requery from the code in the combobox event?

[CreganTur]

The 'Me' keyword refers to the currently active form. It requeries the entire form.

Please explain in more detail exactly what you are trying to do with this combobox.

[Touni102]

Ok, In the form, I have a combo box that is already populated with stuff (from another query). Also, I dragged the desired query onto the form, and It displays the records depending on the selected item in the combo box. The problem is, when I click a new entry on the combo box, nothing happens.

[CreganTur]

Add Me.Requery to the end of the code for your combobox that updates the query; see if that fixes your issue.

[Touni102]

I tried that already. This is what I have now:

Private Sub cbProgram_AfterUpdate()
Me.Requery
End Sub

cbProgram is the name of my Combo Box.
Nothing happens. I figured I had to tell the Subform to redisplay it's contents, but I don't know how

[CreganTur]

Sorry, somehow the fact that it's a subform didn't register with me. For that you have to use something like this:
Forms![MainFormName]![SubformControlName].Requery

[Touni102]

It works! thanks again!