First off, I must make it clear that I am new at Access and at this forum. My questions are likley to be very trivial - patience please . . .

Right now I have on a form a couple of buttons that run macros that reset the form's "RecordSource" to a different table or query. This works fine.

Now I want to use a combo box and have the "recordSource" reset based on the user's choice. My problem is that I don't know how to determine what the user's choice was !? I am assuming that there is a property off the combo box that indicates this but I could be wrong. ????

The Access "help" files say "You can use an unbound combo box to store a value that you can use with another control." but there is not a clue anywhere as to how this is actually done.

Thanks in advance.

Well it depends on what you have the combo returning. Assuming that you are changing the recordsource to a named query and the queryname is the bound column then all you need is:

Me.RecordSource = Me.cboRecordsource
Me.Requery

in the After Update event of the combo.

When a user makes a selection in a combo (or enters data into any control), the selected value is stored in the Value property of the control. That property is the default property so just referencing the control returns the stored value.

Hi Scott,

Thanks for your quick reply. You have probably answered my question but I am so new that I am not sure! I will restate my question using an exact example.

Right now I have a command button that runs a macro that I "built".

The macro "action" is a SetValue,
with the first argument being [Forms]![CahpiMainTableForm1].[RecordSource]
and the second being "NameSortQuery".

I want to have a combo box (unbound I think) that lists 3 or 4 queries by name, such that when the user makes a choice my macro can access the string value of the choice and use it as the second argument above.

So I would have SetValue
and [Forms]![CahpiMainTableForm1].[RecordSource]
and ????

Are you suggesting Me.cboRecordsource where I have the ???? ?

Thanks again.

Controls raise events that are most often caused by a user action.
You make your application respond to events by writing code in the
corresponding event procedure.

For your example do the following.

Open the form in design view.
Right click on the combobox
Select properties from the popup menu.
Select the Event tab in the property sheet.
Find After Update in the property sheet.
Click the textbox, select [Event procedure]
Click the build button that appears.
When the VB editor opens paste the following between Sub/End Sub
lines of the AfterUpdate procedure.

[Forms]![CahpiMainTableForm1].[RecordSource] = Me.cboRecordsource


Open the form, make a selection and see what happens.

The advantage to using VBA code and Event procedures is that when you
become more experienced you can add to the robustness of your application
using error handling and all manner of other clever things.

SUCCESS!

Thanks "Ace" for your suggestion, and thanks Scott for your earlier reply. At this time I am steering away from code, but I do understand that it is a more robust and flexible approach.

What now is working for me in a "built" macro is:

SetValue
[Forms]![CahpiMainTableForm1].[RecordSource]
[Forms]![CahpiMainTableForm1]![SortCombo].[Text]

This only works because the strings that I have typed into the combo box definition are the exact names of the queries, but that is OK for what I am trying to do. Next project I'll give myself more time and learn how to do it right.

I don't think you should waste your time on macros. Iu sey Ace beat me to it with exact step by step instructions. Really, the right way to do this is with 2 simple lines of code. They are easily understandable.

Me.RecordSource = Me.SortCombo
Me.Requery

The Me. is shorthand that replaces [Forms]![CahpiMainTableForm1]. So the left side of the first line is the same as your macro. The = sign replaces SETVALUE. The right side is what you are setting the Recordsource to. You don't need to specify the Text Property. I'm not sure the second line is even necessary, but it makes sure the form refrehes with the new Recordsource.

Thanks Scott. That is good explanation. I actually have a bit of a programming background That is the approach that I will use from now on.

Right now it is working well enough that all of the old paper based files can be entered into the database (not by me thank goodness). When more features or changes are asked for I will revisit this macro and apply the advice that you guys have given.

This is my first forum and I'm really impressed by how quickly I could get answers. Thanks again.

One more question though - am I responsible to somehow end this topic or does that happen automatically?