Results 1 to 9 of 9
  1. #1
    Joe8915 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10

    Question combo box preventing duplicaties enteries

    First time here and a big shout out to all.



    I have attach a snapshot. As you can see I have several combo boxes. What I don't want to see is duplicates names. The user can only select it one time only.

    Listed below is how they are link together:

    On the Main form the Record source is:
    SELECT TblTimeCard2.Endtime, TblTimeCard2.ID, TblTimeCard2.[Employee Name], TblTimeCard2.Strttime, TblTimeCard2.TC_ID, TblTimeCard2.Qty, DateDiff("n",[Strttime],[EndTime]) AS Minutes, [Minutes]\60 & Format([Minutes] Mod 60,"\:00") AS [Total Hrs Work], (Nz(DateDiff("n",[Strttime],[Endtime])*[AHrRate]/60,0)) AS PayAmount, ([AHrRate]*[payAmount]) AS Ahratetimespayamount, [stdcost]*[Qty] AS xtdCost1, [minutes]/60 AS [Total Hrs Work1], TblTimeCard2.TotHrs, TblTimeCard2.Lane, TblTimeCard2.Batch, (Nz(DateDiff("n",[Strttime1],[Endtime1])*[AHrRate]/60,0)) AS PayAmount1, TblTimeCard2.dtd, TblTimeCard2.FresQty, TblTimeCard2.Washnvac, TblTimeCard2.MreServ, TblTimeCard2.ClockTypes, TblTimeCard2.ServiceDesc, TblTimeCard2.Emp1, TblTimeCard2.Emp2, TblTimeCard2.Emp3, TblTimeCard2.Emp4, TblTimeCard2.Emp5, TblTimeCard2.Emp6, TblTimeCard2.Emp7, TblTimeCard2.Emp8, TblTimeCard2.Emp9, TblTimeCard2.Emp10, TblTimeCard2.Emp11, TblTimeCard2.Emp12 FROM TblTimeCard2 ORDER BY TblTimeCard2.Strttime;

    The combo boxes:
    The Row Source is:
    SELECT QryEmpName.EmpFullName FROM QryEmpName ORDER BY QryEmpName.EmpFullName;
    The Control Source is:
    Emp1

    Just note all the combo boxes are the same as above except for the Control Source, that will refer to each Employee, example: Emp2,Emp3 etc.

    That is about all the information I can see. If you need to see how db is put together I can attach or email the shell of db.

    and big thanks to anyone who can help

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    this does not look like a normalized table structure, it seems you're storing your data like this:


    Code:
    FPK EmployeeID1 EmployeeID2, EmployeeID3...etc
    I'm using FPK to indicate foreign primary key

    instead of
    Code:
    FPK ItemID EmployeeID
    items 1 - 10...
    Am I correct?

    If I am the control source for your comboboxes can not be

    SELECT QryEmpName.EmpFullName FROM QryEmpName ORDER BY QryEmpName.EmpFullName;

    it would have to be (for example on the employee 1 field)

    SELECT QryEmpName.EmpFullName FROM QryEmpName ORDER BY QryEmpName.EmpFullName
    WHERE (EmpID <> forms!<formname>!<empID2> AND
    EmpID <> forms!<formname>!<empID3> AND
    EmpID <> forms!<formname>!<empID4> AND
    EmpID <> forms!<formname>!<empID5> AND
    EmpID <> forms!<formname>!<empID6> AND
    EmpID <> forms!<formname>!<empID7> AND
    EmpID <> forms!<formname>!<empID8> AND
    EmpID <> forms!<formname>!<empID9> AND
    EmpID <> forms!<formname>!<empID10>)

    For each combo box you have you'd have to have a similar statement excluding the name of only the current field you're were doing data entry on, and on top of that after every field had been updated you'd have to refresh the contents of every other combo box with the command

    me.combo1.refresh
    etc.

  3. #3
    Joe8915 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    rpeare, first thanks for the reply. You are so ever correct about the "normalized table structure" I could not come up with another idea on just how to do this one. I am just about completely lost on this one.

    Would you have any other ideas on how could accomplish this. I tried just using one single combo, but that did work as well. I need the user only select the name once per batch or time frame.

    There just has to be a way I can do this!!!

    Open to any suggestions, would even attach the db to review.

    Once again thanks for taking your time to respond.

    Joe

  4. #4
    Joe8915 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    repeare:
    I pasted and reconstructed the qry with the following:

    SELECT QryEmpName.EmpFullName, QryEmpName.Emp1, QryEmpName.Emp2, QryEmpName.Emp3, QryEmpName.Emp4, QryEmpName.Emp5, QryEmpName.Emp6, QryEmpName.Emp7, QryEmpName.Emp8, QryEmpName.Emp9, QryEmpName.Emp10, QryEmpName.Emp11, QryEmpName.Emp12 FROM QryEmpName ORDER BY QryEmpName.EmpFullName WHERE (EmpID <> forms!<FrmBatch2b>!<empID2> AND
    EmpID <> forms!< FrmBatch2b>!<empID3> AND
    EmpID <> forms!< FrmBatch2b>!<empID4> AND
    EmpID <> forms!< FrmBatch2b>!<empID5> AND
    EmpID <> forms!< FrmBatch2b>!<empID6> AND
    EmpID <> forms!< FrmBatch2b>!<empID7> AND
    EmpID <> forms!< FrmBatch2b>!<empID8> AND
    EmpID <> forms!< FrmBatch2b>!<empID9> AND
    EmpID <> forms!< FrmBatch2b>!<empID10>AND

    EmpID <> forms!< FrmBatch2b>!<empID11>AND
    EmpID <> forms!< FrmBatch2b>!<empID12>AND);

    Nothing happen I can still duplicate the name, boy this is
    aggravating

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    You have to also run the requery command on every combo box or it won't work and you'll still see what you're terming duplicates. As to your other post I have no clue what you're trying to do in this table or what your end result is so I don't really know what to tell you.

    in the ON EXIT property of each of your combo boxes you're going to have to requery all the other combo boxes on your form and your have to take out the <> marks in my query I use those to denote where there's variable name that I don't know that you would have to substitute so in your case (using your posted query) you'd have:

    Code:
    SELECT QryEmpName.EmpFullName, QryEmpName.Emp1, QryEmpName.Emp2, QryEmpName.Emp3, QryEmpName.Emp4, QryEmpName.Emp5, QryEmpName.Emp6, QryEmpName.Emp7, QryEmpName.Emp8, QryEmpName.Emp9, QryEmpName.Emp10, QryEmpName.Emp11, QryEmpName.Emp12 FROM QryEmpName ORDER BY QryEmpName.EmpFullName WHERE (EmpID <> forms!FrmBatch2b!EmpID2 AND
    EmpID <> forms!FrmBatch2b!empID3 AND
    EmpID <> forms!FrmBatch2b!empID4 AND
    EmpID <> forms!FrmBatch2b!empID5 AND
    EmpID <> forms!FrmBatch2b!empID6 AND
    EmpID <> forms!FrmBatch2b!empID7 AND
    EmpID <> forms!FrmBatch2b!empID8 AND
    EmpID <> forms!FrmBatch2b!empID9 AND
    EmpID <> forms!FrmBatch2b!empID10 AND
    EmpID <> forms!FrmBatch2b!empID11 AND
    EmpID <> forms!FrmBatch2b!empID12 AND)
    
    and in the ON EXIT property of your EMPID1 field you'd have to have

    me.empid2.requery
    me.empid3.requery
    ...
    me.empid12.requery

  6. #6
    Joe8915 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    Once again. thanks ever so much. I will give it whirl and see what happens.

  7. #7
    Joe8915 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    Well this looks like a good challange for me, why me....................ugh

    Once again a big thanks

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is why it's better to have a normalized structure

  9. #9
    Joe8915 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    10
    I think you said it in a nut shell. I guess I should start from scratch.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Preventing other values in combo box
    By cheese9799 in forum Forms
    Replies: 3
    Last Post: 02-14-2011, 03:31 PM
  2. Replies: 15
    Last Post: 12-03-2010, 10:14 AM
  3. Replies: 1
    Last Post: 02-03-2010, 08:27 AM
  4. Preventing multiple checkboxes
    By emerywang in forum Forms
    Replies: 2
    Last Post: 01-26-2010, 01:43 PM
  5. Data preventing Referential Integrity
    By RubberStamp in forum Access
    Replies: 0
    Last Post: 12-14-2008, 05:43 AM

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