Test for Duplicate record.

  • Thread starter Aliennation2002
  • Start date
A

Aliennation2002

Hi I want to be able to create an input screen to add new records and test
for duplicates before saving.
I created an entry screen with unbound text windows. Software and version
txt windows will be saved into a single field called Strsoftwarewithversion.
I need to be able to test the result of Strsoftwarewithversion and if it
finds anthing a message will warn that I have a duplicate record, then it
will clear the two fields ready for me to try again.
This is what I have so done so far.

Option Compare Database

Private Sub SoftwareWithVersion_LostFocus()

End Sub

Private Sub Command12_Click()

End Sub

Private Sub BtnSave_Click()
Dim db As Database
Dim rst As Recordset
Dim Strsoftwarewithversion As String

Set db = CurrentDb
Set rst = db.OpenRecordset("IssueLog")

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
With rst
.AddNew
!SoftwareWithVersion = Strsoftwarewithversion
!TypeOfIssue = Me.TypeofIssueTxt
!DateRaised = Me.DateRaisedTxt
!RaisedBy = Me.RaisedByTxt
.Update
Me.SoftwareTxt.Value = ""
Me.VerTxt.Value = ""
Me.TypeofIssueTxt = ""
Me.DateRaisedTxt = ""
Me.RaisedByTxt = ""
End With
rst.Close

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub VerTxt_LostFocus()
Dim Strsoftwarewithversion As String
Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt


End Sub
 
O

Ofer

Add a dlookup to check thr values, put the code after this line

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
If not isnull(dlookup("SoftwareWithVersion","IssueLog","SoftwareWithVersion
='" & Strsoftwarewithversion & "'")) then
msgbox "Record Found"
Exit sub
End If

I would recomand saving the field as two fields in the table, and then use a
query to join the two fields, Its easier to join the two fields then seperate
between them in a future needs.
 
A

Aliennation2002

I put the code you showed in,but the if then staement showed up in red.

Private Sub VerTxt_LostFocus()
Dim Strsoftwarewithversion As String
Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
If not isnull(dlookup("SoftwareWithVersion","IssueLog","SoftwareWithVersion
='" & Strsoftwarewithversion & "'")) then
MsgBox "Record Found"
Exit Sub
End If



End Sub

Regards John
 
O

Ofer

Sorry, my mistake, put the code in the on click event

Private Sub BtnSave_Click()
Dim db As Database
Dim rst As Recordset
Dim Strsoftwarewithversion As String

Set db = CurrentDb
Set rst = db.OpenRecordset("IssueLog")

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
If Strsoftwarewithversion = " " then
msgbox "No value in fields"
Exit sub
End if
If not isnull(dlookup("SoftwareWithVersion","IssueLog","SoftwareWithVersion
='" & Strsoftwarewithversion & "'")) then
msgbox "Record Found"
Exit sub
End If

With rst
.AddNew
!SoftwareWithVersion = Strsoftwarewithversion
!TypeOfIssue = Me.TypeofIssueTxt
!DateRaised = Me.DateRaisedTxt
!RaisedBy = Me.RaisedByTxt
.Update
Me.SoftwareTxt.Value = ""
Me.VerTxt.Value = ""
Me.TypeofIssueTxt = ""
Me.DateRaisedTxt = ""
Me.RaisedByTxt = ""
End With
rst.Close

End Sub
 
A

Aliennation2002

Hi Ofer
I added the routine but get a compile error Sytax error on this code below

If not isnull(dlookup("SoftwareWithVersion","IssueLog","SoftwareWithVersion
='" & Strsoftwarewithversion & "'")) then

Below is the complete code

Option Compare Database

Private Sub SoftwareWithVersion_LostFocus()

End Sub

Private Sub Command12_Click()

End Sub

Private Sub BtnSave_Click()
Dim db As Database
Dim rst As Recordset
Dim Strsoftwarewithversion As String

Set db = CurrentDb
Set rst = db.OpenRecordset("IssueLog")

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
With rst
.AddNew
!SoftwareWithVersion = Strsoftwarewithversion
!TypeofIssue = Me.TypeofIssueTxt
!DateRaised = Me.DateRaisedTxt
!RaisedBy = Me.RaisedByTxt
.Update
Me.SoftwareTxt.Value = ""
Me.VerTxt.Value = ""
Me.TypeofIssueTxt = ""
Me.DateRaisedTxt = ""
Me.RaisedByTxt = ""
End With
rst.Close

End Sub

Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub VerTxt_LostFocus()
Dim db As Database
Dim rst As Recordset
Dim Strsoftwarewithversion As String

Set db = CurrentDb
Set rst = db.OpenRecordset("IssueLog")

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
If Strsoftwarewithversion = " " Then
MsgBox "No value in fields"
Exit Sub
End If
If not isnull(dlookup("SoftwareWithVersion","IssueLog","SoftwareWithVersion
='" & Strsoftwarewithversion & "'")) then
MsgBox "Record Found"
Exit Sub
End If

With rst
.AddNew
!SoftwareWithVersion = Strsoftwarewithversion
!TypeofIssue = Me.TypeofIssueTxt
!DateRaised = Me.DateRaisedTxt
!RaisedBy = Me.RaisedByTxt
.Update
Me.SoftwareTxt.Value = ""
Me.VerTxt.Value = ""
Me.TypeofIssueTxt = ""
Me.DateRaisedTxt = ""
Me.RaisedByTxt = ""
End With
rst.Close

End If



End Sub
 
O

Ofer

You put the code I provided on the lost focus, put it on the click event
before the saving, and lets try a different approach, there is no need to use
the dlookup.
Don't check the fields on the lost focus.

Private Sub BtnSave_Click()
Dim db As Database
Dim rst As Recordset
Dim Strsoftwarewithversion As String

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
If Strsoftwarewithversion = " " then
msgbox "No value in fields"
Exit sub
End if

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From IssueLog Where SoftwareWithVersion
='" & Strsoftwarewithversion & "'")
If not rst.eof then
msgbox "Record Exist"
Exit sub
End If
With rst
.AddNew
!SoftwareWithVersion = Strsoftwarewithversion
!TypeofIssue = Me.TypeofIssueTxt
!DateRaised = Me.DateRaisedTxt
!RaisedBy = Me.RaisedByTxt
.Update
Me.SoftwareTxt.Value = ""
Me.VerTxt.Value = ""
Me.TypeofIssueTxt = ""
Me.DateRaisedTxt = ""
Me.RaisedByTxt = ""
End With
rst.Close

End Sub
 
A

Aliennation2002

Hi Ofer
I've put it in the Place you suggest but I now get compile error Sytax error
on this code below

Set rst = db.OpenRecordset("Select * From IssueLog Where SoftwareWithVersion
='" & Strsoftwarewithversion & "'")

below is the full code

Option Compare Database


Private Sub BtnSave_Click()
Dim db As Database
Dim rst As Recordset
Dim Strsoftwarewithversion As String

Strsoftwarewithversion = Me.SoftwareTxt + " " + Me.VerTxt
If Strsoftwarewithversion = " " Then
MsgBox "No value in fields"
Exit Sub
End If

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From IssueLog Where SoftwareWithVersion
='" & Strsoftwarewithversion & "'")
If Not rst.EOF Then
MsgBox "Record Exist"
Exit Sub
End If
With rst
.AddNew
!SoftwareWithVersion = Strsoftwarewithversion
!TypeofIssue = Me.TypeofIssueTxt
!DateRaised = Me.DateRaisedTxt
!RaisedBy = Me.RaisedByTxt
.Update
Me.SoftwareTxt.Value = ""
Me.VerTxt.Value = ""
Me.TypeofIssueTxt = ""
Me.DateRaisedTxt = ""
Me.RaisedByTxt = ""
End With
rst.Close

End Sub
 
O

Ofer

Does the code:
Set rst = db.OpenRecordset("Select * From IssueLog Where SoftwareWithVersion
='" & Strsoftwarewithversion & "'")

Is in one line or, it break into two lines? it should be one line.
If it is one line, what is the compile error you are getting?
 
A

Aliennation2002

Thank you for your help
It was a line break issue one backspace and it worked great.
Regards John
 
O

Ofer

Try this, assuming that you have a field in the form called DeskTopVer

Set rst = db.OpenRecordset("Select * From IssueLog Where SoftwareWithVersion
='" & Strsoftwarewithversion & "' AND DeskTopVer ='" & me.DeskTopVer & "'")

I hope that helped
 
A

Aliennation2002

Hi Again Ofer
Just as I complete my Database my Boss asked for Another field called
DesktopVer to be added This means that for every DeskTopVer I could have a
repeat of the SoftwareAndVersion eg
DeskTopVer= DT1 and SoftwareAndVersion = Groupwise 5.5
DeskTopVer=DT2 and SoftwareAndVersion = Groupwise 5.5
what I now need is to check That there is only one software version for each
DT Record. before reporting a duplicate.
Can you Help On this?
 
A

Aliennation2002

Thanks that worked fine

Ofer said:
Try this, assuming that you have a field in the form called DeskTopVer

Set rst = db.OpenRecordset("Select * From IssueLog Where SoftwareWithVersion
='" & Strsoftwarewithversion & "' AND DeskTopVer ='" & me.DeskTopVer & "'")

I hope that helped
Click to expand...
 
Top