Syntax Error

[Guest]

I keep getting a syntax error and i don't know what i am doing wrong.
I have two list boxes. box 1 is lstAccreditation, box 2 is lstAccreditationFR

I use the following code to call each box depending on the language
required. The english one works great, but the french one keeps throughing a
syntax error. I have narrowed it down to the On Activate trigger...but
what!!!

Both boxes have AccreditationID as the bound colum (or it should be).
lstAccrediation has a second column of Accreditation, while
lstAccreditationFR has a second column of AccreditationFrench

Can anyone help me? Here is the code i am using:

Dim db As DAO.Database, rs As DAO.Recordset, rs2 As DAO.Recordset
Dim strSQL As String, Criteria As String
Dim i As Integer


If Forms!frmAgreementAndAccreditation!Check36 = -1 Then

Me.lstAccreditation.Visible = False


strSQL = "SELECT tblAccreditation.AccreditationID, " & _
"tblAccreditation.AccreditationFrench, " & _
"tblAgreementDetails.AgreementID " & _
"FROM tblAccreditation INNER JOIN tblAgreementDetails " & _
"ON tblAccreditation.AccreditationID =
tblAgreementDetails.AccreditationID " & _
"WHERE tblAgreementDetails.AgreementID= " &
[Forms]![frmAccreditationSelect]!txtAgreementID

'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For i = 0 To lstAccreditationFR.ListCount - 1
Criteria = "[AccreditationFrench] = '" & Me.lstAccreditationFR.Column(1,
i) & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
lstAccreditationFR.Selected(i) = True
Else
lstAccreditationFR.Selected(i) = False
End If
End If
Next i


Else



strSQL = "SELECT tblAccreditation.AccreditationID, " & _
"tblAccreditation.Accreditation, " & _
"tblAgreementDetails.AgreementID " & _
"FROM tblAccreditation INNER JOIN tblAgreementDetails " & _
"ON tblAccreditation.AccreditationID =
tblAgreementDetails.AccreditationID " & _
"WHERE tblAgreementDetails.AgreementID= " &
[Forms]![frmAccreditationSelect]!txtAgreementID

'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For i = 0 To lstAccreditation.ListCount - 1
Criteria = "[Accreditation] = '" & lstAccreditation.Column(1, i) & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
lstAccreditation.Selected(i) = True
Else
lstAccreditation.Selected(i) = False
End If
End If
Next i

End If

 

[Michael J. Strickland]

I keep getting a syntax error and i don't know what i am doing wrong.
I have two list boxes. box 1 is lstAccreditation, box 2 is
lstAccreditationFR

I use the following code to call each box depending on the language
required. The english one works great, but the french one keeps
throughing a
syntax error. I have narrowed it down to the On Activate trigger...but
what!!!

Both boxes have AccreditationID as the bound colum (or it should be).
lstAccrediation has a second column of Accreditation, while
lstAccreditationFR has a second column of AccreditationFrench

Can anyone help me? Here is the code i am using:

Dim db As DAO.Database, rs As DAO.Recordset, rs2 As DAO.Recordset
Dim strSQL As String, Criteria As String
Dim i As Integer


If Forms!frmAgreementAndAccreditation!Check36 = -1 Then

Me.lstAccreditation.Visible = False


strSQL = "SELECT tblAccreditation.AccreditationID, " & _
"tblAccreditation.AccreditationFrench, " & _
"tblAgreementDetails.AgreementID " & _
"FROM tblAccreditation INNER JOIN tblAgreementDetails " & _
"ON tblAccreditation.AccreditationID =
tblAgreementDetails.AccreditationID " & _
"WHERE tblAgreementDetails.AgreementID= " &
[Forms]![frmAccreditationSelect]!txtAgreementID

I think the above line should be in quotes.

'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For i = 0 To lstAccreditationFR.ListCount - 1
Criteria = "[AccreditationFrench] = '" &
Me.lstAccreditationFR.Column(1,
i) & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
lstAccreditationFR.Selected(i) = True
Else
lstAccreditationFR.Selected(i) = False
End If
End If
Next i


Else



strSQL = "SELECT tblAccreditation.AccreditationID, " & _
"tblAccreditation.Accreditation, " & _
"tblAgreementDetails.AgreementID " & _
"FROM tblAccreditation INNER JOIN tblAgreementDetails " & _
"ON tblAccreditation.AccreditationID =
tblAgreementDetails.AccreditationID " & _
"WHERE tblAgreementDetails.AgreementID= " &
[Forms]![frmAccreditationSelect]!txtAgreementID

I think the above line should be in quotes.

'MsgBox strSQL
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)

For i = 0 To lstAccreditation.ListCount - 1
Criteria = "[Accreditation] = '" & lstAccreditation.Column(1, i) & "'"
'MsgBox Criteria
If Not rs.BOF Then
rs.FindFirst Criteria
If rs.NoMatch = False Then
lstAccreditation.Selected(i) = True
Else
lstAccreditation.Selected(i) = False
End If
End If
Next i

End If

--