ACCESS FEED: RE: [MS_AccessPros] Append query not working

Duh. The original SQL is correct. I was thinking ApplicaNTID, not ApplicaTIONID.

When you run this SQL:

INSERT INTO tblApplicationHousehold (ApplicationID, FirstName, MiddleName, LastName, Suffix,
MaidenName, DOB, SSN, Gender, Race, EnrolledInTribeFlag, Tribe,
TribalEnrollNumber, DisabledFlag, DisabledDescription )
SELECT tblApplication.ApplicationID, tblApplicant.FirstName, tblApplicant.MiddleName,
tblApplicant.LastName,
tblApplicant.Suffix, tblApplicant.MaidenName, tblApplicant.DOB,
tblApplicant.SSN, tblApplicant.Gender, tblApplicant.Race,
tblApplicant.EnrolledInTribeFlag, tblApplicant.Tribe,
tblApplicant.TribalEnrollNumber, tblApplicant.DisabledFlag,
tblApplicant.DisabledDescription
FROM tblApplicant INNER JOIN (tblApplication INNER JOIN tblApplicationHousehold
ON tblApplication.ApplicationID=tblApplicationHousehold.ApplicationID) ON
tblApplicant.ApplicantID=tblApplication.ApplicantID
WHERE [tblApplication].[ApplicationID]=[Forms]![frmApplication]![ApplicationID];

It should put new row(s) in tblApplicationHousehold that are an exact copy of all the rows currently in tblApplicationHousehold for the specified ApplicationID. Those rows won't show up on any open form until you Requery the form.

You still haven't answered why you want to do this.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Wednesday, May 25, 2011 8:56 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Append query not working

First I created the original query using the query designer so Access through in all the neat joins you see. I need the join to the application table because I need to have tblApplication.ApplicationID to use in my WHERE statement.

WHERE [tblApplication].[ApplicationID]=[Forms]![frmApplication]![ApplicationID];

Right now it prompts me for tblApplication.ApplicationID when I run the query.

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, May 25, 2011 1:24 PM
Subject: RE: [MS_AccessPros] Append query not working

Art-

I should have removed all the extraneous joins. How did you build this bizarre query? It should be:

INSERT INTO tblApplicationHousehold ( FirstName, MiddleName, LastName, Suffix, MaidenName, DOB, SSN, Gender, Race, EnrolledInTribeFlag, Tribe, TribalEnrollNumber, DisabledFlag, DisabledDescription )
SELECT tblApplicant.FirstName, tblApplicant.MiddleName, tblApplicant.LastName, tblApplicant.Suffix, tblApplicant.MaidenName, tblApplicant.DOB, tblApplicant.SSN, tblApplicant.Gender, tblApplicant.Race, tblApplicant.EnrolledInTribeFlag, tblApplicant.Tribe, tblApplicant.TribalEnrollNumber, tblApplicant.DisabledFlag, tblApplicant.DisabledDescription
FROM tblApplicant
WHERE [tblApplication].[ApplicationID]=[Forms]![frmApplication]![ApplicationID];

Why are you wanting to exactly duplicate the current record?

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Wednesday, May 25, 2011 8:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Append query not working

Ok. I took your query:

INSERT INTO tblApplicationHousehold ( FirstName, MiddleName, LastName, Suffix, MaidenName, DOB, SSN, Gender, Race, EnrolledInTribeFlag, Tribe, TribalEnrollNumber, DisabledFlag, DisabledDescription )
SELECT tblApplicant.FirstName, tblApplicant.MiddleName, tblApplicant.LastName, tblApplicant.Suffix, tblApplicant.MaidenName, tblApplicant.DOB, tblApplicant.SSN, tblApplicant.Gender, tblApplicant.Race, tblApplicant.EnrolledInTribeFlag, tblApplicant.Tribe, tblApplicant.TribalEnrollNumber, tblApplicant.DisabledFlag, tblApplicant.DisabledDescription
FROM tblApplicant INNER JOIN (tblApplication INNER JOIN tblApplicationHousehold ON tblApplication.ApplicationID=tblApplicationHousehold.ApplicationID) ON tblApplicant.ApplicantID=tblApplication.ApplicantID
WHERE [tblApplication].[ApplicationID]=[Forms]![frmApplication]![ApplicationID];

Still no insert.

I have uploaded the a copy of the form to Needs Assistance folder so you can see what I am looking at. The 535 is the ApplicationID.

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947

1316 E. 7th Street
Sioux Falls, SD 57103
SQL Server Development
Database Adminstration Services
Microsoft Access Development
Grant Writing TA Services
IT Assessment Services
Software Application Training

From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, May 25, 2011 12:54 PM
Subject: RE: [MS_AccessPros] Append query not working

Art-

Your insert query is trying to insert the exact same row, so it should fail. If
you want to insert a *new* row based on the current ApplicantID but in a new
record, your query should be:

INSERT INTO tblApplicationHousehold (FirstName, MiddleName, LastName, Suffix,
MaidenName, DOB, SSN, Gender, Race, EnrolledInTribeFlag, Tribe,
TribalEnrollNumber, DisabledFlag, DisabledDescription )
SELECT tblApplicant.FirstName, tblApplicant.MiddleName, tblApplicant.LastName,
tblApplicant.Suffix, tblApplicant.MaidenName, tblApplicant.DOB,
tblApplicant.SSN, tblApplicant.Gender, tblApplicant.Race,
tblApplicant.EnrolledInTribeFlag, tblApplicant.Tribe,
tblApplicant.TribalEnrollNumber, tblApplicant.DisabledFlag,
tblApplicant.DisabledDescription
FROM tblApplicant INNER JOIN (tblApplication INNER JOIN tblApplicationHousehold
ON tblApplication.ApplicationID=tblApplicationHousehold.ApplicationID) ON
tblApplicant.ApplicantID=tblApplication.ApplicantID
WHERE [tblApplication].[ApplicationID]=[Forms]![frmApplication]![ApplicationID];

I'm assuming that ApplicationID is an AutoNumber.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Wednesday, May 25, 2011 7:20 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Append query not working

I have a form called frmApplication which contains a field called ApplicationID.
On the form frmApplication is a subform called sfrmHouseholdMembers which is a
continuous form.

Also on the frmApplication is a commnd button called cmdAddApplicant

The code behind cmdAddApplication is as follows:

Private Sub cmdAddApplicantToHousehold_Click()
On Error GoTo Err_cmdAddApplicantToHousehold_Click
Dim Response As Integer

' Displays a message box with the yes and no options.
Response = MsgBox(prompt:="Add Applicant to Household?", Buttons:=vbYesNo)

' If statement to check if the yes button was selected.
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryAddApplicant")
DoCmd.SetWarnings True
Me.sfrmApplicationHouseHold.Requery

Else
' The no button was selected.
Exit Sub

End If

Exit_cmdAddApplicantToHousehold_Click:
Exit Sub
Err_cmdAddApplicantToHousehold_Click:

ErrorLog "cmdAddApplicantToHousehold_Click", "frmApplication", Err, Error
Resume Exit_cmdAddApplicantToHousehold_Click
End Sub

The qryAddApplicant is defined as following:
INSERT INTO tblApplicationHousehold ( ApplicationID, FirstName, MiddleName,
LastName, Suffix, MaidenName, DOB, SSN, Gender, Race, EnrolledInTribeFlag,
Tribe, TribalEnrollNumber, DisabledFlag, DisabledDescription )
SELECT tblApplication.ApplicationID, tblApplicant.FirstName,
tblApplicant.MiddleName, tblApplicant.LastName, tblApplicant.Suffix,
tblApplicant.MaidenName, tblApplicant.DOB, tblApplicant.SSN,
tblApplicant.Gender, tblApplicant.Race, tblApplicant.EnrolledInTribeFlag,
tblApplicant.Tribe, tblApplicant.TribalEnrollNumber, tblApplicant.DisabledFlag,
tblApplicant.DisabledDescription
FROM tblApplicant INNER JOIN (tblApplication INNER JOIN tblApplicationHousehold
ON tblApplication.ApplicationID=tblApplicationHousehold.ApplicationID) ON
tblApplicant.ApplicantID=tblApplication.ApplicantID
WHERE [tblApplication].[ApplicationID]=[Forms]![frmApplication]![ApplicationID];

The issue is, qryAddApplicant is not inserting anything even though there is a
value in the ApplicationID field on frmApplication.

Pretty much stumped on this one. Any advise!

Thank you,

Arthur Lorenzini
Sioux Falls,SD

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links