When I try to execute the following VB I get a complie error: Invalid use of key word me
Can any help? Thanks
Private Sub cmdProcess_Click() On Error GoTo err_end Dim conn As ADODB.Connection Set conn = CurrentProject.Connection Dim ssq1 As String Dim ssq2 As String Dim ssq3 As String Dim rs As New ADODB.Recordset Dim rs2 As New ADODB.Recordset Dim F As Boolean ' First Name Dim L As Boolean 'Second Name
'save checkbox values F = False L = False
If Me.chkFirstName = True Then F = True If Me.chkLastName = True Then L = True
'clear out the results table conn.Execute "Delete * From Results"
'open recordset on data table ssql = "Select * From Data1 Order By LastName" rs.Open ssql, conn, adOpenKeyset, _ adLockOptimistic Do Until rs.EOF 'build SQL to open inner recordset ssql2 = "Select * From Data1 Where "
If F = False And L = False Then ssql2 = ssql2 & "FirstName<>'" & _ rs.Fields("FirstName") & _ "' And LastName<>'" & _ rs.Fields("LastName") & "'" End If
If F = False And L = True Then ssql2 = ssql2 & "FirstName<>'" & _ rs.Fields("FirstName") & _ "' And LastName='" & _ rs.Fields("LastName") & "'" End If
If F = True And L = False Then ssql2 = ssql2 & "FirstName='" & _ rs.Fields("FirstName") & _ "' And LastName<>'" & _ rs.Fields("LastName") & "'" End If
If F = True And L = True Then ssql2 = ssql2 & "FirstName='" & _ rs.Fields("FirstName") & _ "' And LastName='" & _ rs.Fields("LastName") & "'" End If
'open inner recordset rs2.Open ssql2, conn, adOpenKeyset, _ adLockOptimistic Do Until rs2.EOF 'test if value from one recordset field 'is in the same field of the other recordset If (InStr(1, rs.Fields("FirstName"), _ rs2.Fields("FirstName") > 0) Or _ (InStr(1, rs2.Fields("FirstName"), _ rs.Fields("FirstName"))) > 0) And _ ((InStr(1, rs.Fields("LastName"), _ rs2.Fields("LastName")) > 0) Or _ (InStr(1, rs2.Fields("LastName"), _ rs.Fields("LastName"))) > 0) Then
'if values match on at least one field, 'write a result record in Results ssql3 = "Insert Into Results Values (" ssql3 = ssql3 & "'" & _ rs.Fields("FirstName") & "', " ssql3 = ssql3 & "'" & _ rs.Fields("LastName") & "', " ssql3 = ssql3 & "'" & _ rs2.Fields("FirstName") & "', " ssql3 = ssql3 & "'" & _ rs2.Fields("LastName") & "')" conn.Execute ssql3 End If
rs2.MoveNext Loop rs2.Close
rs.MoveNext Loop
rs.Close Set rs = Nothing Set rs2 = Nothing conn.Close MsgBox "done" Exit Sub err_end: MsgBox Err.Description End Sub
This conversation is currently closed to new comments.
you don't need me. It should be able to reference the check boxes any way.
PS Insert Into Results Select d1.FirstName,D1.LastName,D2.FirstName, D2.LastName From data1 D1, data1 D2
& one of the fours possibilities.
Both true (instr not required)
Where D1.FirstName = D1.FirstName and D1.LastName = D2.LastName
Both False Where (D1.FirstName <> D1.Lastname) And D1.Lastname <> D2.Lastname) and ((Instr(1,D1.Firstname,D2.Firstname) > 0)) Or (Instr(1,D2.Firstname,D1.FirstName) > 0)) And ((Instr(1,D1.Lastname,D2.Lastname) > 0)) Or (Instr(1,D2.Lastname,D1.LastName) > 0))
First True Last False
Where (D1.FirstName = D2.FirstName) And D1.lastName <> D2.Lastname And
((Instr(1,D1.Lastname,D2.Lastname) > 0)) Or (Instr(1,D2.Lastname,D1.LastName) > 0))
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Find Similiar MS Access Records
Invalid use of key word me
Can any help?
Thanks
Private Sub cmdProcess_Click()
On Error GoTo err_end
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim ssq1 As String
Dim ssq2 As String
Dim ssq3 As String
Dim rs As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
Dim F As Boolean ' First Name
Dim L As Boolean 'Second Name
'save checkbox values
F = False
L = False
If Me.chkFirstName = True Then F = True
If Me.chkLastName = True Then L = True
'clear out the results table
conn.Execute "Delete * From Results"
'open recordset on data table
ssql = "Select * From Data1 Order By LastName"
rs.Open ssql, conn, adOpenKeyset, _
adLockOptimistic
Do Until rs.EOF
'build SQL to open inner recordset
ssql2 = "Select * From Data1 Where "
If F = False And L = False Then
ssql2 = ssql2 & "FirstName<>'" & _
rs.Fields("FirstName") & _
"' And LastName<>'" & _
rs.Fields("LastName") & "'"
End If
If F = False And L = True Then
ssql2 = ssql2 & "FirstName<>'" & _
rs.Fields("FirstName") & _
"' And LastName='" & _
rs.Fields("LastName") & "'"
End If
If F = True And L = False Then
ssql2 = ssql2 & "FirstName='" & _
rs.Fields("FirstName") & _
"' And LastName<>'" & _
rs.Fields("LastName") & "'"
End If
If F = True And L = True Then
ssql2 = ssql2 & "FirstName='" & _
rs.Fields("FirstName") & _
"' And LastName='" & _
rs.Fields("LastName") & "'"
End If
'open inner recordset
rs2.Open ssql2, conn, adOpenKeyset, _
adLockOptimistic
Do Until rs2.EOF
'test if value from one recordset field
'is in the same field of the other recordset
If (InStr(1, rs.Fields("FirstName"), _
rs2.Fields("FirstName") > 0) Or _
(InStr(1, rs2.Fields("FirstName"), _
rs.Fields("FirstName"))) > 0) And _
((InStr(1, rs.Fields("LastName"), _
rs2.Fields("LastName")) > 0) Or _
(InStr(1, rs2.Fields("LastName"), _
rs.Fields("LastName"))) > 0) Then
'if values match on at least one field,
'write a result record in Results
ssql3 = "Insert Into Results Values ("
ssql3 = ssql3 & "'" & _
rs.Fields("FirstName") & "', "
ssql3 = ssql3 & "'" & _
rs.Fields("LastName") & "', "
ssql3 = ssql3 & "'" & _
rs2.Fields("FirstName") & "', "
ssql3 = ssql3 & "'" & _
rs2.Fields("LastName") & "')"
conn.Execute ssql3
End If
rs2.MoveNext
Loop
rs2.Close
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set rs2 = Nothing
conn.Close
MsgBox "done"
Exit Sub
err_end:
MsgBox Err.Description
End Sub