Setting up an autonumber type field

[jgarnick]

Is there a way to create an "autonumber" type field or expression but the catch is I need the field to remain the same three records in a row and then change on the 4th record and so on.

Any ideas?

Thanks!

jgarnick
jgarnick@aol.com

 

[TheInsider]

Hi,
Access won't do that for you. I would write a VBA function to do this then use the function in the default value of the field. In any case the question arises - What happens if a record is deleted? Is the database supposed to delete all three associated records? If so, perhaps you should be breaking this table down into a hierarchy of a parent and a child table - ie Clients and Orders. This way any time a records is entered into the Orders table, Access can automatically assign the correct ID of the Client to the Order. Just a thought.

Rob Marriott
rob@career-connections.net

 

[jgarnick]

It is a very unique situation and the records in the table will never need to be deleted--I wouldn't want all three records deleted anyways.

Could you give me an idea of what the function would look like?

Thanks--

jgarnick
jgarnick@aol.com

 

[MichaelRed]

jgarnick,

I wrote a FAQ re autonumber in one of the Ms. Acccess Forums. The title included the word "AutoNumber". Do a site-wide keyword search for this term and I am sure you will find it. It generates a unique "Number" on each call. You could add a static counter, Increment it on each call, reset it (to zero) when it reaches 3 (same value for calls 0, 1, and 2) and generate a new value when you do the reset.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TheInsider]

Hi,
Well I would write the function to do this:

1) Get the Max value of the ID field.
2) Get a count of the number of records where ID = MaxOfID.
3) If CountOfID < 3 return Max Else return Max + 1.

Public Function NextID() As Long
Dim rs As Recordset, MaxID As Long

Set rs = CurrentDb.OpenRecordset(&quot;SELECT Max(Table1.ID) AS MaxOfID FROM Table1&quot

If Not (rs.RecordCount = 0) Then
rs.MoveFirst
'Nz() Accounts for case of 0 records
MaxID = Nz(rs![MaxOfID], 0)

Set rs = CurrentDb.OpenRecordset(&quot;SELECT Count(Table1.ID) AS CountOfID FROM Table1 WHERE (((Table1.ID) = &quot; & MaxID & &quot)&quot

If Not (rs.RecordCount = 0) Then
rs.MoveFirst

If (rs![CountOfID] < 3) Then
NextID = rs![CountOfID]
Else
NextID = rs![CountOfID] + 1
End If
Else
NextID = 0
End If
Else
NextID = 0
End If

rs.Close
End Function

The only thing to note is that I don't believe that you can place user-defined function in the &quot;Default Value&quot; field in the tables design view. However, you can place it in the &quot;Default Value&quot; of the text box on the form - go figure! And depending on your version of Access, you may need to define rs as DAO.Recordset instead of Recordset.

Rob Marriott
rob@career-connections.net

 

[Craig0201]

Rather more simply than doing that, i've had a similar problem and solved it by using two tables and two update queries. The first table (COUNT) contained a count from 1 to 5 and the second (NUMBER) the numbering. Each table contained one field and one record.

I then set up two update queries with if statements in.....

Update Count!Count = iif(count!count=5,1,count!count+1)
Update Number!Number = iif(count!count=5,number!number+1,number!number+1)

Now you can use them as you wish......

Craig

 

[jgarnick]

I'm still stuck--

I have a macro that runs an append query which appends data to table1 based on a crosstab query's results. The macro then opens a report that is based on table1. It is when the append query adds data to table1 that I need a field in table1 called GROUP to stay the same for three records and then increment by one and so on.

I don't have a form to put the user-defined function as a default value. I was too confused by Michael Red's FAQ and suggestion and I can't figure out how to relate the count and number tables to my table! HELP!

jgarnick
jgarnick@aol.com