Validate Data Entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form for a user to enter data. Once they type in all the
information for a record, they click an 'Add Record' button which saves the
record and moves the user to a blank, new record. How can I make this work
so that a check is performed to see if this record has already been entered
before...ie...look for a duplicate record? A duplicate would be determined
by records having the same Location and Part Number.
 
On the before update event of the form, you can write the code that check if
the record already exist, and if it does, stop the saving, and give a message

If not isnull(Dlookup("Location", "TableName", "[Part Number field Name] = "
& Me.[Part Number Field Name in the form] & " And [Location] = '" &
Me.[Location] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

In the dlookup above I assume that the Location is string and the Part
Number is a number field type.
If I was wrong, the string should have single quote ' before and after, and
the number doesn't
 
I'm writing this in VBA, and it doesn't seem to work. The fields that
determine a duplicate record are LOCATION and PART_NUM, and would be in the
table called ADDS. The fields on my form are also named LOCATION and
PART_NUM. Can anyone help me? Thanks.

Ofer said:
On the before update event of the form, you can write the code that check if
the record already exist, and if it does, stop the saving, and give a message

If not isnull(Dlookup("Location", "TableName", "[Part Number field Name] = "
& Me.[Part Number Field Name in the form] & " And [Location] = '" &
Me.[Location] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

In the dlookup above I assume that the Location is string and the Part
Number is a number field type.
If I was wrong, the string should have single quote ' before and after, and
the number doesn't
--
I hope that helped
Good luck


scorpiorc said:
I have created a form for a user to enter data. Once they type in all the
information for a record, they click an 'Add Record' button which saves the
record and moves the user to a blank, new record. How can I make this work
so that a check is performed to see if this record has already been entered
before...ie...look for a duplicate record? A duplicate would be determined
by records having the same Location and Part Number.
Click to expand...
Click to expand...
 
Try this

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = "
& Me.[PART_NUM] & " And [LOCATION] = '" &
Me.[LOCATION] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

That assuming that PART_NUM is a number and LOCATION is a text
If PART_NUM is a text field then try this

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = '"
& Me.[PART_NUM] & "' And [LOCATION] = '" &
Me.[LOCATION] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

Now, when you say that it doesn't work please specify the problem, do you
get an error message? if you do, please tell us which one.
--
I hope that helped
Good luck


scorpiorc said:
I'm writing this in VBA, and it doesn't seem to work. The fields that
determine a duplicate record are LOCATION and PART_NUM, and would be in the
table called ADDS. The fields on my form are also named LOCATION and
PART_NUM. Can anyone help me? Thanks.

Ofer said:
On the before update event of the form, you can write the code that check if
the record already exist, and if it does, stop the saving, and give a message

If not isnull(Dlookup("Location", "TableName", "[Part Number field Name] = "
& Me.[Part Number Field Name in the form] & " And [Location] = '" &
Me.[Location] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

In the dlookup above I assume that the Location is string and the Part
Number is a number field type.
If I was wrong, the string should have single quote ' before and after, and
the number doesn't
--
I hope that helped
Good luck


scorpiorc said:
I have created a form for a user to enter data. Once they type in all the
information for a record, they click an 'Add Record' button which saves the
record and moves the user to a blank, new record. How can I make this work
so that a check is performed to see if this record has already been entered
before...ie...look for a duplicate record? A duplicate would be determined
by records having the same Location and Part Number.
Click to expand...
Click to expand...
Click to expand...
 
Sorry to ask, but I'm having problems with the "'s. LOCATION is a numeric
field and PART_NUM is a text field. Can you tell me how to switch this code
around to make if work with those field types?


Ofer said:
Try this

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = "
& Me.[PART_NUM] & " And [LOCATION] = '" &
Me.[LOCATION] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

That assuming that PART_NUM is a number and LOCATION is a text
If PART_NUM is a text field then try this

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = '"
& Me.[PART_NUM] & "' And [LOCATION] = '" &
Me.[LOCATION] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

Now, when you say that it doesn't work please specify the problem, do you
get an error message? if you do, please tell us which one.
--
I hope that helped
Good luck


scorpiorc said:
I'm writing this in VBA, and it doesn't seem to work. The fields that
determine a duplicate record are LOCATION and PART_NUM, and would be in the
table called ADDS. The fields on my form are also named LOCATION and
PART_NUM. Can anyone help me? Thanks.

Ofer said:
On the before update event of the form, you can write the code that check if
the record already exist, and if it does, stop the saving, and give a message

If not isnull(Dlookup("Location", "TableName", "[Part Number field Name] = "
& Me.[Part Number Field Name in the form] & " And [Location] = '" &
Me.[Location] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

In the dlookup above I assume that the Location is string and the Part
Number is a number field type.
If I was wrong, the string should have single quote ' before and after, and
the number doesn't
--
I hope that helped
Good luck


:

I have created a form for a user to enter data. Once they type in all the
information for a record, they click an 'Add Record' button which saves the
record and moves the user to a blank, new record. How can I make this work
so that a check is performed to see if this record has already been entered
before...ie...look for a duplicate record? A duplicate would be determined
by records having the same Location and Part Number.
Click to expand...
Click to expand...
Click to expand...
 
Try this then

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = '"
& Me.[PART_NUM] & "' And [LOCATION] = " &
Me.[LOCATION])) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

--
I hope that helped
Good luck


scorpiorc said:
Sorry to ask, but I'm having problems with the "'s. LOCATION is a numeric
field and PART_NUM is a text field. Can you tell me how to switch this code
around to make if work with those field types?


Ofer said:
Try this

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = "
& Me.[PART_NUM] & " And [LOCATION] = '" &
Me.[LOCATION] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

That assuming that PART_NUM is a number and LOCATION is a text
If PART_NUM is a text field then try this

If not isnull(Dlookup("LOCATION", "ADDS", "[PART_NUM] = '"
& Me.[PART_NUM] & "' And [LOCATION] = '" &
Me.[LOCATION] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

Now, when you say that it doesn't work please specify the problem, do you
get an error message? if you do, please tell us which one.
--
I hope that helped
Good luck


scorpiorc said:
I'm writing this in VBA, and it doesn't seem to work. The fields that
determine a duplicate record are LOCATION and PART_NUM, and would be in the
table called ADDS. The fields on my form are also named LOCATION and
PART_NUM. Can anyone help me? Thanks.

:

On the before update event of the form, you can write the code that check if
the record already exist, and if it does, stop the saving, and give a message

If not isnull(Dlookup("Location", "TableName", "[Part Number field Name] = "
& Me.[Part Number Field Name in the form] & " And [Location] = '" &
Me.[Location] & "'")) Then
msgbox "Duplicate"
cancel = True ' Wont save the record
me.[Location].SetFocus
End If

In the dlookup above I assume that the Location is string and the Part
Number is a number field type.
If I was wrong, the string should have single quote ' before and after, and
the number doesn't
--
I hope that helped
Good luck


:

I have created a form for a user to enter data. Once they type in all the
information for a record, they click an 'Add Record' button which saves the
record and moves the user to a blank, new record. How can I make this work
so that a check is performed to see if this record has already been entered
before...ie...look for a duplicate record? A duplicate would be determined
by records having the same Location and Part Number.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top