Jumping to a Record

[Ragnar78]

Hy,

I have records in a table that will not change (the values will but not the record number), they will still be know as record number 100 for example...


How can jump between those records calling them by their respective number to change them...

 

[Norie]

In the bottom left of a table there are navigation buttons and a record selector.

Just type the record number you want in there and press enter.

 

[Ragnar78]

Sorry, i forgot to mention in VBA...
Actually this is what i'm trying to do:

Field 1 Field 2 Field 3 Field 4
C00515 2003w37 2 7
C00515 2003w37 2 8
C00515 2003w41 2 5
C00515 2003w43 2 5
C00515 2004w09 4 19

Between Record 2 and Record 3 their is a diffrence of 3 weeks missing...
I want to add them, and to do that i have to check that the field1.value are the same between records and that the diffrence in values between Field 2 are superior to 1


So it should look like this after inserting...
Field 1 Field 2 Field 3 Field 4
C00515 2003w37 2 7
C00515 2003w37 2 8
C00515 2003w38 2 8
C00515 2003w39 2 8
C00515 2003w40 2 8
C00515 2003w41 2 5
C00515 2003w42 2 5
C00515 2003w43 2 5

Now i already managed this With excel, but i cant do it here cause i have a small Access object knowledge...
Thx for the help again..

 

[giacomo]

You cannot append new records between existing ones. The new records must be added as new rows at the end of your table. Then you can sort on whatever fields you like in a query.

 

[Ragnar78]

I totaly agree with you, what i gave was just an explantion of how i wanted the data values to be...

I'll think i made i explained it wrong...

Can you tell me how to compare between 2 records?
let us say between Record 10 and Record 13?

Tx

 

[Norie]

Here is how you move between records and how to add a new record.

I've used some of your data and added an Autonumber field

Sub test()
Dim db As Database
Dim rst As Recordset
Dim fld As Field
Dim strRcrd As String

Set db = CurrentDb
Set rst = db.OpenRecordset("Table2")


rst.MoveFirst

While Not (rst.EOF)

    For Each fld In rst.Fields
        strRcrd = strRcrd & fld.Value & vbCr
    Next
    MsgBox strRcrd
    strRcrd = ""
    rst.MoveNext
Wend

With rst
    .AddNew
    .Fields(1) = "C00515"
    .Fields(2) = "2003w38"
    .Fields(3) = 2
    .Fields(4) = 7
    .Update
End With

End Sub

To compare two records:

1 Go to a record

2 Store values to be compared

3 Move to next record

4 Compare values

In your example I would make sure you sort on the week field.

 

[Ragnar78]

Hy,

Even though its not what i'm searching for, you gave me a good idea on how to work it out.

This is what i intend to do:
I have to create 8 temp variables: 4 holding the values of the previous record, and 4 holding values of the current record...
Like this i can compare and add the new records depending on my need using loops to set the amount of records to be created...

Thx alot, it was really helpfull...

 

[Norie]

This will create new records with the correct week value.

Sub test()
Dim db As Database
Dim rst As Recordset
Dim PreviousWeek As Integer
Dim CurrentWeek As Integer
Dim PreviousYear As Integer
Dim CurrentYear As Integer
Dim I, J As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("Table2")

rst.MoveFirst

PreviousWeek = CInt(Right(rst.Fields(2), 2))
PreviousYear = CInt(Left(rst.Fields(2), 4))

For J = 1 To rst.RecordCount - 1
    rst.MoveNext
    If Not rst.EOF Then
        
        CurrentWeek = CInt(Right(rst.Fields(2), 2))
        CurrentYear = CInt(Left(rst.Fields(2), 4))
        
        If (CurrentWeek - PreviousWeek > 1) And (CurrentYear = PreviousYear) Then
        
            For I = PreviousWeek + 1 To CurrentWeek - 1
                With rst
                    .AddNew
                    .Fields(2) = PreviousYear & "w" & I
                    .Update
                End With
            
            Next
            
        End If
        
        PreviousYear = CurrentYear
        PreviousWeek = CurrentWeek
        
    End If
    
Next

End Sub

[/code]

 

[Ragnar78]

This is the code i ended up with....

Type RecValues
     NUMBERPRGN As String
     TH_STATUS As String
     SYSMODTIME As Date
     DATE_ENTERED As Date
     WEEK_STATUS As String
End Type

Sub Moveinto()

Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim MyTable As DAO.TableDef
Dim MyRec As DAO.Recordset
Dim x As String


Set MyDB = CurrentDb
Set MyTable = MyDB.TableDefs!Retreived_Data

With MyTable
     .Fields.Append .CreateField("WEEK_STATUS", dbText, 7)
End With

Set MyRS = MyDB.OpenRecordset("Retreived_Data", dbOpenDynaset)
Set MyRec = MyDB.OpenRecordset("Retreived_Data", dbOpenTable)

    With MyRS
         .MoveFirst
         .Requery
         While Not .EOF
               x = CStr(Year(.Fields("SYSMODTIME").Value) & "w" & Format(Format(.Fields("SYSMODTIME").Value, "ww"), "00"))
               .Edit
               !WEEK_STATUS = x
               .Update
               Debug.Print .Fields(0).Value & .Fields(4).Value
               .MoveNext
         Wend
    End With
    
Set MyRec = MyDB.OpenRecordset("Retreived_Data", dbOpenTable)

Dim OldRec As RecValues
Dim NewRec As RecValues
Dim RecToAdd As Integer
Dim Adder As Integer

With MyRec
     .MoveFirst
'First Record Values
         OldRec.DATE_ENTERED = .Fields(3).Value
         OldRec.NUMBERPRGN = .Fields(0).Value
         OldRec.SYSMODTIME = .Fields(2).Value
         OldRec.TH_STATUS = .Fields(1).Value
         OldRec.WEEK_STATUS = .Fields(4).Value
     While Not (.EOF)
         .MoveNext
         If .EOF Then
            Exit Sub
         End If

'New Record Values
         NewRec.DATE_ENTERED = .Fields(3).Value
         NewRec.NUMBERPRGN = .Fields(0).Value
         NewRec.SYSMODTIME = .Fields(2).Value
         NewRec.TH_STATUS = .Fields(1).Value
         NewRec.WEEK_STATUS = .Fields(4).Value
         
         RecToAdd = CInt(Right(NewRec.WEEK_STATUS, 2)) - CInt(Right(OldRec.WEEK_STATUS, 2))
     
'Compare Values
         If NewRec.NUMBERPRGN = OldRec.NUMBERPRGN Then
            If Left(OldRec.WEEK_STATUS, 4) = Left(NewRec.WEEK_STATUS, 4) Then
               Adder = 0
            ElseIf Left(OldRec.WEEK_STATUS, 4) < Left(NewRec.WEEK_STATUS, 4) Then
               Adder = 52
            End If
               
               If RecToAdd + Adder > 1 Then
                  For I = 1 To RecToAdd - 1
                      .AddNew
                      ![NUMBERPRGN] = OldRec.NUMBERPRGN
                      ![TH_STATUS] = OldRec.TH_STATUS
                      ![SYSMODTIME] = OldRec.SYSMODTIME
                      ![DATE_ENTERED] = OldRec.DATE_ENTERED
                      ![WEEK_STATUS] = Left(OldRec.WEEK_STATUS, 5) & Format(CInt(Right(OldRec.WEEK_STATUS, 2) + I), "00")
                      .Update
                  Next I
               End If
         End If
         OldRec.DATE_ENTERED = NewRec.DATE_ENTERED
         OldRec.NUMBERPRGN = NewRec.NUMBERPRGN
         OldRec.SYSMODTIME = NewRec.SYSMODTIME
         OldRec.TH_STATUS = NewRec.TH_STATUS
         OldRec.WEEK_STATUS = NewRec.WEEK
    
     Wend
End With


End Sub