Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-16-2018, 07:04 PM   #1
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Auto update of a field depending on the number of entries

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
Attached Files
File Type: zip Carers.v12.accdb.zip (261.5 KB, 7 views)

david.paton is offline   Reply With Quote
Old 04-16-2018, 08:38 PM   #2
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,092
Thanks: 68
Thanked 1,371 Times in 1,293 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Auto update of a field depending on the number of entries

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.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
david.paton (04-25-2018)
Old 04-16-2018, 10:00 PM   #3
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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

david.paton is offline   Reply With Quote
Old 04-16-2018, 10:20 PM   #4
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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
david.paton is offline   Reply With Quote
Old 04-18-2018, 08:47 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 26,937
Thanks: 13
Thanked 1,318 Times in 1,257 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: Auto update of a field depending on the number of entries

To reference a field on a form:

Forms!yourformname!yourcontrolname
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 04-25-2018, 05:16 PM   #6
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

Quote:
Originally Posted by Galaxiom View Post
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.
Attached Files
File Type: zip Carers.v19.accdb.zip (286.9 KB, 5 views)
david.paton is offline   Reply With Quote
Old 04-26-2018, 03:23 AM   #7
mike60smart
Newly Registered User
 
mike60smart's Avatar
 
Join Date: Aug 2017
Location: Dunbar, Scotland
Posts: 144
Thanks: 21
Thanked 24 Times in 24 Posts
mike60smart is on a distinguished road
Re: Auto update of a field depending on the number of entries

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.

Carers.v19.accdb.zip

mike60smart is offline   Reply With Quote
The Following User Says Thank You to mike60smart For This Useful Post:
david.paton (04-26-2018)
Old 04-26-2018, 04:10 AM   #8
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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
Attached Files
File Type: zip Carers.v21.accdb.zip (432.2 KB, 7 views)
david.paton is offline   Reply With Quote
Old 04-26-2018, 05:33 AM   #9
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,987
Thanks: 54
Thanked 1,922 Times in 1,836 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Auto update of a field depending on the number of entries

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.
Attached Files
File Type: zip Carers.v21.accdb.zip (185.2 KB, 9 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-26-2018, 03:27 PM   #10
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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 is offline   Reply With Quote
Old 04-26-2018, 07:39 PM   #11
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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
Attached Files
File Type: zip Carers.v23.accdb.zip (538.0 KB, 6 views)
david.paton is offline   Reply With Quote
Old 04-26-2018, 11:23 PM   #12
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,987
Thanks: 54
Thanked 1,922 Times in 1,836 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Auto update of a field depending on the number of entries

Here you may try. There is also an error on your searc form.
Attached Files
File Type: zip Carers.v23.zip (161.6 KB, 5 views)
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-27-2018, 01:35 AM   #13
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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
david.paton is offline   Reply With Quote
Old 04-27-2018, 01:42 AM   #14
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 5,987
Thanks: 54
Thanked 1,922 Times in 1,836 Posts
arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough arnelgp is a jewel in the rough
Re: Auto update of a field depending on the number of entries

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-27-2018, 02:02 AM   #15
david.paton
Newly Registered User
 
Join Date: Jun 2013
Location: NSW, Australia
Posts: 282
Thanks: 51
Thanked 0 Times in 0 Posts
david.paton is on a distinguished road
Re: Auto update of a field depending on the number of entries

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.

david.paton is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] VBA coding update - change value in other field depending on value of this field Valery Modules & VBA 4 04-05-2016 08:16 PM
[SOLVED] Generate Number of Rows In Report Depending on a Number in Another Field Bridiewms Reports 11 11-20-2013 06:07 AM
Auto Populate Date field depending on another EdwardsJ92 Tables 3 09-05-2013 06:37 AM
Automatic update for a field depending on the value selected in another field. itsmepaz General 2 03-28-2007 04:36 AM
Auto increasing a number, depending on maximum number when condition is met dannewestis General 3 09-22-2006 10:00 AM




All times are GMT -8. The time now is 02:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World