• 2 controll in recodset

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » 2 controll in recodset

    Author
    Topic
    WSsal21
    AskWoody Lounger
    October 8, 2009 at 2:19 pm #463048

    I jus have tipical ADO conn opened with rs1 and a table named FIDI

    an have my_var1=123456789, var_prova07=1, var_prova08=5, var_prova09=7

    I want with a query this controll:

    first condition:
    based my_var1 find in prova02 the value of my_var1 (i have indexed this filed) and compare
    var_prova07=1, var_prova08=5, var_prova09=5 with fileds prova07,prova08,prova09

    No one or more avalue of prova07,prova08,prova09=5 are changed no action.

    Instead if one or more value of var_prova07,var_prova08, var_prova09 are change update prova07,prova08,prova09 with new value.

    second condition:
    my_var1=87988787 not exitsis in prova02 and var_prova07=7, var_prova08=11, var_prova09=48 add new rset prova02, prova07,prova08,prova09 with the related value of var in table.

    in the case of my mdb attached add new rset based my_var1=87988787 in prova02 , var_prova07=7, var_prova08=11, var_prova09=48 in prova07,prova08,prova09

    Reply | Quote
    Viewing 1 reply thread
    Author
    Replies
    • WSHansV
      AskWoody Lounger
      October 8, 2009 at 2:32 pm #1180556

      About your first question, it seems that you want to ensure that all records with prova02=my_var1 have prova07=var_prova07, prova08=var_prova08 and prova09=var_prova09. Is that correct?

      Reply | Quote
      • WSsal21
        AskWoody Lounger
        October 8, 2009 at 2:34 pm #1180558

        About your first question, it seems that you want to ensure that all records with prova02=my_var1 have prova07=var_prova07, prova08=var_prova08 and prova09=var_prova09. Is that correct?

        perfect!

        Reply | Quote
        • WSHansV
          AskWoody Lounger
          October 8, 2009 at 2:43 pm #1180560

          You don’t need to open a recordset for this question. You can execute an SQL statement:

          Code:
          cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
  "', PROVA08='" & var_prova08 & "', PROVA09='" & _
  var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"

          where cn1 is your ADO connection variable.

          Reply | Quote
          • WSsal21
            AskWoody Lounger
            October 8, 2009 at 2:55 pm #1180562

            You don’t need to open a recordset for this question. You can execute an SQL statement:

            Code:
            cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
  "', PROVA08='" & var_prova08 & "', PROVA09='" & _
  var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"

            where cn1 is your ADO connection variable.

            but i think your code not compare the value of var with thew value in fileds, or not?

            sorry i dont have see the last your post not consider my note;-(

            Reply | Quote
          • WSsal21
            AskWoody Lounger
            October 8, 2009 at 2:58 pm #1180563

            You don’t need to open a recordset for this question. You can execute an SQL statement:

            Code:
            cn1.Execute "UPDATE FIDI SET PROVA07='" & var_prova07 & _
  "', PROVA08='" & var_prova08 & "', PROVA09='" & _
  var_prova09 & "' WHERE PROVA02='" & my_var1 & "'"

            where cn1 is your ADO connection variable.

            ummmmmm… but this code update the rset in other cae and bot compre the value of the 3 var in rset?

            Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 8, 2009 at 3:07 pm #1180566

              The UPDATE statement will also update the records that already had the correct values but that doesn’t matter – they will remain the same.

              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 8, 2009 at 3:31 pm #1180571

              The UPDATE statement will also update the records that already had the correct values but that doesn’t matter – they will remain the same.

              but the query update only the value in prova07,prova08 and prova09 and remain inaltered the valu in other fileds for example in prova01, prova03, prova04 remain the same?

              in other case is this correct:

              ‘ Open a recordset that selects the records with PROVA02=my_var1
              rs1.Open “SELECT PROVA02 FROM FIDI WHERE PROVA02='” & my_var1 & “‘”, _
              cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

              ‘ If there are no such records, then…
              If rs1.EOF Then
              ‘ … insert a new record
              cn1.Execute “INSERT INTO FIDI (PROVA02, PROVA07, PROVA08, PROVA09) ” & _
              “VALUES(” ‘” & my_var1 & “‘, ‘” & var_prova07 & “‘, ‘” & _
              var_prova07 & “‘, ‘” & var_prova07 & “‘)”

              else

              cn1.Execute “UPDATE FIDI SET PROVA07='” & var_prova07 & _
              “‘, PROVA08='” & var_prova08 & “‘, PROVA09='” & _
              var_prova09 & “‘ WHERE PROVA02='” & my_var1 & “‘”

              End If

              rs1.Close
              Set rs1 = Nothing

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 8, 2009 at 3:33 pm #1180572

              but the query update only the value in prova07,prova08 and prova09 and remain inaltered the valu in other fileds for example in prova01, prova03, prova04 remain the same?

              Yes. The other fields will not be updated since they are not mentioned in the SQL statement.

              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 8, 2009 at 3:39 pm #1180574

              Yes. The other fields will not be updated since they are not mentioned in the SQL statement.

              ahhhhhhhh.. the satement if rs1.EOF Then… ecc is thr best in order of fasted time then .seek .find or other method?

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 8, 2009 at 3:45 pm #1180575

              Unless your table is very large it won’t make much difference, but if you have many records, I think that opening a recordset with just the records for which PROVA02=my_var1 and testing EOF will be faster.

              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 8, 2009 at 3:53 pm #1180576

              Unless your table is very large it won’t make much difference, but if you have many records, I think that opening a recordset with just the records for which PROVA02=my_var1 and testing EOF will be faster.

              but dont understand if the ELSE condition in the my reply is corect?

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 8, 2009 at 4:19 pm #1180583

              I hadn’t seen that you had edited your reply.
              It should work OK.

              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 12, 2009 at 8:33 am #1180949

              I hadn’t seen that you had edited your reply.
              It should work OK.

              Hi Hans…

              About find method peraphs i am wrong in this piece of code!!!!

              Code:
               RS3.MoveFirst
				 Do While RS3.EOF = False
				 If RS3("AG") = AGENZIA Then
				  MsgBox ("TROVATO OK")
				  Exit Do
				  Else
				  MsgBox ("NON TROVATO KO")
				 End If
			RS3.MoveNext
		Loop
              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 12, 2009 at 9:09 am #1180954

              Looping through a recordset to find a value is very slow!

              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 12, 2009 at 9:33 am #1180959

              Looping through a recordset to find a value is very slow!

              hummmmmmmmm….

              Suggestion?

              Note:
              AGENZIA is filled dinamic and assume value from a txt import with a looping in lines

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 12, 2009 at 9:39 am #1180961

              See my replies higher up in this topic.

              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 12, 2009 at 10:18 am #1180970

              See my replies higher up in this topic.

              …
              RS3.Open “SELECT AG FROM MERCATI”, CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect

              CAB = “AG='” & AGENZIA & “‘”
              RS3.Find CAB

              If RS3.EOF = True Then
              MsgBox (“NON TROVATO”)
              Else
              GoTo RS_OK
              End If

              ???

              In mdb table filed AG in text format and AGENZIA is dimensioned as string

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 12, 2009 at 10:30 am #1180972

              I’d use

              Code:
              RS3.Open "SELECT AG FROM MERCATI WHERE AG='" & AGENZIA & "'", CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect
If RS3.EOF = True Then
  MsgBox ("NON TROVATO")
Else
  GoTo RS_OK
End If
              Reply | Quote
            • WSsal21
              AskWoody Lounger
              October 12, 2009 at 10:34 am #1180975

              I’d use

              Code:
              RS3.Open "SELECT AG FROM MERCATI WHERE AG='" & AGENZIA & "'", CN1, adOpenKeyset, adLockOptimistic, adCmdTableDirect
If RS3.EOF = True Then
  MsgBox ("NON TROVATO")
Else
  GoTo RS_OK
End If

              ok….
              But AGENZIA is dinamic is possible to open the recordsrt in the head of code and not reopen for ecah time AGENZIA changed
              ??????

              Reply | Quote
            • WSHansV
              AskWoody Lounger
              October 12, 2009 at 10:48 am #1180977

              Yes but why? This will work as well.

              Reply | Quote
    • WSHansV
      AskWoody Lounger
      October 8, 2009 at 2:54 pm #1180561

      About the second question: if I understand it correctly, you can use the following:

      Code:
      ' Open a recordset that selects the records with PROVA02=my_var1
rs1.Open "SELECT PROVA02 FROM FIDI WHERE PROVA02='" & my_var1 & "'", _
  cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

' If there are no such records, then...
If rs1.EOF Then
  ' ... insert a new record
  cn1.Execute "INSERT INTO FIDI (PROVA02, PROVA07, PROVA08, PROVA09) " & _
	"VALUES(" '" & my_var1 & "', '" & var_prova07 & "', '" & _
	var_prova07 & "', '" & var_prova07 & "')"
End If

rs1.Close
Set rs1 = Nothing
      Reply | Quote
    Viewing 1 reply thread
    Reply To: 2 controll in recodset

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    May 2025
    S M T W T F S
     123
    45678910
    11121314151617
    18192021222324
    25262728293031

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.