 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / October 2005Adding records
I have a form linked to a customer table. On the form is an Add New button. I need to check for duplicated codes after user enters the customer and not after all the form has been filled in. Can anyone tell me which event I should do this on i.e. Before Update, After Update etc etc. If a duplicate is found I put out my own error message, spacefill the customer code and go to the customer code control. Unfortunately though the code is blanked out, control goes to the next field and I finish up writing away a record with a blank customer code. Any ideas or places where I could look at how this should be handled? Thanks in anticipation. In the Before Update event oth the control where you enter the customer. For example purposes, I will assume you are using a numeric customer ID. I will also make up names to use: Private Sub txtCustID_BeforeUpdate If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " & Me.txtCustID)) Then MsgBox "This Customer Already Exitst" Cancel = True End If The Cancel = True line cancels the update of the customer id and leaves the focus in the customer ID text box. "tezza" w rote: > I have a form linked to a customer table. On the form is an Add New button. I > need to check for duplicated codes after user enters the customer and not [quoted text clipped - 7 lines] > Any ideas or places where I could look at how this should be handled? > Thanks in anticipation. Cheers.. The DLookup line had syntax errors. I have corrected it and it works fine now apart from when I hit the Close button. When I do this I get an Access error message about "Cannot have a null value in field". Because this is the key field I have set it so that it does not allow zero length. Is there a way round this. Presumably hitting the Close button causes the record to be written? > In the Before Update event oth the control where you enter the customer. For > example purposes, I will assume you are using a numeric customer ID. I will [quoted text clipped - 23 lines] > > Any ideas or places where I could look at how this should be handled? > > Thanks in anticipation. It will cause a record to be written if changes have been made. Modify your code to correct this problem: Private Sub txtCustID_BeforeUpdate If Not IsNull(DLookup("[CUST_ID]", "Customers", "[CUST_ID] = " & Me.txtCustID)) Then MsgBox "This Customer Already Exitst" Cancel = True Else If Me.Dirty And IsNull(Me.txtCustID) Then Cancel = True End If End If I am also curious about what syntax error there is in the DLookup. The only thing I see is that if your Customer ID is text instead of numeric, then it would need to be this: DLookup("[CUST_ID]", "Customers", "[CUST_ID] = '" & Me.txtCustID & "'") If that is the case, remember I said I was assuming a numeric value. If it is something else, I don't see it. > Cheers.. > The DLookup line had syntax errors. [quoted text clipped - 34 lines] > > > Any ideas or places where I could look at how this should be handled? > > > Thanks in anticipation. You are correct....the customer ID is text. The code change I made marries to yours exactly. I have amended the code for the extra bit but when I now hit the Close button nothing happens. It would appear that I can't close the form when the Customer ID is null. HELP!!!! Here is the code.... Private Sub Company_ID_BeforeUpdate(Cancel As Integer) If Not IsNull(DLookup("[Company ID]", "Customers", "[Company ID] = '" & Me.Company_ID & "'")) Then MsgBox "Duplicate Customer - Please use a unique code", vbOKOnly, "Serious Error" Cancel = True Else If Me.Dirty And IsNull(Me.Company_ID) Then Cancel = True End If End If End Sub > It will cause a record to be written if changes have been made. Modify your > code to correct this problem: [quoted text clipped - 56 lines] > > > > Any ideas or places where I could look at how this should be handled? > > > > Thanks in anticipation. HHHHHHMMMMMMMMMM I think I understand the problem. The only other thing I can think of would be to put the test for Me.Dirty and IsNull(Me.Company_ID) in the form's Unload event. The Close event doesn't have a Cancel in it. So, try taking that new part out, and putting it in the form Unload Event and see what happens. > You are correct....the customer ID is text. > The code change I made marries to yours exactly. [quoted text clipped - 77 lines] > > > > > Any ideas or places where I could look at how this should be handled? > > > > > Thanks in anticipation. Hi Have done that. Get an Access message telling me that the Close action was cancelled. I can't close the form. Funnily enough, if I click on the Add Customer button, all fields are blanked as you would expect and if I press the Close button then the Form closes. If I check the Customer table it is ok, there isn't an empty record. However, if I click on the Add Customer button, type in a duplicate code, get the error message about duplicates, highlight the customer code that I entered, press Del followed by Close then when I check the Customer table it now contains a blank record? What is the difference between the two? > HHHHHHMMMMMMMMMM > I think I understand the problem. The only other thing I can think of would [quoted text clipped - 84 lines] > > > > > > Any ideas or places where I could look at how this should be handled? > > > > > > Thanks in anticipation. The difference is that in the first case, the form is not dirty, but in the second case, you have entered data, and the form is dirty. Try adding the Me.Undo below. I think that should cure the problem. If Me.Dirty And IsNull(Me.Company_ID) Then Cancel = True Me.Undo End If > Hi > Have done that. [quoted text clipped - 97 lines] > > > > > > > Any ideas or places where I could look at how this should be handled? > > > > > > > Thanks in anticipation. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.