Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8

    Dcount prevent duplicate

    i need your help
    i want to prevent duplicate recored in form which tabe have fields name 1- empname type text 2-bookno type number and bookdate type date who can i solve my problem with Dcount I try this code below but i have runtime error 3075.

    if DCOUNT ("*",[TMAIN],[bookno]=' "& me.bookno &"and [bookdate]= #" & me.bookdate& "#")
    pls if u don't mind help me to solve it
    > appreciate your help
    >

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You don't want the single quote before the numeric field, and you never started the double quotes in the criteria, and don't have the quotes at all in the table argument.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    You don't want the single quote before the numeric field, and you never started the double quotes in the criteria, and don't have the quotes at all in the table argument.
    thank you for your explanation, i correct it as you mention but after exeute this msg appear
    compile error, syntax error
    below the procedure , if u don't mind corret it if there is any mistake

    Private Sub letterdate_AfterUpdate()
    If DCount("*", "maintable", & "[bookno]=" & Me!bookno & "'and [bookdate]= #" & Me!bookdate & "#")> 0 Then
    MsgBox " letterno&letterdate found ", vbExclamation
    Me.Undo
    Cancel = True
    End If
    End Sub

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Closer; you've added an & at the beginning of the criteria that doesn't belong. You've also added a single quote after the bookno value that doesn't belong (if bookno is really a numeric data type). Replace that with a space.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by pbaldy View Post
    Closer; you've added an & at the beginning of the criteria that doesn't belong. You've also added a single quote after the bookno value that doesn't belong (if bookno is really a numeric data type). Replace that with a space.
    oh , relly it is work perfictlly thank youuuuuuuuuuuu ery much

  6. #6
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help, and welcome to the site!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8
    Hi;
    my Q. is who can i make a combobox in the header of form dataentery for empname to search and edit empinformation, where i have acombobox for empname to select empname to dataentery ,
    i'm trying to do it but i have only the idempname display in the combobox for search
    table name contian idempname type autonumber and empname type text
    the main table contian idempname type number and,empno type autonumber, job , gender ..etc
    the sub contain empno type number and other fieldes ..
    thanks in advanc

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm sorry, I don't really understand the question. If you want a combo to find people, you can use the combo wizard, choosing the third option "Find a record...".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8

    combo

    Quote Originally Posted by pbaldy View Post
    I'm sorry, I don't really understand the question. If you want a combo to find people, you can use the combo wizard, choosing the third option "Find a record...".
    yes i used the third option but the main table have only the idempname type number
    and i add the empname from combo i get the date from table have two fields empname autonumber and idempname text
    what i need i want to enter data empname from combo and search and edit from same form

  10. #10
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8
    Quote Originally Posted by ayamali View Post
    yes i used the third option but the main table have only the idempname type number
    and i add the empname from combo i get the date from table have two fields empname autonumber and idempname text
    what i need i want to enter data empname from combo and search and edit from same form
    i attached the database file

  11. #11
    ayamali is offline Novice
    Windows XP Access 2003
    Join Date
    Feb 2011
    Posts
    8
    اHi;
    yes i used the third option but the main table have only the idempname type number
    and i add the empname from combo i get the date from table have two fields empname autonumber and idempname text
    what i need i want to enter data empname from combo and search and edit from the same form

  12. #12
    Join Date
    Apr 2013
    Posts
    6
    I am trying to apply this process to my current database but need the code spelled out for me as I am not a programmer. I have 2007 but the database is in 2000. I have a table called subpoenatable, five of the field names are OfficersName, Case1, case2, case3, an eventnumber. The eventnumber field is an autonumber field and the primary key, I really prefer to leave that part alone. This table feeds a from called propertyroomdisposaltrack. On the propertyroomdisposaltrack form is a lookup field called combo172, this combo pulls a list of officers names from an employee table for validation entry. What I am hopeful to accomplish is create a warning and stop a duplicate entry when combo172 and case1, case2, or case3 have previously been entered into the system. Any help is greatly appreciated.

  13. #13
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've been out of the country, so had limited ability to connect. Did you get this sorted out? Your un-normalized design (repeating fields) will make things more difficult.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    Join Date
    Apr 2013
    Posts
    6
    Not sure if my last post made it. I am still stuck on this, I can do a design change but that will be a lot of work as the table is used to track subpoenas as well and will be interesting at best to redesign

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure I understand the data, so a sample or picture would help. If I understand correctly, you basically need to do 3 tests instead of the usual 1. You need to test the combinations of the combo and case1, then the combo and case2, etc.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Prevent Saving of Form
    By bburton in forum Access
    Replies: 4
    Last Post: 02-25-2011, 09:26 PM
  2. Replies: 2
    Last Post: 02-12-2011, 09:54 PM
  3. Prevent a form from closing
    By ksmithson in forum Forms
    Replies: 0
    Last Post: 07-15-2010, 12:49 PM
  4. Prevent Multiple bookings
    By Rory898 in forum Forms
    Replies: 2
    Last Post: 02-05-2010, 11:59 AM
  5. Prevent Duplicate Values on Combination of Two Fields
    By LornaM in forum Database Design
    Replies: 8
    Last Post: 05-05-2009, 11:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums