I Believe this should work but its not.

B

BrianPaul

I have on a subform a command button. Here is the Code

DoCmd.OpenQuery "QFactAdd", acViewNormal, acEdit

In the Query here is the Code

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT TStudy.Subject, TStudyScriptures.Scripture, TStudyScriptures.remarks
FROM TStudy INNER JOIN TStudyScriptures ON TStudy.studyid =
TStudyScriptures.studyId
GROUP BY TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks, TStudyScriptures.ScriptureId
HAVING (((TStudyScriptures.ScriptureId)=[forms]![study Edit]![Study Edit
Sub]![ScriptureID]));.

When I run the code, a Box comes up asking me for the scriptureid. If I
input the number it recognizes it and adds it to the new table. I am
assuming that it doesnt know the scriptureID and thats why its promting me
for it. What am I failing to do or not getting from this. Thanks any
responses greatly appreciated.
 
D

Dirk Goldgar

BrianPaul said:
I have on a subform a command button. Here is the Code

DoCmd.OpenQuery "QFactAdd", acViewNormal, acEdit

In the Query here is the Code

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM TStudy INNER JOIN TStudyScriptures ON TStudy.studyid =
TStudyScriptures.studyId
GROUP BY TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks, TStudyScriptures.ScriptureId
HAVING (((TStudyScriptures.ScriptureId)=[forms]![study Edit]![Study Edit
Sub]![ScriptureID]));.

When I run the code, a Box comes up asking me for the scriptureid. If I
input the number it recognizes it and adds it to the new table. I am
assuming that it doesnt know the scriptureID and thats why its promting me
for it. What am I failing to do or not getting from this. Thanks any
responses greatly appreciated.
Click to expand...

Is it asking you for [forms]![study Edit]![Study Edit Sub]![ScriptureID] ?
If so, I would guess that "Study Edit Sub" is not really the name of the
subform control (on form "Study Edit") that is displaying the subform. In
subform references, it;s the name of the subform *control* that you have to
use, and that may or may not be the same as the form object that is
displayed by that control.

If it's asking you for TStudyScriptures.ScriptureId, then I'd guess that you
misspelled the name of the field.

Either way, I suspect that your query would be more efficient using a WHERE
clause instead of a HAVING clause:

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM
TStudy
INNER JOIN
TStudyScriptures
ON TStudy.studyid = TStudyScriptures.studyId
WHERE TStudyScriptures.ScriptureId =
[forms]![study Edit]![Study Edit Sub]![ScriptureID]
GROUP BY
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks,
TStudyScriptures.ScriptureId
 
D

Dirk Goldgar

Mark Andrews said:
Try changing
[forms]![study Edit]![Study Edit Sub]![ScriptureID]
to
[forms]![study Edit]![Study Edit Sub].Form.[ScriptureID]

http://www.allenbrowne.com/casu-04.html
Click to expand...


Allen's article is misleading. If you do all your object qualifying using
the dot (.) operator, then you must use the ".Form" qualifier after the
subform control name. If you use the bang (!) operator, you don't.
 
M

Mark Andrews

Good to know! I've been doing it that way for years. I would guess then
the subform name on the form is NOT "Study Edit Sub" as you mentioned.

Mark


Dirk Goldgar said:
Mark Andrews said:
Try changing
[forms]![study Edit]![Study Edit Sub]![ScriptureID]
to
[forms]![study Edit]![Study Edit Sub].Form.[ScriptureID]

http://www.allenbrowne.com/casu-04.html
Click to expand...


Allen's article is misleading. If you do all your object qualifying using
the dot (.) operator, then you must use the ".Form" qualifier after the
subform control name. If you use the bang (!) operator, you don't.

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Mark Andrews said:
Good to know! I've been doing it that way for years. I would guess then
the subform name on the form is NOT "Study Edit Sub" as you mentioned.
Click to expand...


Or something else is wrong, but I'd guess at that as the most likely
candidate. We'll see.
 
B

BrianPaul

Here is the form name Study Edit
Here is the subform name Study Edit Sub

I tried both of them. Still pops up the box, If I enter the Number it will
copy to the table. Still wondering what it could be. I have another
database that it does work. I even checked properties to see if was locked,
etc.


Dirk Goldgar said:
BrianPaul said:
I have on a subform a command button. Here is the Code

DoCmd.OpenQuery "QFactAdd", acViewNormal, acEdit

In the Query here is the Code

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM TStudy INNER JOIN TStudyScriptures ON TStudy.studyid =
TStudyScriptures.studyId
GROUP BY TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks, TStudyScriptures.ScriptureId
HAVING (((TStudyScriptures.ScriptureId)=[forms]![study Edit]![Study Edit
Sub]![ScriptureID]));.

When I run the code, a Box comes up asking me for the scriptureid. If I
input the number it recognizes it and adds it to the new table. I am
assuming that it doesnt know the scriptureID and thats why its promting me
for it. What am I failing to do or not getting from this. Thanks any
responses greatly appreciated.
Click to expand...

Is it asking you for [forms]![study Edit]![Study Edit Sub]![ScriptureID] ?
If so, I would guess that "Study Edit Sub" is not really the name of the
subform control (on form "Study Edit") that is displaying the subform. In
subform references, it;s the name of the subform *control* that you have to
use, and that may or may not be the same as the form object that is
displayed by that control.

If it's asking you for TStudyScriptures.ScriptureId, then I'd guess that you
misspelled the name of the field.

Either way, I suspect that your query would be more efficient using a WHERE
clause instead of a HAVING clause:

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM
TStudy
INNER JOIN
TStudyScriptures
ON TStudy.studyid = TStudyScriptures.studyId
WHERE TStudyScriptures.ScriptureId =
[forms]![study Edit]![Study Edit Sub]![ScriptureID]
GROUP BY
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks,
TStudyScriptures.ScriptureId


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
 
C

Clif McIrvin

BrianPaul said:
Here is the form name Study Edit
Here is the subform name Study Edit Sub
Click to expand...

What is the name of the *subform control* that holds [Study Edit Sub] ??

change [forms]![study Edit]![Study Edit Sub]![ScriptureID]
to [forms]![study Edit]![name of subform control]![ScriptureID]

As Dirk pointed out, the subform control name and the subform name could
quite easily be different.

--
Clif
I tried both of them. Still pops up the box, If I enter the Number it
will
copy to the table. Still wondering what it could be. I have another
database that it does work. I even checked properties to see if was
locked,
etc.


Dirk Goldgar said:
BrianPaul said:
I have on a subform a command button. Here is the Code

DoCmd.OpenQuery "QFactAdd", acViewNormal, acEdit

In the Query here is the Code

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM TStudy INNER JOIN TStudyScriptures ON TStudy.studyid =
TStudyScriptures.studyId
GROUP BY TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks, TStudyScriptures.ScriptureId
HAVING (((TStudyScriptures.ScriptureId)=[forms]![study Edit]![Study
Edit
Sub]![ScriptureID]));.

When I run the code, a Box comes up asking me for the scriptureid.
If I
input the number it recognizes it and adds it to the new table. I
am
assuming that it doesnt know the scriptureID and thats why its
promting me
for it. What am I failing to do or not getting from this. Thanks
any
responses greatly appreciated.
Click to expand...

Is it asking you for [forms]![study Edit]![Study Edit
Sub]![ScriptureID] ?
If so, I would guess that "Study Edit Sub" is not really the name of
the
subform control (on form "Study Edit") that is displaying the
subform. In
subform references, it;s the name of the subform *control* that you
have to
use, and that may or may not be the same as the form object that is
displayed by that control.

If it's asking you for TStudyScriptures.ScriptureId, then I'd guess
that you
misspelled the name of the field.

Either way, I suspect that your query would be more efficient using a
WHERE
clause instead of a HAVING clause:

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM
TStudy
INNER JOIN
TStudyScriptures
ON TStudy.studyid = TStudyScriptures.studyId
WHERE TStudyScriptures.ScriptureId =
[forms]![study Edit]![Study Edit Sub]![ScriptureID]
GROUP BY
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks,
TStudyScriptures.ScriptureId


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
B

BrianPaul

ScriptureId Is the control, SubjectID links the 2 forms, ScriptureID is on
the subform

Clif McIrvin said:
BrianPaul said:
Here is the form name Study Edit
Here is the subform name Study Edit Sub
Click to expand...

What is the name of the *subform control* that holds [Study Edit Sub] ??

change [forms]![study Edit]![Study Edit Sub]![ScriptureID]
to [forms]![study Edit]![name of subform control]![ScriptureID]

As Dirk pointed out, the subform control name and the subform name could
quite easily be different.

--
Clif
I tried both of them. Still pops up the box, If I enter the Number it
will
copy to the table. Still wondering what it could be. I have another
database that it does work. I even checked properties to see if was
locked,
etc.


Dirk Goldgar said:
I have on a subform a command button. Here is the Code

DoCmd.OpenQuery "QFactAdd", acViewNormal, acEdit

In the Query here is the Code

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM TStudy INNER JOIN TStudyScriptures ON TStudy.studyid =
TStudyScriptures.studyId
GROUP BY TStudy.Subject, TStudyScriptures.Scripture,
TStudyScriptures.remarks, TStudyScriptures.ScriptureId
HAVING (((TStudyScriptures.ScriptureId)=[forms]![study Edit]![Study
Edit
Sub]![ScriptureID]));.

When I run the code, a Box comes up asking me for the scriptureid.
If I
input the number it recognizes it and adds it to the new table. I
am
assuming that it doesnt know the scriptureID and thats why its
promting me
for it. What am I failing to do or not getting from this. Thanks
any
responses greatly appreciated.

Is it asking you for [forms]![study Edit]![Study Edit
Sub]![ScriptureID] ?
If so, I would guess that "Study Edit Sub" is not really the name of
the
subform control (on form "Study Edit") that is displaying the
subform. In
subform references, it;s the name of the subform *control* that you
have to
use, and that may or may not be the same as the form object that is
displayed by that control.

If it's asking you for TStudyScriptures.ScriptureId, then I'd guess
that you
misspelled the name of the field.

Either way, I suspect that your query would be more efficient using a
WHERE
clause instead of a HAVING clause:

INSERT INTO TFacts ( Subject, Scripture, remarks )
SELECT
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks
FROM
TStudy
INNER JOIN
TStudyScriptures
ON TStudy.studyid = TStudyScriptures.studyId
WHERE TStudyScriptures.ScriptureId =
[forms]![study Edit]![Study Edit Sub]![ScriptureID]
GROUP BY
TStudy.Subject,
TStudyScriptures.Scripture,
TStudyScriptures.remarks,
TStudyScriptures.ScriptureId


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
D

Dirk Goldgar

BrianPaul said:
ScriptureId Is the control, SubjectID links the 2 forms, ScriptureID is
on
the subform

Clif McIrvin said:
What is the name of the *subform control* that holds [Study Edit Sub] ??

change [forms]![study Edit]![Study Edit Sub]![ScriptureID]
to [forms]![study Edit]![name of subform control]![ScriptureID]

As Dirk pointed out, the subform control name and the subform name could
quite easily be different.
Click to expand...
Click to expand...


I'm not sure you understand the distinction we're trying to make. As I
understand it, you have two form objects, one named "Study Edit", and one
named "Study Edit Sub". "Study Edit Sub" is being displayed as a subform on
"Study Edit". There is a control on "Study Edit" that displays the subform.
That control is what is called a "subform control", and it has a name of its
own. That name may also be "Study Edit Sub", or it may be something else
altogether.

If you open the main form, "Study Edit" in design view, then click just once
on the subform, and then open its property sheet, that property sheet should
include (on the Other tab) the name of the control. What is it?
 
B

BrianPaul

That was It. Not the name of the form but the source object. Took me a while
to catch on. Since I used another form as a template and did a save as, That
is what threw me. Thanks.
 
C

Clif McIrvin

BrianPaul said:
That was It. Not the name of the form but the source object. Took me a
while
to catch on. Since I used another form as a template and did a save
as, That
is what threw me. Thanks.
Click to expand...


Glad you got it sorted. Learning Access development terminology can
definately be a challenge <smile>.

By the way ... your app sounds interesting. If you're at all interested
in discussing or sharing it with others, I'd like to hear more about it.
You can reply directly to me by removing the never and .invalid
from my reply-to address.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

requery works at breakpoint but not otherwise 2
Why will this code work stepping through but not in window 2
Shortcuts: I should know this but my brain's not working today 1
run macro on other computer but not this one (workbook open event) 0
I'm not sure which NG I should ask this question. 2
Edit and Update of field not working 5
REPOST with More info - tables are there but IsTablesPresent() tells me its not ! 0

Top