Query not capturing all Entries 1

[houstonbill]

This has me stumpted. Have a query composed to 2 tables that make up the form. When the user selects a challenge ID and fills in a response, it appears the most recent entry is replacing the previous. Therefore, if several people responded at different times to the same ID #, then I will not see all the responses and cannot pull them all into a report. Can you take a look at my SQL and let me know what might be wrong?

SELECT tbl_Challenge.ChallID, tbl_Challenge.Challenge, tbl_Challenge.NeedBy, tbl_Challenge.BadgeID, tbl_Challenge.Department, tbl_Challenge.ChallDate, tbl_CallengeReview.ChallComments, tbl_Challenge.ClosedDte, tbl_CallengeReview.ChDate
FROM tbl_Challenge LEFT JOIN tbl_CallengeReview ON tbl_Challenge.ChallID = tbl_CallengeReview.ChallID;

 

[dhookom]

Your posting doesn't make much sense. There is nothing in the SQL that suggests "user selects a challenge ID and fills in a response".

It sounds like users are entering new information in old records.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[houstonbill]

It is a bit confusing to word the question as well. User goes from search box, double clicks the Chall ID, and goes to a form that has a couple of fields prepopulated, yet the portion that the user will fill in is blank. they fill it in and I should think that it would download to my table but only the most recent shows up. It it helps, below is the coding for the search box where the user will double click the Challenge ID they want to respond to.

Private Sub List93_DblClick(Cancel As Integer)
On Error GoTo Err_List93_DblClick
Dim rs As Object

DoCmd.OpenForm "frmChallResp"

Set rs = Forms!frmChallResp.Recordset.Clone
rs.FindFirst "[ChallID] = " & Str(Nz(Me![List93], 0))
If Not rs.EOF Then
With Forms!frmChallResp
.Bookmark = rs.Bookmark
![ChallComments] = ""
End With
End If

Exit_List93_DblClick:
Exit Sub

Err_List93_DblClick:
MsgBox Err.Description, , " Challenge"
Resume Exit_List93_DblClick
End Sub

 

[traingamer]

It looks to me like you'd be better off with a sub-form containing your tbl_CallengeReview data - otherwise how do you know which record you are updating / entering?

What is the key to the review table?

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[houstonbill]

challengeID is the Primary Key for the Challenge table and aligns with the challenge ID on the tbl_Challenge Review (one to many??) which has a primary key of ID. Unless you can think of anything else, perhaps I will try the subform route and see how that works.

One thing that I have been running into with the subform (datasheet view)is that even though the comments field is set to Memo and Can Grow, somehow it cuts off some of my words on the report end, even though they are on the table. What might cause that? I am afraid I will run into that with this situation as well.

 

[dhookom]

If a memo field is being truncated in the report, check to see if it is actually being truncated in the report's record source query. If the query is the culprit, it is usually caused by phrases in your SQL like "GROUP BY" or "DISTINCTROW" or "DISTINCT".

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[houstonbill]

I am always humbled when my questions are answered. Does one ever know it all?? In any event, you were right on both counts. The sub form does exactly the right thing now, picking all multiple entries. In addition, the report works great now. It is true, I had a GroupBy on the underlying query and once removed the memo field showed all my data. I would never have figured that one out. You are the best!