Problem using VBA to insert records into a table (1 Viewer)

[reglarh]

I am getting frustrated at not being able to sort what looks like a simple problem. I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?????

The code is:

Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim dbs As Database
Dim LastMemberNumber As Integer
Dim LastFullName As String
Dim SQLStr1 As String
Dim SQLStr2 As String
Dim SQLStr3 As String
Set dbs = CurrentDb
DoCmd.RunSQL "Delete * from DuplicateNames"

SQLStr3 = "select MemberNumber, FullName from Members Order By FullName"
Set rs = dbs.OpenRecordset(SQLStr3)
LastMemberNumber = 0
LastFullName = " "
rs.MoveFirst
Do While Not rs.EOF
If rs!FullName = LastFullName Then
MsgBox "Duplicate" & vbCrLf & "Member name: " & LastFullName
MsgBox "Duplicate" & vbCrLf & "Member number: " & LastMemberNumber
SQLStr1 = "INSERT INTO DuplicateNames ([MemberNumber], [FullName]) VALUES (LastMemberNumber, LastFullName)"
DoCmd.RunSQL SQLStr1
SQLStr2 = "INSERT INTO DuplicateNames ([MemberNumber], [FullName]) VALUES (rs!MemberNumber, rs!FullName)"
DoCmd.RunSQL SQLStr2
End If
LastFullName = rs!FullName
LastMemberNumber = rs!MemberNumber
rs.MoveNext
Loop
MsgBox "End"

Set rs = Nothing
dbs.Close
End Sub

 

[pbaldy]

You have to concatenate the values into the string. Presuming the first is a number and the second is text:

SQLStr2 = "INSERT INTO DuplicateNames ([MemberNumber], [FullName]) VALUES (" & rs!MemberNumber & ", '" & rs!FullName & "')"

 

[reglarh]

Goodness, that was quick! Where would I have found that out?

The code now runs, and seems to write 6 records (which is correct). When I look at the Duplicate table it has 3 records, with all fields, including the autonumber, set to #Deleted.

When I rerun the code again, it warns me that I am going to delete 6 records.

Any ideas?

 

[pbaldy]

Did you fix the first SQL too? Can you post the db here?

 

[reglarh]

The answer to the first question is no, I didn't change the other SQL string and it has now worked for the last three runs. I don't quite know what happened but it works and I cannot reproduce the fault mentioned earlier, so sleeping dog etc.!

I don't know whether you can help on another general question. Having developed a system with 30 queries, 21 Forms 20 Reports and a number of other bits and pieces including some 20 bits of Visual Basic, I have a lot of redundant bits and pieces that I cannot be certain are not used deep in the bowels of the system. Is there any free software that can analyse the usage of queries so that I can eliminate unused features?

Many thanks for the help.

 

[pbaldy]

I would expect the first SQL to have to changed to concatenate the values as well. Otherwise you'd get input prompts.

 

[reglarh]

Yes, I am sorry, you are quite correct. I must have changed that one in my sleep!

 

[pbaldy]

Ah, sleep-coding, I do that too.