Difficult one: dynamically select field to update

[mkblack]

I have a table with 43 fields and I am building a form to allow users to update the fields in the table. A lot of the fields are memo format, since the data consists of lengthy contract paragraphs.



I know I can make a huge form with all the fields listed one under the other on the page, and the user would then scroll down and fill each box with data. However, I would prefer to make the user's life easy and allow the user to use a combo box to select wich field they want to work on, and then a box right next to the combo would show the value of the current record for that field.

(I know that I can set the combo box Field List property with a list of field names in it, but can I link this to a text box so that anything that is written or displayed within it relates to the field displayed in the combo box?)

Any suggestion would be gretly appreciated!

[pbaldy]

I think you can change the control source of the textbox on the fly, so try that in the after update event of the combo. Presuming the combo has the field name in the bound column, try

Me.TextboxName.ControlSource = Me.ComboName

You may have to add code to save the record, but see if it works without.

[mkblack]

Thank you Paul!

[mkblack]

Paul,

It doesn't seem to work. When I try to update the combo box, the combo just goes blank, and nothing happens witht he control source of the text box.

Thanks!

[pbaldy]

Can you post the db here? I just did a very quick test, and it worked as expected.

[mkblack]

Unfortunately, the Db contains confidential info, otherwise I would be happy to post it. This is the code that I have:

Private Sub Combo0_AfterUpdate()
Me.Text2.ControlSource = Me.Combo0
End Sub

[mkblack]

I get #Name in the text box

[pbaldy]

I tested with this and it worked fine:

Private Sub Combo6_AfterUpdate()
  Me.Text0.ControlSource = Me.Combo6
End Sub

Make sure your combo's bound column contains valid field names. The #Name error is what you'd get if Access didn't recognize the field name.

[mkblack]

Paul,

Thank you for taking the time to help! Let me give you some detial on the form and underlying table. I will act like the table onyl has five columns:

table name: Documents_P
Fields:
DOCUMENT_ID
Document_Date
Address_Change_Terms
Insurance_Requirement_Terms
Address_Change_Date

Let's assume that on the form I will be on a current record having DOCUMENT_ID=10. For that document I want to be able to enter into either of the fields. In the combo I want to select which field I want to edit (or to enter a new value for tha field) and then I want the text box to show me the value currently in the field, which I could then edit.

Is this indicative enough of what I am trying to accomplish?

[mkblack]

got it!!! thank you! I just had to add
Me.Text2.ControlSource = Me.Combo0.Column(1)

[pbaldy]

Excellent, glad we got it sorted out!

[mkblack]

Yes. Sorry to bug again, but one issue remains: The update works well, but the combo is still blank after the update. I am trying to put in another text box that would show the name of the field being updated, and I set its control source to =[Combo0], but naturally that text box is blank, as well.

[pbaldy]

What are the row source, bound column and column width properties of the combo?

[mkblack]

Row Source: Documents_P
Bound Column: 1
Column Width: 1

[mkblack]

This is a screen shot of the basic form layout.