Listbox will not Requery AfterUpdate
in [General]
|
Prev: prova
Next: off with their masks
From: Nathan D Clark on 3 Mar 2010 02:52 I am running Access 2007. I have two listboxes sitting side by side on the same form. One has phone number categories - lstCategories The second listbox has name, phone numbers - lstPhoneNumbers Both listboxes are unbound I had this working once where you could click the category in the first box and it would filter the second listbox to the phone number in a different category. Example: click category box select family, friend etc. the second box would only show numbers of family or category you selected in the first box. Now, the boxes work perfectly fine as far as retrieving the data. The problem is the Requery code stopped working. I have to actually click a Refresh button to refresh after clicking the category. I have two copies of the same DataBase. On the original DB it works perfectly fine. The copy of this same DB will not requery after selection of the category. I am lost on why the code would stop working in the copy. Here is the code: Private Sub lstCategories_AfterUpdate() Me.lstPhoneNumbers.Requery Me.lstPhoneNumbers = Null End Sub Here is the Row Source of the First list box - lstCategories SELECT [PhoneCategories].[PhoneCategory] FROM PhoneCategories ORDER BY [PhoneCategory]; Here is the Row Source of the Second listbox - lstPhoneNumbers SELECT Contacts.ContactID, (FirstName+" ") & LastName, PhoneNumber, Category FROM Contacts INNER JOIN ContactPhones ON Contacts.ContactID=ContactPhones.ContactID WHERE PhoneCategory=Form!lstCategories Or Form!lstCategories Is Null ORDER BY LastName, FirstName;
From: Jeanette Cunningham on 3 Mar 2010 03:03 The simplest explanation is corruption in the listbox and/or the form that doesn't work. Import the working form into the database with the form where the listbox doesn't work and see it that fixes the problem Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Nathan D Clark" <NathanDClark(a)discussions.microsoft.com> wrote in message news:9EBA270F-49EC-41AC-9BB4-95FCF8E1D8CE(a)microsoft.com... >I am running Access 2007. I have two listboxes sitting side by side on the > same form. > > One has phone number categories - lstCategories > The second listbox has name, phone numbers - lstPhoneNumbers > Both listboxes are unbound > > I had this working once where you could click the category in the first > box > and it would filter the second listbox to the phone number in a different > category. > > Example: > click category box select family, friend etc. > the second box would only show numbers of family or category you selected > in > the first box. > > Now, the boxes work perfectly fine as far as retrieving the data. The > problem is the Requery code stopped working. I have to actually click a > Refresh button to refresh after clicking the category. > > I have two copies of the same DataBase. > > On the original DB it works perfectly fine. The copy of this same DB will > not requery after selection of the category. I am lost on why the code > would stop working in the copy. > > Here is the code: > Private Sub lstCategories_AfterUpdate() > Me.lstPhoneNumbers.Requery > Me.lstPhoneNumbers = Null > End Sub > > Here is the Row Source of the First list box - lstCategories > SELECT [PhoneCategories].[PhoneCategory] FROM PhoneCategories ORDER BY > [PhoneCategory]; > > Here is the Row Source of the Second listbox - lstPhoneNumbers > SELECT Contacts.ContactID, (FirstName+" ") & LastName, PhoneNumber, > Category > FROM Contacts INNER JOIN ContactPhones ON > Contacts.ContactID=ContactPhones.ContactID WHERE > PhoneCategory=Form!lstCategories Or Form!lstCategories Is Null ORDER BY > LastName, FirstName; > > > > >
From: Nathan D Clark on 3 Mar 2010 07:11 Ms Cunningham, I cannot figure this out to save my life. I can make a copy and the copy works fine. However, if I move the copy off of my desktop and put it on another drive the listboxes will not requery. I can move the DB back to my desktop and it will work. Same goes with a copy of the DB. Can you suggest anything on why this may be happening? "Jeanette Cunningham" wrote: > The simplest explanation is corruption in the listbox and/or the form that > doesn't work. > Import the working form into the database with the form where the listbox > doesn't work and see it that fixes the problem > > > Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia > > "Nathan D Clark" <NathanDClark(a)discussions.microsoft.com> wrote in message > news:9EBA270F-49EC-41AC-9BB4-95FCF8E1D8CE(a)microsoft.com... > >I am running Access 2007. I have two listboxes sitting side by side on the > > same form. > > > > One has phone number categories - lstCategories > > The second listbox has name, phone numbers - lstPhoneNumbers > > Both listboxes are unbound > > > > I had this working once where you could click the category in the first > > box > > and it would filter the second listbox to the phone number in a different > > category. > > > > Example: > > click category box select family, friend etc. > > the second box would only show numbers of family or category you selected > > in > > the first box. > > > > Now, the boxes work perfectly fine as far as retrieving the data. The > > problem is the Requery code stopped working. I have to actually click a > > Refresh button to refresh after clicking the category. > > > > I have two copies of the same DataBase. > > > > On the original DB it works perfectly fine. The copy of this same DB will > > not requery after selection of the category. I am lost on why the code > > would stop working in the copy. > > > > Here is the code: > > Private Sub lstCategories_AfterUpdate() > > Me.lstPhoneNumbers.Requery > > Me.lstPhoneNumbers = Null > > End Sub > > > > Here is the Row Source of the First list box - lstCategories > > SELECT [PhoneCategories].[PhoneCategory] FROM PhoneCategories ORDER BY > > [PhoneCategory]; > > > > Here is the Row Source of the Second listbox - lstPhoneNumbers > > SELECT Contacts.ContactID, (FirstName+" ") & LastName, PhoneNumber, > > Category > > FROM Contacts INNER JOIN ContactPhones ON > > Contacts.ContactID=ContactPhones.ContactID WHERE > > PhoneCategory=Form!lstCategories Or Form!lstCategories Is Null ORDER BY > > LastName, FirstName; > > > > > > > > > > > > > . >
From: John Spencer on 3 Mar 2010 09:41 Are you putting the database in a TRUSTED location? Code runs in trusted location - code does not run in non-trusted location. Check the library references. To do its job, Access makes use of various external program and object libraries. If you move a database from one machine to another, these references may be "broken". When this happens, you need to take steps to let Access repair the reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING. Here are MVP Doug Steele's instructions for how to do it: *** Quote *** Any time functions that previously worked suddenly don't, the first thing to suspect is a references problem. This can be caused by differences in either the location or file version of certain files between the machine where the application was developed, and where it's being run (or the file missing completely from the target machine). Such differences are common when new software is installed. On the machine(s) where it's not working, open any code module (or open the Debug Window, using Ctrl-G, provided you haven't selected the "keep debug window on top" option). Select Tools | References from the menu bar. Examine all of the selected references. If any of the selected references have "MISSING:" in front of them, unselect them, and back out of the dialog. If you really need the reference(s) you just unselected (you can tell by doing a Compile All Modules), go back in and reselect them. If none have "MISSING:", select an additional reference at random, back out of the dialog, then go back in and unselect the reference you just added. If that doesn't solve the problem, try to unselect as many of the selected references as you can (Access may not let you unselect them all), back out of the dialog, then go back in and reselect the references you just unselected. (NOTE: write down what the references are before you delete them, because they'll be in a different order when you go back in) For far more than you could ever want to know about this problem, check out http://www.accessmvp.com/djsteele/AccessReferenceErrors.html Just so you know: the problem will occur even if the library that contains the specific function that's failing doesn't have a problem. **** End Quote **** John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Nathan D Clark wrote: > Ms Cunningham, I cannot figure this out to save my life. I can make a copy > and the copy works fine. However, if I move the copy off of my desktop and > put it on another drive the listboxes will not requery. I can move the DB > back to my desktop and it will work. Same goes with a copy of the DB. > > Can you suggest anything on why this may be happening? > > "Jeanette Cunningham" wrote: > >> The simplest explanation is corruption in the listbox and/or the form that >> doesn't work. >> Import the working form into the database with the form where the listbox >> doesn't work and see it that fixes the problem >> >> >> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia >> >> "Nathan D Clark" <NathanDClark(a)discussions.microsoft.com> wrote in message >> news:9EBA270F-49EC-41AC-9BB4-95FCF8E1D8CE(a)microsoft.com... >>> I am running Access 2007. I have two listboxes sitting side by side on the >>> same form. >>> >>> One has phone number categories - lstCategories >>> The second listbox has name, phone numbers - lstPhoneNumbers >>> Both listboxes are unbound >>> >>> I had this working once where you could click the category in the first >>> box >>> and it would filter the second listbox to the phone number in a different >>> category. >>> >>> Example: >>> click category box select family, friend etc. >>> the second box would only show numbers of family or category you selected >>> in >>> the first box. >>> >>> Now, the boxes work perfectly fine as far as retrieving the data. The >>> problem is the Requery code stopped working. I have to actually click a >>> Refresh button to refresh after clicking the category. >>> >>> I have two copies of the same DataBase. >>> >>> On the original DB it works perfectly fine. The copy of this same DB will >>> not requery after selection of the category. I am lost on why the code >>> would stop working in the copy. >>> >>> Here is the code: >>> Private Sub lstCategories_AfterUpdate() >>> Me.lstPhoneNumbers.Requery >>> Me.lstPhoneNumbers = Null >>> End Sub >>> >>> Here is the Row Source of the First list box - lstCategories >>> SELECT [PhoneCategories].[PhoneCategory] FROM PhoneCategories ORDER BY >>> [PhoneCategory]; >>> >>> Here is the Row Source of the Second listbox - lstPhoneNumbers >>> SELECT Contacts.ContactID, (FirstName+" ") & LastName, PhoneNumber, >>> Category >>> FROM Contacts INNER JOIN ContactPhones ON >>> Contacts.ContactID=ContactPhones.ContactID WHERE >>> PhoneCategory=Form!lstCategories Or Form!lstCategories Is Null ORDER BY >>> LastName, FirstName;
From: Nathan D Clark on 3 Mar 2010 16:52
Thanks John! That was it. I had to add the location in the Trust Center. "John Spencer" wrote: > Are you putting the database in a TRUSTED location? Code runs in trusted > location - code does not run in non-trusted location. > > Check the library references. > > To do its job, Access makes use of various external program and object > libraries. If you move a database from one machine to another, these > references may be "broken". > > When this happens, you need to take steps to let Access repair the > reference(s) ON THE COMPUTER WHERE THE FAILURE IS OCCURING. > > Here are MVP Doug Steele's instructions for how to do it: > > *** Quote *** > > Any time functions that previously worked suddenly don't, the first thing to > suspect is a references problem. > > This can be caused by differences in either the location or file version of > certain files between the machine where the application was developed, and > where it's being run (or the file missing completely from the target machine). > Such differences are common when new software is installed. > > On the machine(s) where it's not working, open any code module (or open the > Debug Window, using Ctrl-G, provided you haven't selected the "keep debug > window on top" option). Select Tools | References from the menu bar. Examine > all of the selected references. > > If any of the selected references have "MISSING:" in front of them, unselect > them, and back out of the dialog. If you really need the reference(s) you just > unselected (you can tell by doing a Compile All Modules), go back in and > reselect them. > > If none have "MISSING:", select an additional reference at random, back out of > the dialog, then go back in and unselect the reference you just added. If that > doesn't solve the problem, try to unselect as many of the selected references > as you can (Access may not let you unselect them all), back out of the dialog, > then go back in and reselect the references you just unselected. (NOTE: write > down what the references are before you delete them, because they'll be in a > different order when you go back in) > > For far more than you could ever want to know about this problem, check out > http://www.accessmvp.com/djsteele/AccessReferenceErrors.html > > Just so you know: the problem will occur even if the library that contains the > specific function that's failing doesn't have a problem. > > **** End Quote **** > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Nathan D Clark wrote: > > Ms Cunningham, I cannot figure this out to save my life. I can make a copy > > and the copy works fine. However, if I move the copy off of my desktop and > > put it on another drive the listboxes will not requery. I can move the DB > > back to my desktop and it will work. Same goes with a copy of the DB. > > > > Can you suggest anything on why this may be happening? > > > > "Jeanette Cunningham" wrote: > > > >> The simplest explanation is corruption in the listbox and/or the form that > >> doesn't work. > >> Import the working form into the database with the form where the listbox > >> doesn't work and see it that fixes the problem > >> > >> > >> Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia > >> > >> "Nathan D Clark" <NathanDClark(a)discussions.microsoft.com> wrote in message > >> news:9EBA270F-49EC-41AC-9BB4-95FCF8E1D8CE(a)microsoft.com... > >>> I am running Access 2007. I have two listboxes sitting side by side on the > >>> same form. > >>> > >>> One has phone number categories - lstCategories > >>> The second listbox has name, phone numbers - lstPhoneNumbers > >>> Both listboxes are unbound > >>> > >>> I had this working once where you could click the category in the first > >>> box > >>> and it would filter the second listbox to the phone number in a different > >>> category. > >>> > >>> Example: > >>> click category box select family, friend etc. > >>> the second box would only show numbers of family or category you selected > >>> in > >>> the first box. > >>> > >>> Now, the boxes work perfectly fine as far as retrieving the data. The > >>> problem is the Requery code stopped working. I have to actually click a > >>> Refresh button to refresh after clicking the category. > >>> > >>> I have two copies of the same DataBase. > >>> > >>> On the original DB it works perfectly fine. The copy of this same DB will > >>> not requery after selection of the category. I am lost on why the code > >>> would stop working in the copy. > >>> > >>> Here is the code: > >>> Private Sub lstCategories_AfterUpdate() > >>> Me.lstPhoneNumbers.Requery > >>> Me.lstPhoneNumbers = Null > >>> End Sub > >>> > >>> Here is the Row Source of the First list box - lstCategories > >>> SELECT [PhoneCategories].[PhoneCategory] FROM PhoneCategories ORDER BY > >>> [PhoneCategory]; > >>> > >>> Here is the Row Source of the Second listbox - lstPhoneNumbers > >>> SELECT Contacts.ContactID, (FirstName+" ") & LastName, PhoneNumber, > >>> Category > >>> FROM Contacts INNER JOIN ContactPhones ON > >>> Contacts.ContactID=ContactPhones.ContactID WHERE > >>> PhoneCategory=Form!lstCategories Or Form!lstCategories Is Null ORDER BY > >>> LastName, FirstName; > . > |