List box row source (2k3)

Topic

New Reply

Again, the conversion to 2k3 is killing me. I have a list box that I use to display training information from a table filtered for a specific person (By SSN). There IS information in the table, so that’s not the problem. Here’s the row source:

SELECT tblPFT.PFT_DATE AS [DATE], tblPFT.PFT_SCORE AS SCORE, tblPFT.PFT_CLASS AS CLASS FROM tblPFT WHERE (((tblPFT.SSN)=forms!frms1!sfrms1_individualinfo!ssn));

forms!frms1!sfrms1_individualinfo!ssn is a bound text box, and the field is a text field.

Reply | Quote

Replies

What is your question?

Reply | Quote

Can you see any inherent problem with my row source? When I view the subform this listbox rests on, no information is displayed, even though there is text in the box.

Reply | Quote

Create a query with the same SQL as the row source of the list box.
Open the form and navigate to a suitable record, then switch the query to datasheet view. What happens?
– Records are displayed
– No records are displayed
– A parameter prompt is displayed
– You get an error message

Reply | Quote

No records are displayed.

Reply | Quote

If you don’t get a parameter prompt or an error message, the SQL is valid and the reference to the form is recognized. So the query really doesn’t find any matching records, and you have to find out why. Any chance that the SSN on the form is slightly different from the one in the table? For example, could one of the two contain leading or trailing spaces? The query looks for an exact match.

Reply | Quote

Hans,
It is an exact match. I even opened tblPFT to double-check.

Reply | Quote

I don’t think we’ll get further without seeing the database…

Reply | Quote

Grumble grumble grumble…I knew that was coming. Here it is.

Reply | Quote

When I select Jezza in lstNames, lstPFT shows 2 records with SSN = 0888888888
When I select Hans in lstNames, lstPFT shows 1 record with SSN = 0999999999

This is in Access 2002. I won’t be able to test on Access 2003 before tomorrow afternoon at the earliest.

Reply | Quote

Hans,
I am also able to use it in 2002….I’m trying to find ONE fix for BOTH versions. No worries, although the I’m leaving the unit I created this database for tomorrow morning.

Reply | Quote

I was able to test a bit earlier than I expected. The list box works correctly for me in Access 2003 too. Behavior is the same as in Access 2002.

Since I can’t reproduce the problem, I can only suggest some more testing. You could make the text box SSN on sfrmS1_IndividualInfo visible and place it somewhere you can see it. And add SSN to the row source of lstPFT (you must also increase the Column Count to 4, and make the list box wider). That’ll enable you to see if the SSNs match up.

Reply | Quote