Is it possible to change the record source of an data control based on user input? I am trying to use the search criteria input by the user to create an SQL string that will serve as the RecordSource for an data control, and I have had absolutely no success. I am obviously new at this, and am completely lost. The reference books I've been using, while generally helpful, have been almost useless as far as this sort of thing goes.
Alternatively, if there is an easier way to filter a database based on a query and display the appropriate records, I would be just as happy to learn that. SQL seems very straightforward, but I am having a tough time integrating it into my VB work. I m sending my code but i dont know why it is not working.it get stuck up at ----marked datselectcandidate.refresh in first part but works fine in the other part. record_source is a string i.e my SQL string

If record_source <> "" Then 'SQL for search
datSelectCandidate.RecordSource = record_source & "Order By Candidate." & _
dbgrdCandidate.Columns(ColIndex).DataField
------------------------------------------
datSelectCandidate.Refresh 'data control
-------------------------------------------
dbgrdCandidate.Refresh 'grid refreshed
Else
'Selects the fields to be displayed and 'sorts the records by field being clicked
datSelectCandidate.RecordSource = _
"select ID,FirstName,LastName,FName,City,Birthdate,AppliedOn," & _
"Gender,Status,Remarks From Candidate Order By Candidate." & _
dbgrdCandidate.Columns(ColIndex).DataField
datSelectCandidate.Refresh 'data control refreshed
dbgrdCandidate.Refresh 'grid refreshed
End If

record_source="SELECT DISTINCTROW Candidate.ID, Candidate.FirstName,Candidate.LastName, Candidate.FName," & _
"Candidate.City, Candidate.Birthdate,Candidate.AppliedOn, Candidate.Gender," & _
"Candidate.Status , Candidate.Remarks FROM Candidate WHERE(Candidate.Experience > "cboExperience.text)

my SQL is same as above but it gives error no. 3464. Data type mismatch in criteria experission. i have also checked the names of field they are correct(same as that of table.I want to know why it is giving the problem while refreshing when SQL is supplied as recordsource.