Problems displaying an SQL select array to a listbox

[MForrest]

Morning, its my first time using SQL select arrays, the query is working fine but when it comes to displaying it to my listbox the query results do not show, there is no error message but four column width lines representing where my results should be. Is it simply a property setting on my listbox. Any ideas? This is my code:

partnumber = thisform.text1.value
select ;
bom.bomchild, ;
partmaster.partdesc, ;
bom.bomqty, ;
bom.bomqty ;
from efacs_tables!bom inner join efacs_tables!partmaster ;
on bom.bomchild == partmaster.partnum;
where bom.bomparent == partnumber ;
order by bom.bompos ;
into array a_query1

if a_query1(1,1) = " " then
= messagebox('There is no Bom Child Number for this part.',16,'No Search')
else
thisform.list1.rowSourceType = 5
thisform.list1.rowSource = 'a_query1'
endif

 

[sampaloc]

try setting values for

list1.ColumnWidths = ?
list1.ColumnCount = ?

 

[MForrest]

Thanks for your reply

I have set the following to:

list1.ColumnWidths = 175,475,50,50
list1.ColumnCount = 4

I am using the debugger to check my query to see if it is working and to check what should be displayed to my listbox. When I return to my form, the query seems to be displayed but then dissappears instantly and will not reappear when re-queried.

.....confused!

 

[Mike Lewis]

MForrest,

It sounds like the array is going out of scope.

If your SELECT is in the Init of the combo box (or the form), then you probably need to make the array a custom property of the form. Then, reference it as THISFORM.a_query1.

Apart from that, your code looks OK (at a quick glance).

One other point. You write:

if a_query1(1,1) = " " then

It would be better to use _TALLY if you want to see if any results were returned. It is more reliable than the method you used.

Also, you don't need then after an IF.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[ROGERDODGE]

For my money, I would use a cursor everytime over an array.
You wont get these kind of problems.

Dont you just love Fox !

Roger Maynard
Frome
Somerset

 

[mkrausnick]

I had the same problem last week, and fixed it be adding a call to REFRESH() after setting the ROWSOURCE:

thisform.list1.REFRESH()

Mike Krausnick
Dublin, California

 

[Mike Lewis]

ROGERDODGE,

Hey, Roger. I think we met on Compuserve a while back. Don't know if you remember?


Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[MForrest]

Thanks for all replies, I have just tried using the refresh method on my list but it does not seem to solve my problem, I feel I may have missed setting one of the properties on my list. Again I can see the 4 column width lines on my list which seem to depict how many returns I am displaying to the list but the data is not showing.

How frustrating

 

[MForrest]

Hi Mike, yep still having trouble. Im new to this game so a lil short on where to go with this one.

My code for my many select SQL queries is within a method of the form, I have not tried making my arrays, custom properties of the form as yet but would like to give it a try. Can u point me to any info on how to set up my arrays as custom properties of a form, I know how to add the properties but how to implement it through my code is my next step.

Dazed and confused :-D

 

[craigsboyd]

Mforrest,

In design view with your form up...go to the Form menu and click New Property...then in the resulting dialog box type in a_query1(1) and click Add. Now, if you look in the properties window you will see the property at the bottom of the list for your form. You can select into it by doing something like:

Select * from MyTable into Array (thisform.a_query1)

...and you can reference elements like:


thisform.a_query1(1) &&or whatever

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[MForrest]

Hi slighthaze, I have added the following properties to the form: a_query1,a_query2,a_query3 and a_query4 when added default to .F. (unsure what this is about)
I have changed my code to reference the array, here is an example of my code when querying into array a_query1. When I run this I get an error message saying a_query1 is not an array.

select ;
bom.bomchild, ;
partmaster.partdesc, ;
bom.bomqty, ;
bom.bomqty ;
from efacs_tables!bom inner join efacs_tables!partmaster ;
on bom.bomchild == partmaster.partnum;
where bom.bomparent == partnumber ;
order by bom.bompos ;
into array (thisform.a_query1)
query_count = _tally

Is it something obvious??

 

[craigsboyd]

type in a_query1(1) not just a_query1 when you are creating the properties.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[MForrest]

Thanks for the help with common properties I have them in place and working. I still have not fixed my first problem of not seeing my array query results in the grid though

 

[craigsboyd]

...I still have not fixed my first problem of not seeing my array query results in the grid though

I must have missed something, I thought we were working with a listbox here not a grid.

Slighthaze = NULL

[ul][li]FAQ184-2483
An excellent guide to getting a fast and accurate response to your questions in this forum.[/li][/ul]

 

[KoenPiller]

Hi Mr. Forrest,

Regarding your 1st problem: Please read Q129329 in Micorsoft Knowledge Base "How to populate a form's ListBox object from an array"[COLOR=/blue]

Further any specific reason why you populate the array 2 times with the same value? (BOMQTY form your table BOM?).

Koen

 

[jhodge]

MForrest I was wondering if you ever solved your dilema. Having the same problem and can't seem to get the listbox to hold the values. If you have solved it could you post your solution.

 

[iyarosh]

I guess it's my turn now to ask if either MForrest or Koen were able to find the solution to this problem. Having exactly the same situation. Thanks in advance!