Solved

VBA SQL doesn't recognise variable value

Posted on 2003-11-01
2
322 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Workplace bullying has increased with the use of email and social media. Retain evidence of this with email archiving to protect your employees.
In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
The viewer will learn how to set up a document for the web and print and the recommended PPI for printing.
The viewer will learn how to create multiple layers to apply various filters and how to delete areas from each layer’s filter.

746 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

12 Experts available now in Live!

Get 1:1 Help Now