?
Solved

Increment an ID field in existing many-to-one table

Posted on 2012-08-11
6
Medium Priority
?
435 Views
Last Modified: 2012-08-12
I would like to update a many-to-one table by incrementing the "UnitID" field for each record that exists for the Ppty_ID.  Here's the table (I've also attached it):

tblVacyUnits
Ppty_ID      UnitID      UnitType      
7                                 Studio
7                                 1Bed
7                                  2 Bed
8                                  2 Bed

And here's what I want it to look like after it's updated:

Ppty_ID      UnitID      UnitType      
7                      1        Studio
7                      2        1Bed
7                      3        2 Bed
8                      1         2 Bed

I read the other questions about auto incrementing, but those seem to apply to inserting new records in forms, rather than dealing with an existing table.

Could someone please help me with how to do this?

Thanks,
Patty
UnitID.accdb
0
Comment
Question by:Patty01Access
  • 2
  • 2
  • 2
6 Comments
 
LVL 29

Accepted Solution

by:
IrogSinta earned 1000 total points
ID: 38284809
Try running this code in a module:
Sub UpdateUnitIDs()
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim strID As String
    
    i = 1
    Set rst = CurrentDb.OpenRecordset("Select * From tblVacyUnits Order By Ppty_ID")
    Do While Not rst.EOF
        If strID <> rst!Ppty_ID Then
            strID = rst!Ppty_ID
            i = 1
        End If
        rst.Edit
        rst!UnitID = i
        rst.Update
        rst.MoveNext
        i = i + 1
    Loop
    rst.Close
    Set rst = Nothing
End Sub

Open in new window

0
 
LVL 40

Assisted Solution

by:als315
als315 earned 1000 total points
ID: 38284817
You can use this sub:
Sub number_units()
Dim rs As DAO.Recordset
Dim SQL As String
Dim Ppty As String, i As Long
SQL = "Select * from tblVacyUnits Order by Ppty_ID, UnitID, UnitType;"
Set rs = CurrentDb.OpenRecordset(SQL)
i = 0
Ppty = rs!Ppty_ID
Do Until rs.EOF
    If rs!Ppty_ID <> Ppty Then
        Ppty = rs!Ppty_ID
        i = 0
    End If
    If Not IsNull(rs!UnitID) Then
            i = rs!UnitID
    Else
            rs.Edit
            i = i + 1
            rs!UnitID = i
            rs.Update
    End If
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

Open in new window

0
 
LVL 40

Expert Comment

by:als315
ID: 38284829
Sorry, Irogsinta, did not refreshed window. I've added check for already existing UnitIDs. But code was with error. SQL string should be:
SQL = "Select * from tblVacyUnits Order by Ppty_ID, nz(UnitID,99999999), UnitType;"
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Patty01Access
ID: 38285382
Thank you, as both solutions worked perfectly!

I'm new at this, and uncertain as to how to award the points.  

I don't understand the difference in the code between the 2.  I believe that ALS code would ignore existing UnitIDs, but Irogsinta's worked fine--I guess it just overwrote the existing ones, which was fine for my application.

Could one of you please give some guidance as to how to award the points?  Split equally?

Thanks again.
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38285857
Well we both took the time to write some working code for you so splitting the points equally would work.
0
 

Author Comment

by:Patty01Access
ID: 38285918
Will do!  Thanks for taking the time to answer.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question