Pass text box value to Access Pass Thru Query

[bconner]

I have and Access form and I want to Pass some text box values as the criteria for a Pass Thru Query, can this be done?

I tried writing the SQL statement out and passing that to the Pass Thru Query Definition but that didn't work.....

Dim db As Database
Set db = CurrentDb
Dim strSQL_250_999_Demographics As String 
 
strSQL_250_999_Demographics = "SELECT dbo_Tbl_AP.[MEDICAL RECORD], Sum(dbo_Tbl_AP.[Current Inv Balance]) AS [SumOfCurrent Inv Balance], Sum(dbo_Tbl_AP![Current Inv Balance]*0.25) AS [Prompt Pay Discount]," & _
"Sum(dbo_Tbl_AP![Current Inv Balance]-(dbo_Tbl_AP![Current Inv Balance]*0.25)) AS [Prompt Pay Payment], dbo_Tbl_AP.[PATIENT NAME], dbo_Tbl_AP.[PATIENT DOB], dbo_Tbl_AP.[PATIENT SSN]," & _
"dbo_Tbl_AP.[REG INS NAME], dbo_Tbl_AP.[REG INS ADDRESS], dbo_Tbl_AP.[REG INS ADDRESS 2], dbo_Tbl_AP.[REG INS CITY STATE], dbo_Tbl_AP.[REG INS ZIP], dbo_Tbl_AP.[Date Worked], dbo_Tbl_AP.PTPhone, dbo_Tbl_AP.[Payment Increment], dbo_Tbl_AP.[Payment Amount] " & _
"FROM dbo_Tbl_AP " & _
"GROUP BY dbo_Tbl_AP.[MEDICAL RECORD], dbo_Tbl_AP.[PATIENT NAME], dbo_Tbl_AP.[PATIENT DOB], dbo_Tbl_AP.[PATIENT SSN], dbo_Tbl_AP.[REG INS NAME], dbo_Tbl_AP.[REG INS ADDRESS], dbo_Tbl_AP.[REG INS ADDRESS 2], dbo_Tbl_AP.[REG INS CITY STATE], dbo_Tbl_AP.[REG INS ZIP]," & _
"dbo_Tbl_AP.[Date Worked], dbo_Tbl_AP.PTPhone, dbo_Tbl_AP.[Payment Increment], dbo_Tbl_AP.[Payment Amount], dbo_Tbl_AP.MRN_CPC_ONLY " & _
"HAVING (((dbo_Tbl_AP.[MEDICAL RECORD])= '" & [Forms]![Frm_Main]![Frm_All_Not_Worked_subform]![List_All_Not_Worked] & "' " & " OR (dbo_Tbl_AP.[MEDICAL RECORD])= '" & [Forms]![Frm_Main]![txt_Mrn_Search] & "'" & ") AND" & " ((Sum(dbo_Tbl_AP.[Current Inv Balance])) BETWEEN " & "250.01 AND " & "999.99" & ") AND " & "((dbo_Tbl_AP.MRN_CPC_ONLY) Is Null)) " & _
"ORDER BY Sum(dbo_Tbl_AP.[Current Inv Balance]) DESC;"
 
 
 
 
db.QueryDefs("Generic_Pass_Thru").Connect = "ODBC;DRIVER=SQL Server Native Client 10.0;SERVER=MWNSQLD16;Trusted_Connection=YES;Database=Patient_Collection;"
 
db.QueryDefs("Generic_Pass_Thru").SQL = strSQL_250_999_Demographics

 

[tehNellie]

That should work, but without more detail on the nature of the error it's difficult to suggest what the issue might be.

Though you do need to have SET NOCOUNT ON, I believe, in your pass through query to prevent errors.

 

[jdraw]

You could put a line

Debug.print strSQL_250_999_Demographics
before your querydefs stuff and see exactly what the substituted string looks like.

 

[pbaldy]

I agree that it should work. Here's a visual on the technique jdraw mentioned:

http://www.baldyweb.com/ImmediateWindow.htm