Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    62

    Unanswered: Setting criteria for field entry

    Hi,
    I'm not sure if this is possible as I can't seem to find anything on it!
    I have several fields. If I enter "1" in Field 7, how can I tell the database that if field 7 = 1, then field 3 must equal "1", "3" or "4"?

    I've tried different version of the above in an if statement but no luck! For example:
    IIF ([tblname]![field7] = "1" AND [tblname]![Field3] = "1" OR "3" OR "4", "", "msgbox")

    I'm not sure how to get the message box up either? This is the extent of my coding knowledge, putting in expressions in queries etc.
    any help would be great,
    thanks
    Sue

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    How are you entering data into field 7? Is it on a form? If so, then in the event OnExit for the box Field7 do something like

    PHP Code:
    If Me.Field7 1 then
         Redo
    :
         
    Result InputBox("What value would you like for Field 3?" _
                       vbnewline 
    "You must enter 1,3, or 4.""Field 3?")
         If 
    Result "1" or Result "3" or Result "4" Then
              me
    .Field3 Result
         
    Else goto Redo
         End 
    If
    End If 
    This is pretty crude way of doing it (and untested admitedly). If you want more of a listbox look, you could create a custom form that pops up on the exit of Field 7, then have these 3 values on that form that the user can select. This would require a little bit more programming, but might look better and be more user-friendly.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the only way to do this in plain vanila access is to do the data capture in a form and impose this sort of logic in visual basic functions called as required. Server databases can have triggers to impose this sort of logic, Access/JET doesn't support triggers

    Id suggest you create a function that checks the values of field3 & field7 (incidentally I think you should consider what you call your columns/variables in you tables Fieldx isnt particularly helpful).

    say

    private function IsValid() as boolean
    isValid=FALSE 'set a default value
    if field7=1 then
    if field3=1 or field3=3 or field3=4 then
    isvalid=true
    else
    isvalid=false
    msgbox (If you have set a value of 1 in field7 then fiiled3 must be one of 1,3 OR 4',vbcritical)
    endif
    else
    isvalid=true
    endif
    end function

    place a call to that fuicntion in the follwoing events
    field3 lostfocus
    field7 lostfocus
    Id also suggest you place a in the forms before update event but here use the form

    cancel=isvalid() 'the cancel stops updating the recordset if the settign is invalid


    to do this open the code window
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2004
    Posts
    62
    Hi,
    I have called my columns more appropriate names, I just thought this as an easier way of explaining what I needed.
    I'm not sure if I conveyed it very well.
    I have a form for different experiments (expno).
    Each experiment has several criteria, ie. date started, purpose, procedure used, category.

    The user will go in to the form and choose from a list box for each crtieria, i.e.
    Purpose - biological 1, toxicology 2, environmental 3. Then the will put in procedure and then category.
    When they get to category there are several options. If they choose a particular option, for the example No 1, then because of protocol, if this is 1, the Purpose must be either 2 or 3. If it isn't, the user would need to go back and check what they have chosen.
    Does that make sense?
    So
    If [category] = "1", then [purpose] must equal either "1" or "3". If it doesn't then ...msgbox "please check Purpose field".
    Does make it clearer?

    I really appreciate the help, the all that coding was a little beyond my knowledge. I vaguely understand but not sure where I would put it all!!
    thanks again,
    Sue

  5. #5
    Join Date
    Jul 2006
    Posts
    108
    open the prop. window of the [purpose] textbox and goto the events tab, find the on_update field and set it to "Event Procedure" and then click the 3 dots.. input this into the appearing window:

    Code:
    dim X as string = purpose.text
    
    if [category] = "1" AND X <> "1" OR X <> "3" then 'check criteria'
    msgbox("Please check purpose number. (invalid)") 'error message'
    [purpose].getfocus 'set cursor to purpose field
    'elseif category = 2 and X <> "2" OR X <> "4" then 'additional criteria'
    'msgbox("please check purpose number. (invalid)") 'additional criteria'
    endif
    i hope this helps you understand, ive color coded it as follows:
    red: check the purpose field being = to 1 or 3
    blue: Error message displayed
    Green : additonal cirteria, to enable it just remove the yellow single quote
    Last edited by loquin; 01-12-07 at 14:58. Reason: Lime green was unreadable on gray background...

  6. #6
    Join Date
    Apr 2004
    Posts
    62
    thanks for this. I've giving it a whirl. Doesn't seem to be working at the moment but it's probably something I'm going!?

  7. #7
    Join Date
    Apr 2004
    Posts
    62
    I can't seem to get it to work, this is what I have:

    Private Sub category_AfterUpdate()
    If Me.[category] = 1 Then

    Me.[purpose] = 1 Or Me.[purpose] = 3

    Else

    MsgBox "Please check"

    End If

    End Sub

    This sort of works but the message only comes up if category = anything but 1! Can I add a line in to say if purpose equals anything but 1 then do nothing, else if if does = 1, the category must = 1 or 3. If no do message?
    thanks
    Sue

  8. #8
    Join Date
    Apr 2004
    Posts
    62
    This line is in red - how to I make it fit!?!
    msgbox (If you have set a value of 1 in field7 then fiiled3 must be one of 1,3 OR 4',vbcritical)
    endif
    from healdem's message!

  9. #9
    Join Date
    Jul 2006
    Posts
    108
    Try putting my code into the purpose_after update code area

Posting Permissions

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