Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2014
    Posts
    11

    Question Unanswered: [Q]INSERT value based on another value statement

    Hi, I'm newbie need help here. Any hint for insert statement and how how to check for the 2nd time run.. (This is not a school homework question but for work
    Much appreciate

    I have table name ADDRESS with 3 columns. (TypeKey is value auto genarate by Access). I would like to have a Sub VBA that when i run it will check the name column if it sees the same name then copy("INSERT") the address record but make a new name. However if I run the second time it should check for duplicate and don't "INSERT" if both Person_nm and address_nm exist

    From ADDRESS
    Type_key Person_nm address_nm
    dfgladif Peter xyz
    didjkliod Peter pyv
    cafdosdl Susan abc

    Update to: ADDRESS

    Type_key Person_nm address_nm
    dfgladif Peter xyz
    didjkliod Peter pyv
    cafdosdl Susan abc
    hdfdaioo Smith xyz
    dhkdlllll Smith pyv

    Code:
        Dim rs As New ADODB.Recordset
        Dim db As ADODB.Connection
        Set db = CurrentProject.Connection
        rs.ActiveConnection = db
       
         rs.Open "Select * from [ADDRESS]", , adOpenStatic, adLockOptimistic
        
        Do While Not rs.EOF
            If rs("Person_nm") = "Peter" Then
                
                ' insert new recordset
                Dim updaters As New ADODB.Recordset
                Dim updatedb As ADODB.Connection
                
                Set updatedb = CurrentProject.Connection
                updaters.ActiveConnection = db
                
                DoCmd.RunSQL "Insert into ADDRESS (Person_nm, address_nm) Values (Smith,??????)"........ hint please
    
            End If
        rs.MoveNext
        Loop  
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    Thank you
    Last edited by sweetboy02125; 04-15-14 at 14:37.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by sweetboy02125 View Post
    I would like to have a Sub VBA that when i run it will check the name column if it sees the same name then copy("INSERT") the address record but make a new name. However if I run the second time it should check for duplicate and don't "INSERT" if both Person_nm and address_nm exist
    1. What do you mean with if it sees the same name? You can use the domain function DCount() to know the number of occurences of a specific value in one or several columns:
    Code:
    Dim lngOccurence As Long
    
    Do While Not rs.EOF
        lngOccurence = DCount("*", "Address", "Person_nm='" & rs!Person_nm & "'")
        If lngOccurence < 2 Then
            ....
        End If
    Loop
    2. "... but make a new name..." Where should this new name come from? Will it always be "Smith"?

    3. There is something I don't understand in your sample data:
    Code:
    Type_key	Person_nm	address_nm
    ------------------------------------------
    dfgladif	Peter 		xyz
    didjkliod 	Peter 		pyv
    cafdosdl 	Susan 		abc
    hdfdaioo 	Smith 		xyz
    dhkdlllll 	Smith 		pyv
    Where does the last line come from? It does not seem to make sense.

    4. As I never use the ADODB library when I'm not obliged to do so (and this is very rare), here's an example using DAO. It works with the reserves stated here above:
    Code:
        Const c_SQL As String = "INSERT INTO Address (Person_nm, address_nm) VALUES ( '@P', '@A' );"
        
        Dim rst As dao.Recordset
        Dim strSQL As String
        
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Address", dbOpenDynaset)
        
        With rst
            Do Until .EOF
                If DCount("*", "Address", "Person_nm='" & !Person_nm & "'") < 2 Then
                    strSQL = Replace(Replace(c_SQL, "@P", "Smith"), "@A", !address_nm)
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
    Have a nice day!

  3. #3
    Join Date
    Mar 2014
    Posts
    11
    Thanks for answering. Reply in Blue

    Quote Originally Posted by Sinndho View Post
    1. What do you mean with if it sees the same name? You can use the domain function DCount() to know the number of occurences of a specific value in one or several columns:

    The point is I would like to check Peter only (there will be lots of Peters) with different addresses.

    Code:
    Dim lngOccurence As Long
    
    Do While Not rs.EOF
        lngOccurence = DCount("*", "Address", "Person_nm='" & rs!Person_nm & "'")
        If lngOccurence < 2 Then
            ....
        End If
    Loop

    2. "... but make a new name..." Where should this new name come from? Will it always be "Smith"?

    Yes the new name always Smith (if see 1 Peter and insert 1 Smith and go on) then take Peter's address insert to the Smith's
    3. There is something I don't understand in your sample data:
    Code:
    Type_key	Person_nm	address_nm
    ------------------------------------------
    dfgladif	Peter 		xyz
    didjkliod 	Peter 		pyv
    cafdosdl 	Susan 		abc
    hdfdaioo 	Smith 		xyz
    dhkdlllll 	Smith 		pyv
    Where does the last line come from? It does not seem to make sense.

    Answer from #2

    4. As I never use the ADODB library when I'm not obliged to do so (and this is very rare), here's an example using DAO. It works with the reserves stated here above:

    Thanks I'll try yours below. Never use DAO or known the difference
    Code:
        Const c_SQL As String = "INSERT INTO Address (Person_nm, address_nm) VALUES ( '@P', '@A' );"
        
        Dim rst As dao.Recordset
        Dim strSQL As String
        
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Address", dbOpenDynaset)
        
        With rst
            Do Until .EOF
                If DCount("*", "Address", "Person_nm='" & !Person_nm & "'") < 2 Then
                    strSQL = Replace(Replace(c_SQL, "@P", "Smith"), "@A", !address_nm)
                    CurrentDb.Execute strSQL, dbFailOnError
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing

  4. #4
    Join Date
    Mar 2014
    Posts
    11

    Found the solution, But don't understand...

    I found it but don't understand what the

    ' " + stradd+ " ' meant? Can some one to explain please

    Dim strregime As String
    stradd = rs("address_nm").Value
    "Insert into ADDRESS ( address_nm, Person_nm,) Values ( '" + stradd + "', 'Peter')"

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What you need to get is (let's suppose that stradd = xxxx):
    Code:
    strSQL = "INSERT INTO ADDRESS ( ( address_nm, Person_nm,) Values ( 'xxxx', 'Peter');"
    To get this, you must concatenate 3 strings (by the way, the proper operator for this is & not +):
    -String 1: "INSERT INTO ADDRESS ( ( address_nm, Person_nm,) Values ( '"
    -String2: stradd
    -String 3: "', 'Peter')"
    So:
    String 1 & String 2 & String 3.

    Note: You can make the code easier to read by using:
    Code:
    strSQL = Replace("INSERT INTO ADDRESS ( address_nm, Person_nm,) VALUES ( '@', 'Peter');", "@", stradd)
    Have a nice day!

  6. #6
    Join Date
    Mar 2014
    Posts
    11

    Thank you Mod

    Quote Originally Posted by Sinndho View Post
    What you need to get is (let's suppose that stradd = xxxx):
    Code:
    strSQL = "INSERT INTO ADDRESS ( ( address_nm, Person_nm,) Values ( 'xxxx', 'Peter');"
    To get this, you must concatenate 3 strings (by the way, the proper operator for this is & not +):
    -String 1: "INSERT INTO ADDRESS ( ( address_nm, Person_nm,) Values ( '"
    -String2: stradd
    -String 3: "', 'Peter')"
    So:
    String 1 & String 2 & String 3.

    Note: You can make the code easier to read by using:
    Code:
    strSQL = Replace("INSERT INTO ADDRESS ( address_nm, Person_nm,) VALUES ( '@', 'Peter');", "@", stradd)
    Thank you. I'm not sure but the statement with the + and the single/double quote did the work. I still don't understand. Can you help to explain?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The concatenation operator (&) was introduced in the basic language sometime earlier when VB began to use variant data type (in VB3 if my memory serves me right). Until then the addition operator (+) was overloaded to act as a concatenation operator when dealing with string data. The problem it was trying to solve was this:
    Suppose you have:
    Code:
    Dim var1 As Variant
    Dim var2 As Variant
    Dim var3 As Variant
    '
    ' Some code...
    '
    var3 = var1 + var2
    What would contain var3? Imagine that var1 contains 4 and var2 contains 5, you can expect that var3 = 9. Earlier in basic that was not certain. During the process something could do so that var1 or var2 would be trated as strings, so var3 would equal "45". The only solution would have consisted in explicitly casting the variables:
    Code:
    var3 = CStr(var1) + CStr(var2)
    The & operator was a way to simplify such situations:
    Code:
    var3 = var1 + var2 ' --> var3 = 9
    var3 = var1 & var2 ' --> var3 = "45"
    See also: Concatenation Operators in Visual Basic

    At the same time, VB3, that included the Jet Engine 1.1, was given the capability of interacting with databases through the DAO library. In SQL, the usual mark of a string is the single quote ('), while double quotes (") are used in basic. To a certain extend, the VB/VBA language can handle both, as far as an SQL expression is used. This can be confusing and rather difficult to decipher, so a commonly used convention (although not mandatory) is to use the single quote when dealing with a SQL expression and to use the double quotes when dealing with a "purely basic" string expression.
    see also: Visual Basic .NET Codemaster's Library - Matt Tagliaferri - Google Livres, Visual Basic .NET: A Laboratory Course - Nell B. Dale, Michael McMillan - Google Livres, Start-to-Finish Visual Basic 2005: Learn Visual Basic 2005 as You Design and ... - Tim Patrick - Google Livres
    Have a nice day!

  8. #8
    Join Date
    Mar 2014
    Posts
    11

    Thanks Mod

    Thanks Mod, I understood the & and the + sign in VB or VBA meant. However the whole statement, I can't figure it out what is that meant (note with the sing and double quote and the + sign in front and after...)

    '" + stradd + "' represnet for the "address_nm" column

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what datatype is address_nm
    if its numeric then using the + symbol wioll try to add the value of address_nm
    also if its numeric you don't need to delimit the value of address_nm in a SQL statement

    myWhereClause = "WHERE anumericcolumn = " & anumericvariable
    myWhereClause = "WHERE atextcolumn = '" & astringvaraibale & "'"
    myWhereClause = "WHERE adatecolumn = #" format(adatevariable,"mm/dd/yyyy") & "#"
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Mar 2014
    Posts
    11
    Quote Originally Posted by healdem View Post
    what datatype is address_nm
    if its numeric then using the + symbol wioll try to add the value of address_nm
    also if its numeric you don't need to delimit the value of address_nm in a SQL statement

    myWhereClause = "WHERE anumericcolumn = " & anumericvariable
    myWhereClause = "WHERE atextcolumn = '" & astringvaraibale & "'"
    myWhereClause = "WHERE adatecolumn = #" format(adatevariable,"mm/dd/yyyy") & "#"
    address_nm just a string text value (including numbers and characters).

    I just Don't understand the: ' " + stradd + " ' (what actually happening) in that statement
    PS: This solution provided by a Senior Developer in the company help.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if address_nm is alphanumeric/text then the VBA and/or JHET runtime will probably successfully interpret this as a string concatenation. you'd use + in JAVA, PHP and probably C and its various realtives.

    VBA is often too forgiving in my books, the designers of the language have built in lots of safe fall backs which means it can be a bit confusing at times.

    if you have doubts as to what the runtime actually DOES, then put a breakpoint on the code and then step through code or type in values in the immediate window
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I don't see any other way to explain this. Let's try again:
    Code:
    Dim str As String
    str = "'single quoted string'"
    Debug.Print "This is a " & str & "used as an example."
    The code yields:
    This is a 'single quoted string' used as an example.
    Have a nice day!

  13. #13
    Join Date
    Mar 2014
    Posts
    11

    Thanks Mod

    Quote Originally Posted by Sinndho View Post
    I don't see any other way to explain this. Let's try again:
    Code:
    Dim str As String
    str = "'single quoted string'"
    Debug.Print "This is a " & str & "used as an example."
    The code yields:
    Thank you. What about the + signs (in front and after)???

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is no plus sign (+) in my last example. You can chose to use either the plus sign (+) or the ampersand sign (&) as the concatenation operator. For reasons that were formerly explained, it's better to use the ampersand (&).

    Note: Although nothing prevents you from mixing both operators, please do not so: it makes the code quite unreadable.
    Have a nice day!

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK lets give it one more try
    the + symbol is used to concatenate strings in Java, C and relatives, although it can be used with varying degrees of success inside VBA IT SHOULDN'T. Why:-
    primarily the string concatenation operator in VBA is the ampersand character &. If you are dumb enough to use the wrong symbol then VBA will try to work out what you meant, and may be able to resolve that you want to tack one string after another. but you are lettign the runtime system interpret what it thinks you meant as opposed to explicitly informing the runtime what you wanted

    eg
    myvar = 'a bit of string,' & ' another string'
    ..so myvar contains 'a bit of string, another string'
    the runtime may be able to make
    myvar = 'a bit of string ' + ' another string'
    reach the same conclusions, but it depends
    ..on the datatypes of the variables (assuming you do declare your varaibles and not leave that to the runtime)

    myvar = '1234' + ' 4321'
    could result in
    1234 4321
    OR
    5555, the result of adding 1234 and 4321
    ..depending on context, datatypes the state of the runtime interpreter and who knows perhaps the phase of the moon
    computers are far better if you give them explicit instructions, than letting them get all uppity and make decisions for themselves.

    Now as to why a senior colleague of yours is suggesting you use the + symbol to concatenate strings... I dunno. why don't you ask him/her/it.

    but until they do tell you why, use & to stitch string values together not the + sign
    Last edited by healdem; 04-30-14 at 15:32.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •