Oh yeah, the tblApplicationHousehold primary key is HouseholdID not ApplicationID. Thats why I need to insert into the new row also. It's what links the tblApplicationHousehold table to the tblApplication table.
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]
Tidak ada komentar:
Posting Komentar