Access 2007 Help needed

[wahabltd]

Hi
I dont know if it is possible or not. I need to do following in a single table database designed in accecc 2007. I have designed form. Everythign is working fine.
I want to add following things:-
1-I want to add a field that automatically assign label to record based on their primarykey. e.g. if record number is between 1 and 50, an entry "1-50"is automatically assigned in a new feild "Group". The feild "Group" is already there in the table. If record is between 51 to 100 then entry entered to be "51-100" and so on. How can I do it.?

Thanks

[mshmyob]

The first thing I would like to point out is that ACCESS does not use Record Numbers. What you see at the bottom are not really record numbers.

Took me awhile to work this out but here it is. The key point you must remember when using the code is that you MUST ALWAYS open the table in the EXACT same sort order all the time. Since ACCESS does not use record numbers if you open the table with a different sort order you will get different results.

    Dim rs As Recordset
    Dim vPK As Variant
    Dim vRecNum As Long
    Dim vNewValue As String
' create a recordset     
Set rs = Me.RecordsetClone
' populate the recordset - you must do this
    rs.MoveLast
    rs.MoveFirst
' end populating recordset
 
' when user moves through the records I want to store the primary key each time from the main form
   vPK = Me.txtYourPK
' move through the recordset to the matching primary key from main form
    rs.FindFirst "[txtYourPK] = " & vPK
 
' grab the 'record number'   
    vRecNum = rs.AbsolutePosition + 1
 
' run your select case to determine what to put in your field
    Select Case vRecNum
        Case Is <= 50
            vNewValue = "1-50"
        ' put all your other CASE's here
        Case Else
    End Select
 

Put this code in the ONCURRENT event or a Button click or wherever you want it run.

Thanks for the challenge this was an interesting one.

cheers,

Hi
I dont know if it is possible or not. I need to do following in a single table database designed in accecc 2007. I have designed form. Everythign is working fine.
I want to add following things:-
1-I want to add a field that automatically assign label to record based on their primarykey. e.g. if record number is between 1 and 50, an entry "1-50"is automatically assigned in a new feild "Group". The feild "Group" is already there in the table. If record is between 51 to 100 then entry entered to be "51-100" and so on. How can I do it.?

Thanks

[mshmyob]

This is an addendum to my previous post.

I would strongly suggest you not to use 'Record Numbers' with Access since it is not consistant and you are asking for trouble in the future (in my opinion).

cheers,