Solved

VBA SQL doesn't recognise variable value

Posted on 2003-11-01
2
332 Views
Last Modified: 2012-08-14
I'm just learning VBA so I'm sure there is a simple answer to this.  I've got a form with abutton that fires a VBA procedure;


Option Compare Database

Private Sub yy_Click()
Dim strSQL As String
Static name As String
name = "ali"
strSQL = "INSERT INTO staff2 (NAME, PHONE) VALUES ( " & name & ", 02067737783)"
DoCmd.RunSQL (strSQL)

End Sub

When the button is clicked Access always pops a  "Enter parameter value" box with the word 'ali'  above an input field.  If I type a value into the input box the query is successfuly completed.

However, I really want the query to insert the value of the variable 'name', and not to ask me to input a value.  What am I doing wrong?

Cheers,
Ali
0
Comment
Question by:trident2
2 Comments
 
LVL 2

Expert Comment

by:proziath
ID: 9664336
Since name is a string it needs quotes around it, so you may need to do something like this

strSQL = "INSERT INTO staff2 (NAME, PHONE) VALUES ( " & "'"& name &"'" & ", 02067737783)"
0
 
LVL 1

Accepted Solution

by:
Mutare99 earned 125 total points
ID: 9699170
I imagine the NAME and PHONE fields are both text fields so this should do the trick:

strSQL = "INSERT INTO staff2 (NAME, PHONE) VALUES ('" & name & "','02067737783')"
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SAP PM 5 114
Dropbox 3 72
Win 7 OS unable to install Win updates 3 136
Software for IT quotations, invoices, delivery orders 2 35
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now