By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,453 Members | 3,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,453 IT Pros & Developers. It's quick & easy.

Append Query - Only Appends 1 ID (Need more)!!

P: 99
Hey,

I have 2 append queries. The first is based off of code given and works great. For the second, I followed the format of the 1st and adjusted it to meet my needs. Query 1 works great. It appends each specific clientId to the table when the command button from form 1 is selected (code below). The 2nd query works as well. But it will only append 1 employee record to the table. I thought this must be because it is only allowing for 1 clientID to be appended, but when I switch to a different client and try to append an employee, it has the same response (not allowing it). If anyone has suggestions I would appreciate them. Thanks.

Background: 2 tables are being appended to. Table1 (tblAnswers), query1 (qryappendSelect), and form 1 all work perfectly. Table 2 (tblAnswersEmployee) follows the same format as tblAnswers, except it includes a EmlpoyeeId field. Qry2 and Form 2 were based off of the respective 1, qry2 had some code alterations to append the correct fields.


Query 1
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblAnswers ( ClientAutoID, QuestionNumber )
  2. SELECT tblClient.ClientAutoID, tblQuestions.QuestionNumber
  3. FROM tblClient, tblQuestions
  4. WHERE (((tblClient.ClientAutoID)=[Forms]![frmClient]![ClientAutoID]) AND ((tblQuestions.QuestionNumber) Not Between 601 And 799));
Form 1
Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_Click()
  2. On Error GoTo Err_Update_Click
  3.     Dim stDocName As String
  4.     stDocName = "qryAppendSelect"
  5. If Nz(DLookup("[ClientAutoID]", "tblAnswers", "[ClientAutoID]=" & Me.ClientAutoID), 0) = 0 Then
  6.    DoCmd.OpenQuery stDocName, acNormal, acEdit
  7. End If
  8. Exit_Update_Click:
  9.     Exit Sub
  10. Err_Update_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_Update_Click
  13. End Sub
Query 2
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblAnswersEmployee ( ClientAutoID, EmployeeAutoID, QuestionNumber )
  2. SELECT tblEmployee.ClientAutoID, tblEmployee.EmployeeAutoID, tblQuestions.QuestionNumber
  3. FROM tblQuestions, tblEmployee
  4. WHERE (((tblEmployee.EmployeeAutoID)=[Forms]![sbfEmployee1]![EmployeeAutoID]) AND ((tblQuestions.QuestionNumber) Between 601 And 605 Or (tblQuestions.QuestionNumber) Between 701 And 704)); 
Form 2
Expand|Select|Wrap|Line Numbers
  1. Private Sub Update_Click()
  2. On Error GoTo Err_Update_Click
  3.     Dim stDocName As String
  4.     stDocName = " qryEmployeeAppendSelect"
  5. If Nz(DLookup("[EmployeeAutoID]", "tblAnswersEmployee", "[ EmployeeAutoID]=" & Me.EmployeeAutoID), 0) = 0 Then
  6.    DoCmd.OpenQuery stDocName, acNormal, acEdit
  7. End If
  8. Exit_Update_Click:
  9.     Exit Sub
  10. Err_Update_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_Update_Click
  13. End Sub
Dec 18 '06 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Robert in Query 1 we were allowing for the fact that each client needed to correspond to all questions. The second query is different because each client won't correspond to all Employees.

As you have put ClientAutoID in tblEmployees that means each employee only has one client. This doesn't sound right - can you confirm?

Mary
Dec 19 '06 #2

P: 99
Robert in Query 1 we were allowing for the fact that each client needed to correspond to all questions. The second query is different because each client won't correspond to all Employees.

As you have put ClientAutoID in tblEmployees that means each employee only has one client. This doesn't sound right - can you confirm?

Mary
Sorry for the confusion. The Employees discussed are the ones who work for the client. So each client (company) can have several employees. Each employee should only have 1 client (company).

I will try to look at my DB from an outside perspective when posting. I just take it for granted ya’ll all know what I’m thinking when I type which of course is not the case. So my bad. Thanks again.
Dec 19 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Sorry for the delay in replying Robert. Can you post the structure of the tblEmployees and tblEmployeeAnswers tables for me to look at.

Mary
Dec 20 '06 #4

P: 99
Sorry for the delay in replying Robert. Can you post the structure of the tblEmployees and tblEmployeeAnswers tables for me to look at.

Mary
Sure thing.

tblEmployee:

EmployeeAutoID (PK); ClientAutoID (FK) linked to tblClient 1 to many; the other fields are all general employee information (name….).

tblAnswersEmployee:

EmployeeAnswerAutoID (PK); EmployeeAutoID (FK) linked to tblEmployee 1 to many; ClientAutoID (FK) linked to tblClient 1 to many; QuestionNumber (FK) linked to tblQuestions 1 to many; EmployeeAnswer.
Dec 21 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Robert,

Try this. I've moved some of the Brackets. Copy and paste it as is. Access may add some more don't worry about it. If this doesn't solve the problem email me the database after christmas and I'll have a look.

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblAnswersEmployee (ClientAutoID, EmployeeAutoID, QuestionNumber )
  2. SELECT tblEmployee.ClientAutoID, tblEmployee.EmployeeAutoID, tblQuestions.QuestionNumber
  3. FROM tblQuestions, tblEmployee
  4. WHERE ((tblEmployee.EmployeeAutoID = [Forms]![sbfEmployee1]![EmployeeAutoID]) 
  5. AND ((tblQuestions.QuestionNumber Between 601 And 605) 
  6. Or (tblQuestions.QuestionNumber Between 701 And 704))); 
  7.  
Merry Christmas

Mary
Dec 21 '06 #6

Post your reply

Sign in to post your reply or Sign up for a free account.