UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Data Macro - Before Update Not Working, Access 2013    
 
   
hl7guy
post Dec 3 2019, 02:31 PM
Post#1



Posts: 6
Joined: 3-December 19



I am successfully updating a certain field ( Patient Number) in a table via a query that I call on a Form OnTimer event every 60 seconds

I want to write a data macro that will reset certain fields to empty strings in this table wherever the patient number is different than the previous one

I am using a Before Change Macro:

If [Patient Number] <> [Old].[Patient Number] Then
SetField
Name SomeFieldName
Value =""

The macro isn't working at all. I'm wondering if it's because its being updated via query and not manually. I have attached a screenshot.

Thank you for the help.
This post has been edited by hl7guy: Dec 3 2019, 02:32 PM
Attached File(s)
Attached File  datamacro.png ( 19.01K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Dec 3 2019, 02:53 PM
Post#2


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

Are you getting any errors in the Application Log?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
theDBguy
post Dec 3 2019, 02:59 PM
Post#3


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. I just gave it a try, and I didn't have any problems with it.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
hl7guy
post Dec 3 2019, 03:04 PM
Post#4



Posts: 6
Joined: 3-December 19



Thank you for the welcome!

I checked the Event Viewer/Applications/Microsoft Office Alerts and I have no Errors, Warnings, or Criticals.
Go to the top of the page
 
theDBguy
post Dec 3 2019, 03:37 PM
Post#5


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (hl7guy)
I checked the Event Viewer/Applications/Microsoft Office Alerts and I have no Errors, Warnings, or Criticals.

No, I was referring to the System Application Log table in Access. I think it's name is called USysApplicationLog.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
hl7guy
post Dec 3 2019, 04:12 PM
Post#6



Posts: 6
Joined: 3-December 19



I just looked at USysApplicationLog and there are no errors. I'm new to access and didn't know about this feature. Great to know!





Go to the top of the page
 
theDBguy
post Dec 3 2019, 04:19 PM
Post#7


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. Like I said though, it was working for me. Are you able to post a demo version of your db for testing?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
hl7guy
post Dec 3 2019, 05:32 PM
Post#8



Posts: 6
Joined: 3-December 19



Here is a test db. UpdateQuery updates Table2 from Table1. Form1 has a button that turns off prompts, runs the query, turns on prompts again then refreshes the form. Data Macro on table2 should update comments if patient number changes.

Thank you again
Attached File(s)
Attached File  TestDBforMacro.zip ( 49K )Number of downloads: 1
 
Go to the top of the page
 
theDBguy
post Dec 3 2019, 05:56 PM
Post#9


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks. Check it out now. Hope it's what you wanted...


Attached File(s)
Attached File  TestDBforMacro.zip ( 22.1K )Number of downloads: 2
 

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
hl7guy
post Dec 4 2019, 09:06 AM
Post#10



Posts: 6
Joined: 3-December 19



Thanks! Can you tell me what you changed?
Go to the top of the page
 
hl7guy
post Dec 4 2019, 09:42 AM
Post#11



Posts: 6
Joined: 3-December 19



Oh now I see, the Nz Function! That fixed it! Basically I was trying to compare the new patient number (which is null) with the old number (which was a string). The nz function lets me return a value for comparison even if null. Being more of a SQL guy i am used to seeing NULL. I thought it was just an empty string i was comparing. Doh! Adding screenshot of new code for anyone seeing this. Thanks for the help!
This post has been edited by hl7guy: Dec 4 2019, 10:01 AM
Attached File(s)
Attached File  nzfunction.png ( 7.69K )Number of downloads: 0
 
Go to the top of the page
 
theDBguy
post Dec 4 2019, 11:25 AM
Post#12


UA Moderator
Posts: 76,866
Joined: 19-June 07
From: SunnySandyEggo


Hi. You're welcome. Sorry for the delay. Glad to hear you figured it out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 08:30 AM