2000 Access VBA Code Doesn't work in 2007 Access

[tted78]

I'm trying to use a program developed in Access 2000 in Access 2007.
Supposedly the old program should work, but the following bit of VBA coding
fails. The value of "strPick" is not known in the Where clause and the user
is prompted for the value. I use MSGBOX to check the value immediately
before the statement and all is well. Can anyone give me a clue as to the
problem in 2007?

Dim strSQL As String, strPick As String
Dim MyBox As ListBox

Set MyBox = [Forms]![Family Display Selection]![ListNames]
strPick = MyBox.Value

strSQL = "DELETE [SSNPick].* " & _
"FROM [SSNPick];"
DoCmd.RunSQL strSQL
MsgBox "strpick " + [strPick]
strSQL = "INSERT INTO [SSNPick] ( SSNPicked ) " & _
"SELECT ALL [SSN Found].SearchSSN " & _
"FROM [SSN Found] " & _
"WHERE ([SSN Found].SearchName = strPick);"
DoCmd.RunSQL strSQL

 

[Rick]

I'm trying to use a program developed in Access 2000 in Access 2007.
Supposedly the old program should work, but the following bit of VBA
coding fails. The value of "strPick" is not known in the Where clause
and the user is prompted for the value. I use MSGBOX to check the
value immediately before the statement and all is well. Can anyone
give me a clue as to the problem in 2007?

Dim strSQL As String, strPick As String
Dim MyBox As ListBox

Set MyBox = [Forms]![Family Display Selection]![ListNames]
strPick = MyBox.Value

strSQL = "DELETE [SSNPick].* " & _
"FROM [SSNPick];"
DoCmd.RunSQL strSQL
MsgBox "strpick " + [strPick]
strSQL = "INSERT INTO [SSNPick] ( SSNPicked ) " & _
"SELECT ALL [SSN Found].SearchSSN " & _
"FROM [SSN Found] " & _
"WHERE ([SSN Found].SearchName = strPick);"
DoCmd.RunSQL strSQL

Unless there's some feature in 2007 I'm unaware of, you aren't
substituting the value of strPick (i.e. "where searchname='" & strPick &
"'"). Added the single quotes since you defined it as a string.

 

[david]

Global variables on forms can't be used like that in Access 2007.

You have to place an actual control (textbox or something) if you want
it to be seen in DoCmd or in a control source.

(david)