List box no longer showing latest entry

[vector39]

Hi Everyone


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
Me.SearchResults.Requery

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!

[CJ_London]

you need to requery the listbox after changing the underlying data

[vector39]

you need to requery the listbox after changing the underlying data

So would something like:

Me.SearchResults.Requery

be included in the AfterUpdate or AfterInsert event procedure?

[Micron]

Since this is a select query, it has no direct effect on your table (I'm saying it's not directly responsible for updating your table). Which leads me to ask-
if you paste this sql into a new query, and the form is open and the necessary fields have data, does it actually work? You have a bunch of AND / OR operators all grouped into one set of parentheses, which could be interpreted much differently than you expect. Your post reads as if the sql statement has been updated, now it doesn't work. I'd try it in a new query and see if Access highlights any problems. If you get no records, it's usually easier to trouble shoot it there.

It is also possible that your sql could be a whole lot simpler. Perhaps you have the record id (autonumber or whatever) hidden on the form, in which could shorten things, assuming the id is common to any other table involved (it is a foreign key).

[vector39]

Hey guys

So I deleted the previous button, made a new form based on the agency table and added a new button. And now the new entry appears on the list box. So the simple solution here was to delete and add a new button. Even though this problem is now technically solved, I'm still open to suggestions about list boxes and requerying in general. Thank you all so much!

[CJ_London]

depends on how your form and listbox is intended to work


the listbox is always requeried when the form is opened

you can set code to requery it after any event where you know or think the data might change - could be

the form current event (user has selected a new record)
the listbox afterupdate event (user has selected a different option) - so perhaps the option needs to be removed from the list so it can't be selected again.
the listbox enter event (the user has changed something somewhere else on the form which affects what should be listed) - alternatively the after update event of the control which changed which affects the values to be listed (typically what are called cascading combo's) and depends on when you want the changed values to be visible - in your case perhaps the click event on your button
and for combo's, not listboxes - the 'on not in list' event - user has added a new value

but don't go requerying all the time, only when necessary, otherwise your form will slow down with constant interrogation of the tables.

[vector39]

Thank you!