Fill List Box in Form From Query

[accessuserva]

I have a form from which I run a query based on info from selected text boxes in the form. I have a list box in which I would like the results from the query to show up in.

My listbox parameters are:
Listbox name=searchresults
Control Source:[qrysitedistance]![sitename]
Row Source Type: Table/Query
Row Source: qrysitedistance

Textbox=Search Radius (final part of the info needed to run the query)

Search Button: Built from the button wizard

The query runs fine-it shows up in front of the form with the correct info in it.

The listbox though remains unfilled...Do I need to have a sql or VB statement to fill the box from the query. I am running a parameter query to get the info that I need.

Thanks

accessuserva

 

[thornmastr]

I am going to assume that what is loaded into your listbox is dependent upon the value within a control on your form. For purposes of example let’s say the criteria is a text value located in a control named tfindit. Since you need the value of tfindit to fill your listbox, put something along the following ******air code***** in the after update event of tfindit.

Dim strSQL asstring

StrSQL = “Select whatever,whatever1,etc from mytbl where somevalue = ‘” _
& me.tfindit.value & “’”

‘ double quotes are for string; not necessary if tfindit is numeric

me.searchresults.rowsource = strSQL

you’ve done it
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[FoxProProgrammer]

Hi,

I am confused by your statement "the query runs fine" and "the listbox though remains unfilled". Which query runs fine? I'm not sure what you mean by "shows up in front of the form"

Also, since you set the Control Source for the list box to Control Source:[qrysitedistance]![sitename], you have bound the control to the query. Is this what you intended to do?

The list box should be populated when the form first opens. If you want to display new data in the list box, you should execute the statement searchresults.Requery.

If you clarify some of my confusion, I'll try to help you further.

Best, dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Sorry, Robert. We were typing at the same time - again. Setting the Rowsource is another way to do it because Access Requeries the list box automatically when you set the rowsource.

Regards, dz
dzaccess@yahoo.com

 

[accessuserva]

The particular query (qrysitedistance) does not run when the form first opens because it needs data from the form in order to work. i.e. I am using lats & longs to calculate the distance between 2 sites. The base site information fill in the form after the site number is picked from another list box. The search radius is required to find any other site within that radius by virtue of lats & longs.

What I mean by the query runs fine is..after you choose the base site & input a search radius you click the search button..the query results pop up in front of the form.. they dont fill the form with the results.

Please remember that my Access knowledge to this point is very limited...this is my first database..so being simplistic in your answer would be greatly appreciated so I can understand were I need to go with this to solve the problem.

Thanks

Tw

 

[FoxProProgrammer]

Hi Tw,

If you have a query stored in the Row Source of the list box, the the query gets executed when you open the form. That's how Access initially populates the list box. Are you saying that the list box is empty when you first open the form? If a query is stored in the Row Source of the list box and it is empty when the form opens, it means that the query didn't return any records.

I don't understand what you mean by the query results pop up in front of the form. Did you write code to make the results pop up? What kind of window do the results pop up in and how did they get there?

You can do this one of two ways:

1. You can use Robert's suggestion of setting the Rowsource Property to the query. You would reference the value of the search radius and base site in the Where clause of the query. When you set the Rowsource, Access automatically does a Requery on the list box.

2. You can store the query in the Rowsource and execute searchresults.Requery in the OnClick procedure of the Search button.

In the first example, you build a new query each time the values change. In the second example, the query is fixed and you execute a Requery statement. In both cases you will need to reference the value of the other controls in the Where clause. Take your pick. Either should work. If you post the names of the controls and their data types, one of us can give you a specific query. dz
dzaccess@yahoo.com

 

[accessuserva]

You are correct in that when the form first opens the list box is empty..it should be until after the query gets run.
When I say it pops up in front of the from I am talking about in datasheet view..and no I did not write code for it to do that.....

the data needed to run the query comes from these text boxes
latdeg1
latmin1
latsec1
longdeg1
longmin1
longsec1
Search Radius

None of those have a source etc..

Search Radius is the only 1 of the lot in which you have to enter the info..the rest get autofilled after the site number is picked ...data coming from a table

the distance gets calculated by first converting the lats & longs to decimal degrees by another query and running the base against all the other sites in the database..works great except the data doesnt go where I want it to..

Note: the query (qrysitedistance) is pulling its info from another query which in turn is pulling data from another query..would that cause a problem in filling the list box?

The Query (qrysitedistance) does not have any data in it until after you give it the search parameters from the text boxes..thus the reason for the empty list box..

So is it in this case better to not have the Query stored in the row source of the list box?

I run the query from a command button based on the wizard

I am only looking for 1 field from the query to show up in the list box (sitename)
I have the column count set correctly with the other columns width set to "0"

I hope I am not being to long winded here..I am just trying to give you enough info for you to be able to help... I have been fighting with this for a week now..I cant do my report from the from because there is no data to get from it...aaaaargh

Thanks for your help & understanding

Tw