Controlling a Listbox with Macro Builder

[TerpiesatBelmont]

Good Evening All,

I was looking to make the "Criteria" in the roia query a bit more dynamic. I have the Criteria (in this example, on the field tTRK) controlled by a listbox in a form called frmCritieria using:

[Forms]![frmCriteria]![Track Criteria]

It works and I plan to make a listbox for each field in roia. When I go to the form and click on a track so that its highlighted the roia is then filtered to only that track.

What I am trying to do was use Macro Builder to select/highlight a record in the listbox, but I can't seem to get it to work. I tried using the OpenForm, GoToControl, and GoToRecord actions, but none of them actually select a record in the listbox after opening the form.

If anyone has any tips, please let me know.

Thank you very much and BOL!

[njcurveball]

Did a quick search and found something that might apply to what you are doing. Maybe some of the Access Gurus can take it further?

Put a list box onto the form using the toolbox, but make sure that the
wizard is turned on.

Follow the steps:
- select "I want the list box to look up the values in a table or query.
- Next: select the Workers table from the provided list.
- Next: select the address and map code related fields from the list at the left side. Do not leave out the record's unique ID, even you won't see it in the box.
- Next: you can sort the records, but this step can be ignored.
- Next: you will see the layout of the list box. The unique ID will be hidden (Hide key column box is checked).
- Next: give the box a meaningful name (like MapCodeLokkup). It will be very important later.

At this point you have ALL records in the list box, so you need to put a
filter on. Do the following:
- Open the properties window of the list box. Scroll to the Row Source line,
and click OUTSIDE the end of the line. It will become a small button with 3
dots. The query will open.
- Now you need to put a criteria onto the streeetname filed: [Forms]![Workers].[StreetName] -- be careful with the syntax, type it as you see it here, except change the name of your field if it is different. This is how you refer to the form's field: you want to see in the list box only records which have the same StreetName value. Close the query with the upper right X, and save it when asked.
- The next step will be filling the code on your form by the selected record
in the list box. Before doing so, take a look at the query again and count the columns. The first column (the unique record ID which is hidden in the box) is zero, the second column is 1, the third is 2, etc.
- Now back to the box property window, click on the Events tab, and select
[Event procedure] from the dropdown list at the On Click or On Double Click
line. (When you click into the empty line it becomes a dropdown list.)
- Once the words are there, click on the small button with 3 dots at the right side, outside on the same line.
- Now you are at the VBA code window. You have 2 lines already. Type the following between the 2 lines:
Me.MapCode = Me.MapCodeLookup.Column(x)
- Replace the x with the column number where your map code is.
- Change the field name in this code since I don't know what you call it. The
MapCodeLookup is the name if the list box.

If you have a map code field on your Workers form and fill it with the selected record in the list box, you can open your Map form with a button, based on the map code on the Workers form.

[TerpiesatBelmont]

Thanks so much NJ!

I am going to have to take some time to dissect everything you posted, I don't have the time right this second. It appears that I have everything done up until the last few lines (the VBA), which is what I would be looking to add. Hopefully it answers my question!

I probably should have mentioned that I am clearly new to access and have been using Access 2010, which has the "Macro Builder" feature. It basically allows users to choose commands and is much easier for people like me who do not know VBA. It appears that even if I want to do the bulk of my processing using the Macro Builder built-in functions, I might have to do this part the hard way (VBA).

The weird think about what I was looking to do is that I have been googling it like crazy and I haven't been able to find anything. If I explain it correctly, it doesn't seem like that obscure of a process to want to do. I was hoping I would be able to find a Macro Builder function that I could use, but there might not be a lot of processes written about it yet since it is somewhat of a new feature.

Thanks again, maybe other people will chime in, and I will let you know how it goes from here!

[Donnie]

Am I following your train of thought properly? You are attempting to have a list of tracks in a list box, and when you choose a track out of the list you want that to work as a filter for the ROI Query on the form?

[TerpiesatBelmont]

Yes, that is correct, however I already have that part setup correctly. I have many listboxes with many factors that serve as a filter on the ROI query.

For example, if I go to the Track listbox on my "Criteria Form" and select BEL so that it is highlighted and then go to Race Number listbox on the same form and select 4 and then run the query and the ROI reports I can see the results for all of the 4th races at Belmont. I can easily go back and choose AP and then re-run the reports and see all of the results for 4th races at Arlington Park to compare.

I want to expand on this already existing system by using the macro builder in Access 2010 (or VBA, if I must) to cycle through what is selected in the listbox.

For example, if I want to see how every track does in the 4th race, rather then having to select the first track, run the query, record the results, then go in and manually click the second track, run the query, records the results, etc. I was looking to automatically select the first item in the listbox, run the query, record the results, and then automatically move onto the "next" record in the listbox.

[Donnie]

You can achieve the same result if you use parameter fields in the criteria boxes.
IE: In the track criteria box, type:

[Which track would you like to see?]

When you run the query a parameter box opens first and you type AQU into it and all you get is AQU races. If you put one into the Race Number field, you can also filter to just specific race numbers, as you pointed out above. I would encourage you to use the LIKE keyword before the opening "["...they will then accept wildcards.....

Which track.....? Put in an "*"
<second question>Which race?.....enter 4.

You will receive all tracks, all 4th races.

I use this technique to find and scratch horses at a moments notice.

[tomcat]

That doesn't seem to work for me. I'm using 2000 access. Too old? I know the problem.

[Donnie]

No not at all Tom...I'll post a graphic later tonight, since I will be watching the Breeder Cup races tonight!

[TerpiesatBelmont]

Donnie,

Thanks so much for your response. While what you are saying is a valid alternative, it doesn't answer exactly what I am looking for.

I can do the samething that I already do with listboxes with a parameter field, but I already have it setup with listboxes so it probably doesn't pay to change it. If I used parameter fields, I still would have to manually type each track name in, run the query, then type in the next track, and so on. I am looking for a way to select a track as the criteria, run the query, and then automatically go on to the next track and run the report again.

Perhaps if I give an example that would be more complicated to do manually rather then my example of selecting a track and selecting Race 4 (which isn't unrealistic to do manually) it would make more sense.

Let's say I have a form solely made up of listboxes for the fields in the ROI query. The default setting would be to have all of the items selected in all of the listboxes (which I haven't done yet, but hopefully can figure out). I would like to use the Macro Builder (or VBA) to:

1. Open the Form (I have already done this with the OpenForm action)

2. Select a track from the Track Listbox (this is what I can't figure out. How to select and highlight a listbox item/record automatically. I think if I get this down I can hopefully eventually figure everything else out)

3. Starting with one of the listboxes, select the first item, run the query, record the results

4. Move onto the next item in the same listbox and repeat

5. Re-select all the items in the first listbox and then move onto the next listbox and repeat going through each item.


If it was just one listbox, such as Race Number, it wouldn't be that big of a deal to select each race number 1-10 (or however high it goes) and see if their is a difference in ROI at a certain track. It also wouldn't be a big deal to use parameter fields, where I can type in the number 1, then re-run the query, type in 2, and so on.

As you can see, however, this would get to be a hassel if I had to select each item in many different listboxes. If I first look at the K-Rank listbox and then move onto the HTR-Rank list box, and then the FR1-Rank listbox etc. there are going to be hundreds of selections made, and thats just for one track. Recording the ROI once the query is run on each item in each listbox would eventually make some kind of ROI dump (as mentioned in a previous thread) for each track (or for all the tracks if I select them all).

As much as I greatly appreciate responses, all of this is not that important, I won't be bothered if no one responses, it's an annoying/niche question. More importantly, Donnie (and everyone else) I hope you enjoy the Breeder's Cup Races!!