pass a value from a inputbox to a sql statement excel vba

Question

hello all 

i have the code below in which  i am trying to pass a value from an inputbox to a sql statement i am getting an error on .open saying 

no value given for 1 or more required parameters .

can anyone see where i am going wrong 

Sub database2()

Dim databaseconn As ADODB.Connection

Dim databaserecords As ADODB.Recordset

Dim fld As ADODB.Field

Dim databasepath As String

Dim w1 As Worksheet

Dim entry1 As String

databasepath = databases()

Set databaseconn = New ADODB.Connection

Set w1 = Sheets("results")

w1.Range("a1:z100000").Select

Selection.ClearContents

databaseconn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & databasepath & ";Persist Security Info=False;"

databaseconn.Open

Set databaserecords = New ADODB.Recordset

entry1 = InputBox("Enter Consultant")**********inputbox

With databaserecords

.ActiveConnection = databaseconn

'.Source = " select * from qryplacmain where year= year(now())"

.Source = "select * from qryplacmain where appcon= entry1"*******SQL STATEMENT

.CursorType = adOpenDynamic

.LockType = adLockOptimistic

.Open ******ERROR FROM THIS POINT (no value given for 1 or more required parameters .)

End With

w1.Range("a1").Select

'use this to open a new worksheet

'Worksheets.Add

'On Error Resume Next

'ActiveSheet.Name = databaserecords.Source

'On Error GoTo 0

For Each fld In databaserecords.Fields

ActiveCell.Value = fld.Name

ActiveCell.Offset(, 1).Select

Next fld

Range("a2").CopyFromRecordset databaserecords

Range("a1").CurrentRegion.EntireColumn.AutoFit

databaserecords.Close

databaseconn.Close

Set databaserecords = Nothing

Set databaseconn = Nothing

Answer 1

Two things. Firstly, the SQL statement in your code here is passing the name of the variable rather than the content. It should be:

.Source = "SELECT * from qryplacmain where appcon='" & entry1 & "'"

Secondly, the error seems to indicate that the query you are using as the source of recordset has a named parameter which you need to supply rather than adding a WHERE clause. If this is the case, you either need to:

• rewrite/copy the query to remove this parameter

or

• create the recordset a different way using an ADO Command object where you can loop through the required parameters and pass in the values as required.

Answer 2

Two things. Firstly, the SQL statement in your code here is passing the name of the variable rather than the content. It should be:

.Source = "SELECT * from qryplacmain where appcon='" & entry1 & "'"

Secondly, the error seems to indicate that the query you are using as the source of recordset has a named parameter which you need to supply rather than adding a WHERE clause. If this is the case, you either need to:

• rewrite/copy the query to remove this parameter

or

• create the recordset a different way using an ADO Command object where you can loop through the required parameters and pass in the values as required.

hello the cosh

yes the '" & entry1 & "'" worked fine arrr i was sending the variable name not the content! i see

thanks for the reply