Solved If Statement

[Number11]

Hi,

So i have on a form a button that runs an update query to allocate a user 20 records from the main table of records that need to be processed. as the update runs it completes the user name in the field "Allocated to". I am looking for a way so that when they click the buton again it does not allocate a further 20 until they have completed the 20 they already have.

so was thinking of using .... but this doesn't work it just adds a further 20

If DCount("*", "Working") = 20 Then
DoCmd.Close acForm, "Working_Completed"
Else

DoCmd.SetWarnings False
DoCmd.OpenQuery "Allocate_Completed"
DoCmd.SetWarnings True

 

[Gasman]

That logic is not going to work?
You allocate 20, and they work that 20. How is the code meant to run, when 20 have already been allocated.?

I would have thought you would need to check that no records are left to be processed for that user before allocating another 20, else when they complete one, they could add another 20.?

 

[Number11]

yes very good point, so i need to have some code that will look to see if they have records of 20 or less if so not allocate allocation but if zero allocate a further 20

 

[Gasman]

That could work.

Not sure how you would select a number of records in the query unless you use VBA. for the actual query? Probably TOP x

Or you could not add any from/run your query until the Dcount returned zero for that user.?

 

[Number11]

That could work.

Not sure how you would select a number of records in the query unless you use VBA. for the actual query? Probably TOP x

Or you could not add any from/run your query until the Dcount returned zero for that user.?

so i am using the top to allocate 2o and thats working its just how to not allocate a further 20 if they have not completed the ones they got thats the bit i am stuck on

 

[jocph]

Do you have a field where you would know if the records you allocated are finished? Maybe add a boolean field that is in False status when allocated, which you change to True when done updating. You can then check if there is still a False entry in that field and prompt the user to finish updating all 20 records first.

 

[Number11]

Do you have a field where you would know if the records you allocated are finished? Maybe add a boolean field that is in False status when allocated, which you change to True when done updating. You can then check if there is still a False entry in that field and prompt the user to finish updating all 20 records first.

#
yes i have a completed on date field that is the last think that gets updated before save and refresh query is run

 

[The_Doc_Man]

If you have a date field that is blank or null until the task is completed, test it for NZ([datefield],0) to be 0 or not zero. If your person has any assigned items that are still 0 then they haven't been completed yet.

 

[Number11]

If you have a date field that is blank or null until the task is completed, test it for NZ([datefield],0) to be 0 or not zero. If your person has any assigned items that are still 0 then they haven't been completed yet.

how would i call this and if 0 then continue code to run update if not 0 not run update open form to work

 

[Number11]

Ok got it working like this


If DCount("*", "Working") <> 0 Then
DoCmd.OpenForm "Working_Completed"
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "Allocate"
DoCmd.SetWarnings True
DoCmd.OpenForm "Working_Completed"
end if

 

[Gasman]

Again, not good, unless only one person uses the database. You would need to check for the user as well.?