Access Visual Basic Code to Validate Field on Current Record Equals Value of Previous Record Plus 10

I have Access 2010 on a Windows 7 PC.

I have an Access form.  I just added a record and I'm creating a close form button.  Before the form closes I'd like to validate the NwMtrNum field on the record I just added equals the NwMtrNum field of the previous record plus 10.

I was able to originally add 10 to the previous record when I added the new record, but I can't seem to get it to see the previous record once the new record is added.  Note the "NwMtrNum" field can be overwritten and needs to be changed sometimes which is why I want to check it.  This way when it's changed I can provide further instruction.

' Go to last record and get the value of the "NwMtrNum" field
    DoCmd.RunCommand acCmdRecordsGoToLast
        intCopyNum = Me.NwMtrNum.Value
        intCopyNum = intCopyNum + 10

    Me.AllowAdditions = True
        DoCmd.GoToRecord , , acNewRec
            Me.NwMtrNum.Value = intCopyNum


Can someone provide me with the VBA code to do this?

Thanks in advance!
Senniger1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Just so you understand, tables do not have first and last records, they are more like buckets of balls with each ball representing a record.  You reach into the bucket and you are never quite sure which ball you are going to get.

So, to define the "previous record" you need some sorting mechanism (date/time, autonumber, self generated increment), which should be used in a query to sort records.  Assuming that the code you posted above applies to a click event of a "New Record" button, I would use something like:

Dim strCriteria as string
strCriteria = "[CustomerID] = 10"   '<= example only
intCopyNum = NZ(DMAX("NwMtrNum", "queryName", strCriteria),0) + 10

You didn't give many details, but I'm assuming that this NwMtrNum is not necessarily unique, and might apply to a specific customer or order or some other value.

HTH
Dale
This will avoid the jumping around in records and the execution of any code associated with the Form_Current event.
0
Senniger1Author Commented:
Hi,

Thanks for your response.  Here's some more detail.  Hopefully this will help.

I have already clicked the Add New Record button and added the new number 1810.  My form looks something like this below.

Record#         Matter#            Client
6622                 1780                  TCN
6623                 1790                  GHB
6624                 1800                  RME
6625                 1810                  OTR

Now I'm getting ready to close my form, but I want to validate the value for the MATTER# for record 6625 is 10 more than the value of the MATTER# for record 6624.

Either I don't quite understand your code or it's not doing what I need it to do.

Thanks much!
0
Dale FyeOwner, Developing Solutions LLCCommented:
At the point in time where you are ready to "close the form", is the record already saved?

Normally, I put my checks in the Form_BeforeUpdate event, which allows me to cancel the update operation if the entries don't meet my validity checks.  In your case, that might look like:
Private Sub Form_BeforeUpdate(Cancel as integer)

    Dim lngPrevMatterNum as long
    Dim strCriteria as string
    Dim lngCopyNum as long

    strCriteria = "[NwMtrNum] < " & me.txt_NwMtrNum
    lngCopyNum = NZ(DMAX("NwMtrNum", "YourTableName", strCriteria),0) + 10
    if me.txt_NwMtrNum <> lngCopyNum then
        msgbox "Invalid entry in Matter#, value should be " & lngCopyNum
        me.txt_NwMtrNum.SetFocus
        Cancel = true
    end if

End Sub

Open in new window

Now, you could actually offer to update that value to the correct value if you wanted, but this is the simplest method.

Also, you might want to consider either using code in the Form_Current event to actually fill this value in for the user, something like:

Private Sub Form_Current

    if me.NewRecord = false then 
       Exit Sub
    Else
        me.txt_NwMtrNum = NZ(DMAX("NwMtrNum", "YourTableName"),0) + 10
    End If

End Sub

Open in new window

0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Senniger1Author Commented:
The record is not already saved when I click on the close form button unless it makes it through some other validating items in the code of that button.

My code is already completing the correct value automatically for the new record.

Here's the deal...  The user clicks on the Add Record button and the form adds the new record and autocompletes the NwMtrNum field which is 10 more than the value of the previous record's NwMtrNum field.

The user can use that number or they can overwrite that number to something else.  If they overwrite that number to something else, then when they click the Form Close button, I want it to check the value in the NwMtrNum field and make sure that it is exactly 10 more than the value of the previous record's NwMtrNum field.  

If it is not, then I will write something to prompt the user of this and then more things will be done which I will write in later.

Does this make sense?
0
Dale FyeOwner, Developing Solutions LLCCommented:
Yes, that makes sense.

I would use the Form_BeforeUpdate event with code similar to what I provided in my previous response.  The key is setting the CANCEL variable to TRUE when the conditions you require to be met have not been met.
0
Senniger1Author Commented:
Here is my actual code...

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngPrevMatterNum As Long
    Dim strCriteria As String
    Dim lngCopyNum As Long

    strCriteria = "[NwMtrNum] < " & Me.txt_NwMtrNum
    lngCopyNum = Nz(DMax("NwMtrNum", "NewMtr", strCriteria), 0) + 10
    If Me.txt_NwMtrNum <> lngCopyNum Then
        MsgBox "Invalid entry in Matter#, value should be " & lngCopyNum
        Me.txt_NwMtrNum.SetFocus
        Cancel = True
    End If
End Sub

When I try to compile it I get a compile error:  Method or data member not found.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Which line gets highlighted when you get the error message?

Have you stepped through the code to see what each step is doing?
0
Senniger1Author Commented:
Sorry.  It doesn't like the Me.txt_NwMtrNum.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Well, you have to use the control names in your form.  

Would that have been clearer if I used me.txt_YourMeterNumControlName?
0
Senniger1Author Commented:
I know we are almost there, but something just doesn't seem quite right with the code.

If this is your code...
Private Sub Form_BeforeUpdate(Cancel as integer)
    Dim lngPrevMatterNum as long
    Dim strCriteria as string
    Dim lngCopyNum as long

    strCriteria = "[NwMtrNum] < " & me.txt_YourMeterNumControlName
    lngCopyNum = NZ(DMAX("NwMtrNum", "YourTableName", strCriteria),0) + 10
    if me.txt_YourMeterNumControlName <> lngCopyNum then
        msgbox "Invalid entry in Matter#, value should be " & lngCopyNum
        me.txt_YourMeterNumControlName.SetFocus
        Cancel = true
    end if
End Sub


And my table name is NewMtr, my Record# field which is an autocomplete field is named NwMtrID and the Matter# field which needs to increment by 10 is named NwMtrNum, then my code would look like this, correct?

Private Sub Form_BeforeUpdate(Cancel as integer)
    Dim lngPrevMatterNum as long
    Dim strCriteria as string
    Dim lngCopyNum as long

    strCriteria = "[NwMtrNum] < " & me.txt_NwMtrID
    lngCopyNum = NZ(DMAX("NwMtrNum", "NewMtr", strCriteria),0) + 10
    if me.txt_NwMtrID <> lngCopyNum then
        msgbox "Invalid entry in Matter#, value should be " & lngCopyNum
        me.txt_NwMtrID.SetFocus
        Cancel = true
    end if
End Sub

And if that is the case, then I don't understand the strCriteria.
0
Dale FyeOwner, Developing Solutions LLCCommented:
The point of the DMAX domain function is to find the maximum value in field [NwMtrNum].  Now that I think about it, you may be on a better track than I was.  By using the NwMtrID in the criteria, you would be identifying the max value in the NwMtrNum field across all records other than the current record.  Using my previous method, you would get the max value less than the value entered by the user in the NwMtrNum field, but if the user inadvertently type 508 instead of 608, then my previous dLookup would return 500, and add 10 to that.  With your method, it would find 600 (assuming that the default value in the NwMtrNum field was 610 for the new record).  So, using your field and control names.
Private Sub Form_BeforeUpdate(Cancel as integer)
    Dim lngPrevMatterNum as long
    Dim strCriteria as string
    Dim lngCopyNum as long

    strCriteria = "[NwMtrID] <> " & me.txt_NwMtrID
    lngCopyNum = NZ(DMAX("NwMtrNum", "NewMtr", strCriteria),0) + 10
    if me.txt_NwMtrNum <> lngCopyNum then
        msgbox "Invalid entry in Matter#, value should be " & lngCopyNum
        me.txt_NwMtrNum.SetFocus
        Cancel = true
    end if
End Sub

Open in new window

BTW, it is easier to read if you tag your code segments by highlighting the text and clicking the "Code" button in the editor window.  Another advantage of this is that it provides the user with a method to open the code in another window, making it easier to read.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Senniger1Author Commented:
I copied your exact code and am getting the error:  Compile error:  Method or data member not found.

It doesn't like me.txt_NwMtrID.  See my screenshot.

Can you tell me what the txt_ part of this means.  Perhaps if I understand it, I can fix it.

And thanks for the advise about the code.  I didn't know that.
Error.jpg
0
Dale FyeOwner, Developing Solutions LLCCommented:
you need to replace that ("txt_NWMtrID") with whatever the name is of the control that displays the NwMtrID value.  Or if you are not displaying the value anywhere, you could probably replace it with:

me!NwMtrID

but that will only workin the NwMtrID is a field that has been included in the recordsource of the form.

Dale
0
Senniger1Author Commented:
I appreciated all your help and guidance on this.

Thank you!
0
Dale FyeOwner, Developing Solutions LLCCommented:
glad to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.