Combo Box not retaining its value from table

[gmazza via AccessMonster.com]

Hey there,
I have a combo box on a form and when I make a choice it is saved in the
table the form is based on.
When I close the form and re-open, the combo box is blank. The record in the
table has been updated properly, but how can I get the value in the table to
stay in the combo box. Even though the Row Source is a Select query I made, I
tried putting the Control Source to the field in the table and leaving the
Row Source the same and it works, but when I choose a different value in the
combo box I get an error saying the record is locked, can't update.
I am updating the table in the On Change event of the combo box, hope that
helps!
Any help is appreciated!
Thanks!

 

[Jeanette Cunningham]

Hi gmazza,
I'm sorry to see that you are having so much trouble with getting this to
work.
You are asking specific questions (good), but I think you need to go back to
the beginning for us to help you.
Let us know what tables are in the database, the names of their primary key
fields, the names of their foreign key fields and the relationships between
the tables and what is the main purpose for the database.

I don't have any experience with patient details databases, so if I can't
help you, I'm sure that there others with experience in patient databases
that will be able to help.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Beetle]

If the field in question is the Control Source of the combo box
then you shouldn't need an event procedure at all (the On Change
event would be the wrong one anyway) to update the table.

Whatever you select in the combo box would be stored in the
table automatically.

Maybe I'm missing something here?

 

[gmazza via AccessMonster.com]

Thanks Jeanette, hope this helps!

I have a Criteria table with these columns:
SequenceNo - number - primary key
CriteriaValue - Text

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

I have a PatientData table with these columns:
PatientId - autonumber - primary key
Value1- Text
Value2 - Text

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

CriteriaOption table
CriteriaOptionValueText SequenceNo
yes 1
no 1
maybe 1
yes 2
no 2

PatientData table
PatientId Value1 Value2
1 yes
yes

So on the form, based on the PatientData table, I have a label that is being
updated by the Criteria Value in the Criteria table. I then have a combo box
for the corresponding label.
In the example above, I choose yes from the combo box for the first label,
and I choose yes for the second label. They both get saved to the PatientData
table just fine.
The only problem I am having is when I close the form and reopen, the values
aren't staying in the combo box, they are blank. I wanted the first combo box
to retain its value in the table of 'yes', and same for the second combo box.
I realize the Control Sources for the combo boxes aren't Value1 and Value2,
but is there a way the combo box can retain its value from the table?


My query for my combo box is this:
SELECT CriteriaOption.CriteriaOptionValueText, CriteriaOption.SequenceNo,
FROM PatientData, Criteria INNER JOIN CriteriaOption ON Criteria.SequenceNo =
CriteriaOption.SequenceNo WHERE (((CriteriaOption.SequenceNo)=1)

The next combo box has the same query, except the WHERE is = 2 instead of 1.

Hi gmazza,
I'm sorry to see that you are having so much trouble with getting this to
work.
You are asking specific questions (good), but I think you need to go back to
the beginning for us to help you.
Let us know what tables are in the database, the names of their primary key
fields, the names of their foreign key fields and the relationships between
the tables and what is the main purpose for the database.

I don't have any experience with patient details databases, so if I can't
help you, I'm sure that there others with experience in patient databases
that will be able to help.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hey there,
I have a combo box on a form and when I make a choice it is saved in the

[quoted text clipped - 13 lines]

Any help is appreciated!
Thanks!

 

[Jeanette Cunningham]

Thanks Jeanette, hope this helps!

I have a Criteria table with these columns:
SequenceNo - number - primary key
CriteriaValue - Text

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

I have a PatientData table with these columns:
PatientId - autonumber - primary key
Value1- Text
Value2 - Text

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

CriteriaOption table
CriteriaOptionValueText SequenceNo
yes 1
no 1
maybe 1
yes 2
no 2

PatientData table
PatientId Value1 Value2
1 yes
yes

So on the form, based on the PatientData table, I have a label that is
being
updated by the Criteria Value in the Criteria table. I then have a combo
box
for the corresponding label.
In the example above, I choose yes from the combo box for the first label,
and I choose yes for the second label. They both get saved to the
PatientData
table just fine.
The only problem I am having is when I close the form and reopen, the
values
aren't staying in the combo box, they are blank. I wanted the first combo
box
to retain its value in the table of 'yes', and same for the second combo
box.
I realize the Control Sources for the combo boxes aren't Value1 and
Value2,
but is there a way the combo box can retain its value from the table?


My query for my combo box is this:
SELECT CriteriaOption.CriteriaOptionValueText, CriteriaOption.SequenceNo,
FROM PatientData, Criteria INNER JOIN CriteriaOption ON
Criteria.SequenceNo =
CriteriaOption.SequenceNo WHERE (((CriteriaOption.SequenceNo)=1)

The next combo box has the same query, except the WHERE is = 2 instead of
1.

Hi gmazza,
I'm sorry to see that you are having so much trouble with getting this to
work.
You are asking specific questions (good), but I think you need to go back
to
the beginning for us to help you.
Let us know what tables are in the database, the names of their primary
key
fields, the names of their foreign key fields and the relationships
between
the tables and what is the main purpose for the database.

I don't have any experience with patient details databases, so if I can't
help you, I'm sure that there others with experience in patient databases
that will be able to help.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hey there,
I have a combo box on a form and when I make a choice it is saved in the

[quoted text clipped - 13 lines]

Any help is appreciated!
Thanks!