Autonumber generator (help required)

[Zeeshan7]

=NZ(DMax("[SerialNo]","Table1")+1,1)

This can be added in a form field / properties / Data tab in Default Value to generate auto number.

Please help to add the same in a table 'default value? Secondly, can we loop numbers after specific number? e.g. if we want to go up to number 8; can we start next record from serial no 1 and go up to 8 and again 1 to 8 and so on....?

Thanks in advance.

[MMcCarthy]

Firstly you can't set a number programmatically in the table only on a control on the form.

for your other question it would be extremely complicated to do and very bad for data integrity. What exactly are you trying to do and why? Knowing this would help to design a solution. It sounds as if you need to change your table structures.

[Zeeshan7]

Thanks for reply.

Actually I have another table i.e. employee table that contains total 8 employees. Fields are employee numbers i.e.1, 2, 3 up to 8 and employee name.

Now the second table i.e. accounts table contains a list of account holders i.e. account_number, account_title and that autonumber field i.e. from 1 to 8; again 1 to 8 and so on for e.g. 100 records.

Actually I want to assign/ distribute these 100 accounts to the employees through relationship between the two tables (employee number and autonumber field). This can be possible if accounts table contains repetition of 1 to 8 for all records.

[MMcCarthy]

Firstly, I understand from what you've posted that you would like to populate a foreign key in the Accounts table with a reference to the employeeID of which there are 8 employees and distribute them apportionately. This can be done as a one off routine on existing data but I don't see how it could be built as an existing routine.

For a one off build:

 
Function assignAccounts()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Accounts")
    Set rs2 = db.OpenRecordset("Employees")
 
    rs1.MoveFirst
    rs2.MoveFirst
    Do Until rs1.EOF
 
  rs1.Edit
  rs1!SerialNo = rs2!EmployeeID
  rs1.Update
 
  rs2.MoveNext
  If rs2.EOF Then
      rs2.MoveFirst
  End If
  rs1.MoveNext
    Loop
 
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
End Function
 
 

[Zeeshan7]

I have pasted your code in a module but it is not working... It is giving a compile error. Sometime it asks to name and save a Macro.

I have created a database named as CurrentDb

I have created following tables:-

1- Accounts: fields are AccountNo, AccountTitle and SerialNo
2- Employees: fields are EmployeeID and EmployeeName

Is it possible to send you the database to check where I have done a mistake? Or guide me, accordingly.

Thanks for all your help.

[MMcCarthy]

Two things:

1. In the module window go to the menu bar - Tools - References

This is a list of libraries. Check that one of the libraries ticked is a Microsoft DAO library if not the scroll down the list until you find one and tick it.

2. You can't run this function directly. You have to call it from somewhere else. Underneath the function put the following.

 
Public Sub temp()
 
    assignAccounts
 
End Sub
 
 

Then highlight this piece of code by clicking before Public and dragging the mouse to after sub. It should be highlighted in blue. Then go to the icon bar and click on the sideways triange.

I have pasted your code in a module but it is not working... It is giving a compile error. Sometime it asks to name and save a Macro.

I have created a database named as CurrentDb

I have created following tables:-

1- Accounts: fields are AccountNo, AccountTitle and SerialNo
2- Employees: fields are EmployeeID and EmployeeName

Is it possible to send you the database to check where I have done a mistake? Or guide me, accordingly.

Thanks for all your help.

[Zeeshan7]

Two things:

1. In the module window go to the menu bar - Tools - References

This is a list of libraries. Check that one of the libraries ticked is a Microsoft DAO library if not the scroll down the list until you find one and tick it.

2. You can't run this function directly. You have to call it from somewhere else. Underneath the function put the following.

 
Public Sub temp()
 
    assignAccounts
 
End Sub
 
 

Then highlight this piece of code by clicking before Public and dragging the mouse to after sub. It should be highlighted in blue. Then go to the icon bar and click on the sideways triange.

Hi mmccarthy,

You are genius. Its is working!

Thanks a lot.

Regards

[Zeeshan7]

Hi,

Your code successfully assign accounts equally among employees. I need a bit more help from you.

Now if we add a "Balance" field in ‘Accounts’ table; is it possible to distribute account balance-wise?

Instead of assigning e.g. 6 accounts among 2 employees like:-
(Your code distribute accounts in following manner)

1
1
1
2
2
2

The result should be like:-

1
2
1
2
1
2

as Accounts table contains account Balances in descending order before we run the code. We can re-set Accounts table/SerialNo in descending order later to distribute them equally (Balance-wise).

This will finally resolve my query.

Thanks & regards

[MMcCarthy]

I need more information.


What fields are in your accounts table and what criteria based on the values in these fields do you want to use to decide on how to assign them.

We can add criteria to the routine I just need to understand exactly what that criteria is.

[Zeeshan7]

I need more information.


What fields are in your accounts table and what criteria based on the values in these fields do you want to use to decide on how to assign them.

We can add criteria to the routine I just need to understand exactly what that criteria is.

Hi,

I have added one more field as per my earlier post i.e. account "Balance".

1- I want to assign all accounts equally among all employees -- this is working fine.

2- I want to assign equal account balances as well to all employees. Accounts table contain balances in descending order. So when we assign alternate SerialNo through code, every Employee will get high to low balance accounts; so we can sort it and distribute accordingly. Thanks a lot for your help.

[MMcCarthy]

Not 100 percent sure what you mean.


Accounts are sorted by account balance in descending order

81 Accounts
8 Employees

First Employee gets top 11 accounts
Second Employee gets next 10 accounts
Or
Each employee gets one each of top 8 accounts

[Zeeshan7]

Not 100 percent sure what you mean.


Accounts are sorted by account balance in descending order

81 Accounts
8 Employees

First Employee gets top 11 accounts
Second Employee gets next 10 accounts
Or
Each employee gets one each of top 8 accounts

Each employee should get high to low balance accounts.

Before distribution of 9 accounts between 3 employees:-

(Accounts table)
AccountNo Balance SerialNo
1234 100,000
4321 98,000
4646 95,000
3535 90,000
5356 85,000
5466 80,000
9696 76,000
4747 70,000
3666 65,000

After Assignment result should be like this:-

AccountNo Balance SerialNo (as EmployeeID)
1234 100,000 1
3535 90,000 1
9696 76,000 1
4321 98,000 2
5356 85,000 2
4747 70,000 2
4646 95,000 3
5466 80,000 3
3666 65,000 3

You can see that each employee has got high to low balance account in sequence. Thanks for your support.

[Zeeshan7]

Tables are not pasted appropriately. Please note that first 4 digits are AccountNo next e.g. 100,000 & 98,000 etc. are Balances and 1, 2 and 3 are SerialNos

Thanks and regards

[MMcCarthy]

OK try this:

 
Function assignAccounts()
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Accounts")
    Set rs2 = db.OpenRecordset("Employees")
    rs1.MoveFirst ' first acc
 
RESET_EMP:
 
    rs2.MoveFirst ' first emp
    Do Until rs2.EOF ' run through employees
    If rs1.EOF Then
       GoTo END_ASSIGN
    End If
 
    rs1.Edit
    rs1!SerialNo = rs2!EmployeeID 'emp to acc
    rs1.Update
 
    rs1.MoveNext ' next emp
    rs2.MoveNext ' next acc
    Loop
 
    If rs1.EOF Then
        GoTo END_ASSIGN
    ELSE
        GoTo RESET_EMP
    End If
 
END_ASSIGN: 
 
    rs1.Close
    rs2.Close
    Set rs1 = Nothing
    Set rs2 = Nothing
    Set db = Nothing
 
End Function
 
 
 

[Zeeshan7]

Hi, mmccarthy

Thanks a lot for all your assistance. I have made few changes in the table and both of your codes are working perfectly. Thanks again. Regards