Assigning numbers up to a certain limit

[rcorbett]

I have a field titled Box# which is unique. This Box# contains 81 slots for test tubes. There is a field titled Slot# also. I want to be able to enter in the Box# and have it automatically put in what the next available slot# is (1-81)for that box. In essence I want it to be able to remember the last slot # for a given Box# but also recognize when a new box # is inserted.

Thank you to all who can lend some support on this intriguing dilema for me. I am not a VB whiz, but can follow directions fairly well

Robert.

 

[Aqif]

Hi

Y don't try using DMax function with Where argument

On AfterUpdate event of ur Box#

Dim StrMaxSlot as String
StrMaxSlot=DMax("ID","YourTable","Box#=" & Me![Box#])
Me!ID=StrMaxSlot+1

Not sure that this is what you want. You can further elaborate what you want specifically.




Cheers!
ÙÇãá

 

[Aqif]

OOpps...forgot to include the maximum check. Your code should read this

On AfterUpdate event of ur Box#

Dim StrMaxSlot as String
StrMaxSlot=DMax("ID","YourTable","Box#=" & Me![Box#])

if StrMaxSlot=81 then
Msgbox("Maximum number reached&quot
Me.Form.Undo
Cancel=True
Else
Me!ID=StrMaxSlot+1
End if


Good Luck!




Cheers!
ÙÇãá

 

[rcorbett]

Aqif,

Thank you for the assistance - I am having some issues getting it to work though. I am receiving this error message: "Syntax error in date in query expression Box# =32"

The 32 is the box# I typed in. I will try to provide a little more info that may help. I want to be able to type in the box # each time a new person is entered. Each box contains 81 slots to put test tubes in. I want the Box# to remember what slot number is next for each box#.

I hope this helps - thank you for helping out.

 

[rcorbett]

Aqif,

Thank you for the assistance - I am having some issues getting it to work though. I am receiving this error message: "Syntax error in date in query expression Box# =32"

The 32 is the box# I typed in. I will try to provide a little more info that may help. I want to be able to type in the box # each time a new person is entered. Each box contains 81 slots to put test tubes in. I want the Box# to remember what slot number is next for each box#.

I hope this helps - thank you for helping out.

 

[Aqif]

Hi

I think it didn't liked the Fieldname Box#...as # sign is a reserved character. Try fieldname such as BoxNum and get back to me if u need further help.



Cheers!
ÙÇãá