473,327 Members | 1,892 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,327 software developers and data experts.

How do I write the value of a variable to a new table from a VBA sub?

Expand|Select|Wrap|Line Numbers
  1.     Dim myVar As Double
  2.  
  3.     DoCmd.RunSQL "CREATE TABLE myTable (myVariable Double)"
  4.  
  5.     myVar = 100#
  6.  
  7.        DoCmd.SetWarnings False
  8.        DoCmd.RunSQL "INSERT INTO myTable (MyVariable) VALUES (myVAr)"
  9.        DoCmd.SetWarnings True
  10.  
  11.     DoCmd.Close acTable, "myTable", acSaveYes
** Edit (NeoPa) **
PP Chris Brown.

I have written some code to add a record into my table with a value taken from a variable in my code (myVar - See code above).

When I run the code it doesn't create the record as expected (More detail required here - Does it run at all? Does it give a specific error message that should be shared in the question?). I'd be grateful for any suggestions as to how I can make it work reliably as described.
** Edit End **
Apr 7 '11 #1
7 4646
gershwyn
122 100+
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO myTable (MyVariable) VALUES (" & myVar & ")"
Apr 7 '11 #2
Rabbit
12,516 Expert Mod 8TB
Variables referenced within a string are not considered variables. IE they don't evaluate to their value.
Apr 7 '11 #3
Thanks very much. I don't see this explained in either VBA book I have. You are concatenating and forcing spaces around the variable so it is recognized distinctly?
Apr 7 '11 #4
Sorry i am new to this. To do what you said, would I give a short title of the question in the 80 character limited field and then post the more lengthy discussion here in the message window and post the code here too?
Thanks for you help
cb
Apr 8 '11 #5
NeoPa
32,556 Expert Mod 16PB
Basically Yes Chris, but look at the example I gave when editing your OP (Original Post - or Poster in some situations). Also see the threads I pointed you to in my edit comment.

You may also find the following helpful (for this question specifically) :
One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.
Apr 8 '11 #6
thanks very - i think i get it. I will try that in the future - just getting the hang of the immediate window.
Apr 11 '11 #7
NeoPa
32,556 Expert Mod 16PB
Very pleased to hear it Chris.

To be fair, developing with debugging tools is so much more functional than trying to do the job without. It's much easier.
Apr 12 '11 #8

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

Similar topics

8
by: Ben | last post by:
Hi all, Just wondering how to write (using document.write) to a table cell. I have table with 3 rows and 3 colums. I want to write from within the Javascript to say third column of a first row....
4
by: dixie | last post by:
I want to write a query where one of the two tables in it is variable and its name is picked up from a text box control on an open form. The query is very simple and is only a select query with...
3
by: zeno | last post by:
Hi All ! is it possible to use variable table and/or field names in stored procedures i.e. construct the name of a table/field based on an input parameter as in : create procedure toto(in...
3
by: HareshKarkar | last post by:
Hi , I'm calling some Javascript function to write value in the layer. I'm trying to achieve it by using eval() function. Please look at the code below: function showMore(tempLayerNum,...
5
by: prawasini | last post by:
hi, I have a code where a value in one of a table cell needs to be populated on a command button click event. Scenario: There is a main window having multiple <DIV>s In one of the Div there is a...
3
by: priyanka1915 | last post by:
Situation is : i got output like open=4324= volumn=435.456, change=56, etc... now i want to enter this value in table and this value updates after 3 sec and it should also update in dat table
4
by: billa856 | last post by:
Hi, I want to know how can we set the value of Textbox = value of field in table when we select a value form combobx. example i have a table customer CID CNAME CSALARY 1 Billa ...
4
rajiv07
by: rajiv07 | last post by:
Hi to all I want to know How to update null value in table. Is any idea please. Thanks Regards RajivGandhi
10
by: jpollack | last post by:
I don't know JavaScript but have been tasked to write a script that will change the value of a Boolean variable to the word "Yes" on a table row. I have been trying to achieve this based on my...
4
by: Greg (codepug | last post by:
Private Sub cboBody_KeyDown(KeyCode As Integer, SHIFT As Integer) 'Delete combobox entry if Del or Backspace key is hit. If (KeyCode = 46) Or (KeyCode = 8) Then Me.cboBody = "" End Sub I have...
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...
0
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: 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...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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: 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...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.