Duplicated record

[Sam 44]

Hi I wonder if someone can help me with this.

I have this form with one combo box [Mail_ID] and one text box
[File_number] what I want its to have some data validation after the user
enter the data on the text box access verify if there’s already some Mail_ID
and File number with the same combination.

I can have the some Mail_ID duplicated and also the File_number but not the
same combination twice.

Hope I explain my self

I’m not really that good on VB (just learning) so the more detail help I get
will be appreciated.

 

[Ofer]

Create a function under the form, that will check for duplicate

Function FunctionName()
If not isnull(me.[Mail_ID]) and not isnull([File_number]) then
If not isnull(dlookup("[Mail_ID]","[TableName]","[Mail_ID] =" &
Me.[Mail_ID] & " And [File_number] = " & Me.[File_number])) then
msgbox "data exist"
FunctionName = False
End If
End if
End Function

Assuming that both fields are numbers, if they are string type it should
include a single quote befor and after the parameter
If not isnull(dlookup("[Mail_ID]","[TableName]","[Mail_ID] ='" &
Me.[Mail_ID] & "' And [File_number] = '" & Me.[File_number] & "'")) then

==========================================
On the before update event of each field call the function

If FunctionName = False then
cancel=true ' Wont let you exit the field
End if

 

[Sam 44]

on field File_number I use the after update event procedure and wrote

Private Sub File_number_AfterUpdate()
Function FunctionName()
If Not IsNull(Me.[Mail_ID]) And Not IsNull([File_number]) Then
If not isnull(dlookup([Mail_ID],[Mailing records],[Mail_ID] = &
Me.[Mail_ID] & And [File_number] = & Me.[File_number])) then
MsgBox "data exist"
FunctionName = False
End If
End Function

The field Mail_ID its a text field and the File_number its a number
I get the compile error after I enter data on File_number

Can you help me figure out what I'm doing wrong.

Thanks for your help.

Create a function under the form, that will check for duplicate

Function FunctionName()
If not isnull(me.[Mail_ID]) and not isnull([File_number]) then
If not isnull(dlookup("[Mail_ID]","[TableName]","[Mail_ID] =" &
Me.[Mail_ID] & " And [File_number] = " & Me.[File_number])) then
msgbox "data exist"
FunctionName = False
End If
End if
End Function

Assuming that both fields are numbers, if they are string type it should
include a single quote befor and after the parameter
If not isnull(dlookup("[Mail_ID]","[TableName]","[Mail_ID] ='" &
Me.[Mail_ID] & "' And [File_number] = '" & Me.[File_number] & "'")) then

==========================================
On the before update event of each field call the function

If FunctionName = False then
cancel=true ' Wont let you exit the field
End if

Hi I wonder if someone can help me with this.

I have this form with one combo box [Mail_ID] and one text box
[File_number] what I want its to have some data validation after the user
enter the data on the text box access verify if there’s already some Mail_ID
and File number with the same combination.

I can have the some Mail_ID duplicated and also the File_number but not the
same combination twice.

Hope I explain my self

I’m not really that good on VB (just learning) so the more detail help I get
will be appreciated.

 

[Ofer]

No, the function should be outside, and call the function from the before
update event and not from the after update event

Private Sub File_number_BeforeUpdate(Cancel As Integer)
If FunctionName = false then
cancel = true
end if
End Function

Function FunctionName()
If Not IsNull(Me.[Mail_ID]) And Not IsNull([File_number]) Then
If not isnull(dlookup([Mail_ID],[Mailing records],[Mail_ID] = &
Me.[Mail_ID] & And [File_number] = & Me.[File_number])) then
MsgBox "data exist"
FunctionName = False
End If
End Function

on field File_number I use the after update event procedure and wrote

Private Sub File_number_AfterUpdate()
Function FunctionName()
If Not IsNull(Me.[Mail_ID]) And Not IsNull([File_number]) Then
If not isnull(dlookup([Mail_ID],[Mailing records],[Mail_ID] = &
Me.[Mail_ID] & And [File_number] = & Me.[File_number])) then
MsgBox "data exist"
FunctionName = False
End If
End Function

The field Mail_ID its a text field and the File_number its a number
I get the compile error after I enter data on File_number

Can you help me figure out what I'm doing wrong.

Thanks for your help.

Create a function under the form, that will check for duplicate

Function FunctionName()
If not isnull(me.[Mail_ID]) and not isnull([File_number]) then
If not isnull(dlookup("[Mail_ID]","[TableName]","[Mail_ID] =" &
Me.[Mail_ID] & " And [File_number] = " & Me.[File_number])) then
msgbox "data exist"
FunctionName = False
End If
End if
End Function

Assuming that both fields are numbers, if they are string type it should
include a single quote befor and after the parameter
If not isnull(dlookup("[Mail_ID]","[TableName]","[Mail_ID] ='" &
Me.[Mail_ID] & "' And [File_number] = '" & Me.[File_number] & "'")) then

==========================================
On the before update event of each field call the function

If FunctionName = False then
cancel=true ' Wont let you exit the field
End if

Hi I wonder if someone can help me with this.

I have this form with one combo box [Mail_ID] and one text box
[File_number] what I want its to have some data validation after the user
enter the data on the text box access verify if there’s already some Mail_ID
and File number with the same combination.

I can have the some Mail_ID duplicated and also the File_number but not the
same combination twice.

Hope I explain my self

I’m not really that good on VB (just learning) so the more detail help I get
will be appreciated.