Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2005
    Posts
    102

    Unanswered: If exist in Combo Box then do this....

    Hi guys,

    Okay I have a question, I have a form that has unbound controls except one combo box (comSSN). The combo box displays list of values (a lookup) that are stored in a query. On the form there is a SocialSecurityNumber unbound field that is bound to another field. There's a command button (cmdCreateSSN) as well. I want to insert a code in the command button that follows this condition.

    If SocialSecurityNumber already exist in the combo box list then
    MsgBox "You cannot duplicate a SSN"
    Else
    Create the record.

    What I want to accomplish is to prevent the user from duplicating a SSN. In other words when a user is inputting a number into the SocialSecurityNumber field, I want the form to lookup that value from the list (comSSN) to see if it exist, if it does then msgbox, if not then add record.

    Did that make sense, hope it did.

    Thanks guys...

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Where does the combo box get its information?
    oh yeah... documentation... I have heard of that.

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

  3. #3
    Join Date
    Dec 2003
    Posts
    17
    Public Sub cmdCreateSSN_Click()

    If IsNull(DLookup("[SSN Field]","tblSSNTable","[SSN Field] = '" & txtNewSSN & "'") Then
    DoCmd.RunSQL "INSERT INTO tblSSNTable ([SSN Field]) SELECT '" & txtNewSSN & "' AS NewSSN"
    Else
    MsgBox("SSN Already Exists",vbOkOnly+vbInformation,"Duplicate SSN")
    End If

    End Sub

  4. #4
    Join Date
    Jun 2005
    Posts
    102
    Quote Originally Posted by Teddy
    Where does the combo box get its information?
    I first created a query based on a table (tblSSNRecords) that contains duplicate SSN's then I grouped it, so it only shows unique SSN and I saved that query as qrySSNListing. When I created a combo box on the form, the wizard showed up asking where do i get the data for the combo box, I chosed to look it up using a table or query, i told the wizard to qrySSNListing... that's where the combo box gets its information...

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, now use benfinkel's solution and point it at qrySSNListing.
    oh yeah... documentation... I have heard of that.

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

  6. #6
    Join Date
    Jun 2005
    Posts
    102
    Okay, first thanks for both of your responses. Second, sorry if I misled you somewhere, because I used SSN as examples, I should have used the real names of the objects and tables, etc in my database. So I decided to attached an example, so you guys can show me how to accomplish what I am trying to do. Please download the attachment, unzip it and open the DB. Once opened, look at and open the form frmPOEntry in design view. You should see a unbound text box bound to another textbox. That unbound textbox is used for data input. Whatever is inputted in there, I want it to look for that record (PONumber) in the combo box (comPONumber) that is in that form. If the PO Exist then MsgBox "Duplicate PO"..... I'm going to insert the code that you guys provide into the Create PO command button. I'm sorry again, I hope this clear things up. Thanks you guys.
    Attached Files Attached Files

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Do you understand the concept of what we're trying to say? If you do, simple control names shouldn't hold you back...
    oh yeah... documentation... I have heard of that.

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

  8. #8
    Join Date
    Jun 2005
    Posts
    102
    Quote Originally Posted by Teddy
    Do you understand the concept of what we're trying to say? If you do, simple control names shouldn't hold you back...
    That's the problem I don't... I tried applying that code and changing the names but it gives me a compile error

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You know, lately I've been accused of being on a nazi'esque rampage towards people who try to rip code off forums without knowing what it does... How do you know the code you just tried to run wouldn't thoroughly compromise your system? Maybe it was intentionally malicious to make sure you knew what it was. Never, ever, EVER run VBA code in your applications if you don't understand what it does.

    I'm just sayin...



    With that out of the way, what we're suggesting is to look a little deeper. Don't bother looking through the combobox to see if an entry exists, look into the table/query the combo box gets its information from instead. That's what the code posted earlier would do.


    If IsNull(DLookup("[SSN Field]","tblSSNTable","[SSN Field] = '" & txtNewSSN & "'") Then
    Lets break this down. If you don't know what the "If" and "Then" words do, stop RIGHT NOW and go snag yourself a good tutorial on VBA for Access, trust me.

    IsNull() is a simple function to check for null. We're using it here to find out if DLookup() returns a value or not.

    DLookup(), you guessed it, looksup information in a table. This scenario is looking in a table called "tblSSNTable" for rows that have a field "[SSN Field]" that match whatever the current value of txtNewSSN is. If it finds one, the value will obviously not be Null, and you can derive that your SSN is already present.

    DoCmd.RunSQL "INSERT INTO tblSSNTable ([SSN Field]) SELECT '" & txtNewSSN & "' AS NewSSN"
    This bit is executing an insert statement if an existing SSN was not found.

    Else
    MsgBox("SSN Already Exists",vbOkOnly+vbInformation,"Duplicate SSN")
    End If
    Relatively self explanatory, pop up a message box if the SSn WAS found and abort the insert.



    If you dont' understand any of those functions, I highly, HIGHLY recommend perusing the help file and checking out the examples, it will help you immensly.
    oh yeah... documentation... I have heard of that.

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

  10. #10
    Join Date
    Jun 2005
    Posts
    102
    Thanks a lot Ted for the "Address me like I was a 2 year old" explanation. I unlike others do not resent your thorough explanation. Rather I greatly appreciate that you took the time to explain this to me. However, I do understand how to read if and else then do this statements. I'm just not familiar with the Dlookup function. Again, I commence and thank you in taking the time to help. And also thanks to you benfinkel for the help you provide as well.

Posting Permissions

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