using sub forms

[Guest]

Hi,
I have the following code which is activated by a command butto
------------------------------------------------------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO [keyskill_comp] ( [Person Code], [Course Code]
) SELECT [learners courses].[Person Code], [learners courses].[Course Code]
FROM [learners courses] GROUP BY [learners courses].[Person Code], [learners
courses].[Course Code] HAVING ((([learners courses].[Person Code])=
[forms]![learners courses subform active]![Person Code]) and (([learners
courses].[Course Code])= [forms]![learners courses subform active]![Course
Code])); "

stDocName = "Result pop up"

stLinkCriteria = "[Person Code]=" & Me![person code] & " and [Course
Code]=" & "'" & Me![course code] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.SetWarnings True

-----------------------------------------------------------------------------------------------
The query and forms work perfectly when I view them as standalone forms.
However, I actually have the form with the button on it as a subform on a
main form. The query stops at the point where it should refer to the form,
to get the person code and course code, and asks me to enter my parameter
values Do I have to refer to it differently for a subform?

 

[Steve Schapel]

Boots,

Yes, if [learners courses subform active] is a subform, then it is not
open as a form. You need to include the reference to the main form,
like this...
[Forms]![YourMainForm]![learners courses subform
active].[Form]![Person Code]

By the way, there is something peculiar about your SQL statement, given
that it has a GROUP BY clause but no aggregate function (Sum or whatever).

 

[Guest]

Steve,
Maybe I didn't explain properly. The point where it is falling over is
within the SQL. The parameter it is asking me for is the "forms!learners
courses subform active!Person Code"

I agree my SQL does look a little strange without any aggregate function and
it suprised me when it worked, but it wouldn't work without the GROUP BY.

Boots,

Yes, if [learners courses subform active] is a subform, then it is not
open as a form. You need to include the reference to the main form,
like this...
[Forms]![YourMainForm]![learners courses subform
active].[Form]![Person Code]

By the way, there is something peculiar about your SQL statement, given
that it has a GROUP BY clause but no aggregate function (Sum or whatever).

--
Steve Schapel, Microsoft Access MVP

Hi,
I have the following code which is activated by a command button
------------------------------------------------------------------------------------------------
Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO [keyskill_comp] ( [Person Code], [Course Code]
) SELECT [learners courses].[Person Code], [learners courses].[Course Code]
FROM [learners courses] GROUP BY [learners courses].[Person Code], [learners
courses].[Course Code] HAVING ((([learners courses].[Person Code])=
[forms]![learners courses subform active]![Person Code]) and (([learners
courses].[Course Code])= [forms]![learners courses subform active]![Course
Code])); "

stDocName = "Result pop up"

stLinkCriteria = "[Person Code]=" & Me![person code] & " and [Course
Code]=" & "'" & Me![course code] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.SetWarnings True

-----------------------------------------------------------------------------------------------
The query and forms work perfectly when I view them as standalone forms.
However, I actually have the form with the button on it as a subform on a
main form. The query stops at the point where it should refer to the form,
to get the person code and course code, and asks me to enter my parameter
values Do I have to refer to it differently for a subform?

 

[Steve Schapel]

Boots,

Maybe I didn't explain properly. The point where it is falling over is
within the SQL. The parameter it is asking me for is the "forms!learners
courses subform active!Person Code"

No, maybe it's I who didn't explain properly. That was exactly the
point I was trying to make. You are being prompted for the value of
[forms]![learners courses subform active]![Person Code] because Access
is unable to evaluate this expression. Access is unable to evaluate
this expression because you can't refer to the [learners courses subform
active] form because it is not open at the time. It is displayed via
the medium if a subform control on the main form, whatever that is
called. So you need to reference it as such, using the syntax I showed you.

I agree my SQL does look a little strange without any aggregate function and
it suprised me when it worked, but it wouldn't work without the GROUP BY.

Do you mind explaining what you mean by "wouldn't work"?

 

[Guest]

I got an error message that said "HAVING clause <clause> without grouping or
aggregation".
I put in the grouping and it worked straight away. The only problem it has
now is when it is used as a subform

Boots,

Maybe I didn't explain properly. The point where it is falling over is
within the SQL. The parameter it is asking me for is the "forms!learners
courses subform active!Person Code"

No, maybe it's I who didn't explain properly. That was exactly the
point I was trying to make. You are being prompted for the value of
[forms]![learners courses subform active]![Person Code] because Access
is unable to evaluate this expression. Access is unable to evaluate
this expression because you can't refer to the [learners courses subform
active] form because it is not open at the time. It is displayed via
the medium if a subform control on the main form, whatever that is
called. So you need to reference it as such, using the syntax I showed you.

I agree my SQL does look a little strange without any aggregate function and
it suprised me when it worked, but it wouldn't work without the GROUP BY.

Do you mind explaining what you mean by "wouldn't work"?

 

[Guest]

I have tried referencing it the way you suggested;

HAVING ((([learners courses].[Person Code])= [forms]![main form]![learners
courses subform active].[form]![Person Code]) and
(([learners courses].[Course Code])= [forms]![main form]![learners courses
subform active].[form]![Course Code]));

but I am still getting the same "Enter Parameter Value" box with
"Forms!main form!learners courses subform active.Form!Person Code" as the
prompt.

Thanks for your help so far

Boots,

Maybe I didn't explain properly. The point where it is falling over is
within the SQL. The parameter it is asking me for is the "forms!learners
courses subform active!Person Code"

No, maybe it's I who didn't explain properly. That was exactly the
point I was trying to make. You are being prompted for the value of
[forms]![learners courses subform active]![Person Code] because Access
is unable to evaluate this expression. Access is unable to evaluate
this expression because you can't refer to the [learners courses subform
active] form because it is not open at the time. It is displayed via
the medium if a subform control on the main form, whatever that is
called. So you need to reference it as such, using the syntax I showed you.

I agree my SQL does look a little strange without any aggregate function and
it suprised me when it worked, but it wouldn't work without the GROUP BY.

Do you mind explaining what you mean by "wouldn't work"?

 

[Guest]

I have it working now. Thanks alot Steve. The subform for some reason was
namd differently within the main form than it is on its own, but I didn't
realise. Your syntax was perfect thanks

I have tried referencing it the way you suggested;

HAVING ((([learners courses].[Person Code])= [forms]![main form]![learners
courses subform active].[form]![Person Code]) and
(([learners courses].[Course Code])= [forms]![main form]![learners courses
subform active].[form]![Course Code]));

but I am still getting the same "Enter Parameter Value" box with
"Forms!main form!learners courses subform active.Form!Person Code" as the
prompt.

Thanks for your help so far

Boots,

Maybe I didn't explain properly. The point where it is falling over is
within the SQL. The parameter it is asking me for is the "forms!learners
courses subform active!Person Code"

No, maybe it's I who didn't explain properly. That was exactly the
point I was trying to make. You are being prompted for the value of
[forms]![learners courses subform active]![Person Code] because Access
is unable to evaluate this expression. Access is unable to evaluate
this expression because you can't refer to the [learners courses subform
active] form because it is not open at the time. It is displayed via
the medium if a subform control on the main form, whatever that is
called. So you need to reference it as such, using the syntax I showed you.

I agree my SQL does look a little strange without any aggregate function and
it suprised me when it worked, but it wouldn't work without the GROUP BY.

Do you mind explaining what you mean by "wouldn't work"?

 

[Steve Schapel]

Very good, Boots. Before you clarified, I would have suspected that the
the name of the main form was not [main form], but apparently it is!
Anyway, pleased to hear it is working correctly now.

 

[Steve Schapel]

Boots,

Well, without the GROUP BY, the correct syntax is WHERE instead of
HAVING. It should work like this...

SELECT [learners courses].[Person Code], [learners courses].[Course Code]
FROM [learners courses]
WHERE ((([learners courses].[Person Code])=[Forms]![main form]![learners
courses subform active].[Form]![Person Code]) And (([learners
courses].[Course Code])= [Forms]![main form]![learners courses subform
active].[Form]![Course Code]));

If this results in duplicate records returned by the query, you might
then require the query's Unique Values property to be set to Yes,
resulting in this SQL...

SELECT DISTINCT [learners courses].[Person Code], [learners
courses].[Course Code]
FROM [learners courses]
WHERE ((([learners courses].[Person Code])=[Forms]![main form]![learners
courses subform active].[Form]![Person Code]) And (([learners
courses].[Course Code])= [Forms]![main form]![learners courses subform
active].[Form]![Course Code]));