Listbox will not Requery AfterUpdate

  • Thread starter Thread starter Nathan D Clark
  • Start date Start date
N

Nathan D Clark

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;
 
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
 
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 said:
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 said:
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;
Click to expand...


.
Click to expand...
 
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
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 said:
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 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Thanks John! That was it. I had to add the location in the Trust Center.

John Spencer said:
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
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 said:
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

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;
Click to expand...
Click to expand...
.
Click to expand...
 
Nathan D Clark said:
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;
Click to expand...
 
Back
Top