473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Macro pop up reminder

I have a save button on a form that enables the database user to run command to save and reserve equipment that has been selected via a Filter box.

The macro runs behind the button to check if equipment and loan period has been selected and that a loan agreement has been signed and checked. I have added to the database some auxiliary equipment that is intended to augment a specific piece of kit. For example the administrator should be reminded to ask the borrower if he wants a bag to protect a tablet loan item. So far I have managed to get the pop up to work easy enough but it blocks the command to save and complete the reservation. Any help with this would be much appreciated. The code I have so far is as follows:

Expand|Select|Wrap|Line Numbers
  1. …ElseIf  [EquipmentID] = “ Tablet-01” OR “Tablet-02”, etc…
  2. MessageBox
  3.         Message “This item requires a bag or case. Would you like one?”
  4.         Beep Yes
  5.         Type Information
  6.         Title Reminder
  7.     Else
  8.         Runcode
  9.         Function Name CheckValidReservation (False)
  10.         EndIf
Mar 21 '16 #1
9 2452
zmbd
5,501 Expert Mod 4TB
This is one of the quirks of the Access-Macro-Scripts and one of many reasons most of us have moved to VBA for the majority of application development.

The message box should have only one [OK] button and that should dismiss the pop-up once clicked... this is the normally desired behavior for such a pop-up.

On a side note: I noticed that you've hard coded your equipment ids for this pop-up. You might want to consider having a table and field that you check instead. This would make your maintenance much easier instead of having to go back in to the code and add the equipment to the conditional.

Tbl_equipment
[Pk]
[name]
[etc]

This table would have just the equipment inventory, including your case/bag/pencil/widgets

Table_Accessories_Profile
[PK]
[Fk_Tbl_Equipment]
[Fk_Tbl_Equipment_Options]
[Required]

In this table you would have a record entry for each accessory you want to either prompt for or require for the check out.... so say the Primary Key [Tbl_equipment!][Pk] for Tablet-01 = 101 and one of the Cases has a [Tbl_equipment!][Pk] = 5000 and a stylus has [Tbl_equipment!][Pk]=6000

then in table Table_Accessories_Profile you would have:
Table_Accessories_Profile
[PK][Fk_Tbl_Equipment][Fk_Tbl_Equipment_Options][Required]
[1 ][ 101 ][ 5000 ][ 1 ]
[2 ][ 101 ][ 6000 ][ 0 ]

Then in your work flow you could add Tablet-01 to the check out list and when the Clerk click on [Finalize] or [Finish] etc... the code would look at the check out list, and then see if the item exists in the Table_Accessories_Profile table and then either prompt the clerk to up-sale the item (ie the stylus) or send the clerk back to the tentative checkout form and prompt the clerk to add the Case to the list.

I've highly over simplified the above example as it's a tad off topic (ie, I wouldn't use the case or stylus in the profile table, instead using an equipment classification); however, I did want to give you something else to consider (also to consider moving away from Macro to VBA for your application. Unless you are publishing to a SharePoint site, there really isn't much call for the macro script and it's limited in it's abilities to things such as I've mentioned above!) If you want to pursue this then a new thread would be required.
May 3 '16 #2
Hi Zmbd,

Thanks for the reply, I was beginning to think that no one had any interest in the issue.

However, for all your advice, the simple OK doesn't let the save command operate either. That's why I looked for the Yes/No options.

I'm not proficient at VBA. That's why I went for the Macro options, which worked perfectly so far in providing blocks against users checking out items without selecting dates, times and ticking the loan agreement.

That all said, there is a sizeable chunk of VBA that exists behind the forms. I have contributed to some, while other code was put their by others, which I am still getting my head around.

Any help completing the VBA for this would be well appreciated. It's not just the code, which I guess can be sourced from books or the internet. It's a question of where to put it! Also, may be I'm wrong but I suspect the database will not let me mix VBA with macros within the same form.

In regard to your comments about the hard coding, I think I understand what you are driving at even though I don't quite understand the nomenclature you have used. I'm not sure of the motive the designer (not me, I'm only the developer) used when he hard coded set times and dates. It may be because he originally set up the database to work with much less equipment types than planned, then had to hard code things to get around other problems. Perhaps you might know why this happens?

Thanks,
DEM
May 19 '16 #3
zmbd
5,501 Expert Mod 4TB
ahh, macros... we have a love-hate relationship with them.

VBA and Macro can co-exist on the same form.
In fact, they can call each other
+DoCmd.RunMacro Method
+RunCode Macro Action

If you are using Access2010 or newer we can get a copy of your macro

Simply open the macro in design view
Select any item in the script
<ctrl><a>
this will select all of your code within the script (it may not look like it depending on your system)
<ctrl><c>
to copy
Post it to the thread with the usual <ctrl><v>
Select the entire pasted text within the post box and the click on the [CODE/] formatting tool to insert the [code][/code] tags around the macro script.

Hopefully one of us will have a chance to look thru the code and find the fault.
May 20 '16 #4
Thankyou, code as requested...

Expand|Select|Wrap|Line Numbers
  1.  <?xml version="1.0" encoding="UTF-16" standalone="no"?>
  2. <UserInterfaceMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><UserInterfaceMacro For="Command11" Event="OnClick"><Statements><Action Collapsed="true" Name="OnError"/><ConditionalBlock><If><Condition>IsNull([cboUserSelect])</Condition><Statements><Action Name="CancelEvent"/><Action Name="MessageBox"><Argument Name="Message">Please select a user</Argument><Argument Name="Type">Information</Argument><Argument Name="Title">Value Required</Argument></Action><Action Name="GoToControl"><Argument Name="ControlName">[PersonID]</Argument></Action></Statements></If><ElseIf><Condition>IsNull([DateCheckedOut])</Condition><Statements><Action Name="MessageBox"><Argument Name="Message">Please enter a date checked out</Argument><Argument Name="Type">Information</Argument><Argument Name="Title">Value Required</Argument></Action><Action Name="CancelEvent"/><Action Name="GoToControl"><Argument Name="ControlName">[DateCheckedOut]</Argument></Action></Statements></ElseIf><ElseIf><Condition>IsNull([DueDate])</Condition><Statements><Action Name="CancelEvent"/><Action Name="MessageBox"><Argument Name="Message">Please enter a due date</Argument><Argument Name="Type">Information</Argument><Argument Name="Title">Value Required</Argument></Action><Action Name="GoToControl"><Argument Name="ControlName">[DueDate]</Argument></Action></Statements></ElseIf><ElseIf><Condition>IsNull([TimeOut])</Condition><Statements><Action Name="CancelEvent"/><Action Name="MessageBox"><Argument Name="Message">Please Enter Time Out</Argument><Argument Name="Type">Information</Argument><Argument Name="Title">Value Required</Argument></Action><Action Name="GoToControl"><Argument Name="ControlName">[TimeOut]</Argument></Action></Statements></ElseIf><ElseIf><Condition>IsNull([TimeIn])</Condition><Statements><Action Name="CancelEvent"/><Action Name="MessageBox"><Argument Name="Message">Please Enter Time Out</Argument><Argument Name="Type">Information</Argument><Argument Name="Title">Value Required</Argument></Action><Action Name="GoToControl"><Argument Name="ControlName">[TimeIn]</Argument></Action></Statements></ElseIf><ElseIf><Condition>[EquipmentID]="Tablet-01"</Condition><Statements><Action Name="MessageBox"><Argument Name="Message">This item requires a bag or case</Argument><Argument Name="Type">Information</Argument><Argument Name="Title">Reminder</Argument></Action></Statements></ElseIf><Else><Statements><Action Name="RunCode"><Argument Name="FunctionName">CheckValidReservation(False)</Argument></Action></Statements></Else></ConditionalBlock></Statements></UserInterfaceMacro></UserInterfaceMacros>
May 21 '16 #5
zmbd
5,501 Expert Mod 4TB
Ok,
Taken a look at your macro actions, and I think that we need a clearer understanding of what you mean by it blocks the save command. There's nothing in the code that should leave the message box open on the form; thus, I think I have a misunderstanding of what your actual question is...


If you are saying that the message box is staying open then:

+ The Action Name:="OnError" is the equivalent to the VBA On Error Resume Next

This is suppressing any errors in your macro and executing the next action command regardless of the error that happened. This is better error trapping than Macros have had in the past; however, still very limited. You can go in and change this to "Fail" while you are trouble shooting.

+ You have a series of <Action Name="CancelEvent"/> in the code, I'm not sure what you are attempting in the on_click event of a command button as there is no action to cancel.

This action is most often used in the before_update event of the form or control as part of a validation.
MS: CancelEvent Macro Action

+ You may be in an infinite loop, most likely because of the On Error Resume Next

>> Troublshooting:
Open your macro in design view
<option> Change the OnError to fail - I would do this while trouble shooting!

In the [Ribbon>Macro Tools>Design] there is a toggle for "Single Step." Set this to on/selected.

Now this will effect every one of the macros, however, do this from within the macro we're working with here just be sure we have this state set for this macro.

Now save your form

Run the form and click on the button, you will now single step thru the macro actions. We need to find out where your error is occurring.

<< Note >>
Starting latter this week, I will be traveling for the next few weeks with very limited or any access to the internet - you're not abandoned, just in suspended animation.
May 22 '16 #6
Hi, Thanks for the update, much appreciated.

In short, it seems the macro goes into an infinite loop when it reaches the message popup on the EquipmentID = "Tablet-01". Consequently when OK is selected in the message pop-up, it closes. Then when the save command is clicked, the same message pops up again.

The macro goes through the controls and cancels each event if the control has no data entered(cboUser, Dates, times, etc.)

The Single Steps read:

Action Name:
OnError, Error number:0, Arguments:0
Error:IsNUll([cboUserSelect]) Error Number 0
Error:IsNUll([DateCheckedOutSelect])Error Number 0
Error:IsNUll([DueDateSelect])Error Number 0
Error:IsNUll([TimeOutSelect])Error Number 0
Error:IsNUll([TimeInSelect])Error Number 0
Error:[EquipmentID]= "Tablet-01" Error Number 0
RunCode Error Number 0, Arguments: CheckValidReservation(False)
May 23 '16 #7
zmbd
5,501 Expert Mod 4TB
Error:IsNUll([cboUserSelect]) Error Number 0
and the other similar errors.
TBH: not sure here; however, I'll take an educated guess and suspect that something has cased the record to save (loss of focus on the record when clicking on the command button may do this) and you may have at the table level the field property "required" set to yes or have included the field within an index with the "ignore nulls" set to no.

Error:[EquipmentID]= "Tablet-01" Error Number 0
I suspect that Field [EquipmentID] is numeric in your table and you are attempting to enter a string value.
You will need to determine the correct value for the bound field.

The macro goes through the controls and cancels each event if the control has no data entered(cboUser, Dates, times, etc.)
Hmmm, IIUC, the Action-CancelEvent applies only within the scope of the calling event, in this case the on_click event of the command button.
IMHO, you should remove Action-CancelEvent for the time being and see if that eliminates your

RunCode Error Number 0, Arguments: CheckValidReservation(False)
Indicates that your called vba may also be failing.
You'll need to debug it too...
Post#2 Step-A of this thread

I once you have the debug compile done (you may have to repeat the debug/compile several times as it stops on the first encountered error) within the function see if things will run without returning this error. If not then you should follow thru the debugging sections referenced in the above thread located in our insights articles (link at the bottom of this page :-) )


On a side note:
IMHO: You also really should rename your controls from the default.

"Command11" should be something useful such as "cmd_SaveEntry"
and your textbox controls something along the lines of "txt_equipmentid"

This does two things for you: helps you to find and make sure that you are referencing the intended control and it helps to differentiate between the bound data source (ie: [table]![field] ) and the control itself.
IE:
ME.txt_equipment_id is explicit that you are referencing the control - such as when setting the visible property.
Where as
me![equipment_id] is explicit that you are referencing the field [equipment_id] in the form's recordsource.


Unfortunately when you add a control (no matter what type) the name defaults to the same name as the field or object it is bound to (in the case of subform controls etc...). The developer has to go in and change this from something generic such as "textbox11" or "Command11" to something more helpful. Many developers never bother to change this; however, it's been a source of many a headache down the road during development and upkeep.

The only thing I will normally leave as the default name are static labels or the attached label-control-to-control. HOWEVER, if I intend on changing the label control's caption during runtime I will change the default name to something more descriptive such as from default "Lablel6" to "lbl_UserFeedBack"
May 24 '16 #8
Thanks again for your reply and patience in equal measure.

I have looked through all your points. Here's my observations against your comments italicised...

..."you may have at the table level the field property "required" set to yes or have included the field within an index with the "ignore nulls" set to no.

I learned something here. I confess I had to do some internet searches how to find the Index. Having found it, I can report that the macro in the Reserve Equipment Form is based on a join between two tables (Person and Loan). Both these tables are indexed to "ignore nulls" set to no.

I suspect that Field [EquipmentID] is numeric in your table and you are attempting to enter a string value.

No! Equipment ID is set to Short text.

you should remove Action-CancelEvent for the time being and see if that eliminates your...


Tried this and it doesn't affect the outcome.

Indicates that your called vba may also be failing.
You'll need to debug it too...


I might find this the most challenging, so will look at the thread link tomorrow.

In respect to all your other comments, about the advantages of using labelling on tables, forms, queries, etc, I couldn't agree more. Naively about a year ago I began prefixing the objects with the appropriate labels. The problem is when it begins screwing up the queries. The only way to fix that is to go into SQL view and this not only takes the complete knowledge to resolve this, the time needed to do was just not top of my priorities. I am now convinced that the original designer, while at the time when being almost in awe when exposed to the database he created, now realise he did so many things in an unprofessional way. Too much water under the bridge now to fix that when there are so many other priorities.

That said, I do appreciate your incites, and it reaffirms how I would tackle things in future if I ever have to create something similar...
May 24 '16 #9
zmbd
5,501 Expert Mod 4TB
I am afraid thin that I am at a time-impasse here... for some reason my XML editor is not taking the XSD files (would be nice if MS would have those in a developer's kit for free downloading!) that would allow me to validate your macro script offline, and Access2013 is refusing to accept the script as valid - weird, first time that's happened.

The null error is by design then, nothing I can do to help you there.

The [EquipmentID] issue, strange and not enough information from the program to help....

ahh the inherted monster... been there, done that, and there are not family friendly words to be had.

You may be at an impasse yourself until you can find that infinite loop.
May 25 '16 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

69
by: Ken | last post by:
Hi all. When referring to a null pointer constant in C++, is there any reason to prefer using 0 over a macro called NULL that is defined to be 0? Thanks! Ken
3
by: John | last post by:
Hi I want to implement an auto reminder like outlook that pops up at the specified time. I think there would be some sort of timer that will check when an appointment is due and pop-up the...
9
by: Phil G. | last post by:
Hi all, I would like to create a very simple 'lite' version of Outlook Calendar's reminder function. I will use a windows service that will read timedate stamps from a db and compare them to...
28
by: richardlang | last post by:
Anyone out there ever come across a preprocessor macro that compares an argument value against the predefined __DATE__ macro in order to control conditional compilation based on date. Something...
8
by: Seeker | last post by:
Hello, In using Solaris Pro Compiler to compile Pro*C code. I am getting this error: make: Fatal error in reader: parser_proc_online.mk, line 26: Badly formed macro assignment Based on other...
1
by: Jeremy Simpson | last post by:
Has anyone tried to set a reminder in MS Outlook from with Access? I want to, when a checkbox is ticked automatically set a reminder in access that takes information from the current record i.e...
2
by: THEAF | last post by:
hi, I'm trying to create a reminder. i have 2 forms one for to set the reminder and one to view all the reminders. these reminders are recorded on access. i can save the reminder and the other form...
1
by: THEAF | last post by:
OK, i've had quite a few problems with this form and again i have another one. i'm using vb6 and access. THIS IS MY FORM LOAD AND AFTER THE LOOP IS WHERE THE REMINDER CODE IS Private Sub...
3
Shakss2
by: Shakss2 | last post by:
Hi, I have a application which creates a task in outlook on the click of a button. I have made set reminder true. It works fine when it tried on the desktop. But wne tried the same...
6
by: deamond | last post by:
is it possible to add multiple records in one Outlook Reminder? I need to set an Outlook task reminder containing list of workers who need to extend their working contracts three months before it...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.