Listbox nightmare!

[rookery]

Hi guys

I have a Form with a Listbox and a Text box on it. The form is bound to table A and contains contact info etc. I want the listbox to contain date values from a field in Table B. When the user selects a choice from the ListBox I want the Textbox to be populated with the info entered on that particular date. Like a diary I suppose.

I created a Query and put it in the ListBox's Row Source property which selected the dates for that contact, based on their CustID which is in Table A. There is also a CustID field in Table B.

For example: (not proper SQL) "Select all Dates from Table B where CustID=Forms![FormName]!CustID"

Then in the Forms current event I wrote:

List1.Requery

This seems to work to a point but there are occasions when the values in the ListBox aren't in descending order even though thats the way they have been sorted in the table. I cant figure out why this happens occasionally. Doesnt the query start at the top record each time it starts a "search"? Table B has another field as its Primary Key. Can anyone help?

 

[jimmythegeek]

The list box SQL will always sort in the order the records were entered into the table unless you specify otherwise in an ORDER BY statemtent:

Select Datefield FROM TableB WHERE CustID=" & Forms!FormName!CustID & " ORDER BY DateField DESC"

Or remove the DESC to sort ascending. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[rookery]

Cheers Jim works a treat!!