Unanswered: If exist in Combo Box then do this....
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"
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.
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...
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.
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.
MsgBox("SSN Already Exists",vbOkOnly+vbInformation,"Duplicate SSN")
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.
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.