Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 536
  • Last Modified:

VBA function to create a sequence number

hi, i am a beginner, so i think this would be easy for you guys.

I have this function in my module to create a sequence number. I called this function in my update query. I hope that this function will retirn a sequence but instead in retuns the same number for every record. This is my function.

Public currNo As Integer


Function addNo() As Integer

  currNo = currNo + 1
 
  addNo = currNo
 
End Function

* The reason I use currNo as public variable is to make sure that it will keep in the memory what is the current value. Maybe my method is wrong.

One more thing, I put this function in the query design, I assume that it will run this function row by row. Am I wrong about this?

this is my sql statement

UPDATE ACCOUNT_ACTIVITY SET ACCOUNT_ACTIVITY.ACC_SEQ = addNo()
WHERE (((ACCOUNT_ACTIVITY.ACC_ID)="1") AND ((ACCOUNT_ACTIVITY.ACC_DESC)="CLEARING CHQ"));


Really need help on this.
0
koCheE
Asked:
koCheE
1 Solution
 
peter57rCommented:
Hello koCheE,

Change the function so that it accepts a parameter.  Then use any field from the data record as the argument.
This will fool Access into thinking the value must calculated for each record.

Function addNo(vParam) As Integer

  currNo = currNo + 1
 
  addNo = currNo
 
End Function

UPDATE ACCOUNT_ACTIVITY SET ACCOUNT_ACTIVITY.ACC_SEQ = addNo(Acc_seq)
WHERE (((ACCOUNT_ACTIVITY.ACC_ID)="1") AND ((ACCOUNT_ACTIVITY.ACC_DESC)="CLEARING CHQ"));

Pete
0
 
nico5038Commented:
You can also achieve this without your own function as Access already has a DCOUNT function you can use like:

UPDATE ACCOUNT_ACTIVITY SET ACCOUNT_ACTIVITY.ACC_SEQ = DCOUNT("ACC_ID","ACCOUNT_ACTIVITY","ACC_ID='1' AND ACC_DESC='CLEARING CHQ' AND UniqueAccountID<=" & UniqueAccountID);

This assumes you have a unique (e.g. autonum) keyfield on the ACCOUNT_ACTIVITY table.

It can even be used to do this for all ACC_DESC values by changing it into:

UPDATE ACCOUNT_ACTIVITY SET ACCOUNT_ACTIVITY.ACC_SEQ = DCOUNT("ACC_ID","ACCOUNT_ACTIVITY","ACC_ID='1' AND ACC_DESC='" & [ACC_DESC] & "' AND UniqueAccountID<=" & UniqueAccountID);

Clear ?

Nic;o)
0
 
koCheEAuthor Commented:
thanks pete, you have save me a lot of time.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now