listbox shows ID numbers instead of text values

[merlin777]

I have already asked this before but I' struggling to crack it so forgive me for asking again.

I have a table called tblappointments
one of the fields is called therapistsid, it's a number field and is related to the table tbltherapists and a field called therapytype which contains text.

I have a form into which i have put a list box and a text box.
The list box has the contents of a query in it and the query's job is to filter fields in tblappointments by date and show the two fields therapistid and therapy type. It gets the date from the text box on the form.



The datasheet view of the query is correct. It only shows records with the date I put in the text box on the form and it shows the therapist's name and type of therapy.

My problem is that the list box on the form doesn't seem to be displaying the result of the query. It is showing an unfiltered list of the therapistid and therapytype fields - the therapistid is the id number and not the therapist's name but the therapytype is the correct text.

What am I doing wrong?

[June7]

Same answer as given in your other thread:

Advise not to use lookups in table http://access.mvps.org/access/lookupfields.htm

If you want to show the descriptive text in listbox, its RowSource will have to be a query that joins tables.

[alansidman]

Show us the SQL statement from your lookup. Also, what have you set in the properties for the columns to display from the SQL statment?

[merlin777]

Same answer as given in your other thread:

Advise not to use lookups in table http://access.mvps.org/access/lookupfields.htm

If you want to show the descriptive text in listbox, its RowSource will have to be a query that joins tables.

I don't think I am using a lookup field but you've seen my db so if you can see otherwise please point them out to me?

I thought I had joined tables with the query. In the query design view with the table relationships visible, i can see see tblappointments and tbltherapists and they are joined from the therapist field to the id field by a many to one relationship.

[merlin777]

Show us the SQL statement from your lookup. Also, what have you set in the properties for the columns to display from the SQL statment?

where do I find that, alan?

btw - solved the lack of filtering problem... finger trouble.

[June7]

I lose track of what databases I have seen. Doubt I still have it. Want to provide it again or post link to thread it can be found in?

[ssanfu]

@ June.... dB at https://www.accessforums.net/access/...tml#post250412
Post # 9

And yes, "tblAppointment" has multiple lookup fields.
See http://access.mvps.org/access/lookupfields.htm

[merlin777]

If you've used the file from my old thread it may be out of data. the new one is attached

[June7]

DO HAVE lookups set in table. See the Therapist field in tblAppointment. It's set for a combobox and has a RowSource that includes the ID and name fields from tblTherapists. This obscures the actual value in the Therapist field. You see the FirstName but the actual value is ID. Same for Patient, appstartid, appendid.

The listbox on the form has a RowSource that is a query object. That query does not include the LastName and FirstName fields.

Then the listbox ColumnWidths property is not set. Might want to control the widths of the columns and hide the ID column: 0";0.5";0.5";0.5".

Need code in the textbox AfterUpdate event to requery the listbox: Me.Combo18.Requery

If you are going to use non-U.S. non-Access standard dates, should probably review http://allenbrowne.com/ser-36.html

[ssanfu]

You really need to work on a naming scheme.

You have:
a Table named "tblAppointment" (OK)
a Query named "tblAppointment_Crosstab" (bad - a proper prefix would be "qry" or "q")
a Form named "tblAppointment1" (bad - a proper prefix would be "frm" or "f")

You might want to Google "Naming conventions"......

[merlin777]

lol - I can see how bad naming convention makes it harder for a newbie and also those that give their time to help.

but in my defence I'll say 2 things...

I've been accepting access's default object name offerings out of ignorance and impatience - why doesn't access offer suitable prefixes by default?
This data base is my first and its just to learn and experiment with. When I've learned enough i'll redo it from scratch with correct conventions.

[ssanfu]

We've all been there.... I asked a friend to look at my first "real" dB because I was having problems; I was committing "spreadsheet". She worked for a bank as a programmer.
She was very blunt: "This is a very bad design."

why doesn't access offer suitable prefixes by default?

Every one develops their own style of programming: naming conventions, UI, etc.

Another friend got me started using a different way of naming tables and fields. The table names are 3 words: "employee_Name_Def" or "company_carrier_link" (junction table).
The fields are named using the first letters of the table name: "end_city", "end_LName", "end_FName", "ccl_pk", "ccl_icdFK",.....
Makes it easy to determine which table the field is in especially in queries. One thing I always do is rename controls - mostly so I don't get confused if I am referencing a field or control.

The fun thing is you get to develop your own style.

When I've learned enough i'll redo....

I hope to get there too.... someday.....