Results 1 to 5 of 5

Thread: If then trouble

  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: If then trouble

    I am developing a Pavement Management database and I have created a form that we can input data each year for each road segment in Ashland, Ma. Based on the criteria that was input into the form via combo boxes, I have a series of equations that run in the background to produce an overall Pavement Condition Rating. Due to the nature of the formula created the output values exceed 100 and are smaller than zero- I want to have a scale of 0-100. Therefore I am trying to create an If...then statement such that all values that are smaller than 0 are labled as 0 and all values exceeding 100 are labeled 100. I have created a code in VBA (not sure if it is correct) but the form is either not picking it up or it is just not working. The location where the output value of the equation stays and acts the same regardless of the VB code. What am I doing wrong?

    Sample of my code....

    Private Sub DPCR_BeforeUpdate(DPCR As Integer)

    If [DPCR] < "0" Then
    [DPCR] = "0"
    ElseIf [DPCR] > "100" Then
    [DPCR] = "100"
    End If
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Two things:

    First, your syntax is correct for a Text field, but you're apparently working with a Numeric field. You need to drop the Quotation Marks around 0 and 100, i.e.

    If [DPCR] < "0" Then

    should be

    If [DPCR] < 0 Then

    The Quotes are telling Access to look for Text values, not Numeric ones.

    Secondly, when Access generates an event for a Control's BeforeUpdate event, such as it generates for DPCR, you'll see

    Private Sub DPCR_BeforeUpdate(Cancel As Integer)

    not

    Private Sub DPCR_BeforeUpdate(DPCR As Integer)

    On native Access Subs such as the BeforeUpdate Sub, you cannot change the Sub Header like this.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Jun 2011
    Posts
    4
    Thanks Linq for that reply. I changed those syntax errors yet the text box is still unable to update for me. The code was not doing anything if I embedded it within the text box so I created a Macro where I used an OpenModule command to find my script then a RunCode command to execute it. Again, this failed to preform the simple script. I am not sure where it is failing. Do you have any thoughts on why this would be failing to execute? Thanks

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Since you're using free-hand entries anyway, and not pre-assigned inputs that would demand a combobox, why don't you use the InputBox() function? That way, you could test the input for proper range of value before doing any processing, and either fix invalid inputted values programatically with the code you have, or require the user to change the input to a value between 0 and 100.

    Sam

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by natemietk View Post
    ...The code was not doing anything if I embedded it within the text box so I created a Macro where I used an OpenModule command to find my script then a RunCode command to execute it.
    Sorry, I have no idea what you mean by "if I embedded it within the text box."

    Few of the developers here use Macros. They're usable for very simple, uncomplicated tasks, but not really suited for serious programming. And there's certainly no need to use OpenModule followed by RunCmd for this kind of thing.

    Some questions:

    1. What version of Access are you using?
    2. If version 2007/2010 is your file in a "Trusted" location?
    3. Verify, in Table Design View, the Datatype of the DPCR Field and post it back to us.
    4. Copy and paste the exact code you tried in the DPCR_BeforeUpdate event.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •