?
Solved

Unwanted record being added

Posted on 2003-11-13
8
Medium Priority
?
383 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
8 Comments
 
LVL 1

Accepted Solution

by:
alikat538 earned 136 total points
ID: 9743431
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 132 total points
ID: 9744113
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 132 total points
ID: 9744242
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
ID: 9744379
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
ID: 9744408
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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

801 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