Drop down box query returns only first record accessed

G

Guest

I have a drop down box on a continuous form. My query is as follows:

SELECT Projects.*, Projects.Proj_CustomerID
FROM Projects
WHERE (((Projects.Proj_CustomerID)=[Forms]![To Do List - All Active
Items]![To Do_CustomerID]));

The problem I have is that when I use the drop down box for the after the
first time, the where criteria seems not to update.

For example, if the drop down box is used a customer ID of "Mickey", the "To
Do_CustomerID" part of the where clause displays only items associated with
customer "Mickey" from there on out, even if I am on a different customer
record.

If I exit the form, and select a customer of "Mouse", then all all subseqent
drop downs show "Mouse".

Any ideas - This one has to be a simple one you would think!!
 
M

Marshall Barton

MSROOKIE said:
I have a drop down box on a continuous form. My query is as follows:

SELECT Projects.*, Projects.Proj_CustomerID
FROM Projects
WHERE (((Projects.Proj_CustomerID)=[Forms]![To Do List - All Active
Items]![To Do_CustomerID]));

The problem I have is that when I use the drop down box for the after the
first time, the where criteria seems not to update.

For example, if the drop down box is used a customer ID of "Mickey", the "To
Do_CustomerID" part of the where clause displays only items associated with
customer "Mickey" from there on out, even if I am on a different customer
record.

If I exit the form, and select a customer of "Mouse", then all all subseqent
drop downs show "Mouse".


This is a tricky situation on a continuous (or datasheet)
form.

The first thing you need to do regardless of the type of
form is add a line of code to the form's Current event
procedure:

Me.comboboxname.Requery

This will allow you to use the combo box normally. Note
that you should already have the same line of code in the
combo box's AfterUpdate event procedure.

The messy part of the problem will then become apparent.
You won't be able to see the selected item on some/many of
the other rows of the form. There is no simple solution for
this, so if it is a serious issue for you, post back with
more details about your form. The form's RecordSource
table/query and the fields in the combo box's RowSource
query are essential information to dealing with ths issue.
 
G

Guest

Thanks Marshall for the reply

I stumbled onto this solution about 2 hours prior to your post, but
hopefully this will help others as I couldn't find anything on via a search.

Two followups though:

1. What I did was put the refresh in the "lost focus" event. Thought
process there was "on current" is triggered more often (every time a record
is switched) vs. "lost focus" (only when the actual drop down box is
potentially used). Is this a sound strategy? (Its seems to work).

2. I do not understand the"messy" part of the problem. I would appreciate a
liitle more description on the symptoms of this problem so I can recognize it
in the future.

Thanks..


Marshall Barton said:
MSROOKIE said:
I have a drop down box on a continuous form. My query is as follows:

SELECT Projects.*, Projects.Proj_CustomerID
FROM Projects
WHERE (((Projects.Proj_CustomerID)=[Forms]![To Do List - All Active
Items]![To Do_CustomerID]));

The problem I have is that when I use the drop down box for the after the
first time, the where criteria seems not to update.

For example, if the drop down box is used a customer ID of "Mickey", the "To
Do_CustomerID" part of the where clause displays only items associated with
customer "Mickey" from there on out, even if I am on a different customer
record.

If I exit the form, and select a customer of "Mouse", then all all subseqent
drop downs show "Mouse".


This is a tricky situation on a continuous (or datasheet)
form.

The first thing you need to do regardless of the type of
form is add a line of code to the form's Current event
procedure:

Me.comboboxname.Requery

This will allow you to use the combo box normally. Note
that you should already have the same line of code in the
combo box's AfterUpdate event procedure.

The messy part of the problem will then become apparent.
You won't be able to see the selected item on some/many of
the other rows of the form. There is no simple solution for
this, so if it is a serious issue for you, post back with
more details about your form. The form's RecordSource
table/query and the fields in the combo box's RowSource
query are essential information to dealing with ths issue.
 
M

Marshall Barton

You should Requery (not Refresh) the combo box in both the
form's Current event and in the [To Do_CustomerID] control's
AfterUpdate event (LostFocus is a little later, often
indistinguishable, but not quite the optimum place).

If you haven't seen the messy problem, then there's no need
to go into it - yet. You'll recognize it when the combo box
displays the wrong value or just blank on some(?) rows in
the continuous form (the current record is the only one
guaranteed to be correct).
--
Marsh
MVP [MS Access]

I stumbled onto this solution about 2 hours prior to your post, but
hopefully this will help others as I couldn't find anything on via a search.

Two followups though:

1. What I did was put the refresh in the "lost focus" event. Thought
process there was "on current" is triggered more often (every time a record
is switched) vs. "lost focus" (only when the actual drop down box is
potentially used). Is this a sound strategy? (Its seems to work).

2. I do not understand the"messy" part of the problem. I would appreciate a
liitle more description on the symptoms of this problem so I can recognize it
in the future.

MSROOKIE said:
I have a drop down box on a continuous form. My query is as follows:

SELECT Projects.*, Projects.Proj_CustomerID
FROM Projects
WHERE (((Projects.Proj_CustomerID)=[Forms]![To Do List - All Active
Items]![To Do_CustomerID]));

The problem I have is that when I use the drop down box for the after the
first time, the where criteria seems not to update.

For example, if the drop down box is used a customer ID of "Mickey", the "To
Do_CustomerID" part of the where clause displays only items associated with
customer "Mickey" from there on out, even if I am on a different customer
record.

If I exit the form, and select a customer of "Mouse", then all all subseqent
drop downs show "Mouse".
Marshall Barton said:
This is a tricky situation on a continuous (or datasheet)
form.

The first thing you need to do regardless of the type of
form is add a line of code to the form's Current event
procedure:

Me.comboboxname.Requery

This will allow you to use the combo box normally. Note
that you should already have the same line of code in the
combo box's AfterUpdate event procedure.

The messy part of the problem will then become apparent.
You won't be able to see the selected item on some/many of
the other rows of the form. There is no simple solution for
this, so if it is a serious issue for you, post back with
more details about your form. The form's RecordSource
table/query and the fields in the combo box's RowSource
query are essential information to dealing with ths issue.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top