Solved

VBA function to create a sequence number

Posted on 2004-04-07
3
506 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.
0
Comment
Question by:koCheE
3 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 250 total points
ID: 10772809
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10772887
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
 

Author Comment

by:koCheE
ID: 10773119
thanks pete, you have save me a lot of time.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question