We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

VBA function to create a sequence number

koCheE
koCheE asked
on
Medium Priority
577 Views
Last Modified: 2008-03-17
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.
Comment
Watch Question

CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
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)

Author

Commented:
thanks pete, you have save me a lot of time.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.