Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-27-2018, 02:07 AM   #16
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

Just delete AutoExec macro.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 04-27-2018, 09:58 AM   #17
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Posts: 1,097
Thanks: 13
Thanked 204 Times in 202 Posts
Mark_ will become famous soon enough
Thumbs down Re: Auto update of a field depending on the number of entries

Quote:
Originally Posted by david.paton View Post
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.
Mark_ is offline   Reply With Quote
Old 04-27-2018, 10:14 PM   #18
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

Whenever I open frmCarers I get the error message I have attached error_message.png. I have also attached my db
Attached Files
File Type: zip Carers.v23.accdb.zip (220.1 KB, 5 views)

david.paton is offline   Reply With Quote
Old 04-27-2018, 10:46 PM   #19
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

It's strange, sometimes it has shows that error message but sometimes it doesn't show it.
david.paton is offline   Reply With Quote
Old 04-27-2018, 11:47 PM   #20
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

No error this end
mike60smart is offline   Reply With Quote
Old 04-28-2018, 02:11 AM   #21
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

I have modified the Carers Availability to show you what I mean.

Carers.v25.accdb.zip

mike60smart 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:46 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