Solved

Unwanted record being added

Posted on 2003-11-13
8
343 Views
Last Modified: 2006-11-17
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
0
Comment
Question by:garryv
  • 2
  • 2
8 Comments
 
LVL 1

Accepted Solution

by:
alikat538 earned 34 total points
Comment Utility
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
 
LVL 7

Assisted Solution

by:wsteegmans
wsteegmans earned 33 total points
Comment Utility
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
 
LVL 2

Assisted Solution

by:glennkerr
glennkerr earned 33 total points
Comment Utility
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
 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
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
 
LVL 2

Expert Comment

by:glennkerr
Comment Utility
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now