So I have a list box based on a query with a number of records. My last issue was the list box not filtering based on either what the user types in or by date range. However I'm happy to say (thanks to Microsoft Community) that, now both search options work. However a new problem has occurred. A button that is used to open a form for the user to enter new information (new agency, address, insurance info, etc) did previously work as the new agency would appear in the list box but now when the user types in the new agency, nothing shows up at all. The updated SQL for the listbox (in design view) is as follows:
PARAMETERS Forms!frmMain!txtFrom DateTime, Forms!frmMain!txtTo DateTime, Forms!frmMain!SrchTxt Text ( 255 );
SELECT tblAgency.AgencyName, tblSubsidiary.Subsidiary, tblAgency.AAEndDate, tblAgency.LiabilityInsurance, tblAgency.WSIBEmployeeDeclaration, tblAgency.Address, tblAgency.City, tblAgency.Province, tblAgency.PostalCode, tblAgency.ContactName, tblAgency.ContactEmail, tblAgency.ContactPhoneNumber, tblPrograms.Program
FROM tblPrograms INNER JOIN (tblSubsidiary INNER JOIN tblAgency ON tblSubsidiary.ID = tblAgency.[Subsidiary].Value) ON tblPrograms.ID = tblAgency.Programs.Value
WHERE (tblAgency.AAEndDate Between Forms!frmMain!txtFrom And Forms!frmMain!txtTo
OR tblAgency.LiabilityInsurance Between forms!frmMain!txtFrom AND Forms!frmMain!txtTo)
AND tblAgency.AgencyName & tblSubsidiary.Subsidiary LIKE "*" & Forms!frmMain!SrchTxt & "*"
ORDER BY tblAgency.AgencyName;
I have also tried adding this bit of code to button’s on click procedure which is:
DoCmd.OpenForm "frmAddNewAgency", DataMode:=acFormAdd, WindowMode:=acDialog
When the button is clicked it opens the add new agency form (frmAddNewAgency
), the user is able to enter the new information, hit save and then close, but nothing simply happens. The new info is not visible in the list box but appears in the agency table. If anyone has any guidance on how to tackle this issue. That would be much appreciated!