Begginer needs help

[xoymas]

Hi guys I'm trying to learn to put code on access but so far I haven't been succesfull. For example I tried to test a couple lines of code to check that a form checks for duplicates if I enter a persons name more than once.
here is the code.
Private Sub State_AfterUpdate()
IfNot IsNull(DLookup("[LastName]", "Customers", "CustomerID=" & Forms!Customers!LastName & "&quot Then MsgBox ("That entry used,blah..&quot
End If

End Sub
I get a Compaile error and I do not know how to find out what is wrong.
Thanks

 

[Terpsfan]

Your first problem is the event you used. You need to use Before Update instead. I use a Dlookup function in all my databases and it prevents duplicates. See if this works. I'm not sure if you were trying to check on just one field to find a duplicate so I used CustomerID. Often I'll use Firstname, Lastname and Accountnumber.


Private Sub State_BeforeUpdate()
IfDLookup("[LastName]", "Customers", "CustomerID=" & Forms!Customers!CustomerID&quot Then MsgBox ("That entry used,blah..&quot
End If

End Sub

 

[xoymas]

Hi I wanted to thank you for your response I tried to use the sollution you sent me but it doesn't work because I think I put it in the wrong place I do not get any error message when I put a duplicate record. May be you can tell me exactly in what part of the form code I'm suppose to put it. I have very little experience with code.Thank you for your time.

 

[CosmoKramer]

I put the following code in the "On click" section of a command button on my form to add a record:

If Not IsNull(strX = DLookup("[MedRec#]", "tblDemog", "[MedRec#] ='" _
& Forms!frmMain!MedRec# & "'&quot) Then
MsgBox ("This record already exists..........)
Forms!frmMain!MedRec# = ""
Forms!frmMain!MedRec#.SetFocus
Forms!frmMain.Refresh
GoTo Exit_Add_Record_Click
End If

It detects the duplicate, clears out the field on the form, and sets its focus forf re-entry.

 

[Rohdem]

Just make the Last Name a primary key in the table, or at least an indexed field that does not allow duplicates. Then Access will handle the error checking for you. Mike Rohde
rohdem@marshallengines.com
"If builders built buildings the way programmers wrote programs, the first woodpecker to come along would destroy civilization!"

 

[xoymas]

Hi thank you for your response I tried the code but I got an error message. I guess is due to my lack of experience.
I copied the entire code on the add record command botton code.Here it isOption Compare Database.Form name is "Customers" and it is related w/ the customers which has the fields (FirsName,LastName,address table, products, orders, order details

Private Sub Add_Record_Click(If Not IsNull(strX = DLookup("[LastName]", "tblCustomers", "[MedRec#] ='" _ & Forms!frmMain!LastName & "'&quot) Then MsgBox ("This record already exists..........) Forms!frmMain!LastName = "" Forms!frmMain!LastName.SetFocus Forms!frmMain.Refresh GoTo Exit_Add_Record_ClickEnd If)

On Error GoTo Err_Add_Record_Click


DoCmd.GoToRecord , , acNewRec

Exit_Add_Record_Click:
Exit Sub

Err_Add_Record_Click:
MsgBox Err.Description
Resume Exit_Add_Record_Click

End Sub

Thanks