Hello, found this site while searching through google. Hopefully someone can help me. (It's a challenge (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) (IMG:http://www.utteraccess.com/forum/style_emoticons/default/wink.gif) )

Right now, I am building a software that needs to assign leads to sales people who are active. I have two tables and here's the outline:

tbl1 = fresh leads data information
tbl2 = active sales people

At night, it needs to randomly distribute to all active sales people. The schedule task is easy to config, but I can't seem to do a right query that would work. Here's what I currently have:

UPDATE tbl1 SET saleid2 = (select RND(saleid) from tbl2 where status=-1) WHERE saleid=0;

Explainations:
(WHERE saleid=0;) - tbl1 needs to only update leads that has "not" worked on
(select RND(saleid) from tbl2 where status=-1) - I only want to pull active sales people.

When I run this query in Access, it shows this error: " Operation must use an updateable query "

PS: The permission is set correctly on my test server.

Welcome to UA!

I don't think Jet is going to allow you to do this in one query; this particular subquery is making Jet treat the whole thing as non-updateable.

You could write a procedure to do it:

You know what,...that's not a bad idea. I will give it a try. Hopefully it will work.

Thank you for the fast response.

Hope to have helped.

I tried to to do a stored procedure on Access 2000 and unable to do it. Am I doing something wrong?

What I posted was not a stored procedure; it was code to be put in an Access code module.

That's what I thought and I try to run it in Macro and can't seem to get it run...it just launch the codes.

here's what I have for the module1:

==============================
Public Sub sample()

Dim rstLeads As DAO.Recordset
Dim rstStaff As DAO.Recordset

Set rstLeads = CurrentDb.OpenRecordset("SELECT * FROM tbl1 WHERE saleid=0", dbOpenDynaset)

Do Until rstLeads.EOF
Randomize
Set rstStaff = CurrentDb.OpenRecordset("SELECT TOP 1 saleid FROM tbl2 WHERE status=-1 ORDER BY Rnd([saleid])", dbOpenForwardOnly)

rstLeads.Edit
rstLeads("saleid2") = rstStaff(0)
rstLeads.Update
rstStaff.Close
rstLeads.MoveNext
Loop

Set rstStaff = Nothing
rstLeads.Close
Set rstLeads = Nothing

End Sub

==============================

Again, it's not a macro, it's code to be put in a VBA module.

I guess you could LAUNCH it from a macro, if you really wanted to; make it a function instead of a sub, and use the Macro RUNCODE action to execute the function.

I don't understand what "it just launch the codes" means.

Just wanted to let you know thank you for the quick response....

Yeah...understand that it's not macro (just like you said...VBA)...

"it just launch the codes" - means that I run it through macro...it would just open. but I will try to try to do what you suggested.

Thank you for being patient with me. (IMG:http://www.utteraccess.com/forum/style_emoticons/default/grinhalo.gif)

Okay a little trouble. Here's what I have in the module:

==========================================
Function sample()

Dim rstLeads As ADODB.Recordset
Set rstLeads = New ADODB.Recordset

Dim Recordset As ADODB.Recordset
Set Recordset = New ADODB.Recordset

Dim rstStaff, CurrentDb, OpenRecordset, dbOpenDynaset

Set rstLeads = CurrentDb.OpenRecordset("SELECT * FROM articles WHERE saleid=0", dbOpenDynaset)

Do Until rstLeads.EOF

Randomize
Set rstStaff = CurrentDb.OpenRecordset("SELECT TOP 1 saleid FROM sales WHERE status=-1 ORDER BY Rnd([saleid])", dbOpenForwardOnly)

rstLeads.Edit
rstLeads("saleid") = rstStaff(0)
rstLeads.Update
rstStaff.Close
rstLeads.MoveNext

Loop

Set rstStaff = Nothing
rstLeads.Close
Set rstLeads = Nothing

End Function
==========================================

The Error I am getting and maybe I forgot something. Where it says: " rstLeads.Edit "

Compile Error:
Method or Data member not found

Again, thank you in advance.

Actually, I found a way to do it in ASP programming. It's all mathematical.

Again, thank you for the input.