By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,615 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,615 IT Pros & Developers. It's quick & easy.

Autonumber generator (help required)

P: 44
=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.
Oct 23 '06 #1
Share this Question
Share on Google+
14 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 23 '06 #2

P: 44
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.
Oct 23 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function assignAccounts()
  3. Dim db As Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6.     Set db = CurrentDb
  7.     Set rs1 = db.OpenRecordset("Accounts")
  8.     Set rs2 = db.OpenRecordset("Employees")
  9.  
  10.     rs1.MoveFirst
  11.     rs2.MoveFirst
  12.     Do Until rs1.EOF
  13.  
  14.   rs1.Edit
  15.   rs1!SerialNo = rs2!EmployeeID
  16.   rs1.Update
  17.  
  18.   rs2.MoveNext
  19.   If rs2.EOF Then
  20.       rs2.MoveFirst
  21.   End If
  22.   rs1.MoveNext
  23.     Loop
  24.  
  25.     rs1.Close
  26.     rs2.Close
  27.     Set rs1 = Nothing
  28.     Set rs2 = Nothing
  29.     Set db = Nothing
  30.  
  31. End Function
  32.  
  33.  
Oct 23 '06 #4

P: 44
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.
Oct 24 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub temp()
  3.  
  4.     assignAccounts
  5.  
  6. End Sub
  7.  
  8.  
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.
Oct 24 '06 #6

P: 44
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Sub temp()
  3.  
  4.     assignAccounts
  5.  
  6. End Sub
  7.  
  8.  
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
Oct 24 '06 #7

P: 44
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
Oct 24 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 25 '06 #9

P: 44
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.
Oct 25 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 25 '06 #11

P: 44
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.
Oct 25 '06 #12

P: 44
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
Oct 25 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
OK try this:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Function assignAccounts()
  3. Dim db As Database
  4. Dim rs1 As DAO.Recordset
  5. Dim rs2 As DAO.Recordset
  6.     Set db = CurrentDb
  7.     Set rs1 = db.OpenRecordset("Accounts")
  8.     Set rs2 = db.OpenRecordset("Employees")
  9.     rs1.MoveFirst ' first acc
  10.  
  11. RESET_EMP:
  12.  
  13.     rs2.MoveFirst ' first emp
  14.     Do Until rs2.EOF ' run through employees
  15.     If rs1.EOF Then
  16.        GoTo END_ASSIGN
  17.     End If
  18.  
  19.     rs1.Edit
  20.     rs1!SerialNo = rs2!EmployeeID 'emp to acc
  21.     rs1.Update
  22.  
  23.     rs1.MoveNext ' next emp
  24.     rs2.MoveNext ' next acc
  25.     Loop
  26.  
  27.     If rs1.EOF Then
  28.         GoTo END_ASSIGN
  29.     ELSE
  30.         GoTo RESET_EMP
  31.     End If
  32.  
  33. END_ASSIGN: 
  34.  
  35.     rs1.Close
  36.     rs2.Close
  37.     Set rs1 = Nothing
  38.     Set rs2 = Nothing
  39.     Set db = Nothing
  40.  
  41. End Function
  42.  
  43.  
  44.  
Oct 25 '06 #14

P: 44
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
Oct 27 '06 #15

Post your reply

Sign in to post your reply or Sign up for a free account.