Sequential numbering macro not working

[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

 

[Steve Schapel]

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.

 

[dp724 via AccessMonster.com]

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

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(#),

[quoted text clipped - 13 lines]

Would greatly appreciate any help.
Dave

 

[Steve Schapel]

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

 

[dp724 via AccessMonster.com]

Steve,

Thanks so much, it's working.

All the best.
Dave

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

[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.