Continuous form question about cursor

[gmazza via AccessMonster.com]

Hi there,
I have a continuous form with 1 of the fields in the record being a combo box.
When I am on the 1st record, I click a value in the combo box and its all
good.
On the 2nd record, after I click a record in the combo box, the cursor jumps
to the 1st records combo box and sits there.
How can I get the cursor to stay in the combo box I just change on the 2nd
record?
Any help is appreciated!

 

[Jeanette Cunningham]

Hi gmazza ,
this can happen if there is a reason why the first record could not be
saved.
Turn on 'Record selectors' for this form. - use the form's property sheet.
When using the form you can look for the pencil symbol in the record
selector when a record is being edited. If the pencil is showing for the 1st
combo when you try to click in the second combo, this indicates that the 1st
record could not be saved. This can happen if the combo is for a required
field or a field with a record validation like Is Not Null.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[gmazza via AccessMonster.com]

Thanks for your reply Jeanette!
The record is being saved though, thats the problem.
Here's my situation in a little more detail:
I have a Criteria table with these columns:
CriteriaId - autonumber - primary key
CriteriaValue - Text

I have a CriteriaOption table with these columns:
CriteriaOptionId - autonumber - primary key
CriteriaOptionValue - Text
CriteriaId - FK

I have a PatientData table with these columns:
PatientId - autonumber - primary key
PatientOptionValue - Text
CriteriaId - FK

So my 1st form is based on the Criteria table and it allows you to enter a
CriteriaValue.
My 2nd form is based on the CriteriaOption table and has a combo box filled
with CriteriaValue's that were entered on the 1st form. Then, based on the
current CriteriaValue, you can enter in a subform different
CriteriaOptionValue's.
Finally, the 3rd form is based on a combo of all 3 tables.
It is a continuous form and lined up on the left is the field CriteriaValue
from the Criteria table. It is locked and displays a record for each
CriteriaValue from the Criteria table.
The next field in the same record is a combo box based on all the different
CriteriaOptionValue's with that same CriteriaValue from the first field.
After choosing an option from the combo box, I update the Patient table based
on the CriteriaValue record I am on, and the corresponding
CriteriaOptionValue I have chosen.

Here is some data sample:

Criteria table
Criteria Id CriteriaValue
1 Cough - Day 1
2 Cough - Day 2

CriteriaOption table
CriteriaOptionValue CriteriaId
yes 1
no 1
yes 2
no 2

PatientData table
PatientOptionValue CriteriaId
yes 1
no 2

So on the PatientData form, for Cough Day 1 I choose yes from the combo box
and it gets updated to the table, and for the next record I choose no for
Cough Day 2 and it gets updated to the table.

The problem is when I choose yes for the 1st record of Cough Day 1, all the
combo boxes are getting set to yes and the cursor goes to the first records
combo box. However the table is updated properly, the first record says yes.
When I choose no for Cough Day 2 in the second combo box, again, all the
combo boxes say no and the cursor goes to the first records combo box.
However the table's data is correct, it says yes for 1 and no for 2. So the
data is fine, its just that the user is seeing false info and I want whatever
is put into the combo box to stay there on the form and moving to another
record not to touch what I chose for the combo box in the record I was just
on.

I hope this makes sense, I can get even more detailed if you like, just let
me know whatever info you need, I can even send my database to you, its tiny,
1mb.
I thank you for your help, this is driving me nuts!
gmazza

Hi gmazza ,
this can happen if there is a reason why the first record could not be
saved.
Turn on 'Record selectors' for this form. - use the form's property sheet.
When using the form you can look for the pencil symbol in the record
selector when a record is being edited. If the pencil is showing for the 1st
combo when you try to click in the second combo, this indicates that the 1st
record could not be saved. This can happen if the combo is for a required
field or a field with a record validation like Is Not Null.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi there,
I have a continuous form with 1 of the fields in the record being a combo

[quoted text clipped - 7 lines]

record?
Any help is appreciated!

 

[gmazza via AccessMonster.com]

Here is my query for the PatientData form:
SELECT Distinct PatientData.PatientId, Criteria.CriteriaValue, PatientData.
CriteriaId FROM (Criteria INNER JOIN PatientData ON Criteria.CriteriaId =
PatientData.CriteriaId) INNER JOIN CriteriaOption ON Criteria.CriteriaId =
CriteriaOption.CriteriaId ORDER BY Criteria.CriteriaValue;

Thanks for your reply Jeanette!
The record is being saved though, thats the problem.
Here's my situation in a little more detail:
I have a Criteria table with these columns:
CriteriaId - autonumber - primary key
CriteriaValue - Text

I have a CriteriaOption table with these columns:
CriteriaOptionId - autonumber - primary key
CriteriaOptionValue - Text
CriteriaId - FK

I have a PatientData table with these columns:
PatientId - autonumber - primary key
PatientOptionValue - Text
CriteriaId - FK

So my 1st form is based on the Criteria table and it allows you to enter a
CriteriaValue.
My 2nd form is based on the CriteriaOption table and has a combo box filled
with CriteriaValue's that were entered on the 1st form. Then, based on the
current CriteriaValue, you can enter in a subform different
CriteriaOptionValue's.
Finally, the 3rd form is based on a combo of all 3 tables.
It is a continuous form and lined up on the left is the field CriteriaValue
from the Criteria table. It is locked and displays a record for each
CriteriaValue from the Criteria table.
The next field in the same record is a combo box based on all the different
CriteriaOptionValue's with that same CriteriaValue from the first field.
After choosing an option from the combo box, I update the Patient table based
on the CriteriaValue record I am on, and the corresponding
CriteriaOptionValue I have chosen.

Here is some data sample:

Criteria table
Criteria Id CriteriaValue
1 Cough - Day 1
2 Cough - Day 2

CriteriaOption table
CriteriaOptionValue CriteriaId
yes 1
no 1
yes 2
no 2

PatientData table
PatientOptionValue CriteriaId
yes 1
no 2

So on the PatientData form, for Cough Day 1 I choose yes from the combo box
and it gets updated to the table, and for the next record I choose no for
Cough Day 2 and it gets updated to the table.

The problem is when I choose yes for the 1st record of Cough Day 1, all the
combo boxes are getting set to yes and the cursor goes to the first records
combo box. However the table is updated properly, the first record says yes.
When I choose no for Cough Day 2 in the second combo box, again, all the
combo boxes say no and the cursor goes to the first records combo box.
However the table's data is correct, it says yes for 1 and no for 2. So the
data is fine, its just that the user is seeing false info and I want whatever
is put into the combo box to stay there on the form and moving to another
record not to touch what I chose for the combo box in the record I was just
on.

I hope this makes sense, I can get even more detailed if you like, just let
me know whatever info you need, I can even send my database to you, its tiny,
1mb.
I thank you for your help, this is driving me nuts!
gmazza

Hi gmazza ,
this can happen if there is a reason why the first record could not be

[quoted text clipped - 13 lines]

 

[Linq Adams via AccessMonster.com]

Clicking on the combobox in the second record and having the cursor move to
the same control on the first record would indicate in some event for the
combobox, maybe the Change event of the AfterUpdate event, you're forcing a
Requery of the form, usually done with

Me.Requery

Look in these events for the cbo control in the code window and see if this
is, in fact, so.

The fact that all comboboxes on all records refelct the choice from the
current record indicates that the combobox is Unbound. To change this
behavior the combobox has to be Bound to a field in the underlying table.

 

[gmazza via AccessMonster.com]

Thanks for your reply Linq.
You are right, I do have a Me.Requery in the On Change.
My combo box is unbound, but it has to be because if I set it to
CriteriaOptionValue, then I get duplicate records showing up, I get:
Patient Id 1 Cough Day 1 - yes
Patient Id 1 Cough Day 1 - no

What I want is:
Patient Id 1 Cough Day 1 and then my combo box where I can choose yes
or no and it sticks, then I can move down to the next record in my continuous
form and choose a CriteriaOptionValue in the combo box for the next
CriteriaValue, Cough Day 2.

Thanks!!

 

[Jeanette Cunningham]

It is the combination of unbound control and continuous form together that
are working against you.
As Linq says, if the combo box was bound to a field in the form's record
source, we could advise how to get this to work.

Where are you storing the yes or no chocies?
If it is only a yes /no choice, there is a way to do this sort of thing
using a checkbox, Albert Kallal has a way to do this with an unbound
checkbox.
I don't know if this would fit your needs.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia