Sequential numbering macro not working

  • Thread starter Thread starter dp724
  • Start date Start date
D

dp724

I have a form called AddTransaction based on a table called tbl_Transaction.
The table has three fields TransactionID(PK/autonumber), TransactionNo(#),
ProjectID(#), among others. I want the TransactionNo field to record new
transactions, starting from number 1, based on the ProjectID. So when I add a
transaction it assigns the next sequential number, for it’s ProjectID. If a
new ProjectID is added, the TransactionNo should start with number 1. The
TransactionNo for each ProjectID starts with 1.

I’ve tried using the macro below, which I adapted from another posting (
http://www.accessmonster.com/Uwe/Forum.aspx/access-macros/5859/auto-numbering-of-a-kind
), but nothing happens. No number appears on the table.

Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID] ='&[ProjectID]&'"),
0)+1

Would greatly appreciate any help.
Dave
 
Dave,

First of all, the expression that you showed us does not follow the
syntax shown in the post referred to. Incorrect quote characters, and
missing spaces.

It depends on whether ProjectID is number or text.

Number:
Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID]=" &
[ProjectID]),0)+1

Text:
Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID]='" &
[ProjectID] & "'"),0)+1

I'm not sure whether that us the entire problem, but it's a good start
to get that sorted first. If is still doesn'e work for you, please let
us know the details of the macro, and the event that it is being run on.
 
Steve, Thanks for picking up on my errors and writing back to help out.
Unfortunately, I worked your expression in to the form, but the numbering is
still not appearing on the table. I thought perhaps, that because I wasn’t
typing the ProjectID (Datatype=Number) in to the form(tried it in the Dirty
event) , I should try using it in to the AfterUpdate event of another field,
I worked it in to txtTransactionType field (Datatype=Text) using the
expressions below, but still nothing. I’d really like to see the
TransactionNo show up on the form when the form is ‘Dirty’ using code, so
when anything is typed on the form, the next TransactionNo for that ProjectID
shows up. Here’s a little more. My form is actually a subform, linked by a
combo box (ProjectID drop down); when the transactions are entered they
appear on the table listed along with the ProjectID. The ProjectID is not
typed in the form. I can’t use a macro the in the AfterUpdate of the combobox,
because I have a Setfocus procedure which moves the focus to a particular
field box. Ideally, I would like to have the setfocus procedure and
transaction numbering happen at the same time. This is all mostly new to me,
2months on the job, but I’m a really fast learner.

Most appreciative.
Dave

Steve said:
Dave,

First of all, the expression that you showed us does not follow the
syntax shown in the post referred to. Incorrect quote characters, and
missing spaces.

It depends on whether ProjectID is number or text.

Number:
Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID]=" &
[ProjectID]),0)+1

Text:
Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID]='" &
[ProjectID] & "'"),0)+1

I'm not sure whether that us the entire problem, but it's a good start
to get that sorted first. If is still doesn'e work for you, please let
us know the details of the macro, and the event that it is being run on.
I have a form called AddTransaction based on a table called tbl_Transaction.
The table has three fields TransactionID(PK/autonumber), TransactionNo(#),
Click to expand...
[quoted text clipped - 13 lines]
Would greatly appreciate any help.
Dave
Click to expand...
Click to expand...
 
Dave,

If I understand the circumstances correctly, I would expect that a
SetValue action in your macro is what you need, and the form's Before
Insert event would be the place for it. The arguments of the SetValue
action would be like this...
Item: [TransactionNo]
Expression: Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID]="
& [ProjectID]),0)+1
 
Steve,

Thanks so much, it's working.

All the best.
Dave

Steve said:
Dave,

If I understand the circumstances correctly, I would expect that a
SetValue action in your macro is what you need, and the form's Before
Insert event would be the place for it. The arguments of the SetValue
action would be like this...
Item: [TransactionNo]
Expression: Nz(DMax("[TransactionNo]","Tbl_Transactions","[ProjectID]="
& [ProjectID]),0)+1
Steve, Thanks for picking up on my errors and writing back to help out.
Unfortunately, I worked your expression in to the form, but the numbering is
Click to expand...
[quoted text clipped - 13 lines]
transaction numbering happen at the same time. This is all mostly new to me,
2months on the job, but I’m a really fast learner.
Click to expand...
Click to expand...
 
Back
Top