Missing data in Append Query and Convert number to text in Form

[wire_jp]

Hi Hans,

I have linked a Google Sheets which was generated from Google Forms survey questionnaire to a Microsoft Access. The Entertainment Activities Section in Google Forms consisted of some checkboxes and a short answer called "Other". I had to installed an app called "Pretty Forms" to edit and change the word "Other" to "Indicate your talent"
https://i.ibb.co/nwDPfyJ/Google-Sheets- ... ection.png
https://i.ibb.co/84MdtSs/Google-Sheets- ... -Forms.png

In the Microsoft Access Database, the tblEntertainmentOptions table has a Field Name called "EntertainmentOption" with a field called "Indicate your talent". This field has format of Short Text. I created an append query called "qryEntertainmentAppendQuery" and the data entries for the "Indicate your talent" did not appear in the append query (these are short answer responses from the respondents where they type in their talents in the Google forms).

https://i.ibb.co/crbHhLL/Indicate-your-talent-field.png
https://i.ibb.co/ygNJBg6/Entertainment-Append-Query.png
https://i.ibb.co/Gd5pps0/Entertainment- ... uery-2.png
https://i.ibb.co/pjFPkJh/respondents-re ... -Forms.png

Finally, in the Entertainment Responses Form, how do I convert the numbers to text for the respondents' responses e.g. Helping to organise BCT concerts or entertainment activities: 4 to the text " Helping to organise BCT concerts or entertainment activities". Would this require to convert the textbox to a combobox?
https://i.ibb.co/b6vk8hQ/Entertainment- ... o-text.png

The OneDrive link is attached below to view the Microsoft Access database and the Excel spreadsheet linked to the Google Forms:-
https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=1cJeFP

Thanks

[HansV]

1) It's unfortunate that the "Indicate your talent" responses are included in the Entertainment Activities column instead of a separate column.
The append query can only handle the activities listed in the tblentertainentOptions table.
I'll have to think about it, but it will be complicated.

2) No, the form has to be organized differently. I created a new query qryFullName, and a form sbfEntertainmentOptions to act as subform.
See the database (zipped) on dropbox
You'll have to relink the spreadsheet.

[HansV]

I have updated the database on Dropbox. There is a new table for the extra entertainment options, and a subform to display them.

The new table was populated using VBA code in the module modPopulateOtherOptions.

See zip file on Dropbox

[wire_jp]

Hi Hans,

Thank you very much for your kind assistance in providing the VBA code and your other help in resolving this issue.

Best regards.