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
> Stumped By Simple Macro, Access 2007    
 
   
LeicsChris
post Aug 17 2017, 05:00 PM
Post#1



Posts: 97
Joined: 23-May 17



Hi All

On our CRM built using access 2007 we record the outcome of our telephone calls and if customers are due a call back we have a table that holds the 'task' with various fields such as [Company name], [TaskDate] and [TaskTime] and [ClientName]

My staff asked to have a pop up warning on their screen to notify them that a call back is now due.


I use a hidden 'Session'form to hold certain details running all the time the FE is in use, holding the staff logged on details, what campaign they are on etc, and for filtering records on various other forms specifically for the staff member using that FE. I added an 'Ontimer' command to this session form to run a macro every 5 minutes and if a task is due, pops up a message box to notify the user.

When a task is due, the warning works well, when no task is due I get the "An expression you entered is the wrong data type for one of the arguments" and I am stumped.

________________________________________________________________________________
______________________

Macro: CheckDueTasks

1. Open form, [HiddenTaskDueForm], Where [StaffID]=[forms]![Session]![StaffID] (opens the hidden form only for staff using that FE)
2. 'Condition' IsNull([HiddenTaskDueForm]![StaffID]=False, MSGBox, "=[Forms]![HiddenTaskDueForm]![Company Name]&" due a call at "&[forms]![HiddenTaskDueForm]![TaskTime] (the msgbox message)
3. Close form, [HiddenTaskDueForm} - closes hidden checking form
4. stop macro

My thinking is that if no tasks are due, the fields in the hidden form will be null and only if they are not null is the message needed.

As I said it works fine when a task is due and the fields in the form are not null,but brings the error when no records are brought up and the macro error box is on the message line

Any ideas

Thanks

Chris
Go to the top of the page
 
theDBguy
post Aug 17 2017, 05:15 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris,

You might try using an If statement to check if you need to open the form or not depending on if the StaffID is null or not.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Aug 17 2017, 05:26 PM
Post#3



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
Open form, [HiddenTaskDueForm], Where [StaffID]=[forms]![Session]![StaffID]

so you're opening the form, applying a WHERE clause to the underlying RecordSource. if the form does not allow new records, and there are no records returned when the WHERE clause is applied, then the form will be blank - no controls, and no underlying fields. so the IsNull() test will fail because Access can't field the field/control on the form.

suggest you follow DBguy's suggestion and query the underlying table first; if no records are found that fit the WHERE clause, then don't open the form.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
LeicsChris
post Aug 17 2017, 05:27 PM
Post#4



Posts: 97
Joined: 23-May 17



Hi DBGuy

I must be having a cabbage brain evening.

The Isnull([forms]![TaskDueForm]![StaffID])=false is in the condition section of the macro and checked after the form is open. If a task is due the form will have a record, if no task is due all the fields will be null.

The condition part of the macro is like an in, if the equation is true it will run the command, if it is false it will ignore the command.

If you mean to look at the underlying query to see if any records are available before I open the form and only open it if a task record is present then I dont know how to do that, apart from using a hidden form to see if any records are present.

I was thinking that the macro may be running ahead of itself, and that it had closed the form before the message box had been put on the screen.

Thanks
Chris
Go to the top of the page
 
tina t
post Aug 17 2017, 05:29 PM
Post#5



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
If you mean to look at the underlying query to see if any records are available before I open the form and only open it if a task record is present then I dont know how to do that

use a DCount() function on the query, using the WHERE clause in the criteria argument. if the function returns zero, don't open the form.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
LeicsChris
post Aug 17 2017, 05:35 PM
Post#6



Posts: 97
Joined: 23-May 17



Thanks Tina and DBGuy

I get what you are saying now and at least understand why it is failing.

I cant see a Dcount() command in the macro list, so does this have to be done using VBA commands?

My VBA knowledge is even less than my macro knowledge.

Chris
Go to the top of the page
 
tina t
post Aug 17 2017, 05:43 PM
Post#7



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


QUOTE
I cant see a Dcount() command in the macro list, so does this have to be done using VBA commands?

DCount() is a built-in function, it's not a command. you can use the DCount() function almost anywhere in Access, including in a Condition expression in a macro. something like

DCount("[StaffID]","PutTheNameOfYourQueryHere","[StaffID]=" & [forms]![Session]![StaffID])>0

but don't just take that and try to use it as is. read up on the DCount() function in Access Help, so you understand how the function works, and how each argument works. that way you'll be in a better position to troubleshoot any problems.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
LeicsChris
post Aug 17 2017, 06:00 PM
Post#8



Posts: 97
Joined: 23-May 17



Hi Tina

Thanks, I will read up on Dcount as I can see it will be useful.

I must also learn more about VBA, and become less reliant on macros as I know it is more versatile.

on to my googling

Thanks again

Chris
Go to the top of the page
 
theDBguy
post Aug 17 2017, 08:48 PM
Post#9


Access Wiki and Forums Moderator
Posts: 71,230
Joined: 19-June 07
From: SunnySandyEggo


Hi Chris,

Can you post a screenshot of your macro? Thanks.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
LeicsChris
post Aug 19 2017, 08:13 AM
Post#10



Posts: 97
Joined: 23-May 17



Hi Guys

I am still getting the error, tho sometimes it works and other it doesnt.

Attached File  hidden_task_form.png ( 127.92K )Number of downloads: 4


Attached File  task_message_macro.png ( 95.54K )Number of downloads: 6


Sometimes it give a wrong data type error and other times it works fine and I havent been able to pin point why

Thanks

Chris
Attached File(s)
Attached File  task_message_macro.png ( 95.54K )Number of downloads: 2
 
Go to the top of the page
 
tina t
post Aug 19 2017, 11:23 AM
Post#11



Posts: 5,197
Joined: 11-November 10
From: SoCal, USA


okay, not sure i get the logic here. you're checking for due tasks for a specific user, using the DCount() function. if any due tasks are found for the user, then you're opening a hidden form to check for due tasks - which you already did, using the DCount() function - and when they're again found, then you open a msgbox, and then close the hidden form.

my question is: why open the hidden form? is it doing something else that you haven't mentioned? if so, what?

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
LeicsChris
post Aug 19 2017, 02:44 PM
Post#12



Posts: 97
Joined: 23-May 17



Hi Tina

My 'TasksDue' query brings up the records of all tasks due in the next 10 minutes for all staff. (I run the macro on timer every ten minutes so not to slow the front end down too much having it check every minute).

On each front end I also have a hidden 'Session form" which stores things like, which user is using that particular front end, their access level, start time and finish time as well as which 'client' they are working for at that time.

I use the hidden taskdue form to bring the records to that front end for which user is using it, and it also pulls the [ClientName]. [Staff Name] and [Company Name] (the company due the call back by that staff member on behalf of which client), which are stored in other tables and recorded on the 'Task' Table by just their relevant primary key.

So the hidden form really populates the information needed for the message for that particular staff.

My logic was to check the count of Tasks per that staff member, (as per your good idea to us Dcount). If no records, or tasks due close the macro and wait till next check, and if records are present then show the message box with the call due details.

There may well be a flaw in my argument, this is the most advanced database I have built, and as a novice and learning as I go, I may be going about things unusually.

It is strange, because more sophisticated tasks I have managed and work well, and in the scheme of things, this on appearance simple macro is troubling.

Thanks

Chris
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 06:04 AM