Eliminate prompt in requery of subdatasheet in vba

[Accessor]

Hi all. I have two subforms/subqueries on a main form. I want to be able to
scroll through a field on subform1, and with each new field entered on
subform1, requery subform2, with the value from subform1 being a criteria. I
have this almost working. When opening subform2's query as a query, it works
fine. When I try to do it with my procedure below, I get a prompt for the
ID(primary key field) of the table being queried in subform2. When I hit
enter without providing one, subform2 requeries just fine, as it should. Why
am I getting the prompt for that field in the vba execution? The code is
below, hope this makes sense. Thanks in advance...

Private Sub Symbol_GotFocus()
'When a symbol field from the subquery1 gets focus, put the value of the
field
'into a text box.
Forms!frmQueries!txtSymbol = Symbol

'Requery subquery2. The criteria of the symbol field for this query is the
'text box just updated, txtSymbol.
[Forms]![frmQueries].[Form]![Copy Of qryPointOfIntByEnterSymb
subform2].Requery
End Sub

 

[David H]

Check the recordsource for the subform that's giving you problems. I'd bet
that you've got a WHERE statement that is not properly referencing the
correct control.

 

[Accessor]

Hi David, thanks for the response. I'm not sure I fully understand though...
I looked in the sql for the query that is the source of the subform, and it
looks correct to me.

Check the recordsource for the subform that's giving you problems. I'd bet
that you've got a WHERE statement that is not properly referencing the
correct control.

Hi all. I have two subforms/subqueries on a main form. I want to be able to
scroll through a field on subform1, and with each new field entered on
subform1, requery subform2, with the value from subform1 being a criteria. I
have this almost working. When opening subform2's query as a query, it works
fine. When I try to do it with my procedure below, I get a prompt for the
ID(primary key field) of the table being queried in subform2. When I hit
enter without providing one, subform2 requeries just fine, as it should. Why
am I getting the prompt for that field in the vba execution? The code is
below, hope this makes sense. Thanks in advance...

Private Sub Symbol_GotFocus()
'When a symbol field from the subquery1 gets focus, put the value of the
field
'into a text box.
Forms!frmQueries!txtSymbol = Symbol

'Requery subquery2. The criteria of the symbol field for this query is the
'text box just updated, txtSymbol.
[Forms]![frmQueries].[Form]![Copy Of qryPointOfIntByEnterSymb
subform2].Requery
End Sub

 

[David H]

Can you post the queries for all three record sets?

Hi David, thanks for the response. I'm not sure I fully understand though...
I looked in the sql for the query that is the source of the subform, and it
looks correct to me.

Check the recordsource for the subform that's giving you problems. I'd bet
that you've got a WHERE statement that is not properly referencing the
correct control.

Hi all. I have two subforms/subqueries on a main form. I want to be able to
scroll through a field on subform1, and with each new field entered on
subform1, requery subform2, with the value from subform1 being a criteria. I
have this almost working. When opening subform2's query as a query, it works
fine. When I try to do it with my procedure below, I get a prompt for the
ID(primary key field) of the table being queried in subform2. When I hit
enter without providing one, subform2 requeries just fine, as it should. Why
am I getting the prompt for that field in the vba execution? The code is
below, hope this makes sense. Thanks in advance...

Private Sub Symbol_GotFocus()
'When a symbol field from the subquery1 gets focus, put the value of the
field
'into a text box.
Forms!frmQueries!txtSymbol = Symbol

'Requery subquery2. The criteria of the symbol field for this query is the
'text box just updated, txtSymbol.
[Forms]![frmQueries].[Form]![Copy Of qryPointOfIntByEnterSymb
subform2].Requery
End Sub

 

[Accessor]

Sure, thanks. It's a lot, so any help here is greatly appreciated. Here goes:

First subquery:

SELECT tblSymbolsMain.Symbol, tblSymbolsMain.Company, (SELECT Count
(T2.PointOfIntDateAdded) FROM tblPointsOfInterest AS T2 WHERE
(T2.PointOfIntSymb) = (tblSymbolsMain.Symbol) AND (T2.PointOfIntDateAdded) >=
Workday(Date(),-3)) AS SubQCount, [SubQCount] AS CountPOI,
tblStocksPricingVol.PricingVolDate, tblStocksPricingVol.ClosePrice,
tblStocksPricingVol.Volume
FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON tblSymbolsMain.Symbol
= tblStocksPricingVol.Symbol
WHERE ((((SELECT Count (T2.PointOfIntDateAdded) FROM tblPointsOfInterest AS
T2 WHERE (T2.PointOfIntSymb) = (tblSymbolsMain.Symbol) AND
(T2.PointOfIntDateAdded) >= Workday(Date(),-3)))>=2) AND
((tblStocksPricingVol.PricingVolDate)=(SELECT Max (T2.PricingVolDate) FROM
tblStocksPricingVol AS T2 WHERE (T2.Symbol) = (tblSymbolsMain.Symbol))) AND
((tblStocksPricingVol.ClosePrice)<=25) AND
((tblStocksPricingVol.Volume)>=850000));

Second subquery:

SELECT tblPointsOfInterest.PointOfIntDateAdded,
tblPointsOfInterest.PointOfIntSymb, tblPointsOfInterest.PointOfIntQuery,
tblPointsOfInterest.PointOfIntDataPoint,
tblPointsOfInterest.PointOfIntDataPointDesc
FROM tblPointsOfInterest
WHERE
(((tblPointsOfInterest.PointOfIntSymb)=[Forms]![frmQueries].[txtSymbol]));

So it's just the two queries/ recordsets, and the vba posted earlier puts
the symbol value in the form text box, then requeries the second subquery,
passing that text box value.

Thanks again.

Can you post the queries for all three record sets?

Hi David, thanks for the response. I'm not sure I fully understand though...
I looked in the sql for the query that is the source of the subform, and it
looks correct to me.

Check the recordsource for the subform that's giving you problems. I'd bet
that you've got a WHERE statement that is not properly referencing the
correct control.

:

Hi all. I have two subforms/subqueries on a main form. I want to be able to
scroll through a field on subform1, and with each new field entered on
subform1, requery subform2, with the value from subform1 being a criteria. I
have this almost working. When opening subform2's query as a query, it works
fine. When I try to do it with my procedure below, I get a prompt for the
ID(primary key field) of the table being queried in subform2. When I hit
enter without providing one, subform2 requeries just fine, as it should. Why
am I getting the prompt for that field in the vba execution? The code is
below, hope this makes sense. Thanks in advance...

Private Sub Symbol_GotFocus()
'When a symbol field from the subquery1 gets focus, put the value of the
field
'into a text box.
Forms!frmQueries!txtSymbol = Symbol

'Requery subquery2. The criteria of the symbol field for this query is the
'text box just updated, txtSymbol.
[Forms]![frmQueries].[Form]![Copy Of qryPointOfIntByEnterSymb
subform2].Requery
End Sub

 

[David H]

1) Try switching out the [dot] in
(((tblPointsOfInterest.PointOfIntSymb)=[Forms]![frmQueries].[txtSymbol])); to
a bang [!] between [frmQueries] and [txtSymbol]
(((tblPointsOfInterest.PointOfIntSymb)=[Forms]![frmQueries]![txtSymbol]));

2) Also what is the EXACT text of the prompt that you're getting?

Sure, thanks. It's a lot, so any help here is greatly appreciated. Here goes:

First subquery:

SELECT tblSymbolsMain.Symbol, tblSymbolsMain.Company, (SELECT Count
(T2.PointOfIntDateAdded) FROM tblPointsOfInterest AS T2 WHERE
(T2.PointOfIntSymb) = (tblSymbolsMain.Symbol) AND (T2.PointOfIntDateAdded) >=
Workday(Date(),-3)) AS SubQCount, [SubQCount] AS CountPOI,
tblStocksPricingVol.PricingVolDate, tblStocksPricingVol.ClosePrice,
tblStocksPricingVol.Volume
FROM tblSymbolsMain INNER JOIN tblStocksPricingVol ON tblSymbolsMain.Symbol
= tblStocksPricingVol.Symbol
WHERE ((((SELECT Count (T2.PointOfIntDateAdded) FROM tblPointsOfInterest AS
T2 WHERE (T2.PointOfIntSymb) = (tblSymbolsMain.Symbol) AND
(T2.PointOfIntDateAdded) >= Workday(Date(),-3)))>=2) AND
((tblStocksPricingVol.PricingVolDate)=(SELECT Max (T2.PricingVolDate) FROM
tblStocksPricingVol AS T2 WHERE (T2.Symbol) = (tblSymbolsMain.Symbol))) AND
((tblStocksPricingVol.ClosePrice)<=25) AND
((tblStocksPricingVol.Volume)>=850000));

Second subquery:

SELECT tblPointsOfInterest.PointOfIntDateAdded,
tblPointsOfInterest.PointOfIntSymb, tblPointsOfInterest.PointOfIntQuery,
tblPointsOfInterest.PointOfIntDataPoint,
tblPointsOfInterest.PointOfIntDataPointDesc
FROM tblPointsOfInterest
WHERE
(((tblPointsOfInterest.PointOfIntSymb)=[Forms]![frmQueries].[txtSymbol]));

So it's just the two queries/ recordsets, and the vba posted earlier puts
the symbol value in the form text box, then requeries the second subquery,
passing that text box value.

Thanks again.

Can you post the queries for all three record sets?

 

[Dale Fye]

Do you have the form and the subform linked? If you open the main form in
design view, and click on the subform control and view its properties, then
on the Data tab, you will see the following properties:

SourceObject:
Link Master Fields:
Link Child Fields:

Normally, with a form/subform structure, you will use the Master/Child
fields to link the subform to the main form, and it sounds like one of the
fields indicated on the linkage is not in one of the queries.

Since you have written the SQL for you subform so that it explicitly
references a control on your main form, you probably do not need to have the
Master/Child fields properties filled in.