Dynamic Top Value

[ErinL]

Does anyone know if it's possible to have the user enter the number of top records to choose in a query?

I know that you can enter a number or percentage in the Top Values property of the query but I would like the user to be able to enter this number each time they run the query since it may be different each time and not always, say, the top 10 records.

Thank you!!

 

[spikepl]

You'd have to change the SQL of an existing query dynamically. This means using a querydef object (google it) and changing the sql. Or you could create the entire query VBA on the fly. Depends on where the output goes.

 

[ErinL]

Thank you for the response spikepl.

The output would be used to assign associates to a work area. The project is to create a tool that will randomly assign associates to eleven different work areas each day. The number of associates per work area will change every day depending on the work load in each area.

I have never used and know nothing about querydef objects. I will Google it and see what I can find.

Thank you for your advice.

 

[David R]

I think what spike means is that it will depend on where the querydef goes... if you're trying to resave it as the new 'stored query' until someone changes it again, that takes one method. If you want them to put in a TOP value and use that on the fly for this current run, that can be done differently, etc...

So describe how this assignment system is run right now. Screenshots will help.

 

[ErinL]

Hi David -

The project is still in the very early stages so I only have one screen shot to help illustrate what I have envisioned in my mind.

I have created a menu with two subforms. On the first subform, the supervisor will check the names of the associates available for selection on that day. Those associate names are appended to tblAvailableSelectors for the "pool" of people to choose from. They will then use the second subform to enter the number of associates needed per work area. This is the number that can change each day.

So, in response to your suggested methods, I believe I would be looking for them to put in a TOP value and use that on the fly for this current run.

Thank you for your response and assistance.

 

[David R]

Your screenshot is awfully small...

Does each site have a specific number of workers (on a given day), or will the second checkbox apply to all sites? I'm kind of assuming the former, which leads me to ask, will they assign one worksite, then go on to the next? Walk us through the process a little more.

 

[ErinL]

Sorry about the attachment. I attached another screen shot. Hopefully this one is easier to see.

Here is how the process will go:

Before the start of the shift, a supervisor will place a check next to every associate that will be working as an order selector for that day. This list of associates changes every day due to sick call ins, vacations, medical leave of absence, etc. Once the available selectors for that day have been set, they will enter the number of selectors needed in each of the work areas on the second subform. These numbers will be different every day according to the amount of work in each of the areas. This process will only be done once a day at the beginning of the shift.

I would like to be able to then click a button and have each of the selectors randomly assigned to an area.

Clear as mud, right?

 

[David R]

Yes, much clearer, thanks!

Unfortunately while I know what you want to do IS possible, I am not at all sure what the best way to do it is, so I'll leave that to others... my method would involve abusing recordsets and arrays a great deal, but that's because this week I am working primarily in C, not VBA.

 

[spikepl]

Create a recordset that lists the available associates in randomized order (use RND function - google it, and pay attention to not recreate the same random sequence each time).

Loop through the recordset of associates. In the loop assign the associates to first working area. When first working area full, take the next one (the work areas could be in random order too).

In one loop your entire assignment is done.

 

[ErinL]

David - Thank you for your responses and advice.

spike - I do have a query created that uses the RND function to assign a random number to each available selector. Although I struggle a bit with code and am not much above a beginner level , I have seen loops so I will do some research and see if I can set one up to mimic what you described in your post.

Thank you both so much for your time.

 

[ErinL]

Well, I'm getting closer to getting this to work. :banghead:

I have a module that contains the following:

Public Sub SelectorAssignment()

Dim i As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("qryRandomizer1st")

For i = 0 To Forms!frmMenuPTLAssignment1st!txtA1 - 1
CurrentDb.Execute "INSERT INTO tblSelectorAssignments (SelectionDate,SelectionArea,LFName)VALUES (Date(),'A1','LFName')"
rs.MoveNext

Next i

For i = 0 To Forms!frmMenuPTLAssignment1st!txtB1 - 1
CurrentDb.Execute "INSERT INTO tblSelectorAssignments (SelectionDate,SelectionArea,LFName)VALUES (Date(),'B1',[LFName])"
rs.MoveNext

Next i

rs.Close

Set rs = Nothing

db.Close

End Sub

I am having trouble with the LFName field. I have a button on the form that will run the code above. The SelectionDate field populates correctly with the current date, the SelectionArea populates correctly with the area (A1 or B1 in the snipet above) but I cannot get the right code to enter the LFName field with an actual name.

I have tried numerous variations with quotes, single and double, ampersands and brackets but with no luck. If I put single quotes around LFName it runs without error but that is what it enters in the field. Any thing else I have tried brings up either "Too few parameters. Expected 1." or "Compile error: Expected: End of Statement".

Can you please tell me what I'm doing wrong? Thank you!

 

[JHB]

Is LFName a field name in the rs recordset or is it a control on the form?

If a recordset field name then

When a number value:
CurrentDb.Execute "INSERT INTO tblSelectorAssignments (SelectionDate,SelectionArea,LFName)VALUES (... ," & rs![LFName] & ")" 
If a string value:
CurrentDb.Execute "INSERT INTO tblSelectorAssignments  (SelectionDate,SelectionArea,LFName)VALUES (... ,'" & rs![LFName]  & "')"

If it is a control on the form then:

When a number value:
CurrentDb.Execute "INSERT INTO  tblSelectorAssignments (SelectionDate,SelectionArea,LFName)VALUES (...  ," & Me.[LFName] & ")" 
If a string value:
CurrentDb.Execute "INSERT INTO tblSelectorAssignments  (SelectionDate,SelectionArea,LFName)VALUES (... ,'" & Me.[LFName]  & "')"

 

[ErinL]

Thank you JHB! It works perfect now!

I knew it was possible, just didn't know exactly how to do it.

Thank you all so much for your help!