edit records pulled into form

[MES via AccessMonster.com]

I am trying to edit a record by pulling it into a form, editing, then
clicking the command button linked to update query to update the record in
the table. However, I have my form set up as choosing the record by ID# in a
combo box, then the rest of the fields in the form are auto-populated using
the '=ComboBoxName.Column(1)' formula. Due to this, the fields are locked -
I cannot edit them.

Does anyone have any suggestions about how I can do this - whether its
something I'm missing using this process, or suggest something totally
different?

 

[Guest]

You cannot edit a control that has anything other than a field from the
form's record source as it's control source.
You are making this way harder than it should be. Here is how you can fix it.

Make your form's Record Source the table or a query on the table you are
editing.

Bind the control source for each control on your form to the field in the
table it represents except the combo box.

The combo box should not be a bound control and it should not have a column
for each field. It only needs enough columns to allow you to find the record
you want to edit. The first column should be the bound column. If it is not
something the user can easily recognize, make some other field that is unique
to the record and that the user will understand the second column. If you
use a second column, make the width of the first column 0 so it will not be
seen and the width of the second column wide enought to display. Set the
Limit To List property to Yes.

You will need a bound control for the field the combo's bound column refers
to. The way to do that is to create a hidden text box (Visible = No, Tab
Stop = No) that is bound to that field. You can then make the control source
of the hidden text box:
=MyComboName

This is the usual way forms are set up.

 

[MES via AccessMonster.com]

Thanks for the reply. I still am a little confused though.

I have the form bound to the table. I created a combo box to list the
records to choose from in the table - now I would like to select the record
to edit. When I select the record that I would like to edit, the other
fields do not change - they remain as the first record's data in the table.
I would like to be able to select the record to edit from the combo box then
have the rest of the fields for that record populated, so that I can then
make changes.

Any suggestions?

You cannot edit a control that has anything other than a field from the
form's record source as it's control source.
You are making this way harder than it should be. Here is how you can fix it.

Make your form's Record Source the table or a query on the table you are
editing.

Bind the control source for each control on your form to the field in the
table it represents except the combo box.

The combo box should not be a bound control and it should not have a column
for each field. It only needs enough columns to allow you to find the record
you want to edit. The first column should be the bound column. If it is not
something the user can easily recognize, make some other field that is unique
to the record and that the user will understand the second column. If you
use a second column, make the width of the first column 0 so it will not be
seen and the width of the second column wide enought to display. Set the
Limit To List property to Yes.

You will need a bound control for the field the combo's bound column refers
to. The way to do that is to create a hidden text box (Visible = No, Tab
Stop = No) that is bound to that field. You can then make the control source
of the hidden text box:
=MyComboName

This is the usual way forms are set up.

I am trying to edit a record by pulling it into a form, editing, then
clicking the command button linked to update query to update the record in

[quoted text clipped - 6 lines]

something I'm missing using this process, or suggest something totally
different?

 

[Guest]

You are on the right track. Chaning the value in the Combo has no effect on
the other fields on the form. Here is how you get the data from the selected
record to populate the form:

Set rst = Me.RecordsetClone
rst.FindFirst "[PrimaryKeyField] = '" & Me.MyCombo & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

[PrimaryKeyField] should be the name of the field in the recordset you want
to match to.
MyCombo should be the name of your combo box control.

Thanks for the reply. I still am a little confused though.

I have the form bound to the table. I created a combo box to list the
records to choose from in the table - now I would like to select the record
to edit. When I select the record that I would like to edit, the other
fields do not change - they remain as the first record's data in the table.
I would like to be able to select the record to edit from the combo box then
have the rest of the fields for that record populated, so that I can then
make changes.

Any suggestions?

You cannot edit a control that has anything other than a field from the
form's record source as it's control source.
You are making this way harder than it should be. Here is how you can fix it.

Make your form's Record Source the table or a query on the table you are
editing.

Bind the control source for each control on your form to the field in the
table it represents except the combo box.

The combo box should not be a bound control and it should not have a column
for each field. It only needs enough columns to allow you to find the record
you want to edit. The first column should be the bound column. If it is not
something the user can easily recognize, make some other field that is unique
to the record and that the user will understand the second column. If you
use a second column, make the width of the first column 0 so it will not be
seen and the width of the second column wide enought to display. Set the
Limit To List property to Yes.

You will need a bound control for the field the combo's bound column refers
to. The way to do that is to create a hidden text box (Visible = No, Tab
Stop = No) that is bound to that field. You can then make the control source
of the hidden text box:
=MyComboName

This is the usual way forms are set up.

I am trying to edit a record by pulling it into a form, editing, then
clicking the command button linked to update query to update the record in

[quoted text clipped - 6 lines]

something I'm missing using this process, or suggest something totally
different?

 

[MES via AccessMonster.com]

Thanks. Where do I put that code - in a module?

You are on the right track. Chaning the value in the Combo has no effect on
the other fields on the form. Here is how you get the data from the selected
record to populate the form:

Set rst = Me.RecordsetClone
rst.FindFirst "[PrimaryKeyField] = '" & Me.MyCombo & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

[PrimaryKeyField] should be the name of the field in the recordset you want
to match to.
MyCombo should be the name of your combo box control.

Thanks for the reply. I still am a little confused though.

[quoted text clipped - 40 lines]

 

[Guest]

In the After Update event of the combo box

Thanks. Where do I put that code - in a module?

You are on the right track. Chaning the value in the Combo has no effect on
the other fields on the form. Here is how you get the data from the selected
record to populate the form:

Set rst = Me.RecordsetClone
rst.FindFirst "[PrimaryKeyField] = '" & Me.MyCombo & "'"
Me.Bookmark = rst.Bookmark
Set rst = Nothing

[PrimaryKeyField] should be the name of the field in the recordset you want
to match to.
MyCombo should be the name of your combo box control.

Thanks for the reply. I still am a little confused though.

[quoted text clipped - 40 lines]

something I'm missing using this process, or suggest something totally
different?