Unwanted record being added

In the following code, my goal is to duplicate the contact detail records of an existing customer (OldCLientID). All works well except I get the 1st record being added twice.

Can anyone see any errors in the logic of the following code:

Set dbs = CurrentDb
   sqlstr = "Select * from ContactMethods where Contact_ID = " & CStr(OldClientID) & ";"
   Set Q1 = dbs.CreateQueryDef("", sqlstr)
   Set rs = Q1.OpenRecordset
   
   If rs.RecordCount > 0 Then
        rs.MoveLast
        rs.MoveFirst
        For i = 0 To rs.RecordCount
            With rs
                SymbolDescription = !SymbolDescription
                ContactSymbol = !ContactSymbol
                PrimaryMethod = !PrimaryMethod
                Note1 = !Note1
                .AddNew
                !Contact_ID = ClientID
                !SymbolDescription = SymbolDescription
                !ContactSymbol = ContactSymbol
                !PrimaryMethod = PrimaryMethod
                !Note1 = Note1
                .Update
                .MoveNext
            End With
        Next
    End If
garryvAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
alikat538Connect With a Mentor Commented:
Couple things -- and I only looked at this for a couple minutes so take it with a grain of salt....

If rs.RecordCount > 0 Then
        rs.MoveLast
        rs.MoveFirst
---- Why are you moving to the last then first record....I don't see the point

If rs.recordCount > 0 ----- try using if rs.BOF = rs.EOF ---- it also checks for an empty record set...just a thought

Then instead of doing a FOR loop use a while or until loop:

 Do Until rst.EOF
     code code code
rs.MoveNext
Loop


oh, another option is to try starting your for loop at 1 instead of 0.  Just some random troubleshooting --- hope it helps
0
 
wsteegmansConnect With a Mentor Commented:
Something like this?

    Dim rs As Recordset
    Dim dbs As Database
    Dim Q1 As QueryDef

    Set dbs = CurrentDb
    sqlstr = "Select * from ContactMethods where Contact_ID = " & CStr(OldClientID) & ";"
    Set Q1 = dbs.CreateQueryDef("", sqlstr)
    Set rs = Q1.OpenRecordset
   
    Do While Not rs.EOF
        With rs
            SymbolDescription = !SymbolDescription
            ContactSymbol = !ContactSymbol
            PrimaryMethod = !PrimaryMethod
            Note1 = !Note1
            .AddNew
            !Contact_ID = ClientID
            !SymbolDescription = SymbolDescription
            !ContactSymbol = ContactSymbol
            !PrimaryMethod = PrimaryMethod
            !Note1 = Note1
            .Update
            .MoveNext
        End With
    Loop


Some general remarks:
- Try always to declare your variables (by the Dim statement). The best thing you can do is, when you're in your Visual Basic Editor, choose the menu Tools -> Options and check the 'Require Variable Declaration'. By doing so, you must declare each variable. The positive thing is that, when you mispell a variable-name, Access/VB will alert you about it!
If you don't declare your variables, you will always use the Variant-type. Disadvantage is that Access/VBA constantly has to convert the Variant-type to a string, Integer, Recordset, ... Anyway, declaring all your variables is cleaner programming ;-)
- If you use Access 2000 or XP, try to use ADO instead of DAO. ADO is the new way to do database-programming.
More info about migrating from DAO to ADO:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp
0
 
glennkerrConnect With a Mentor Commented:
Use an insert statment - much faster....

   Do While Not rs.EOF
        With rs
            = !SymbolDescription
            ContactSymbol = !ContactSymbol
            PrimaryMethod = !PrimaryMethod
            Note1 = !Note1
            .AddNew
            !Contact_ID = ClientID
            !SymbolDescription = SymbolDescription
            !ContactSymbol = ContactSymbol
            !PrimaryMethod = PrimaryMethod
            !Note1 = Note1
            .Update
            sql "INSERT INTO ContactMethods " & _
               "Contact_ID, SymbolDescription, PrimaryMethod, Note1 Values (" & _
                ClientID & ","" & !ContactSymbol & """, """ & _
                !PrimaryMethod & """,""" & !Note1 & """);"
            dbs.Execute sql
            .MoveNext
        End With
    Loop

It's been a while since I've use DAO, so the use of "" in a string for a quote might be suspect!
0
 
wsteegmansCommented:
I think you have to use single quotes with sql ... something like this ...

            .sql "INSERT INTO ContactMethods " & _
               "Contact_ID, SymbolDescription, PrimaryMethod, Note1 Values (" & _
                ClientID & ",'" & !ContactSymbol & "', '" & _
                !PrimaryMethod & "','" & !Note1 & "');"

But, why is it much faster? I know, it's much faster to use DML (Data Manipulation Language) SQL statements instead of loops. For example deleting records with SQL statement 'DELETE FROM MyTable' is much faster than deleting each record separately with the .Delete statement in a loop. But that's not the case here ...

One little remark using DML:
There is a problem when your data (more specific the text/string-values) holds any single quotes ('). In the French language for example, in every sentence, there is one! To solve this, you must replace every quote with two single quotes. Something like this
Replace(ContactSymbol, "'", "''")

So, I prefer the ADO (or DAO) way. Because, it's more easy to read, and you don't have the single quote problem at all.
0
 
glennkerrCommented:
If I remember right DAO doesn't like the single quotes.

It's faster since you by pass the middle man - the DAO.Recordset

I think you're alright for single quotes in the strings, but it might be a good idea to use the replace function for the double quotes like you suggested.
0
All Courses

From novice to tech pro — start learning today.