I tried it, and it seems like it *will* work, once I get my code straight.
Right now, it's telling me "Join expression not supported."
Private Sub Report_Open(Cancel As Integer)
Dim strInput As String
Dim strSQL As String
strInput = InputBox("Enter your IDs, comma-delimited.", "")
strSQL = "SELECT [Data Entry].Record AS ID, [Data Entry].Group, " _
& "IIf(tblMember!Pt_First_Name Is Null,[First Name],tblMember!Pt_First_Name)
AS Pt_First, " _
& "IIf(tblMember_1!Pt_Last_Name Is Null,[Last Name],tblMember_1!Pt_Last_Name)
AS Pt_Last, " _
& "IIf(tblMember_2!Pt_Member_ID Is Null,[Member Number],tblMember_2!
Pt_Member_ID) AS [Member ID], " _
& "[Data Entry].Case, [Data Entry].[Case Type], [Data Entry].[UBH Received],
[Data Entry].Acknowledged, " _
& "[Data Entry].Determination, [Data Entry].[Due Date], [Data Entry].
Explanation, " _
& "tlkpUser!User_First_Name+' '+tlkpUser_1!User_Last_Name AS Reviewer1, " _
& "tlkpUser_2!User_First_Name+' '+tlkpUser_3!User_Last_Name AS Reviewer2, " _
& "tlkpUser_4!User_First_Name+' '+tlkpUser_5!User_Last_Name AS Reviewer3, " _
& "[Data Entry].[To Change - Case], [Data Entry].[To Change - Ackno], [Data
Entry].[To Change - Det], " _
& "[Data Entry].[To Change - Reviewer], [Data Entry].[Violated Standard], " _
& "[Data Entry].[Due Date Ackno] " _
& "FROM (((((((([Data Entry] LEFT JOIN tlkpUser ON [Data Entry].[1 Reviewer
First] = tlkpUser.User_ID) LEFT JOIN tlkpUser AS tlkpUser_1 ON [Data Entry].
[1 Reviewer Last] = tlkpUser_1.User_ID) LEFT JOIN tlkpUser AS tlkpUser_2 ON
[Data Entry].[2 Reviewer First] = tlkpUser_2.User_ID) LEFT JOIN tlkpUser AS
tlkpUser_3 ON [Data Entry].[2 Reviewer Last] = tlkpUser_3.User_ID) LEFT JOIN
tlkpUser AS tlkpUser_4 ON [Data Entry].[3 Reviewer First] = tlkpUser_4.
User_ID) LEFT JOIN tlkpUser AS tlkpUser_5 ON [Data Entry].[3 Reviewer Last] =
tlkpUser_5.User_ID) LEFT JOIN tblMember ON [Data Entry].[First Name] =
tblMember.Patient_ID) LEFT JOIN tblMember AS tblMember_1 ON [Data Entry].
[Last Name] = tblMember_1.Patient_ID) LEFT JOIN tblMember AS tblMember_2 ON
[Data Entry].[Member Number] = tblMember_2.Patient_ID" _
& "WHERE ((([Data Entry].Record) In (" & strInput & "))) ORDER BY [Data Entry]
Determination;"
Me.RecordSource = strSQL
End Sub
Ouch. That's no good. Thank you for the response.
So, currently, the query is a backend to a report. The user clicks a button,
and is prompted. It generates something that can be saved as snp that has
friendly formatting.
So . . . in the in the Open Event of the report . . .
Dim strInput As String
Dim strSQL As String
strInput = InputBox("Enter your IDs, comma-delimited.","")
strSQL = "put my query in SQL here" _
"WHERE ((([Data Entry].Record) In (" + strInput +"))) ORDER BY [Data Entry].
Determination;"
Me.RecordSource = strSQL
Am I doing that right? I think I'm doing the last line wrong.
[quoted text clipped - 16 lines]