VBA to assign every 5th record? Help! 1

[firebirdta84]

Hey gang,

I need a little assistance. I have a database set up where an individual can go into a form, select the number of records they want assigned to them, and it will go into the table and place their name in the "AssignedTo" field for each record they request. The table is prioritized by the ID field, so if the user requests 5 records, it places their name in the first 5 records, ordered by the ID field. Here's the code I use for that:

DoCmd.RunSQL "Update (Select top " & Combo58 & " * From tbl_Master_BE where AssignedTo is null order by ID) dt Set AssignedTo ='" & Text60 & "'"

Combo58 is the number of records they want, Text60 is their name.

Here's my question - how do I alter this so when they select, say, 10 records, it doesn't assign the first 10, but assigns them every 3rd record until 10 have been assigned to them?

Thanks!
Joe

 

[SkipVought]

Hi,

Try an MS Access forum, like forum702.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

You could do this in pure sql, but I think I would loop with recordsets. This is untested but should be close.

dim numRecords as integer
dim rs as dao.recordset
dim strSql as string
dim strName as string
dim intCounter as integer


numRecords = nz(forms("someFormName").combo58,0)
strName = nz(forms("someFormName").text60,"")

strSql = "Select * From tbl_Master_BE where AssignedTo is null order by ID"
set rs = currentdb.openrecordset (strSql, dbopendynaset)

if numRecords = 0 or strName = "" then exit sub

do while Not rs.eof or intCounter = numRecords
  if rs.absolutepostion mod numRecords = 0 then
    rs.edit
      rs!AssignedTo = strName
    rs.update
    intCounter = intCounter + 1
  end if  
  rs.movenext
loop

 

[MajP]

In Sql assuming you want the Top X of every Y records.

SELECT TOP X
tblData.ID,
tblData.AssignedTo,
(select count(A.ID) from tblData as A where tblData.ID > A.ID order by tblData.ID) AS RecordPosition
FROM
tblData
WHERE
Not tblData.AssignedTo Is Null AND (select count(A.ID) from tblData as A where tblData.ID > A.ID ) Mod 2=0
ORDER BY
tblData.ID;

 

[firebirdta84]

MajP - with a little tweaking to my specific application, your code worked great! Thank you so much.

Joe