Solved If Statement (1 Viewer)

Number11

Member
Local time
Today, 23:05
Joined
Jan 29, 2020
Messages
607
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

Enthusiastic Amateur
Local time
Today, 23:05
Joined
Sep 21, 2011
Messages
14,048
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

Member
Local time
Today, 23:05
Joined
Jan 29, 2020
Messages
607
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

Enthusiastic Amateur
Local time
Today, 23:05
Joined
Sep 21, 2011
Messages
14,048
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

Member
Local time
Today, 23:05
Joined
Jan 29, 2020
Messages
607
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

Member
Local time
Tomorrow, 07:05
Joined
Sep 12, 2014
Messages
61
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

Member
Local time
Today, 23:05
Joined
Jan 29, 2020
Messages
607
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

Immoderate Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 28, 2001
Messages
27,001
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

Member
Local time
Today, 23:05
Joined
Jan 29, 2020
Messages
607
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

Member
Local time
Today, 23:05
Joined
Jan 29, 2020
Messages
607
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

Enthusiastic Amateur
Local time
Today, 23:05
Joined
Sep 21, 2011
Messages
14,048
Again, not good, unless only one person uses the database. You would need to check for the user as well.?
 

Users who are viewing this thread

Top Bottom