Access data with apostrophe and pass through query
Posted on 2013-05-15
I have a form that collects parameters from users to perform a search (frmSearchEstToEdit)
After filling in their criteria, it brings up another form with the results (frmSearchEstToEditResults). This form also passes the data through to a pass-through query to execute a stored procedure to query the tables in the onOpen event.
If the user inputs any data with an apostrophe, then it obviously wont work in the pass through query.
I adjusted my stored procedure so that when it runs, it does not take into account any apostrophes. Then, on the Access end I tried to remedy this by stripping the apostrophe before sending it to the pass through query. I did this by creating a hidden text box on the input form that uses the replace function and simply takes out the apostrophes. Doing this solves the problem on that field, but then creates a "data type mismatch" error if the user enters any data in any of the other fields on the input form. So, I tried solving it by using the replace function in the code. Again, it works beautifully on the field I was trying to fix, but then it creates a "invalid null" error if the user tries to put any data in any of the other fields on the form. Both these errors appear to be occurring on the Access form and not even making it to the pass-through query, so I think it is an issue in my code.
Any ideas on how I can strip the apostrophe so that this will work without getting errors elsewhere?
This is the string I am passing through to the pass-through query from frmSearchEstToEditResults...
Private Sub Form_Open(Cancel As Integer)
Dim dbs As Database
Dim qdf As QueryDef
Dim frm As Form
Set dbs = CurrentDb
Set frm = Forms!frmSearchEstToEdit
Set qdf = dbs.QueryDefs("qrySQLSearchEstToEdit")
Dim rstForm As DAO.Recordset
qdf.SQL = "EXEC spSearchEstToEdit " & "'" & Forms!frmSearchEstToEdit!txtFacilityNumber & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityName & "'" & "," & "'" & Forms!frmSearchEstToEdit!OwnerName & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityAddress & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityCity & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityZip & "'" & "," & "'" & Forms!frmSearchEstToEdit!txtFacilityPIN & "'"
qdf.ReturnsRecords = True
Set rstForm = qdf.OpenRecordset()
If rstForm.EOF Then
MsgBox "There are no records to display. Please change your search criteria."
Set qdf = Nothing
Set frm = Nothing