Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 391
  • Last Modified:

VBA SQL doesn't recognise variable value

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
trident2
Asked:
trident2
1 Solution
 
proziathCommented:
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
 
Mutare99Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now