Unusual ComboBox and ListBox Requery Problem

[edsuk]

Firstly - Happy New Year To All

Below is some code that populates a Contact list box for a Customer:

SELECT CustomerContactTbl.CustomerContactID, CustomerContactTbl.CustomerID, [CustomerContactTbl]![Title] & " " & Trim([CustomerContactTbl]![FirstName] & " " & [CustomerContactTbl]![Surname]) AS Contact, CustomerContactTbl.Position, CustomerContactTbl.Tel AS [DDI Tel], CustomerContactTbl.Mobile, CustomerContactTbl.EMail
FROM CustomerContactTbl
WHERE (((CustomerContactTbl.CustomerID)=[Forms]![CustomersFrm]![CustomerID]));

This works fine when form is loaded and even when searching through records.

However if a new contact is added (using a different form) when the list box is requeried from the other form all the data is displayed again with the exception of the 'Contact' which as can be seen above is a concatenated field (Title, FirstName, Surname).

The code below is used in the new contact form to requery the database:

Form_CustomersFrm.lstContact.Requery

The curveball is that the above worked fine until my return to work this week. During shutdown the company moved to SBS Server and now this odd event is happening.

No data is lost (tables are OK) and if form is closed and opened again the 'Contact' name is visible. It appears to only occurs when the form has lost focus and gets requeried? Can anyone shed any light on this?

Thanks for any help.

This is one example, the same applies to any other list boxes or combo boxes that get requeried (again only concatenated fields are affected - and initially data is visible?)

Mark...

 

[Crowley16]

sounds strange...

have you tried to move the sql into a query, and bounding the control to that query...

--------------------
Procrastinate Now!

 

[TheAceMan1]

How are ya edsuk . . .

. . . to requery the listbox:

[blue]   Dim prp As Property
   
   Set prp = CustomersFrm!lstContact.RowSource
   prp = prp
   Set prp = Nothing[/blue]

See Ya! . . . . . .

 

[edsuk]

Crowley16

Thanks for responding - however this problem also occurs with bound controls and saved queries. Again the data is OK in the back end, for example the UserID is assigned to the customer but you cannot see the name?

TheAceMan1

Always good to hear from you but at the moment I am still experiencing problems.

To add have noticed that it is not only requery that causes this problem.

The database tracks Customers and Enquiries/Jobs. If you open the Enquiry Form (from the Customer Form) then close it the concatenated data fileds in all the list boxes in the Customer Form are blank (although originally populated when form loaded), F9 does repopulate the fields but I cannot at this moment in time understand why? This application has been live some 6 months and was working until this week?? very strange.

 

[TheAceMan1]

edsuk . . .

For reasons I can't explain, [blue]requery[/blue] rarely works on a listbox, but resetting the [blue]rowsource[/blue] does! do try my previous code along with the following SQL for the listbox:

[blue]SELECT CustomerContactID, CustomerID, [Title] & " " & Trim([FirstName] & " " & [Surname]) AS Contact, Position, Tel AS [DDI Tel], Mobile, EMail FROM CustomerContactTbl WHERE ([CustomerID] = [Forms]![CustomersFrm]![CustomerID]);[/blue]

I couldn't say the method of [blue][CustomerContactTbl][purple]![/purple][Title][/blue] is valid. For SQL, [blue][CustomerContactTbl][purple].[/purple][Title][/blue] is the accepted practice . . .

See Ya! . . . . . .

 

[edsuk]

It appears the problem was due to the upgrade. Not all W2K patches had been installed.

Anyway they have now done this and the forms are working as they were last year.

Thanks For Your Suggestions.

Mark...