Avatar of Senniger1
Senniger1
 asked on

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!
VBAWindows 7Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

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

ASKER
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!
Dale Fye

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

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Senniger1

ASKER
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?
Dale Fye

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

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

Which line gets highlighted when you get the error message?

Have you stepped through the code to see what each step is doing?
Senniger1

ASKER
Sorry.  It doesn't like the Me.txt_NwMtrNum.
Dale Fye

Well, you have to use the control names in your form.  

Would that have been clearer if I used me.txt_YourMeterNumControlName?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Senniger1

ASKER
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.
ASKER CERTIFIED SOLUTION
Dale Fye

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Senniger1

ASKER
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
Dale Fye

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Senniger1

ASKER
I appreciated all your help and guidance on this.

Thank you!
Dale Fye

glad to help