Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-08-2017, 05:12 AM   #1
vent
Newly Registered User
 
Join Date: May 2017
Posts: 160
Thanks: 22
Thanked 0 Times in 0 Posts
vent is on a distinguished road
List box no longer shows newest entry

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:

Code:
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:

Code:
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!

__________________
- Rob the Rookie Programmer
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
vent is offline   Reply With Quote
Old 06-08-2017, 05:17 AM   #2
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 203
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: List box no longer shows newest entry

Sounds like you need to requery the listbox recordsource after the new agency record has been saved.

Sent from my SM-G900P using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 06-08-2017, 05:19 AM   #3
vent
Newly Registered User
 
Join Date: May 2017
Posts: 160
Thanks: 22
Thanked 0 Times in 0 Posts
vent is on a distinguished road
Re: List box no longer shows newest entry

Quote:
Originally Posted by BigHappyDaddy View Post
Sounds like you need to requery the listbox recordsource after the new agency record has been saved.
Do you mean something like this:

Code:
Me.SearchResults.Requery
Be applied to AfterUpdate or AfterInsert event procedure?

__________________
- Rob the Rookie Programmer
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
vent is offline   Reply With Quote
Old 06-08-2017, 05:36 AM   #4
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 203
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: List box no longer shows newest entry

If SearchResults is the name of your listbox, then I do believe that is the correct syntax. As for where to put it, I think you are referring to form frmAddNewAgency's AfterUpdate event. Should work, but I don't know the rest of your solution to know if it "fits" there.

Sent from my SM-G900P using Tapatalk
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
BigHappyDaddy is offline   Reply With Quote
Old 06-08-2017, 05:48 AM   #5
vent
Newly Registered User
 
Join Date: May 2017
Posts: 160
Thanks: 22
Thanked 0 Times in 0 Posts
vent is on a distinguished road
Re: List box no longer shows newest entry

Quote:
Originally Posted by BigHappyDaddy View Post
If SearchResults is the name of your listbox, then I do believe that is the correct syntax. As for where to put it, I think you are referring to form frmAddNewAgency's AfterUpdate event. Should work, but I don't know the rest of your solution to know if it "fits" there.
Yes, SearchResults is the list box name. I tried putting:

Code:
Me.SearchResults.Requery
Into fromAddNewAgency's AfterUpdate event, but I get an error saying

Compile Error:
Method or data not found


With SearchResults being highlighted. I assume this is because SearchResults is not on this form, but on another form called frmMain
__________________
- Rob the Rookie Programmer
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
vent is offline   Reply With Quote
Old 06-08-2017, 06:33 AM   #6
BigHappyDaddy
Coding Monkey Wanna-Be
 
BigHappyDaddy's Avatar
 
Join Date: Aug 2012
Location: Puyallup, WA
Posts: 203
Thanks: 6
Thanked 38 Times in 37 Posts
BigHappyDaddy is on a distinguished road
Re: List box no longer shows newest entry

Replace "SearchResults" with the name of your ListBox.
Code:
Me.ListBoxName.Requery
But your error makes me think that this solution is not quite complete. I believe your listbox is on frmMain, but you want to update the listbox AFTER the new record in frmAddNewAgency is saved.
You might have to explicitly reference frmMain in the AfterUpdate event of form frmAddNewAgency. So try something like this:

Code:
 
Forms!frmMain.ListBoxName.Requery
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by BigHappyDaddy; 06-08-2017 at 06:42 AM.
BigHappyDaddy is offline   Reply With Quote
Old 06-08-2017, 06:49 AM   #7
vent
Newly Registered User
 
Join Date: May 2017
Posts: 160
Thanks: 22
Thanked 0 Times in 0 Posts
vent is on a distinguished road
Re: List box no longer shows newest entry

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!


__________________
- Rob the Rookie Programmer
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
vent is offline   Reply With Quote
Reply

Tags
add a new record , listbox value , on click event , vba access 2010

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Report no longer shows all selected data pernic80 Reports 21 12-02-2010 07:58 AM
Expression builder: for newest date and for newest time, then dmax number +1 ssgagosto Forms 3 10-20-2009 07:00 AM
Changed BE to SQL - Sub form no longer allows data entry PeterOC SQL Server 2 12-06-2007 09:32 PM
Find Newest Record, Close Word & Not In List andysgirl8800 Forms 7 07-11-2005 04:19 PM
combo box question - auto selecting newest entry amieth Forms 3 03-11-2004 11:37 AM




All times are GMT -8. The time now is 10:36 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World