Duplicate Records

  • Thread starter Thread starter kaia via AccessMonster.com
  • Start date Start date
K

kaia via AccessMonster.com

Hi, im not great at access but i do have some knowledge, any help to the
problem would be appreciated. im using Access 2000, trying to create an A-
level ICT database.

basically the problem is ive got a main form and a subform included in the
main form and then 5 other subforms linked to the main one (using linking
tables) by buttons. ive tryed looking around the net for a solution to a
problem of in the subforms the user can select a record and then might go
on to select the same one again, i want to create my own error message to
appear saying that they have chosen a record more than once. i think i need
to use expression builder and the DLOOKUP fuctions or something from what i
have seen other people use. but when i try to apply it to my own situation
it doesnt work.

any help on what expression i should use?, what parts of the expression
will apply to my tables? etc. sorry if ive missed out half the information
that i should give this is my first post.
thanks kaia
 
Here's one I wrote more than 10 years ago to check for duplicates with a
text based PersonID Watch for line wrapping:

Private Sub txtPersonID_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Hand

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb

' Add quotes to Me!PersonID for a text type
' strSQL = "SELECT [PersonID] FROM tblPeople WHERE [PersonID] = """ &
Me![txtPersonID] & """"

strSQL = " SELECT [PersonID] FROM tblPeople WHERE [PersonID] = " &
Me![txtPersonID]
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then ' duplicate entry
Cancel = True
MsgBox "This Person ID is already allocated, please select another",
vbOKOnly, "Duplicate PersonID"
End If

ByBy:
Exit Sub

Err_Hand:
MsgBox Err.Description
Resume ByBy

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
would it be possible for you to tell me which each part does as i need to
annotate it for the report write up?.
thanks for your help
kaia.
 
when ive made all the changes to the expression i get an error message
saying that "the expression you have entered contains invalid syntax, you
may have netered a operand without an operator", no idea what ive done
wrong.
 
well if i dont understand how it works there isnt much point in it is there
 
Back
Top