how to populate a list box

[mdavid]

Hi I have a list box with the following Row Source:


SELECT tblCommonNames.CommonName FROM tblCommonNames WHERE (((tblCommonNames.SpeciesID)= Forms!MempSpeciesfrm!SpeciesID) And ((tblCommonNames.NameLang)="English")) ORDER BY tblCommonNames.CommonName;

and Row Source Type = Table/Query

I've created a relationship between tblCommonNames and the forms table source

What do I need to do / check to populate the list box?

Thanks for any help

David

[June7]

What is the issue? What happens - error message, wrong results, nothing?

Exactly what do you mean by 'populate' - show items list or save selected item to a field?

Looks like what you have should create the items list.

[mdavid]

Hi, Sorry I find it difficult to present the whole picture within this text box - at 3am, but in the light of day I'll try and make it clearer.
I have a form displaying the first 3 fields below from the Plant Species table:



I also have a CommonNames table:



There can be several common names in the same language for a species.

On my form I want a list box that displays all the common names in English for the current species, so far I have

List Box
Row Source: SELECT tblCommonNames.CommonName FROM tblCommonNames WHERE (((tblCommonNames.SpeciesID)= Forms!MempSpeciesfrm!SpeciesID) And ((tblCommonNames.NameLang)="English")) ORDER BY tblCommonNames.CommonName;

Row Source Type: Table/Query

In the below relationships the form is based on MempSpecies and items for the listbox in the form are taken from tblCommonNames





























My problem is that the listbox is empty - even though there are English names for the current species - and I'm not receiving any Error message or notificatiion of any problem.

Thanks for any help

David

[June7]

You mean there are no items listed? Are you navigating records? Do you have code that requeries the listbox when moving to another record?

[mdavid]

Correct there are no items listed, yes I am navigating records, no I have no code.

Here is the property sheet data tab for the list box:

[andy49]

You need to requery listbox when the form goes to another record if you haven’t already done so.

On the form update event maybe as list9.requery

[davegri]

In post #3 you gave a query string as the rowsource, but in post #5 it shows a named query. Does running that named query show any rows? Does it throw an error?
Seeing a pic of the property format tab would also be helpful.

[mdavid]

Hi,

Made a little progress, I discovered that the column width was 0 so I increased it, now I see values in the listbox, but they don't change when I navigate thru the MempSpecies table, they're fixed on the value for the 1st record.
Another question do I need a Control Source for the list box?

The named query SQL view:
SELECT tblCommonNames.CommonName
FROM tblCommonNames
WHERE (((tblCommonNames.SpeciesID)=[forms]![MempSpeciesfrm]![SpeciesID]) AND ((tblCommonNames.NameLang)="Hebrew"))
ORDER BY tblCommonNames.CommonName;

Running the query works if I replace [forms]![MempSpeciesfrm]![SpeciesID] with a specific value

Below is the listbox property format tab








































Thanks for all your help
David

[davegri]

private sub Form_Current()
    list9.requery
end sub

[mdavid]

Hi Dave this looks good, excuse my ignorance how/where do I implement it?

OK got it - form property sheet > on current > code builder

How come this doesn't happen automatically when forms!MercSpecies! SpeciesID changes

Thanks, your help is much appreciated, now I can get some sleep!

[June7]

You would also need the same code in the SpeciesID AfterUpdate event or the SpeciesName GotFocus event.

Also, set the SpeciesName field to Null in the SpeciesID AfterUpdate event. Because if the SpeciesID is changed, the saved SpeciesName is no longer appropriate.