Hi
I really appreciate the help I have received so far! I can run this
append query from the Query and it works fine. I have copied the text
into VBA for a command button and I keep getting a "Type Mismatch"
error:

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
tblProjectGender.ProjectGenderID,
tblProjectAgeGroup.ProjectAgeGroupID" & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup" & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
[ProjectID]))"
I would greatly appreciate some more help
Thanks
Heather

On Tue, 4 Oct 2011 17:00:22 -0700 (PDT), Fev <(E-Mail Removed)>
wrote:

>SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
>ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
>"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
>tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
>tblProjectGender.ProjectGenderID,
>tblProjectAgeGroup.ProjectAgeGroupID" & _
>"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
>tblProjectGender, tblProjectAgeGroup" & _
>"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
>[ProjectID]))"
>I

You're missing some critically important blanks. The lines that you're
concatenating don't have trailing blaks so you're ending up with meaningless
items like ProjectAgeGroupIDFROM and tblProjectAgetGroupWHERE. Try including
blanks in the string pieces you're putting together:

SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID, " & _
"ProjectEthnic, ProjectGender, ProjectAgeGroup ) " & _
"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID, " & _
"tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID, " & _
"tblProjectGender.ProjectGenderID, " & _
"tblProjectAgeGroup.ProjectAgeGroupID " & _
"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic, " & _
"tblProjectGender, tblProjectAgeGroup " & _
"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]![ProjectID]))"
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Oct 5, 2:54*am, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Tue, 4 Oct 2011 17:00:22 -0700 (PDT), Fev <hgos...@worldonline.co.za>
> wrote:
>
> >SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
> >ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
> >"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
> >tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
> >tblProjectGender.ProjectGenderID,
> >tblProjectAgeGroup.ProjectAgeGroupID" & _
> >"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
> >tblProjectGender, tblProjectAgeGroup" & _
> >"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
> >[ProjectID]))"
> >I
>
> You're missing some critically important blanks. The lines that you're
> concatenating don't have trailing blaks so you're ending up with meaningless
> items like ProjectAgeGroupIDFROM and tblProjectAgetGroupWHERE. Try including
> blanks in the string pieces you're putting together:
>
> SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID, " & _
> "ProjectEthnic, ProjectGender, ProjectAgeGroup ) " & _
> "SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID, " & _
> "tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID, " & _
> "tblProjectGender.ProjectGenderID, " & _
> "tblProjectAgeGroup.ProjectAgeGroupID " & _
> "FROM tblProjectArea, tblProjectLSM, tblProjectEthnic, " & _
> "tblProjectGender, tblProjectAgeGroup " & _
> "WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]![ProjectID]))"
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com

Hi John
Thanks for the pointers, I have added extra spaces in at the end of
each line, but still get the Type mismatch error. My code now reads:
SQL1 = " INSERT INTO tblLSMDetail ( ProjectLSM , ProjectArea ,
ProjectID , ProjectEthnic , ProjectGender , ProjectAgeGroup ) " &
_
" SELECT tblProjectLSM.ProjectLSMID ,
tblProjectArea.ProjectAreaID , tblProjectLSM.ProjectID ,
tblProjectEthnic.ProjectEthnicID , " & _
" tblProjectGender.ProjectGenderID ,
tblProjectAgeGroup.ProjectAgeGroupID " & _
" FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
tblProjectGender, tblProjectAgeGroup " & _
" WHERE (((tblProjectLSM.ProjectID) = [Forms]![frmSampleBuilder]!
[ProjectID ])) "
Thanks
Heather

On Tue, 4 Oct 2011 22:39:10 -0700 (PDT), Fev <(E-Mail Removed)>
wrote:

>Thanks for the pointers, I have added extra spaces in at the end of
>each line, but still get the Type mismatch error. My code now reads:
>SQL1 = " INSERT INTO tblLSMDetail ( ProjectLSM , ProjectArea ,
>ProjectID , ProjectEthnic , ProjectGender , ProjectAgeGroup ) " &
>_
>" SELECT tblProjectLSM.ProjectLSMID ,
>tblProjectArea.ProjectAreaID , tblProjectLSM.ProjectID ,
>tblProjectEthnic.ProjectEthnicID , " & _
>" tblProjectGender.ProjectGenderID ,
>tblProjectAgeGroup.ProjectAgeGroupID " & _
>" FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
>tblProjectGender, tblProjectAgeGroup " & _
>" WHERE (((tblProjectLSM.ProjectID) = [Forms]![frmSampleBuilder]!
>[ProjectID ])) "
>Thanks
>Heather

Whoa. I may see the problem.

Are tblProjectArea, tblProjectLSM, tblProjectEthnic etc. all ***DIFFERENT
TABLES***? Do they have any relationships, or are they one-row tables, or
what? You have some sort of monster multitable cartesian join going on if
these are multirow tables.

What is the datatype of tblProjectLSM.ProjectID, and does that table have any
relationships to these other tables?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Oct 5, 8:02*am, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
> On Tue, 4 Oct 2011 22:39:10 -0700 (PDT), Fev <hgos...@worldonline.co.za>
> wrote:
>
>
>
>
>
> >Thanks for the pointers, I have added extra spaces in at the end of
> >each line, but still get the Type mismatch error. *My code now reads:
> >SQL1 = " INSERT INTO tblLSMDetail ( ProjectLSM , *ProjectArea ,
> >ProjectID , *ProjectEthnic , *ProjectGender , *ProjectAgeGroup ) *" &
> >_
> >" SELECT *tblProjectLSM.ProjectLSMID ,
> >tblProjectArea.ProjectAreaID , *tblProjectLSM.ProjectID ,
> >tblProjectEthnic.ProjectEthnicID , " & *_
> >" tblProjectGender.ProjectGenderID ,
> >tblProjectAgeGroup.ProjectAgeGroupID *" & *_
> >" FROM *tblProjectArea, tblProjectLSM, *tblProjectEthnic,
> >tblProjectGender, *tblProjectAgeGroup *" & *_
> >" WHERE *(((tblProjectLSM.ProjectID) = [Forms]![frmSampleBuilder]!
> >[ProjectID ])) "
> >Thanks
> >Heather
>
> Whoa. I may see the problem.
>
> Are tblProjectArea, tblProjectLSM, tblProjectEthnic etc. all ***DIFFERENT
> TABLES***? Do they have any relationships, or are they one-row tables, or
> what? You have some sort of monster multitable cartesian join going on if
> these are multirow tables.
>
> What is the datatype of tblProjectLSM.ProjectID, and does that table haveany
> relationships to these other tables?
> --
>
> * * * * * * *John W. Vinson [MVP]
> *Microsoft's replacements for these newsgroups:
> *http://social.msdn.microsoft.com/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com- Hide quoted text -
>
> - Show quoted text -

Hi John
Thanks for the ongoing support. The ProjectID is a long Integer, as
there is a relationship to the ProjectID field as an Autonumber in the
db. When I run the query as an update query from the QBE grid, I am
getting the exact result that I require. It is only in the VBA code
that I get the Mismatch type error. I can run the query from a button
on the form as well with no ill effects. It would just be neater to
run both the query and some other code from a single command button.
I hope I have answered all your questions.
Thanks
Heather

On Tue, 4 Oct 2011 17:00:22 -0700 (PDT), Fev <(E-Mail Removed)>
wrote:

>Hi
>I really appreciate the help I have received so far! I can run this
>append query from the Query and it works fine. I have copied the text
>into VBA for a command button and I keep getting a "Type Mismatch"
>error:
>
>SQL1 = "INSERT INTO tblLSMDetail ( ProjectLSM, ProjectArea, ProjectID,
>ProjectEthnic, ProjectGender, ProjectAgeGroup )" & _
>"SELECT tblProjectLSM.ProjectLSMID, tblProjectArea.ProjectAreaID,
>tblProjectLSM.ProjectID, tblProjectEthnic.ProjectEthnicID,
>tblProjectGender.ProjectGenderID,
>tblProjectAgeGroup.ProjectAgeGroupID" & _
>"FROM tblProjectArea, tblProjectLSM, tblProjectEthnic,
>tblProjectGender, tblProjectAgeGroup" & _
>"WHERE (((tblProjectLSM.ProjectID)=[Forms]![frmSampleBuilder]!
>[ProjectID]))"
>I would greatly appreciate some more help
>Thanks
>Heather

Hrm. So you're not getting the error from the execution of the query, but in
the code! How is SQL1 dimensioned (or is it)? If you step through the code in
debug mode, what line triggers the error and what is highlighted?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com