Autonumber in Query not working properly (1 Viewer)

tamangspace

Registered User.
Local time
Today, 17:16
Joined
Jul 15, 2012
Messages
37
Dear All,
I don't know how to express this problem. I am not expert in vba. just learning a bit. I got some code like this:

Code:
Public wANum As Long
Function GetAutoNumber(EmpNAME) As Long
wANum = wANum + 1
GetAutoNumber = (wANum)
End Function
I created a module from this code. It seems like this:

Code:
Option Compare Database
Option Explicit
Public wANum As Long
Function GetAutoNumber(EmpNAME) As Long
wANum = wANum + 1
GetAutoNumber = (wANum)
End Function

I created a query and used this code. In the AutoNum field I typed like this: AutoNum: GetAutoNumber([EmpName])
It works first time when I open it. After 1 or 2 seconds it automatically updates and autonumber will be not starting from 1. Autonumber starts from like 180 or more. After another 3 - 5 seconds it increases and starts like 350 or more.
I want to start autonumber from just 1. I am sorry if I couldn't express my problem properly, but I am so frustrated because I want the list of employees with autonumber starting from 1.
Any help is most appreciated.
 

plog

Banishment Pending
Local time
Today, 09:16
Joined
May 11, 2011
Messages
11,613
Lot's of 'why's to ask you, but first, here's your answer: wANum is independent of everything. Everytime you ask it for a value, it looks at what the last value and returns one plus that last value---no matter what. You're not saving it so it has no idea that you want it attached to a certain record. Everytime you call GetAutoNumber it returns one plus the last value.


When you close and reopen the query, you are rerunning it. When you sort the query when its open, you are rerunning it. When you filter it, you are rerunning it. The only thing wANum is good for is determining how many times you've accessed wANum.

Why are you passing this function [EmpNAME]? Why not put an autonumber on the table itself? Why must your autonumbers start with 1?
 

tamangspace

Registered User.
Local time
Today, 17:16
Joined
Jul 15, 2012
Messages
37
Thanks a lot Plog, for a quick response. The questions you asked arise me too. I know to put autonumber in table. But I don't know how to do it in qurey. I want to filter the names of employees who got the specific ID cards, where empName is from tblemployeedetails, plant from tblplant and idcard from tblidcards. In this query, I want to put autonumber field and print quickly with serial number. Because if autonumber starts with 1, then it will show how many in the list in the last number like in Ms excel. My query shows only the sn from tblemployeedetails which is the unique number (autonumber) with PK.

In my database, the employees are hired from many companies. The employees work in many sites (plants) . And so there are many kinds of id cards issued by several plants.

Sorry, because I don't know how to express in short.
 
Last edited:

plog

Banishment Pending
Local time
Today, 09:16
Joined
May 11, 2011
Messages
11,613
I know to put autonumber in table. But I don't know how to do it in qurey

You don't put it in the query. You bring it into the query from the table.
 

tamangspace

Registered User.
Local time
Today, 17:16
Joined
Jul 15, 2012
Messages
37
Oh Plog,
There may be some misunderstandings. I want to upload a screen capture here. please analyse it. The SN field from table can be hidden. But I want a field with continuous numbers starting from 1. for ex: 1, 2, 3, 4, ..... and not like 4, 7, 45, 153, ..... When we bring autonumber from table. it will not appear as continuous.

Is there no any solution for this problem. Sorry because I am learning ms access from this forum and also from google.
 

Attachments

  • Query Bonatti.PNG
    Query Bonatti.PNG
    83.8 KB · Views: 136

plog

Banishment Pending
Local time
Today, 09:16
Joined
May 11, 2011
Messages
11,613
Ok, I understand that. Now, why? Is it a fung-shei thing? OCD? What do continous numbers do for you?
 

tamangspace

Registered User.
Local time
Today, 17:16
Joined
Jul 15, 2012
Messages
37
Great! You understand it. Now every time if I print the report, I use to export to excel sheet and reset the SN to continuous number so that my boss can read how many IDs and which number etc. Because my boss wants the continuous numbering in the report. Exporting to excel sheet is so much time consuming and boring...:(

Ok, is there any idea to do this? Or if I didn't learned msaccess properly then give me some link to study. I hardly learned PK and FK and many to many relationship. Learned a little bit of vb language and macro.
 

plog

Banishment Pending
Local time
Today, 09:16
Joined
May 11, 2011
Messages
11,613
I understand what you want, I do not understand why you want it.

If you want a report that numerates each record, you should post that question in the Report section of this forum. I'm not that great with reports--the answer has to do with adding a running total field to each record with a value of 1--they over there will have the specific answer.

If you want this in a query, I iterate--why?
 

tamangspace

Registered User.
Local time
Today, 17:16
Joined
Jul 15, 2012
Messages
37
If the number field does not have any logical need you can just use a number field created by Running Sum method..

Thank you pr2-eugin,
I got it what I was looking for. It works fine. But one thing... I found it that it works in a definite query. If I use it in qryBonatti, it works in that query. But for another query, need I create another module? or how can I use it in all queries?
Could you please write me some codes, if possible?

Once again thank you very much.
 

Users who are viewing this thread

Top Bottom