Solved

VBA SQL doesn't recognise variable value

Posted on 2003-11-01
2
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
This guide will walk you through the essential considerations and tech stack for building scalable websites. Know how to grow your business the smart way!
This video will demonstrate how to find the puppet warp tool from the edit menu and where to put the points to edit.
Viewers will learn how to use the Hootsuite Dashboard.

710 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