How do I auto populate columns based on a single value input on a form?

[wee.gray]

Hiya follks I hope you can help me again!

I need my form to help input thousands of hand filled-in paper forms, of these forms there were many different choice versions and the respondents gave their answer based on that choice, they had 8 choices per form.

My table is set up so that the people digitising these paper forms will first input the "choice 1: choice code" which is a unique number 1-32, they then input what's written on the form and move on to choice 2, here I could give them the 32 choices again but all later choice codes are based on the first choice code (please see the attached table) so I wondered if it's possible to auto populate the 7 remaining columns with the choice code based on the code input for the 1st choice?

I only have access 2003 and I've been trying to use the expression builder (to no avail) as my VBA skills are limited!

 

[Trevor G]

What you need to do is create a combo that lists all of the columns from your choices, then look at the properties of the combo and set the column widths to zero except for the column you want to display.

Then in the Combo Box Properties look at the After Update Event and you can fill the textbox based on the column number from the Combo Box a sample of the code would be like this.

Private Sub Combo22_AfterUpdate()
Me.textBoxName= Me.Combo22.Column(3)
Me.textBoxName2= Me.Combo22.Column(4)

End Sub

 

[wee.gray]

Ok that's all working great thank you;
how do I make these values into column entries in the table rather than text box names? ie in the after update do I replace textBoxName with a columnname from my table?

I have tried it thus:

Private Sub Combo767_AfterUpdate()
Me."Choice 2 Option Code" = Me.Combo767.Column(2)
End Sub

also tried

Private Sub Combo767_AfterUpdate()
Me.Choice 2 Option Code = Me.Combo767.Column(2)
End Sub

also tried changing the column name to C2 so no spaces,

all give syntax errors

 

[Trevor G]

Is your form record source based on the Table?

When you use the After Update Event and place in me. this would then produce a list of objects that you have on the form. so example as follows:

You tried this
Me."Choice 2 Option Code" = Me.Combo767.Column(2)

When it would come out as
Me.Choice_2_Option_Code.Text = Me.Combo767.Column(2)

 

[wee.gray]

Hi Trevor thanks again,

I've attached the file (minus the images to reduce size) I hope it makes sense; there are 4 questionnaire versions each with 8 choices to make and 8 choice variations per choice. The form record source is based on the table via properties rather than code but no object list comes up for me when writing code- is that because I'm on access 2003?

The form is set up so that the combo box (very last tab) updates relative to the "choice_1_option_code" and that works ok but after that the choice option codes for choices 2-7 don't update (choice 8 is manual again as a double-check)

I ut the code I can't get to work at the top, I hope it helps show what I am trying to do better than my terrible descriptions!

All the best,

Gray

 

[Trevor G]

Your database is wrong. To get the combo box working you need it to find results from another table.

I adjusted the code to fill the choice text box but the row source in the combo isn't correct.

I will take a look at it tomorrow if I can find the time.

 

[Trevor G]

Gray, I have looked at your database. I have created a new table which starts to list the options you showed in the drop down, I have only added a few entries but hopefully you will see what I have done.

I have created a new form so when you complete the fields you have made essential to create the record I have then placed a combo box and added the code to fill the fields.

Attached find a copy of the database I hope you can work your way through it.