473,836 Members | 1,423 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Variable retaining value from InputBox

22 New Member
I am importing Excel files into Access and trying to automate queries in order to manipulate and create reports. In a subroutine, several queries are run to CREATE and UPDATE a TEMP table.

In the beginning of a subroutine the user is asked for a variable, MyValue. The code is:
Expand|Select|Wrap|Line Numbers
  1.    'Dim MyValue As Variant
  2.    MyValue = InputBox("1. Enter Sheet #", "MyInputbox")
  3.    tblname = "Sheet" & MyValue
Later in the same subroutine the variable MyValue is used in an UPDATE query. The code is:
Expand|Select|Wrap|Line Numbers
  1.    strSql = "UPDATE [" & tblname & "] SET MyValue1 = MyValue WHERE MyValue1 Is Null"
  2.    DoCmd.RunSQL strSql
Why is Access putting a box up before the UPDATE query? The box indicates 'enter parameter' and asks 'Enter MyValue'?
Apr 18 '12 #1
5 4828
32,584 Recognized Expert Moderator MVP
Because MyValue, as defined in the first set of code is a VBA variable that has scope within that module only (according to what you include, which is unfortunately inadequate for the question.), but you are attempting to access it from within the Jet SQL engine. This has no concept of any variables defined within VBA so asks for you to provide a value for it to use.
Apr 18 '12 #2
22 New Member
Another interesting item occurs when I add the following code just prior to the UPDATE query:
Expand|Select|Wrap|Line Numbers
  1. MyValue = InputBox("1. Enter Sheet #", "MyInputbox")
I find it interesting that the same box comes up and indicates 'enter parameter' and asks 'Enter MyValue'? Is there a method of avoiding answering the MyValue question.
Apr 18 '12 #3
32,584 Recognized Expert Moderator MVP
Try using :
Expand|Select|Wrap|Line Numbers
  1. strSql = "UPDATE [" & tblname & "] SET [MyValue1] = " & MyValue & " WHERE MyValue1 Is Null"
This is assuming your SQL syntax was correct and that MyValue is a numeric value. If it's a string then the SQL syntax is different and your VBA should then be :
Expand|Select|Wrap|Line Numbers
  1. strSql = "UPDATE [" & tblname & "] SET [MyValue1] = '" & MyValue & "' WHERE MyValue1 Is Null"
Apr 18 '12 #4
22 New Member
Thanks for your help. The code worked!!!!! It seems that your answer presents the variable as a variable with expanded wildcard type information. If there is an article on this, please direct me to it. Your answer should be marked as best response but I do not see the button on my screen.
Thanks again,
Apr 22 '12 #5
32,584 Recognized Expert Moderator MVP
It doesn't pertain to wildcards, but to VBA code Carl.

What you need to do to understand this is appreciate that there are two sections to it :
  1. Formulate a SQL string in VBA. Although this may contain SQL commands, at this point it is simply a VBA string. Because you're using VBA you can add items to this string from anything which is available to the VBA. VBA variables, for instance, are obviously accessible to VBA, but would not be accessible to the Jet SQL engine. We can use VBA though, to formulate a string with literals in it to represent values. Have a look at Quotes (') and Double-Quotes (") - Where and When to use them.
  2. Send the resultant string (which at this stage is simply a string value available to VBA - typically stored in a VBA variable) to the Jet SQL engine for the contents to be interpreted by that.

PS. I guess you found the Best Answer button. Thank you :-)
Apr 22 '12 #6

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

Similar topics

by: Remco Groot Beumer | last post by:
Hello, I created a program in which I use modules and classmodules for setting my variables. For example when I need to set the customerID in a variable I use something like: Object.Variables.Customers.CusomerID = .... This works great and is similar in structure as for example the Docmd. or me. structure. Now I created a search form which can be opened from many forms, it consist of only a listbox. The listbox is filled at startup....
by: Newbie | last post by:
hi all, in a webform's codebehind, i have a variable (ie: nTotal). and in UI i have a hidden htmlinput (ie: fld1). Q: how to assign the value of nTotal to now i want to assign the value of nTotal to fld1? many thanks in advance, andy
by: Huayang Xia | last post by:
What will the following piece of code print? (10 or 15) def testClosure(maxIndex) : def closureTest(): return maxIndex maxIndex += 5 return closureTest()
by: php_Boi | last post by:
i have designed an application that is a dynamic submission form. i have text fields and listboxes. now i am able to retain the values of the listboxes when i populate the listbox "manually"(single listbox) like this... <select name="title"> <option value="1"<?php if ($_POST == '1') { echo 'selected="selected"'; } ?>></option> <option value="Mr"<?php if ($_POST == 'Mr') { echo 'selected="selected"'; } ?>>Mr</option>
by: satish2112 | last post by:
Hi, I have a text-area which contains values from mysql database and 2 buttons, Edit and Update. When I click on the Edit button, I can edit the text-area (initially non-editable). After this, if I click on the Update button, the values in the text-area must be updated in the mysql database. I am storing the values of the text-area in a variable. I am using javascript to pass the variable.
by: satish2112 | last post by:
how can i pass a variable's value from javascript to perl script? how can i receive that value in the perl script? please help me!!!
by: satish2112 | last post by:
how can i pass a variable's value from javascript to perl script? how can i receive that value in perl script? Please help me!!!
by: raaman rai | last post by:
Please kindly tell me how to retain a value in the drop down box. I mean if i have a form and i select one option from the drop down box, and when i submit the form, if something is wrong in the submission, the very form is returned with all the values intact. But incase of me i am not able to retain the value in the drop down box. Please see my code: <td class="smallfieldcell"> <?php echo $form->error("class"); ?> <select...
by: teddysnips | last post by:
Weird. I have taken over responsibility for a legacy application, Access 2k3, split FE/BE. The client has reported a problem and I'm investigating. I didn't write the application. The AutoExec macro calls a function "InitApplication" which, in turn, calls a function to set the value of a global string variable
by: spoonybard | last post by:
I have an ASP.Net 3.5 VB web application that has a jQuery UI Tab control on it. I am trying to maintain the selected tab through postbacks so I can reselect that tab when the page reloads. In order to maintain the value between postbacks, I am using a Hidden Field. The issue I am running into is that on the first postback, the Hidden Field does not maintain the value I am giving it. However, from the second postback on, it properly manages...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.