check for two fields instead of one

G

Guest

I found a link via this forum to Prevent dupes entered by a form

http://www.databasedev.co.uk/duplicates.html It works great!!!

However I need help in making it check two fields instead of one.
The below code checks for a duplicate record with a specific dtID. i.e.
Jun07=6 dtID
I have many project with this dtID. I need it to check for the DtID and
ProjectID. To make sure there is not a dupe for ProjectID=4 with DtID =6.

I am desperate for help
Please help. I am just learing VBA, and I am no so good yet.

Private Sub ReportDtID_BeforeUpdate(Cancel As Integer)
Dim DtID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DtID = Me.ReportDtID.Value
stLinkCriteria = "[ReportDtID]=" & DtID
'Check table for duplicate ID
If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Customer Impact for selected Report Date was
previously entered." _
& vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Verify the Report Date and make changes(as
needed).", _
vbInformation, "Duplicate Information"

If Me.ReportDtID = 1 Then
Me.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

'Go to record of original ID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Forms!f2CustImpactsEdit.Form!f2CustImpactsEditDetails.Form.Visible =
True

End If

Set rsc = Nothing

End Sub
 
G

Guest

Use a Boolean AND operation in the criteria for the DCount function call so
that it checks for the values of the DtID and ProjectID equalling, in
combination, those of the current record. Assuming both are number data
types you simply need to change the one line, that which assigns a value to
the stLinkCriteria variable:

stLinkCriteria = "DtID = " & DtID & " And ProjectID = " & ProjectID

Ken Sheridan
Stafford, England
 
G

Guest

I now egt msg about a missing operator and it highlights..

If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then

What am I doing wrong?

--
deb


Ken Sheridan said:
Use a Boolean AND operation in the criteria for the DCount function call so
that it checks for the values of the DtID and ProjectID equalling, in
combination, those of the current record. Assuming both are number data
types you simply need to change the one line, that which assigns a value to
the stLinkCriteria variable:

stLinkCriteria = "DtID = " & DtID & " And ProjectID = " & ProjectID

Ken Sheridan
Stafford, England

deb said:
I found a link via this forum to Prevent dupes entered by a form

http://www.databasedev.co.uk/duplicates.html It works great!!!

However I need help in making it check two fields instead of one.
The below code checks for a duplicate record with a specific dtID. i.e.
Jun07=6 dtID
I have many project with this dtID. I need it to check for the DtID and
ProjectID. To make sure there is not a dupe for ProjectID=4 with DtID =6.

I am desperate for help
Please help. I am just learing VBA, and I am no so good yet.

Private Sub ReportDtID_BeforeUpdate(Cancel As Integer)
Dim DtID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DtID = Me.ReportDtID.Value
stLinkCriteria = "[ReportDtID]=" & DtID
'Check table for duplicate ID
If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Customer Impact for selected Report Date was
previously entered." _
& vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Verify the Report Date and make changes(as
needed).", _
vbInformation, "Duplicate Information"

If Me.ReportDtID = 1 Then
Me.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

'Go to record of original ID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Forms!f2CustImpactsEdit.Form!f2CustImpactsEditDetails.Form.Visible =
True

End If

Set rsc = Nothing

End Sub
Click to expand...
Click to expand...
 
G

Guest

Please ignore last post the error mgs says
Runtime error 2001
You canceled previous operation and then highlights

If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then

--
deb


deb said:
I now egt msg about a missing operator and it highlights..

If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then

What am I doing wrong?

--
deb


Ken Sheridan said:
Use a Boolean AND operation in the criteria for the DCount function call so
that it checks for the values of the DtID and ProjectID equalling, in
combination, those of the current record. Assuming both are number data
types you simply need to change the one line, that which assigns a value to
the stLinkCriteria variable:

stLinkCriteria = "DtID = " & DtID & " And ProjectID = " & ProjectID

Ken Sheridan
Stafford, England

deb said:
I found a link via this forum to Prevent dupes entered by a form

http://www.databasedev.co.uk/duplicates.html It works great!!!

However I need help in making it check two fields instead of one.
The below code checks for a duplicate record with a specific dtID. i.e.
Jun07=6 dtID
I have many project with this dtID. I need it to check for the DtID and
ProjectID. To make sure there is not a dupe for ProjectID=4 with DtID =6.

I am desperate for help
Please help. I am just learing VBA, and I am no so good yet.

Private Sub ReportDtID_BeforeUpdate(Cancel As Integer)
Dim DtID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DtID = Me.ReportDtID.Value
stLinkCriteria = "[ReportDtID]=" & DtID
'Check table for duplicate ID
If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Customer Impact for selected Report Date was
previously entered." _
& vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Verify the Report Date and make changes(as
needed).", _
vbInformation, "Duplicate Information"

If Me.ReportDtID = 1 Then
Me.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

'Go to record of original ID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Forms!f2CustImpactsEdit.Form!f2CustImpactsEditDetails.Form.Visible =
True

End If

Set rsc = Nothing

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

It is now working. I am a complete dummy and mispelled my var.

Thank you for your help!!!
--
deb


Ken Sheridan said:
Use a Boolean AND operation in the criteria for the DCount function call so
that it checks for the values of the DtID and ProjectID equalling, in
combination, those of the current record. Assuming both are number data
types you simply need to change the one line, that which assigns a value to
the stLinkCriteria variable:

stLinkCriteria = "DtID = " & DtID & " And ProjectID = " & ProjectID

Ken Sheridan
Stafford, England

deb said:
I found a link via this forum to Prevent dupes entered by a form

http://www.databasedev.co.uk/duplicates.html It works great!!!

However I need help in making it check two fields instead of one.
The below code checks for a duplicate record with a specific dtID. i.e.
Jun07=6 dtID
I have many project with this dtID. I need it to check for the DtID and
ProjectID. To make sure there is not a dupe for ProjectID=4 with DtID =6.

I am desperate for help
Please help. I am just learing VBA, and I am no so good yet.

Private Sub ReportDtID_BeforeUpdate(Cancel As Integer)
Dim DtID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

DtID = Me.ReportDtID.Value
stLinkCriteria = "[ReportDtID]=" & DtID
'Check table for duplicate ID
If DCount("ReportDtID", "tCustImpacts", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning Customer Impact for selected Report Date was
previously entered." _
& vbCr & "You will now been taken to the record." _
& vbCr & vbCr & "Verify the Report Date and make changes(as
needed).", _
vbInformation, "Duplicate Information"

If Me.ReportDtID = 1 Then
Me.Undo
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
End If

'Go to record of original ID
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
Forms!f2CustImpactsEdit.Form!f2CustImpactsEditDetails.Form.Visible =
True

End If

Set rsc = Nothing

End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Edit code to use integer instead of text 1
if duplicate goto record 3
if error msg then 1
if error then 2
Searching Across Two Tables For A Record 3
If date field is 1 or not entered then prompt to choose before clo 1
Dlookup help for duplicate phone number entry and message box. 8
Checking multiple fields for Duplicate 18

Top