Autofill text box based on selection in combo box.

[msmithtlh]

I have read the post and answers in the autofill post by dbell. I am having similar issues.

I have a form with nested subforms. In the second subform, I have a combo box, where I select a record and fill one field (based on a query). Based on the record selected in the combo box, I want to also fill a text box in the same subform. I have tried multiple things and can't find the one that works. This seems like it should be simple but I have spent a couple of days fighting with this one detail.



SQL from combo box query:
SELECT BPFieldsByTask.BPField, BPFieldsByTask.Score, BPFieldsByTask.Task
FROM BPFieldsByTask
WHERE (((BPFieldsByTask.Task)=[Forms]![SUNTAX Header]![Task]))
ORDER BY BPFieldsByTask.BPField;

This lets me select and populate the BPfield. I want to also fill the "score" field in the same subform, based on the BPfield selected. When the query is run independently, it gives the correct value for the "score" field. I just am having trouble getting the value into the control on the form.

Thanks in advance.

[June7]

Expression in textbox ControlSource can reference the relevant column index of combobox. Index begins with 0 so the index for the Score column is 1.

=[comboboxname].[Column](1)

If you want to save the Score to record, that will require code, but is saving the value necessary?

[msmithtlh]

That got it to display. Yes, I need to save the value to the score field. Can I do that on the "dirty" event?

[June7]

I've never used Dirty event but that might do the job. Or form BeforeUpdate event.

[msmithtlh]

Please help with code to save the data. Thanks.

[June7]

Assuming Score field is included in the form RecordSource, the code is simple.

Me!Score = Me.textboxname

The real trick is picking the event(s) to put the code in.

[msmithtlh]

Both are name score, so I have Me!Score = Me.Score - but it isn't saving the value.

I tried it in "on change" event and "on dirty" event.

[June7]

Events of what - form or control? I would not recommend the OnChange.

Is the code executing? Step debug. Refer to link at bottom of my post for debugging guidelines.

Try the combobox AfterUpdate or the form BeforeUpdate.

[msmithtlh]

Not having any luck. I've tried it several places. Am getting an error that says I can't update this object. Is the syntax more involved since I am in a subform?

[msmithtlh]

I got it!! Saw a similar post, and the "light dawned." Thanks so much!!!