Prevent Duplicates

  • Thread starter Thread starter bw
  • Start date Start date
B

bw

I posted this in the TableDesign group, but was directed to Formscoding.
Please forgive me for posting multiple times.

The first three Field Names of my tblModules has the following information...
BMKey......Autonumber, Indexed (No Duplicates)
AddrID......Number, Indexed (Duplicates OK)
MonicsID...Number, Indexed No, Has a Lookup Table

Since MonicsID is not indexed, I can enter the same value from the lookup table
multiple times. However, I would like to prevent one specific value
from being duplicated.

Is this possible with some coding in the form that uses this table? If so, how?

Thanks,
Bernie
 
Hi Bernie,

Use the DLookUp Domain Aggregrate function. See:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;209479

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| From: bw <[email protected]>
| Newsgroups: microsoft.public.access.formscoding
| Subject: Prevent Duplicates
| Date: Wed, 16 Jun 2004 13:55:45 GMT
| Organization: Posted via Supernews, http://www.supernews.com
| Message-ID: <[email protected]>
| Content-Type: text/plain; charset="windows-1252"
| User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows 2000) Opera 6.05
[en]
| X-Complaints-To: (e-mail address removed)
| Lines: 18
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA07.phx.gbl!TK2MSFTNGXA06.phx.gbl!cpmsftngxa0
6.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshu
b.sdsu.edu!tethys.csu.net!nntp.csufresno.edu!sn-xit-02!sn-xit-01!sn-post-01!
supernews.com!corp.supernews.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.formscoding:236061
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| I posted this in the TableDesign group, but was directed to Formscoding.
| Please forgive me for posting multiple times.
|
| The first three Field Names of my tblModules has the following
information...
| BMKey......Autonumber, Indexed (No Duplicates)
| AddrID......Number, Indexed (Duplicates OK)
| MonicsID...Number, Indexed No, Has a Lookup Table
|
| Since MonicsID is not indexed, I can enter the same value from the lookup
table
| multiple times. However, I would like to prevent one specific value
| from being duplicated.
|
| Is this possible with some coding in the form that uses this table? If
so, how?
|
| Thanks,
| Bernie
|
|
|
 
Well, I've tried valiantly, but I just keep getting errors.

X = DLookup("[MonicsID]", "tblModules", "[MonicsID]= '" _
& Forms!frmModulesSubForm2![Mnemonics] & "'")

This portion of the code gives me the following error:
Run-time error '2465'
Microsoft Access can't find the field "MonicsID" referred to in your expression.

I have used both the Name and Control source in the expression with no success.

And note that the form is a SubForm, so I have replaced the second line with the
following:
& Me.Parent![Mnemonic] & "'")

Do you have a suggestion as to why I'm having the problems?

Thanks,
Bernie

Hi Bernie,

Use the DLookUp Domain Aggregrate function. See:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;209479

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| From: bw <[email protected]>
| Newsgroups: microsoft.public.access.formscoding
| Subject: Prevent Duplicates
| Date: Wed, 16 Jun 2004 13:55:45 GMT
| Organization: Posted via Supernews, http://www.supernews.com
| Message-ID: <[email protected]>
| Content-Type: text/plain; charset="windows-1252"
| User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows 2000) Opera 6.05
[en]
| X-Complaints-To: (e-mail address removed)
| Lines: 18
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA07.phx.gbl!TK2MSFTNGXA06.phx.gbl! cpmsftngxa0
6.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshu
b.sdsu.edu!tethys.csu.net!nntp.csufresno.edu!sn-xit-02!sn-xit-01!sn-post-01!
supernews.com!corp.supernews.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.formscoding:236061
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| I posted this in the TableDesign group, but was directed to Formscoding.
| Please forgive me for posting multiple times.
|
| The first three Field Names of my tblModules has the following
information...
| BMKey......Autonumber, Indexed (No Duplicates)
| AddrID......Number, Indexed (Duplicates OK)
| MonicsID...Number, Indexed No, Has a Lookup Table
|
| Since MonicsID is not indexed, I can enter the same value from the lookup
table
| multiple times. However, I would like to prevent one specific value
| from being duplicated.
|
| Is this possible with some coding in the form that uses this table? If
so, how?
|
| Thanks,
| Bernie
|
|
|
Click to expand...
 
Ok, I have made some progress, but I don't understand why everything is a match.

Here is the procedure:
For each of the four MsgBox's below, the values in order are:
1. frmModules
2. frmModulesSubForm1
3. 3
4. 1
and then I get the message "That value already exists".

The value doesn't exist, so why is there a match?

Thanks,
Bernie

Private Sub MonicsID_BeforeUpdate(Cancel As Integer)
Dim X As Variant

MsgBox Me.Parent.Name
MsgBox Form.Name
MsgBox DLookup("[MonicsID]", "tblModules")
MsgBox Me.Parent.frmModulesSubForm1.Form.MonicsID

X = DLookup("[MonicsID]", "tblModules", "[MonicsID]= " _
& Me.Parent.frmModulesSubForm1.Form.MonicsID)

On Error GoTo CustID_Err

If Not IsNull(X) Then
Beep
MsgBox "That value already exists", vbOKOnly, "Duplicate Value"
Cancel = True
End If

CustID_Exit:
Exit Sub

CustID_Err:
MsgBox Error$
Resume CustID_Exit

End Sub


Hi Bernie,

Use the DLookUp Domain Aggregrate function. See:
http://support.microsoft.com/default.aspx?scid=KB;EN-US;209479

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| From: bw <[email protected]>
| Newsgroups: microsoft.public.access.formscoding
| Subject: Prevent Duplicates
| Date: Wed, 16 Jun 2004 13:55:45 GMT
| Organization: Posted via Supernews, http://www.supernews.com
| Message-ID: <[email protected]>
| Content-Type: text/plain; charset="windows-1252"
| User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows 2000) Opera 6.05
[en]
| X-Complaints-To: (e-mail address removed)
| Lines: 18
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXA07.phx.gbl!TK2MSFTNGXA06.phx.gbl! cpmsftngxa0
6.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!newshu
b.sdsu.edu!tethys.csu.net!nntp.csufresno.edu!sn-xit-02!sn-xit-01!sn-post-01!
supernews.com!corp.supernews.com!not-for-mail
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.formscoding:236061
| X-Tomcat-NG: microsoft.public.access.formscoding
|
| I posted this in the TableDesign group, but was directed to Formscoding.
| Please forgive me for posting multiple times.
|
| The first three Field Names of my tblModules has the following
information...
| BMKey......Autonumber, Indexed (No Duplicates)
| AddrID......Number, Indexed (Duplicates OK)
| MonicsID...Number, Indexed No, Has a Lookup Table
|
| Since MonicsID is not indexed, I can enter the same value from the lookup
table
| multiple times. However, I would like to prevent one specific value
| from being duplicated.
|
| Is this possible with some coding in the form that uses this table? If
so, how?
|
| Thanks,
| Bernie
|
|
|
Click to expand...
 
Back
Top