Data in ListBox.... (1 Viewer)

[Wallie]

Hey again,

The resuls from a search are listed in a listbox. This listbox has a number of columns with data corresponding with a record (duh).
The results also contain values which are selected from a combobox. These values are represented by (id)numbers. Is it possible to get the value corresponding to the (id)number ? So instead of a list with 1,2,3 etc a list of name1,name2,name3 etc

Thanx....

Sorry I think this topic belongs to "Forms"....

[This message has been edited by Wallie (edited 04-08-2002).]

 

[TessB]

Hello Wallie.

Yes, of course what you want to accomplish is absolutely do-able. Go to design view and click on your listbox control, go to data and copy the SQL query that runs your listbox. You will find that in your properties called Row Source. Post that SQL query here and also tell me what number is in the Bound Column in your properties on that Data tab as well.

Question: Is that IDnumber a primary key in your table?

If so, the wizard usually guides you right through setting up your listbox to DISPLAY one field, i.e., a name field while STORING the value of another field, i.e. the primary key or IDnumber.

Anyway, post the query and I'll see if I can tell you what you need to change. (Unless, of course, you figure that out yourself first!)

Tess

 

[Wallie]

This is my query (it's in Dutch ;-))
SELECT *
FROM tblActies
WHERE ((([tblActies].[WerknemerId])=[Forms]![actieOverzicht]![WerknemerId]) And (([tblActies].[soortActieID])=[Forms]![actieOverzicht]![soortActie]))
ORDER BY [tblActies].[WerknemerId], [tblActies].[datum];

number of clumns: 7
Bound column is: 1

Idnumber is Primary yes in its table

(Just for the record...it's not a dropdown box)

Thanx....

For a screenshot: http://www.xs4all.nl/~pcmwalta

[This message has been edited by Wallie (edited 04-09-2002).]

 

[Fizzio]

In your query for the listbox, rather than showing the ID of the linked fields, add the tables where the names are (ie 'bedrijven', 'ContPers' and 'Epmploy') to the query and add the fields for the names you want shown to the grid (ideally, place them next to the corresponding IDFields) Then hide the ID fields by deselecting the show tickbox. If all is well, you should see the ID replaced by the name in your listbox.

HTH

 

[Wallie]

I've rebuilt my query to this:
SELECT [tblActies].[Id], [bedrijven].[bedrijfsnaam], [ContP].[achternaam], [employ].[Voornaam], [tblActies].[datum], [tblSoortActie].[type], [tblActies].[vervolgdatum], [tblActies].[actie]
FROM tblActies, tblBedrijven, tblContactpersonen, tblMedewerkers, tblSoortActie
WHERE ((([tblActies].[bedrijfsNummer])=[bedrijven].[Id]) And
(([tblActies].[contactpersoon])=[ContP].[Id]) And
(([tblActies].[WerknemerId])=[employ].[WerknemerId])And
(([tblActies].[soortActieID])=[type].[soortActieID]))
ORDER BY [bedrijven].[bedrijfsnaam], [tblActies].[datum];
This is the result: http://www.xs4all.nl/~pcmwalta/
Looks good......BUT, when I want to narrow down the search by inserting "[tblActies].[WerknemerId] = [Forms]![form1]![werknemerFld]" into the query it doens't work....I get no results from the query....
Is there another of doing this query ?

Thanx again....

 

[Fizzio]

You are using a lot of where clauses. If you have relationships between the tables, you will not need all the where clauses. Look at the relationships of your tables and make sure that they are all linked. This enables you to pull the other data from the tables. Your only where criteria should be the ones in your previous query to narrow the search. Make sure that you have join lines between the tables in the query design grid.

 

[Wallie]

They are linked, so what's next ? I've read something about INNER/OUTER JOINS .... but i've no idea what to do with them....

[This message has been edited by Wallie (edited 04-09-2002).]

 

[Fizzio]

An Inner Join means that it will only display records where the criteria are the same. An outer join will display all the records of one table and only those that match from another table. The default join is an Inner Join and that should be sufficient for what you want. The relationships in the query design should mirror those in the relationship window. Try this as your SQL

SELECT [tblActies].[Id], [bedrijven].[bedrijfsnaam], [ContP].[achternaam], [employ].[Voornaam], [tblActies].[datum], [tblSoortActie].[type], [tblActies].[vervolgdatum], [tblActies].[actie]
FROM tblActies, tblBedrijven, tblContactpersonen, tblMedewerkers, tblSoortActie WHERE ((([tblActies].[WerknemerId])=[Forms]![actieOverzicht]![WerknemerId]) And (([tblActies].[soortActieID])=[Forms]![actieOverzicht]![soortActie]))
ORDER BY [tblActies].[WerknemerId], [tblActies].[datum]

 

[Wallie]

When I use that Query, it show too many records. Every record AT LEAST 10 times....

 

[Fizzio]

Send it to me if you want. Make sure that you compact and zip it first though.