Module Problem (I think)

[ErinL]

Hi again everyone -

I have racked my brain for days on this problem and cannot see where I am going wrong. Hopefully someone will be able to help...

I am attaching a sample database to this post because I think it would be easier to see it and be able to move through it than for me to post pieces and try to explain.

Here's my dilema: When the database opens it will open to a form. I have all the criteria set so all you have to do is click the "Assign Selectors" button.

The plan is to assign each person in a random order to a specific area. Once they are assigned, they should be removed from the "pool" of selectors to choose from so that each person is only assigned to one area.

This is not the case. The module works without any error messages but the selectors are duplicated across multiple areas.

I would appreciate any and all help with this one as I cannot look at it any longer.:banghead:

Thank you in advance!

 

[namliam]

Command28, really keeping default names of any control is a bad habit that you NEED TO BREAK yesterday. Give controls proper names....

You have some serious design issues in the database, your LFName in your table tblSelectorAssignments shouldnt be the name, instead should be the PK of your LFName...SVNumber.... not duplicate your LFName

The reuse of different queries to do the same thing, like your RandomizerA1, B1, etc....

To exclude your dups, try a query like:

SELECT qryAllSelectors.SVNumber
     , qryAllSelectors.LFName
     , qryAllSelectors.FLName
     , qrySelectorSetUpEligibility.Module
     , TSA.tblSelectorAssignments.LFName
     , Rnd([qrySelectorSetUpEligibility].[SVNumber]) AS Random
FROM (qryAllSelectors 
INNER JOIN qrySelectorSetUpEligibility ON qryAllSelectors.SVNumber = qrySelectorSetUpEligibility.SVNumber) 
LEFT JOIN (select * 
           from  tblSelectorAssignments 
           where selectiondate = #01/28/2014#)  AS TSA ON qryAllSelectors.SVNumber = TSA.SVNumber
WHERE (((TSA.SVNumber) Is Null) 
  AND ((qryAllSelectors.Shift)=1) 
  AND ((qryAllSelectors.Selector)=True) 
  AND ((qrySelectorSetUpEligibility.Eligible)=True) 
  AND ((qryAllSelectors.Assigned)=False))
GROUP BY qryAllSelectors.SVNumber
       , qryAllSelectors.LFName
       , qryAllSelectors.FLName 
       , qrySelectorSetUpEligibility.Module
       , TSA.tblSelectorAssignments.LFName
       , Rnd([qrySelectorSetUpEligibility].[SVNumber])
HAVING (((qrySelectorSetUpEligibility.Module)="B - 1"))
ORDER BY Rnd([qrySelectorSetUpEligibility].[SVNumber]);

You should have this query in your VBA though and run it only JUST PRIOR to needing it...
I.e. dont have...

Set rs1 = db.OpenRecordset("qryRandomizerA1")
Set rs2 = db.OpenRecordset("qryRandomizerB1")
Set rs3 = db.OpenRecordset("qryRandomizerB2")
Set rs4 = db.OpenRecordset("qryRandomizerB3")
....
Use rs....

But instead

Set rs1 = db.OpenRecordset("qryRandomizerA1")
Use and assign A1
Set rs1 = db.OpenRecordset("qryRandomizerB1")
Use and assign B1
Set rs1 = db.OpenRecordset("qryRandomizerB2")
Use and assign B2
Set rs1 = db.OpenRecordset("qryRandomizerB3")
etc....

I hope this helps.

 

[ErinL]

Thank you for the response namliam!


I do have a bad habit of not renaming controls.

I was able to make the changes you suggested with a little tweeking of my own and...it works perfectly now!!

Thank you for taking the time to help me out.