Auto update of a field depending on the number of entries (1 Viewer)

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
I have just thought of a problem when I was reviewing my db. I have a field called CarersAvailable within the table tblCarers. It is a yes/no field and at the moment it requires user input to change it. There is a field in the same table called CarersAuthorised#CareFor, which says how many children the carer is authorised to care for. The database is going to be split to allow multiple users to access it at the same time. I don’t have a great knowledge on sql or coding in general and I was wondering if it was possible to somehow make it so a child can’t be assigned to a carer if the number of children assigned to the carer is already equal to the CarersAuthorised#CareFor field and if the number of children assigned to the carer is equal to the CarersAuthorised#CareFor, the CarersAvailable field to change to No.

I do have a query called qryCarerAvailable, which the manager wanted to be able to search for carers that are available but because many people could access it at the same time, a carer may become unavailable and someone else check the db for availability before the original person has a chance to update the CarersAvailable field.

I hope I explained that well enough.

Thanks
Dave
 

Attachments

  • Carers.v12.accdb.zip
    261.5 KB · Views: 105

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 07:17
Joined
Jan 20, 2009
Messages
12,849
There should not be a CarersAvailable field in tblCarers except to indicate they are unavailable for reasons other than having reached their maximum allowed. The status based on the number of children should be queried every time it needs to be known by summing the number of children currently assigned to the carer and comparing with their allowed number. This way the data is always up to date.

Also I strongly advise you to remove any special characters such as the hash from your fieldnames.
 

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
Could I get a little guidance on how to compare the sum value to the allowed value please?
 

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
I tried making a query and putting some criteria in the field childCarersCarerID of if [qryallowedsum].[CountOfChildCarersCarerID]=[tblCarers].[CarersAuthorisedCareFor] then [frmCarers].[CarersAvailable].value=0


but it didn't work. Not sure how to write criteria. Was this even going about it the right way.


Here is a picture of the query my_criteria.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:17
Joined
Feb 19, 2002
Messages
42,973
To reference a field on a form:

Forms!yourformname!yourcontrolname
 

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
There should not be a CarersAvailable field in tblCarers except to indicate they are unavailable for reasons other than having reached their maximum allowed. The status based on the number of children should be queried every time it needs to be known by summing the number of children currently assigned to the carer and comparing with their allowed number. This way the data is always up to date.

Also I strongly advise you to remove any special characters such as the hash from your fieldnames.

I just found out that my manager wants to be able to manually specify if a carer is unavailable for other reasons, such as they may have just been admitted into hospital. However, he would like it to automatically change to unavailable once they have been assigned the maximum number of children. I guess a check box or something similar would suffice as that also has the ability to be changed manually.

I don't know how to make a query that will sum the children that the carer currently has in care and if the maximum has been reached, the checkbox to be selected automatically.

I think you would need to write a query and assign it to the on load event of frmCarers, which is where the checkbox is located.

Here is a current version of my db.
 

Attachments

  • Carers.v19.accdb.zip
    286.9 KB · Views: 107

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
Thanks for that Mike, only problem is that I can change the carer on my first record that is at the maximum allowed to the bottom entry that is not at their maximum allowed and the text doesn't go away for the first one. How would I make it so they are not at their maximum allowed anymore and for it to update the relevant particulars?

I also wanted some way of incorporating the Is carer available, check box that is on the carer form too as they may be something other then the maximum number of children that is preventing them from working. So someway of making "not available" appear if that check box is selected in that child form. I also think that it should say "not available" instead of "max allowed reached" as that would cover all reasons for not being available.

I am getting very close to finishing this, YAY!

I will upload a recent version of my db so you can add any changes into that.

Thanks
 

Attachments

  • Carers.v21.accdb.zip
    432.2 KB · Views: 120

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
Sorry for cutting in. Made some changes on carers form if tou dont mind. The allocation field will be disabled when carer is not availabke. Also he cannot make the allocation smaller than the number of chikdren he is currently caring.
Also added autoexec macro that runs everytime db is open. Make the carer availabke autonatic if the dateavailable field is >= to todays date.
 

Attachments

  • Carers.v21.accdb.zip
    185.2 KB · Views: 111

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
Thanks for that. Only problem is I tried to open the file and there was some error in the code. I will get back to you with an updated file as I have been working on it and will be working all day today on it.
 

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
Here it is, I have pretty much finished it. That would be awesome if you could make those little adjustments that you made with the last file. Thanks
 

Attachments

  • Carers.v23.accdb.zip
    538 KB · Views: 113

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
Here you may try. There is also an error on your searc form.
 

Attachments

  • Carers.v23.zip
    161.6 KB · Views: 121

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
The error was in this bit of code you put in my db but I can't debug it as I don't understand it.

Public Function fncUpdateCarersAvailability()
' make carers available when the CarersDateAvailable has lapsed
DoCmd.SetWarnings 0

DoCmd.RunSQL _
"Update tblCarers Set CarersAvailable = -1 Where " & _
"CarersAvailable = 0 And " & _
"Nz(CarersDateAvailable, 0) <> 0 And " & _
"CarersDateAvailable >= Date();"
DoCmd.SetWarnings -1
End Function
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
Its not an error it just update the carers availability if the date his availabke date is due today.
You can delete the macro to prevent it from running.
Your search everything form has problem, sonething missing.
Goodluck with your proj.
Im bailing out.
 

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
Can someone help me please as I don't understand this code so it is really hard for me to troubleshoot why it is not working.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:17
Joined
May 7, 2009
Messages
19,169
Just delete AutoExec macro.
 

Mark_

Longboard on the internet
Local time
Today, 13:17
Joined
Sep 12, 2017
Messages
2,111
The error was in this bit of code you put in my db but I can't debug it as I don't understand it.
What error message are you receiving? If we don't know what error you have and on what line, we can't help troubleshooting.
Code:
Public Function fncUpdateCarersAvailability()
This means it can be seen anywhere in your application
Code:
    ' make carers available when the CarersDateAvailable has lapsed
    DoCmd.SetWarnings 0
Turns off warning messages, such as not being able to find a matching record.
Code:
    DoCmd.RunSQL _
        "Update tblCarers Set CarersAvailable = -1 Where " & _
        "CarersAvailable = 0 And " & _
        "Nz(CarersDateAvailable, 0) <> 0 And " & _
        "CarersDateAvailable >= Date();"
This is the bit that actually does something.
It UPDATES your Carers tables setting the available checkbox to true when the check box is false AND the date available is not null or 0 AND the dateAvailable is greater than today. This affects ALL records.

Code:
    DoCmd.SetWarnings -1
Turns warnings back on
Code:
End Function
Ends out the function, but I do hope that was rather obvious... :)

Over view is this resets availability every time the application is opened based on your data.
 

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
Whenever I open frmCarers I get the error message I have attached error_message.png . I have also attached my db
 

Attachments

  • Carers.v23.accdb.zip
    220.1 KB · Views: 110

david.paton

Registered User.
Local time
Today, 13:17
Joined
Jun 26, 2013
Messages
338
It's strange, sometimes it has shows that error message but sometimes it doesn't show it.
 

Users who are viewing this thread

Top Bottom