Results 1 to 11 of 11

Thread: if statement

  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Question Unanswered: if statement

    This is probably a very easy one, I just am stumped!

    I have a combo box that users enter a number I want to make sure it is a valid number in my source (a Table with the ID number).
    If they enter a number that is not in the list I would like:

    a message box to appear
    Ask them to enter a valid number
    with just an OK button
    and then take them back to the same combo box to enter the new data.

    Any help would be greatly appreciated.

    Michael
    Gotta to do some code

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's a couple ways you can do it. You can put some code behind the control itself (probably in the OnEnter or something comparable), or you could put a validation rule on the field in the raw table.

    Alternately, you could just populate the combo box with the relevant numbers to begin wth, set it to not allow new entries, and be done with it.

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Set the Properties of the Combo box to 'Limit to List' = YES
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    If you set your combobox row source to the values in the table that you require and set the combo box "limit to list" property to true (or yes, can't remember which). This prevents people entering a number not in your table. For the next bit, put something like this code in the combo box "not in list" event:

    msgbox "This is not a valid entry. Please enter a value from the list.", , "Invalid Entry"

    me.cmboBox.setfocus

    Hopefully that should do it. Haven't checked but I think it's all there

  5. #5
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thanks for the quick response

    I definetly want to write code behind the after update

    I just want a created msgbox to appear that relates specifically to this box

    If I limit to list and they enter an invalid number, I would like to give them a relivant message. Not just an invalid message.
    Gotta to do some code

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can pop whatever message you want in there; it was just an example. put in as much info and help as you like. you can quote back the number they put in, suggest valid entries, ask them if they want to add this value to the list of valid entries etc..

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Private Sub Combo!_NotInList(NewData As String, Response As Integer)

    End Sub

    The Newdata hold the Value of the Not in List

    can't remeber what the Response is

    Think Microsoft has Some Code Example
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    That would be great to put it in the NotInList but I am not sure what I should write?
    Gotta to do some code

  9. #9
    Join Date
    Apr 2004
    Location
    South Arica
    Posts
    125
    Code:
    Private Sub cboName_NotInList(NewData As String, Response As Integer)
    Dim strMessage As String
    Dim dbGPData As Database
    Dim rstTitle As DAO.Recordset
    
        strMessage = NewData & "Is not a valid option,"
        Response = acDataErrContinue
    End Sub

  10. #10
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    How will it know that the data is not valid unless I have an if statement or some kind of camparison?

    Help

    Michael
    Gotta to do some code

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The NotInList event only occurs if someone enters a value that isn't in the drop down list (i.e. is not an entry in the table/ query you use as the row source). Therefore, if the event triggers the entry is either invalid or your list is incomplete. That's where the NewData variable comes into play, for example

    Private Sub cboName_NotInList(NewData As String, Response As Integer)

    dim strResp as string
    dim strMsg as string
    dim strSQL as string

    strMsg = NewData & " is not in the list. Add it to the list?"

    strResp = msgbox strMsg, vbYesNo, "Add to list?"

    if strResp = vbYes then

    Response = acDataErrAdded

    strSQl= insert into TableName (FieldName) values ('" & newdata & "')"

    docmd.runsql strSQL

    'or
    'currentdb.execute strSQl dbFailOnError
    'if you use DAO

    Else

    Response = acDataErrContinue

    end if

    End Sub

    This offers the user the option to add the new value into the list, therefore making it valid. An option, anyway.

Posting Permissions

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