Auto update of a field depending on the number of entries

[david.paton]

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

 

[Galaxiom]

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]

Could I get a little guidance on how to compare the sum value to the allowed value please?

 

[david.paton]

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

 

[Pat Hartman]

To reference a field on a form:

Forms!yourformname!yourcontrolname

 

[david.paton]

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.

 

[mike60smart]

Hi David

If you open frmChild you will see that the Combobox for selecting a Carer now has a column indicating if he can be Allocated or Not if he has reached his Maximum.

View attachment Carers.v19.accdb.zip

 

[david.paton]

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

 

[arnelgp]

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.

 

[david.paton]

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]

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

 

[arnelgp]

Here you may try. There is also an error on your searc form.

 

[david.paton]

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]

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]

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]

Just delete AutoExec macro.

 

[Mark_]

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.

Public Function fncUpdateCarersAvailability()

This means it can be seen anywhere in your application

    ' 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.

    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.

    DoCmd.SetWarnings -1

Turns warnings back on

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]

Whenever I open frmCarers I get the error message I have attached . I have also attached my db

 

[david.paton]

It's strange, sometimes it has shows that error message but sometimes it doesn't show it.

 

[mike60smart]

Hi David

No error this end