1

Topic: Parameter in request in VB a script

All greetings!
There is a form, on it the button.
It is necessary for me that on button click it was formed an ex-fur-tree of result of my request.
But in my request there is a parameter, which user hands enters.
And at button click at me few parameters swear that though to enter parameter a window do not show.
Request of type:

SELECT * FROM TABLE1
WHERE Date = [Enter date:];

Anchored to the button procedure:

Private Sub Knopka36_Click ()
On Error GoTo Err1
' Variables
Dim XLApp As Object, XLBook As Object, XLSheet As Object, RS As Object
Dim CountColumn As Integer, WidthColumn As Integer, StrSQLInExcel As String
' we Create objects: Excel, the Book, Sheet
Set XLApp = CreateObject ("Excel. Application")
Set XLBook = XLApp. Workbooks. Add
Set XLSheet = XLBook. Worksheets (1)
Set RS = CurrentDb. OpenRecordset ("Test")
' we Learn an amount of columns in Recordset
CountColumn = RS.Fields. Count
' with the Cycle we fill titles of columns
For i = 0 To CountColumn - 1
' we Move on columns in Excel by offset
XLSheet. Range ("A1").offset (0, i).Value = RS.Fields (i).Name
' we correct appearance of outswapping a little
' Width of a column we define dynamic on the basis of field length, but no more than 20 and not less 6
WidthColumn = Len (RS.Fields (i).Name) + 2
If WidthColumn> 20 Then
WidthColumn = 20
ElseIf WidthColumn <6 Then
WidthColumn = 10
End If
' we Set for title
' Transfer by words
XLSheet. Rows (1).WrapText = True
' Alignment
' Background color
XLSheet. Rows (1).Interior. ColorIndex = 15
' Width
XLSheet. Columns (i + 1).ColumnWidth = WidthColumn
Next
' It is writeable Recordset in Excel
XLSheet. Range ("A2").CopyFromRecordset RS
' It is done visible Excel
XLApp. Visible = True
' It is closed Recordset
RS.Close
Set RS = Nothing
Ex1:
Exit Sub
Err1:
MsgBox Err. Description
Resume Ex1
End Sub

2

Re: Parameter in request in VB a script

st90 wrote:

And at button click at me swears that few parameters

In what line - to us are offered to be guessed?

st90 wrote:

though to enter parameter a window does not show.

Means, not in request business. Parameters not only at requests happen - any functions yes procedures too sin with it...
And not to see usage of this request in the shown code...

3

Re: Parameter in request in VB a script

Set RS = CurrentDb. OpenRecordset ("Test")

Here a call.
The Test I resulted an example of request above.
I remove a condition
And instead

SELECT * FROM TABLE1
WHERE Date = [Enter date:];

I do so

SELECT * FROM TABLE1

And all works.

4

Re: Parameter in request in VB a script

:: And from whom you waited for request of parameter - from library DAO, whether that? So you will not wait - at it generally visual components are not present, there is nothing it to ask... To It better to say "shit request" through the standard interface of the error notification.

5

Re: Parameter in request in VB a script

Akina and how to be?
Made so, the request is fulfilled

Dim q As QueryDef
Set q = CurrentDb. QueryDefs ("Test")
q. Parameters (0).Value = "[enter date:]"
DoCmd. OpenQuery "test"

But to  now I do not know as to anchor
I RS is not present now.

6

Re: Parameter in request in VB a script

Here it is correct.

st90 wrote:

to  now I do not know as to anchor

to Specify a file-source immediately in request. Type

SELECT *
FROM [1$A1:B5] IN ' C:\folder\filename.xls ' [Excel 12.0; HDR=No; IMEX=1]
WHERE Date = [Enter date:];

7

Re: Parameter in request in VB a script

Akina, at me on the contrary an output of results with a title of columns and formatting in an ex-fur-tree.
The code see above

8

Re: Parameter in request in VB a script

If without error checks, etc.:

q. Parameters (0).Value = cdate (inputbox ("enter date:", "Set parameter", Date ()))
Set RS = q. OpenRecordset (dbFailOnError)

9

Re: Parameter in request in VB a script

4d_monster, Thanks!
Only for some reason with CDate swore on an incorrect format.
Earned so:

q. Parameters (0).Value = InputBox ("enter date:", "Set parameter", Date)

Akina, Thanks!

10

Re: Parameter in request in VB a script

Instead of...
Swore that erratic argument

(dbFailOnError)