Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Adnmit have My_var=9879812154-05 (correspondent to a records in table) see image.
    How to delete this records and move the rest of recordset to left???

    new recordset after delete the record in question:
    ASS1 ASS2 ASS3 ASS4 ASS5 ASS6

    9879812155-06 9879812156-07 9879812157-08 9879812158-09 9879812159-10 9879812160-11

    Note:
    - i just have opened a conn and my rset is named rst1 with tipical ado connection
    - i just have a code to intecept the record 9879812154-05

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    From your description I get the impression that you don't want to delete a record, but to move the contents of field ASS2 to ASS1, from ASS3 to ASS2 etc. within that record.

    Is that correct?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795633' date='30-Sep-2009 13:36']From your description I get the impression that you don't want to delete a record, but to move the contents of field ASS2 to ASS1, from ASS3 to ASS2 etc. within that record.

    Is that correct?[/quote]

    yes perfect !!!

    but i dont want see the record in question in posztion ASS1, i copy tath in a new table named History...
    I hope you understand me:-)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You'd have to use code like this:

    rst1!ASS1 = rst1!ASS2
    rst1!ASS2 = rst1!ASS3
    ...
    rst1!ASS9 = rst1!ASS10
    rst1!ASS10 = Null
    rst1.Update

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795637' date='30-Sep-2009 13:44']You'd have to use code like this:

    rst1!ASS1 = rst1!ASS2
    rst1!ASS2 = rst1!ASS3
    ...
    rst1!ASS9 = rst1!ASS10
    rst1!ASS10 = Null
    rst1.Update[/quote]

    but if i have understand your code owerwrith the existing records?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The code modifies the current record by moving field values to the left.

    It doesn't do anything with other records.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795640' date='30-Sep-2009 13:57']The code modifies the current record by moving field values to the left.

    It doesn't do anything with other records.[/quote]

    The code work perfect if the record is in a fixed position ASS1!

    Prob!

    I have see my code and not always the recordset to delete is in position ASS1!!!!
    Possible in all part of recorset for example in ASS2, ASS6, ASS10...
    How to use the same technique if the record not is in ASS1?

    Peraphs i understand i need to loop il all ASSX and when i have find the value...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='sal21' post='795642' date='30-Sep-2009 14:29']Peraphs i understand i need to loop il all ASSX and when i have find the value...[/quote]
    Yes, you would have to loop through the fields ASS1, ASS2, etc. until you find the value.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795651' date='30-Sep-2009 15:15']Yes, you would have to loop through the fields ASS1, ASS2, etc. until you find the value.[/quote]

    Like this:

    For B = 5 To 14
    If COD_ASSEGNO = RS1.Fields( Then
    Debug.Print "ASS" & B
    Exit For
    End If
    Next B

    if is ok...

    and i find the value in filed ASS7
    how to modify to make dinamic this piece of code:

    RS1!ASS1 = RS1!ASS2
    RS1!ASS2 = RS1!ASS3
    RS1!ASS3 = RS1!ASS4
    RS1!ASS4 = RS1!ASS5
    RS1!ASS5 = RS1!ASS6
    RS1!ASS6 = RS1!ASS7
    RS1!ASS7 = RS1!ASS8
    RS1!ASS8 = RS1!ASS9
    RS1!ASS9 = RS1!ASS10
    RS1!ASS10 = Null
    RS1.Update

    with a new for next?

    Remeber the last ASS is fixed to 10

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Code:
    For I = 5 To 9
      If COD_ASSEGNO = RS1.Fields(I) Then
    	Exit For
      End If
    Next I
    For J = I to 9
      RS1.Fields("ASS" & J) = RS1.Fields("ASS" & (J + 1))
    Next J
    RS1!ASS10 = Null
    RS1.Update

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795657' date='30-Sep-2009 15:29']
    Code:
    For I = 5 To 9
      If COD_ASSEGNO = RS1.Fields(I) Then
    	Exit For
      End If
    Next I
    For J = I to 9
      RS1.Fields("ASS" & J) = RS1.Fields("ASS" & (J + 1))
    Next J
    RS1!ASS10 = Null
    RS1.Update
    [/quote]


    WORK, naturally!
    But if i have only the value to find in ASS1 and ASS2, ASS3...ASS10 are blank?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What do you want to happen then?

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795667' date='30-Sep-2009 15:47']What do you want to happen then?[/quote]

    Delete it!

    I'm sorry,not delete the cell where the value is finded but delete entire recordset (line) in table...

    In this case, for me all value are blank from ASS1 to ASS10 and not need this recordset

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Try this:

    Code:
    For I = 1 To 9
      If COD_ASSEGNO = RS1.Fields("ASS" & I) Then
    	Exit For
      End If
    Next I
    If I = 1 Then
      F = True
      For J = 2 To 10
    	If Not IsNull(RST1.Fields("ASS" & J)) Then
    	  F = False
    	  Exit For
    	End If
      Next J
      If F = True Then
    	RST1.Delete
    	Exit Sub
      End If
    End If
    For J = I to 9
      RS1.Fields("ASS" & J) = RS1.Fields("ASS" & (J + 1))
    Next J
    RS1!ASS10 = Null
    RS1.Update

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795675' date='30-Sep-2009 16:32']Try this:

    Code:
    For I = 1 To 9
      If COD_ASSEGNO = RS1.Fields("ASS" & I) Then
    	Exit For
      End If
    Next I
    If I = 1 Then
      F = True
      For J = 2 To 10
    	If Not IsNull(RST1.Fields("ASS" & J)) Then
    	  F = False
    	  Exit For
    	End If
      Next J
      If F = True Then
    	RST1.Delete
    	Exit Sub
      End If
    End If
    For J = I to 9
      RS1.Fields("ASS" & J) = RS1.Fields("ASS" & (J + 1))
    Next J
    RS1!ASS10 = Null
    RS1.Update
    [/quote]
    I LOV.... YOU:-)

Page 1 of 2 12 LastLast

Posting Permissions

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