1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Complex form field validation

Discussion in 'Business Applications' started by pronaus, Sep 21, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. pronaus

    pronaus Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    6
    I'm trying to make a simple form but one of the fields needs more complex validation than it allows me to do. Basically, the user chooses a material, enters the number of units and their combined weights. Another field calculates their average weight (total weight/units). I have a table specifying the range that each material's average unit should weight. I'm trying to make access validate the calculated average weight field (or something equivalent) according to the material chosen by the range specified in the other table.

    I'd rather not forgo the table with the ranges by hard coding the ranges in VBA so that users could change the ranges.

    I've put a relationship for materials from each table and display the range in the form but now I can't enter in a new entry.

    I need help.
     
  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Don't you already know what the average weight for the item is? Why let the end user enter the weight at all?
     
  3. pronaus

    pronaus Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    6
    We need the exact weights to figure out pricing down the line. The avg weight validation would simply make sure the weights are with in reason.
     
  4. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Do you know the exact weights of a given item? You can put that directly into the item table and I have seen quite a few databases that do include item weights for shipping calculations, etc.

    I am assunimg from the KitID that you are creating a "kit" based on seperate items. I think you need to break it down to the individual items and create a BOM table for your kits. Then your kits will always be consistent, you will always have weights that are right on and save you a lot of time. I have done something similar with home models and the plumbing fixtures that went into each model.
     
  5. pronaus

    pronaus Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    6
    Well, this database will be used to record materials being processed in a facility so each unit will be slightly different but I do have reasonable ranges for each type of material. The user will be able to put in exact weights of a number of units... sometimes there will be 4 bales of material 1 that weight 12000lbs all together other times it'll be 5 bales of material 2 that weight 20000lbs. I'd like to protect against typos by saying that material 1 has an acceptable avg bale weight range of 2200lbs and 3200lbs and material 2 has an acceptable avg bale weight range of 3600lbs and 4300lbs (material 2 is denser than material 1). Unfortunately, I need to be able to record the variability in bale weights.
     
  6. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Gotcha. If you have the ranges you can vaildate against the values in a given table. Are you storing the high and low numbers in seperate fields in your table?

    Let me see if I can give you an example of some VBA to run when the users input changes.

    Would it be possible to post a sample minus any confidential info?
     
  7. pronaus

    pronaus Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    6
    Sure not a problem.
     

    Attached Files:

  8. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    I can get it to work with the exception of getting the range to work. I will keep at it for a while and post back
     
  9. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Rockn, let me know if you need an assist with the range.
     
  10. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    The ranges are kicking my butt at the moment. I am trying to do it all on one form so it will compare what the person enters in the pounds text box and the afterUpdate event will see if that number is between the min and max. If it is below or above it will pop up a text box and reset the pounds to blank and then set the focus to the pounds box again. No matter what I use in the VBA it will always throws the error.

    If Me.Pounds >= Me.Text48 And Me.Pounds <= Me.Text50 Then
     
  11. pronaus

    pronaus Thread Starter

    Joined:
    Sep 20, 2011
    Messages:
    6
    I was trying to figure out how to use a select case code to do this but I wasn't able to get anywhere.
     
  12. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    We will figure it out. I am sure OBP has a sampl db already with what you are trying to accomplish.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Rockn, what error does it throw up?
    What values do you get for text48 & text50?
     
  14. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    I get the correct values for the text boxes based on what is in the table, but it will not validate as being between those two numbers. Would I declare them as integers or variables.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You shouldn't have to declare them if they are form controls.
    I can't see anything wrong with your line of code, but you haven't sadi what error description you are getting.
    It could be that you need to use something like
    If Me.Pounds >= val(Me.Text48) And Me.Pounds <= val(Me.Text50) Then
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1018714

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice