Modules are not working properly and Subforms are not showing data

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Modules are not working properly and Subforms are not showing data

Post by 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.

User avatar
HansV
Administrator
Posts: 78027
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Modules are not working properly and Subforms are not showing data

Post by 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)
Best wishes,
Hans

wire_jp
StarLounger
Posts: 67
Joined: 19 Jan 2018, 00:00

Re: Modules are not working properly and Subforms are not showing data

Post by 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