Run time error in a form

[monia13]

I'm getting this error when I choose a value in a combo box:

Run time error 2465
Microsoft Access can't find the field 'Subform_By_RCA' referred to in your
expression.

Como box is based on a separate table that lists RCA names. Subform_By_RCA
is based on a query set up using 3 tables, including the table RCA names used
in combo box. Could that be a problem?

Private Sub RCA_AfterUpdate()
Me!Subform_By_RCA.Form.Requery
End Sub

Also I have this code for the UfterUpdate in the combo box:

 

[Steve Schapel]

Monia,

I assume that the RCA combobox is on the main form that also has the
subform on it? The name of the subform control on the main form is not
necessarily the same as the name of the form that it houses. Is the
form you are using for the subform named 'Subform_By_RCA'? Is the
subform control on the main form also named 'Subform_By_RCA'? If they
are different, your code needs to refer to the name of the subform
control on the main form.

 

[monia13]

Yes, combobox and subform are on the same main form.

The name of the subform is Subform_By_RCA. It's displayed as Subform_By_RCA
subform1

The name of the main form that houses the combobox and subform is Form1

 

[Steve Schapel]

Monia,

I am not sure what you mean by "it's displayed as Subform_By_RCA
subform1". What I was referring to is if you go to the design view of
Form1, and then look at the sibform control (i.e. the "box" on Form1
where the subform is displayed), what is this box called? Do you mean
it is called 'Subform_By_RCA subform1'?

 

[monia13]

yes, the box for subform located in the mainform is called Subform_By_RCA
subform1.

 

[Steve Schapel]

Monia,

Well, then, I think your code will need to say:
Me![Subform_By_RCA subform 1].Form.Requery

 

[monia13]

I updated the code per your suggestion below. Now I don't get the Run time
error. However the subform does not display any results based on the value
picked in the comboobx. When I open the subform directly fromt the Form
panel and manually input the value, the subform displayes the results w/o
problems.

What else could be wrong with the combobox?

Monia,

Well, then, I think your code will need to say:
Me![Subform_By_RCA subform 1].Form.Requery

--
Steve Schapel, Microsoft Access MVP

yes, the box for subform located in the mainform is called Subform_By_RCA
subform1.

 

[Steve Schapel]

Monia,

Could you please go to the design view of the query that the
Subform_By_RCA form is based on, from the View menu select SQL, and then
copy/paste the SQL of the query into your reply here? Thanks.

 

[monia13]

Here is the code:

SELECT [RQM Pending - by RCA].[RCA Name], [RQM Pending - by RCA].[Acct
Date], [RQM Pending - by RCA].[CAS ID], [RQM Pending - by RCA].[Client ID],
[RQM Pending - by RCA].[Client Name], [RQM Pending - by RCA].[Pendign Stats],
[RQM Pending - by RCA].Comments
FROM [RQM Pending - by RCA]
WHERE ((([RQM Pending - by RCA].[RCA
Name])=[Forms]![Mnform_RQM_Pend_Upd]![RCA_Name]));

 

[Steve Schapel]

Monia,

Thanks. My understanding was that the subform is supposed to show the
records according to the selection in the combobox. Is that right? The
combobox is apparently on a from called Mnform_RQM_Pend_Upd, is that
right? According to the code you showed earlier, the name of the
combobox is RCA, is that right? Therefore I would have expected the
WHERE clause of the query to refer to RCA, not RCA_Name, i.e. like this....
WHERE (([RQM Pending - by RCA].[RCA
Name])=[Forms]![Mnform_RQM_Pend_Upd]![RCA])
Can you clarify?

 

[monia13]

Steve,

Yes, the subform supposed to show recordes based on the choice in the combo
box.

Yes, the combo box is on the main form called Mnform_RQM_Pend_Upd

I've recreated combo box and the query several times so the most current
combobox is called RCA_Name. I got that name from combobox --- properties --
other tab---name.

Monia,

Thanks. My understanding was that the subform is supposed to show the
records according to the selection in the combobox. Is that right? The
combobox is apparently on a from called Mnform_RQM_Pend_Upd, is that
right? According to the code you showed earlier, the name of the
combobox is RCA, is that right? Therefore I would have expected the
WHERE clause of the query to refer to RCA, not RCA_Name, i.e. like this....
WHERE (([RQM Pending - by RCA].[RCA
Name])=[Forms]![Mnform_RQM_Pend_Upd]![RCA])
Can you clarify?

--
Steve Schapel, Microsoft Access MVP

Here is the code:

SELECT [RQM Pending - by RCA].[RCA Name], [RQM Pending - by RCA].[Acct
Date], [RQM Pending - by RCA].[CAS ID], [RQM Pending - by RCA].[Client ID],
[RQM Pending - by RCA].[Client Name], [RQM Pending - by RCA].[Pendign Stats],
[RQM Pending - by RCA].Comments
FROM [RQM Pending - by RCA]
WHERE ((([RQM Pending - by RCA].[RCA
Name])=[Forms]![Mnform_RQM_Pend_Upd]![RCA_Name]));

 

[monia13]

Steve,

I recreated combo box and subform query for the nth time. In the combo
box, I chose RCA ID (indexed field) along with RCA Name. I adjusted the query
to filter on RCA ID picked in the combo box. Subform populates now when a
value in the como box is chosen. Thanks for your help.

Monia,

Thanks. My understanding was that the subform is supposed to show the
records according to the selection in the combobox. Is that right? The
combobox is apparently on a from called Mnform_RQM_Pend_Upd, is that
right? According to the code you showed earlier, the name of the
combobox is RCA, is that right? Therefore I would have expected the
WHERE clause of the query to refer to RCA, not RCA_Name, i.e. like this....
WHERE (([RQM Pending - by RCA].[RCA
Name])=[Forms]![Mnform_RQM_Pend_Upd]![RCA])
Can you clarify?

--
Steve Schapel, Microsoft Access MVP

Here is the code:

SELECT [RQM Pending - by RCA].[RCA Name], [RQM Pending - by RCA].[Acct
Date], [RQM Pending - by RCA].[CAS ID], [RQM Pending - by RCA].[Client ID],
[RQM Pending - by RCA].[Client Name], [RQM Pending - by RCA].[Pendign Stats],
[RQM Pending - by RCA].Comments
FROM [RQM Pending - by RCA]
WHERE ((([RQM Pending - by RCA].[RCA
Name])=[Forms]![Mnform_RQM_Pend_Upd]![RCA_Name]));