Adding another field to the code

[sandingdude]

How would I add another field into this code? I just want to display the street info in the msg box.

Here is the code:

Function import()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
'SQL string for names that already exist in MAINDB
strsql = "SELECT IMPORTDB.FNAME AS FNAME1, IMPORTDB.LNAME AS LNAME1, " & _
"IMPORTDB.PHONE AS PHONE1, MAINDB.FNAME AS FNAME2, MAINDB.LNAME AS LNAME2, " & _
"MAINDB.PHONE AS PHONE2 " & _
"FROM IMPORTDB INNER JOIN MAINDB ON (IMPORTDB.LNAME = MAINDB.LNAME) WHERE " & _
"(IMPORTDB.FNAME = MAINDB.FNAME) OR (IMPORTDB.FNAME = Left(MAINDB.FNAME,1)) OR (Left(IMPORTDB.FNAME,1) = MAINDB.FNAME);"

Set db = Application.CurrentDb
Set rs = db.OpenRecordset(strsql)
rs.MoveLast
rs.MoveFirst

Do Until rs.EOF

If MsgBox("Name already exists:" & vbCrLf & vbCrLf & _
"IMPORTDB" & vbCrLf & _
rs.Fields("FNAME1").Value & " " & rs.Fields("LNAME1").Value & " " & _
rs.Fields("PHONE1").Value & vbCrLf & vbCrLf & _
"MAINDB" & vbCrLf & _
rs.Fields("FNAME2").Value & " " & rs.Fields("LNAME2").Value & " " & _
rs.Fields("PHONE2").Value & vbCrLf & vbCrLf & _
"Do you want to delete the record in IMPORTDB?", vbYesNo + vbQuestion, _
"Duplicate found") = vbYes Then

'Delete the duplicate record:
DoCmd.RunSQL "DELETE * FROM IMPORTDB WHERE IMPORTDB.FNAME = '" & _
rs.Fields("FNAME1").Value & "' AND IMPORTDB.LNAME = '" & _
rs.Fields("LNAME1").Value & "' AND IMPORTDB.PHONE = '" & _
rs.Fields("PHONE1").Value & "';"

rs.MoveNext

Else:
rs.MoveNext

End If

Loop


End Function



Thanks

 

[TheAceMan1]

How are ya sandingdude . . . . .

Not entirely sure I read you right, but could this be what you mean?

[BLUE]   SQL = "SELECT IMPORTDB.FNAME AS FNAME1, IMPORTDB.LNAME AS LNAME1, " & _
                "[purple][b]IMPORTDB.STREET AS STREET1[/b][/purple], IMPORTDB.PHONE AS PHONE1, " & _
                "MAINDB.FNAME AS FNAME2, MAINDB.LNAME AS LNAME2, " & _
                "[purple][b]MAINDB.STREET AS STREET2[/b][/purple], PHONE AS PHONE2 " & _
         "FROM IMPORTDB " & _
         "INNER JOIN MAINDB ON (IMPORTDB.LNAME = MAINDB.LNAME) " & _
         "WHERE (IMPORTDB.FNAME = MAINDB.FNAME) OR " & _
               "(IMPORTDB.FNAME = Left(MAINDB.FNAME,1)) OR " & _
               "(Left(IMPORTDB.FNAME,1) = MAINDB.FNAME);"[/blue]

See Ya! . . . . . .

 

[JerryKlmns]

Include the street field in your select statement!

strsql = "SELECT IMPORTDB.FNAME AS FNAME1, IMPORTDB.LNAME AS LNAME1, " & _
"IMPORTDB.PHONE AS PHONE1, IMPORTDB.STREETNAME AS STREET1, MAINDB.FNAME AS FNAME2, MAINDB.LNAME AS LNAME2, " & _
"MAINDB.PHONE AS PHONE2, MAINDB.STREETNAME AS STREET2 " & _
"FROM IMPORTDB INNER JOIN MAINDB ON (IMPORTDB.LNAME = MAINDB.LNAME) WHERE " & _
"(IMPORTDB.FNAME = MAINDB.FNAME) OR (IMPORTDB.FNAME = Left(MAINDB.FNAME,1)) OR (Left(IMPORTDB.FNAME,1) = MAINDB.FNAME);"

And the MsgBox looks like

MsgBox("Name already exists:" & vbCrLf & vbCrLf & _
"IMPORTDB" & vbCrLf & _
rs.Fields("FNAME1").Value & " " & rs.Fields("LNAME1").Value & " " & _
rs.Fields("PHONE1").Value & " " & rs.Fields("STREET1").Value & vbCrLf & vbCrLf & _
"MAINDB" & vbCrLf & _
rs.Fields("FNAME2").Value & " " & rs.Fields("LNAME2").Value & " " & _
rs.Fields("PHONE2").Value & " " & rs.Fields("STREET2").Value & vbCrLf & vbCrLf & _
"Do you want to delete the record in IMPORTDB?", vbYesNo + vbQuestion, _
"Duplicate found")

 

[TheAceMan1]

TheAceMan1 . . . . .

With a quick cleanup:

[blue]Function import()
   Dim db As DAO.Database, rs As DAO.Recordset, SQL As String
   Dim Msg As String, Style As Integer, Title As String
   SL As String, DL As String
   
   Set db = Application.CurrentDb
   SL = vbNewLine
   DL = SL & SL
   [green]'SQL string for names that already exist in MAINDB[/green]
   SQL = "SELECT IMPORTDB.FNAME AS FNAME1, IMPORTDB.LNAME AS LNAME1, " & _
                "[purple][b]IMPORTDB.STREET AS STREET1[/b][/purple], IMPORTDB.PHONE AS PHONE1, " & _
                "MAINDB.FNAME AS FNAME2, MAINDB.LNAME AS LNAME2, " & _
                "MAINDB.[purple][b]STREET AS STREET2[/b][/purple], PHONE AS PHONE2 " & _
         "FROM IMPORTDB " & _
         "INNER JOIN MAINDB ON (IMPORTDB.LNAME = MAINDB.LNAME) " & _
         "WHERE (IMPORTDB.FNAME = MAINDB.FNAME) OR " & _
               "(IMPORTDB.FNAME = Left(MAINDB.FNAME,1)) OR " & _
               "(Left(IMPORTDB.FNAME,1) = MAINDB.FNAME);"
   Set rs = db.OpenRecordset(SQL)
   
   Do Until rs.EOF
      Msg = "Name already exists:" & DL & _
            "IMPORTDB" & SL & _
             rs!FNAME1 & " " & rs!LNAME1 & SL & _
            "rs![purple][b]STREET1[/b][/purple]" & SL & _
             rs!PHONE1 & DL & _
            "MAINDB" & SL & _
             rs!FNAME2 & " " & rs!LNAME2 & SL & _
            "rs![purple][b]STREET2[/b][/purple]" & SL & _
             rs!PHONE2 & DL & _
            "Do you want to delete the record in IMPORTDB?"
      Style = vbQuestion + vbYesNo
      Title = "Duplicate Data Detected!"
   
      If MsgBox(Msg, Style, Title) = vbYes Then
         SQL = "DELETE * " & _
               "FROM IMPORTDB " & _
               "WHERE IMPORTDB.FNAME = '" & rs!FNAME1 & "' AND " & _
                     "IMPORTDB.LNAME = '" & rs!LNAME1 & "' AND " & _
                     "IMPORTDB.PHONE = '" & rs!PHONE1 & "';"
         [green]'Delete the duplicate record:[/green]
         DoCmd.RunSQL SQL
      End If
      rs.MoveNext
   Loop

End Function[/blue]

See Ya! . . . . . .