Modules are not working properly and Subforms are not showing data

[wire_jp]

Similar to my last post called "Missing data in Append Query and Convert number to text in Form" https://eileenslounge.com/viewtopic.php?f=29&t=40288, I have a similar situation where there are two text fields in the Google Sheets and I am unable to generate the tables. You had created a module called

modPopulateOtherOptions

in order to populate the table called

tblOtherEntertainmentOptions

with VBA code. I tried to replicate your work by creating two separate module: -

modPopulateOtherVolAreasOptions

and

modPopulateAnyOtherIdeasOptions

and they throw similar error messages: -

In the first moduel called

modPopulateOtherVolAreasOptions

: -

Code: Select all

rstO!AnyOtherVolAreasOption = Mid(strNew, 3)

with the error message: -

rstO!AnyOtherVolAreasOption = <item not found in this collection

In the second module called

modPopulateAnyOtherIdeasOptions

: -

Code: Select all

rstO!AnyOtherIdeasOptionID = Mid(strNew, 3)

with the error message: -

rstO!AnyOtherIdeasOptionID = <item not found in this collection

I created some subforms called: -

• sbfAnyOtherVolAreasResponses

• sbfAnyOtherIdeasResponses

And they are not showing any data.
Similarly the subforms were embedded into the following forms and they are not showing any data:-

• frmAnyOtherVolAreasResponses

• frmAnyOtherIdeasResponses

The One Drive link is attached with the files:-
https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=h53Jel

Thank you in advance for your kind assistance.

[HansV]

The field in tblAnyOtherVolAreasResponses is named AnyOtherVolAreasOptionID, not AnyOtherVolAreasOption. So rename it.
And it should be a text field, not a number field. Before you change that, you have to remove the relationship to tblAnyOtherVolAreasOptions - it makes no sense.
It's similar for the other one (except that there is no relationship to be removed).
The subforms should contain a text box, not a combo box, and it should have the correct field as Control Source.

See database on Dropbox/
(The linked worksheet table should be relinked - I had to link it to my local copy)

[wire_jp]

Hi Hans,

The modules and the tables are updates correctly. Thank you for your kind help. it is greatly appreciated!

Kind regards,

wire_jp