Searching For a record

[seanog2001]

I have a form which allows the user to search for a name in a dtabase and display the deatils in a different form in a list box.

the code i used to write the search method seems fine but it keeps giving me an error. the code is here as follows

Private Sub Command49_Click()
Dim strSQL2 As String

strSQL2 = " SELECT Candidate.LastName, Candidate.FirstName, Candidate.DateSetup, Positions.Title, PositionsAppliedFor.Acknowledged, " _
& "PositionsAppliedFor.DateAcknowledged, PositionsAppliedFor.RefusalLetterSentDate FROM Positions INNER JOIN (Candidate INNER JOIN PositionsAppliedFor ON Candidate.CandidateNo, " _
& " = PositionsAppliedFor.[Candidate No]) ON Positions.PositionNO = PositionsAppliedFor.Position WHERE (((Candidate.LastName) = [Enter Lastname]))ORDER BY Candidate.FirstName, PositionsAppliedFor.DateAcknowledged DESC;"



ListCandidates.lstCandidates.RowSource = strSQL2

the code in bold is the code with the error

The ListCandidates is the form the list box is in
The lstCandidates is the list box

Any one know where i am going wrong
End Sub

 

[modest]

2 Things:
1) To reference forms you generally use the Forms("Form Name") or Forms![Form Name] syntax, unless you have created a form object in your code and set it equal to a form using the above syntax.
e.g.,

Forms("ListCandidates").lstCandidates.RowSource = strSQL2  -or-
Dim ListCandidates As Form
Set ListCandidates = Forms("Form Name")
ListCandidates.lstCandidates.RowSource = strSQL2

2) Your SQL statement has syntax error. i.e., "WHERE (((Candidate.LastName) = [Enter Lastname]))ORDER BY " you need to have a space between the WHERE clause and ORDER BY statement.

Correction would be:
"WHERE (((Candidate.LastName) = [Enter Lastname])) ORDER BY "

 

[seanog2001]

Thanks very much mate, have a guddin!!!