Out of Sequence 1

[OhBother30]

I have a Check Ledger form in a database. It is basically set up like your personal checkbook register. The user has requested a warning box to pop up if they enter a check number that is out of sequence. I'm not sure how to best do that. I can't autonumber the field because there will be ligitimate reasons they will have out of sequence situations in that department. They just basically want to call the user's attention to the possibility of a problem.

Any suggestions would be appreciated. Thanks in advance!

Andrea

 

[JPeters]

Andrea,


Maybe you can do this.

Setup this in an event procedure that you deem appropriate (place it after update of check number on a new record, or whatever)

First off open the recordset of the form.

Sub Form_Wherever()

Dim dbs as Database
Dim rst as Recordset
Set dbs = CurrentDB()
Set rst = dbs.OpenRecordset("tblFormSource&quot ' edit to your table or query
Dim CorrectNum as Integer

With rst
.MoveLast
CorrectNum = rst!CheckNum ' sets CorrectNum equal to the last checknumber
End With
rst.Close ' close the recordset

CorrectNum = CorrectNum + 1 ' add 1 to the check number
If Me.CheckNum <> CorrectNum Then
MsgBox (&quot;Check Number is out of sequence. Please correct.&quot;, vbOkOnly, &quot;Out of Sequence:&quot
Me.CheckNum = &quot;&quot;
Me.CheckNum.SetFocus
End If
Else
Exit Sub
End Sub

Now edit to correct the names of items, add milk, stir, and let sit for 5 hours. ;-)

I didn't test this, so it may need some minor editing in the code also.

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[OhBother30]

Thanks a bunch!
I had to make a couple of minor adjustments, but it works great!

Thanks again
Andrea

http://www.ohbotherpooh.com

 

[JPeters]

Glad it worked.. Whoops, just noticed a few mistakes of mine on the End If .. ;-) ... but you're VBA savvy, you figured it out.

Way to go!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------