listbox population 1

[ytakbob]

ON a subform I have a combo box and a list box.
Both controls have the exact same Row source type and Row source and only the Combo box populates. ?
Part of the criteria is comparing a database field with a field on the subform. I know I have the syntax right because it works for the combobox. I have been looking at this for 4 hours. Anythinig that I should think about or look at?
I can get the list box to populate when I do not compare against a subform field...but of course I need to compare against the subform field.
The reason I am using the list box is that the value shows when the form is initially loaded (when/if it woould work) as compared to the combo box which does not show the "top" value unless you click on the control.
Would appreiciate any help direction. Be gentle ! Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[Taff07]

Does the subform contain a a single set of info or a continous list of records? It maybe that on initial load of the forms the system can`t decide what to look at and so uses null and returns nothing to the list box. You can try to requery the listbox after loading to see if this is the case. just create a temp command button to run it.

Hope this helps in some way, I`m curious as to the reason otherwise

Ian

 

[tomschafer]

Try adding an onopen event to the listbox and add this code to it...

Me.Requery

 

[ytakbob]

Thanks guys..but no luck.
There is no onopen event for a listbox.
and a requery of the listbox does not do it.
Again, the exact same sql statement for a combo box works:

SELECT DISTINCTROW Occurrence.ocdate, Occurrence.Oid, Orders.Oid
FROM Orders INNER JOIN Occurrence ON Orders.Oid = Occurrence.Oid
WHERE (((Orders.Oid)=[Forms]![Patient Orders]![Orderssubform]![Oid]));

Is there a way for me to tell where this sql may be failing
for this list box ?
Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[Taff07]

Not sure of this. Is the SQL completely identical for both controls?

Is there any chance of having a look at the form itself? Maybe give me a clearer idea. Are you running 2k or '97?

Ian

 

[ytakbob]

Sure
I am 97 for this project due to the users license.
Give me your email and I'll send you the 1.2 meg access file.
Is that cool ?
You'll see the Patient Orders form using the orderssubform
I have a renamed orderssubform_listbox which is the one with the non-wrking listbox.

Bob Bob Schmid
bob_schmid@hmis.org

 

[Taff07]

My Email is ian.oldreive@barclays.co.uk

i`ll see what i can do.

 

[ytakbob]

Ian, that did it! I'm getting data. One last thing which
lends it self to something unique to subforms I think.
I am getting the same values for each of the subform "rows".
It's like the listbox sql is only executing once, not for every record on the subform. What event on the form or subform can I force this sql to be executed for every record
showing on the subform ? It's as if access does not recogzie this unbound control as part of the subform.

But....regardless, thanks alot for your help !!!!! Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347

 

[Taff07]

i thought you may come back with that one

You are uanble to do this with the continuos style of form as far as I know. The list box can only be directed to use the values of the record that is currently in focus.
I know how to do it for just one possible instance but you require more than one variant for each record. I`m afraid it`s out of my scope but if anyone else knows a solution then I`d be happy to know myself

Ian

 

[Taff07]

Just thought,

If what you mean is that the values don`t change when you change record then you need to put the following line of code into the On_Current event of your subform:

lstocdate.Requery

This will force the list to refresh on the value of the new selected record each time

Hope this is what you wanted.

Ian

 

[ytakbob]

That's what I needed to know, whether the list box could participate on a continuous form. Where is this documented???

Thank you so much for your help !!!!!! Bob Schmid
bob_schmid@hmis.org
330-746-1010 ext. 1347