By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,611 Members | 1,642 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,611 IT Pros & Developers. It's quick & easy.

access 2003 problem w/ combo box query after return from modal form

P: n/a
Greetings. I have a problem with a combo box incorrectly displaying
blank items when returning to a form from a modal form. It's fine when
the main form first comes up, but gets messed up when the main form is
reactivated following opening and closing a modal form. Strangely,
this was not a problem until I started using my Access 2000 db in
Access 2003 (as an Access 2000 db). Details follow...

I have an unbound combo box on my main form which allows selection of
a user. Its rowsource is a query. When a user is selected, a subform
displays a summary record for each order associated with this user.
Pressing a button in the subform header brings up a modal form
containing the details of the selected user order. This form is opened
by VB code as follows:
DoCmd.OpenForm "orderdetail", , , , , , OrderID
After the orderdetail form is closed by the user, the combo box on the
main form contains a bunch of blank items, nothing else. It looks like
there are the correct number of items, but they're all blank, and
selecting them does nothing. In the main form's Activate event
handler, I have a Requery for the combo box control.

Also, the behavior is somewhat erratic. The usual behavior is that the
1st time I go to the order detail form, then close it, the main form
combo box has the correct items. Any subsequent openings and closings
of the order detail form results in the combo box with blank items.
Sometimes, it will be ok for 2 or 3 times of invoking the detail form;
as I said it's erratic. Once it's messed up it stays messed up until I
close the main form and re-open it.

I've spent a ridiculous amount of time trying to resolve this, and
still have the problem. Any ideas?

Thanks,

Will Eichert
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
DFS
Will,

What's your SQL statement for the combobox rowsource? Maybe you've written
it as an updateable query and the data is being changed by the subform or
modal form.


"will eichert" <wa*******@hotmail.com> wrote in message
news:11**************************@posting.google.c om...
Greetings. I have a problem with a combo box incorrectly displaying
blank items when returning to a form from a modal form. It's fine when
the main form first comes up, but gets messed up when the main form is
reactivated following opening and closing a modal form. Strangely,
this was not a problem until I started using my Access 2000 db in
Access 2003 (as an Access 2000 db). Details follow...

I have an unbound combo box on my main form which allows selection of
a user. Its rowsource is a query. When a user is selected, a subform
displays a summary record for each order associated with this user.
Pressing a button in the subform header brings up a modal form
containing the details of the selected user order. This form is opened
by VB code as follows:
DoCmd.OpenForm "orderdetail", , , , , , OrderID
After the orderdetail form is closed by the user, the combo box on the
main form contains a bunch of blank items, nothing else. It looks like
there are the correct number of items, but they're all blank, and
selecting them does nothing. In the main form's Activate event
handler, I have a Requery for the combo box control.

Also, the behavior is somewhat erratic. The usual behavior is that the
1st time I go to the order detail form, then close it, the main form
combo box has the correct items. Any subsequent openings and closings
of the order detail form results in the combo box with blank items.
Sometimes, it will be ok for 2 or 3 times of invoking the detail form;
as I said it's erratic. Once it's messed up it stays messed up until I
close the main form and re-open it.

I've spent a ridiculous amount of time trying to resolve this, and
still have the problem. Any ideas?

Thanks,

Will Eichert

Nov 12 '05 #2

P: n/a
The data that should be displayed in the combo box (a list of users) is
not being changed by the subform or the modal form. The actual query
follows:

SELECT users.ID, users.lastname & IIf(IsNull(users.[middle initial]) And
IsNull(users.firstname),"",", ") & users.firstname &
IIf(IsNull(users.[middle initial])," "," " & users.[middle initial] & "
") AS fullname
FROM users
ORDER BY users.lastname, users.firstname, users.[middle initial];

- Will

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
DFS

"will eichert" <wf*******@ucdavis.NO_SPAM_PLEASE_DELETE_THIS.ed u> wrote in
message news:3f*********************@news.frii.net...
The data that should be displayed in the combo box (a list of users) is
not being changed by the subform or the modal form. The actual query
follows:
Does your Users table have a primary key on it? Is the lastname field
required? Try changing the query to a SELECT DISTINCT WHERE lastname IS NOT
NULL.

Also, why are you requerying the Users combobox each time the main form is
Activated? Are users being added by someone besides you?

What do you mean when you say "this was not a problem until I started using
my Access 2000 db in Access 2003 (as an Access 2000 db)."?
SELECT users.ID, users.lastname & IIf(IsNull(users.[middle initial]) And
IsNull(users.firstname),"",", ") & users.firstname &
IIf(IsNull(users.[middle initial])," "," " & users.[middle initial] & "
") AS fullname
FROM users
ORDER BY users.lastname, users.firstname, users.[middle initial];

- Will

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #4

P: n/a
DFS,

Thanks for your response.

My Users table has an Autonumber primary key. None of the name fields
are required.

I'm requerying only as an attempt to solve this problem. It doesn't work
anyway, so forget about it.

This combo works fine until I leave the form (open a modal form) and
come back to it (close the modal form, which re-activates this one),
then it displays empty items instead of items containing the user names.

In its current implementation, nobody anywhere is modifying the users
table in any way.

I never had this problem until after upgrading from Access 2000 to
Access 2003. I kept this .mdb in Access 2000 format to stay compatible
with a user. She does not have this problem using her installation of
Access 2000.

Also please see this other thread on what appears to be the same issue
from another user.

newsgroup microsoft.public.access.forms
thread "Access 2002/3 Disappearing Combo Content"
started Tue, 6 Jan 2004 09:45:07 -0800

Thanks for any further help,

Will
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #5

P: n/a
Greg from the other thread discovered that removing the IsNull
statements from the query gets rid of this problem. Works for me, too.
Unfortunately, we both need this functionality (IsNull) to build our
name string from the separate name fields.

We have no idea why the IsNull statements would work the first time the
form is displayed, but on re-activation they cause a combo box filled
with blank items. Any ideas? I suspect there's a Microsoft problem
lurking here.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.