combo box preventing duplicaties enteries

[Joe8915]

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

[rpeare]

this does not look like a normalized table structure, it seems you're storing your data like this:

FPK EmployeeID1 EmployeeID2, EmployeeID3...etc

I'm using FPK to indicate foreign primary key

instead of

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.

[Joe8915]

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

[Joe8915]

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

[rpeare]

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:

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

[Joe8915]

Once again. thanks ever so much. I will give it whirl and see what happens.

[Joe8915]

Well this looks like a good challange for me, why me....................ugh

Once again a big thanks

[rpeare]

This is why it's better to have a normalized structure

[Joe8915]

I think you said it in a nut shell. I guess I should start from scratch.