Conditional autonumber

[Guest]

Hello. Can everyone help me with is?

I have one table called [tbl_Auctions], with two fields:
.. auctionID (autonumber)
.. auctionDate (date)

I have another table called [tbl_Items] with four fields:
.. itemID (autonumber)
.. auctionID (number, linked to the previous table, one-to-many)
.. itemNumber (number)
- itemDescription

For each item in the [tbl_Items] I have to choose in which auction it will
belong to.
I want that [itemNumber] increases automatically for each item added, and
for that I predefined the following formula:
=Nz(DMáx("itemNumber";"tbl_Items"))+1
and it is working fine.

However, I would like that this itemNumber increases automatically but
depending the auction where it belongs to, I mean, for example,

Auction A
Item 1
Item 2
item 3
....

Auction B
Item 1
Item 2
Item 3
....

and not,
Auction A
Item 1
Item 2
Item 4

Auction B
Item 3
Item 5

I hope anyone understand my purpose.
Thank you in advance.
Best regards.
Acores

 

[Al Campagna]

acores,
You need to find the max ItemNumber FOR the indicated AuctionID, and assign that value
(+1)

Also, use a comma between Lookup arguements, not ; ...
I noticed the accent on the a in DMax... so I'm not sure if you're dealing with
anotherv language package, and the ( might be legitimate.
I'll use commas...
=Nz(DMáx("ItemNumber","tbl_Items", "AuctionID = " & AuctionID)) + 1
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."