Modifying Row Source programatically

[galorin]

I have the following RowSource set up, but I need to change it due to structural changes in the database and policy changes. [Forms]![Company]![Company] is a ComboBox that contains a company name and a company ID in a hidden column. The hidden column is new and I haven't yet figured out how to modify the listboxes rowsource to reflect this change. Ideally, I want to change the search so it is looking for the Company_ID rather than a company name.

SELECT companycomments.[Entered Date], companycomments.[Comment By], IIf(Len(companycomments.comment)>=50,Left(companycomments.comment,47) & "...",companycomments.comment) AS Comment, companycomments.[Follow Up Date], companycomments.[Follow Up By], companycomments.CommentID, companies.Company
FROM companies INNER JOIN companycomments ON companies.Company = companycomments.Company
WHERE (((companies.Company)=[Forms]![Company]![Company]))
GROUP BY companycomments.[Entered Date], companycomments.[Comment By], companycomments.[Follow Up Date], companycomments.[Follow Up By], companycomments.CommentID, companies.Company, companycomments.Comment
ORDER BY companycomments.[Entered Date] DESC;

 

[KenReay]

You do not say if the SQL you posted is in VBA code, or in the RowSource of the list box / Combo.

If in the rowsource of the listbox/Combo box, why not just use the builder (three dots to right of the rowsource value, the design view of the form with listbox selected. This will take you to the query design screen which is easy enough to use for such a small change.

If it is VBA code, how about a cheat, put a breakpoint in the code jsut after the SQL value has been set, in the immediate window debug print the SQL value, then copy and paste it into the query design window SQL view. Switch back to design view, make the change, then switch back to SQL view to crib from the generated SQL

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK