Passing parameters

[Matt Williamson]

I wrote some code to loop through a recordset and create an output file from
the data returned from a query. This works fine if I hard code the
parameters in the query, but I need a way to prompt for the input
parameters. I'm not sure how to do it. Given the following code, I'd like to
ask for user input for the portcode parameter. What are the options for
doing this? I haven't created a form to use a textbox. Can I just use
InputBox? If so, how would I code it?

Sub CreateBlotter()

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim t As String, t2 As String
Dim sLine As String, sPath As String
Dim sSymbol As String, sBlotter As String
Dim prm As Parameter, qdf As QueryDef

t = ","
t2 = ",,"

sPath = GetPath(CurrentDb.Name)
sBlotter = sPath & "TaxLots.trn"
If bFileExists(sBlotter) Then Kill sBlotter

'Initialise
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("DataForExportFile", dbOpenSnapshot)

With rsR

Do While Not .EOF
If .Fields("sec type") > 4 Then
sSymbol = .Fields("cusip")
Else
sSymbol = .Fields("Primary Symbol")
End If
sLine = .Fields("portcode") & t & "li" & t2 & .Fields("sectype") & t &
sSymbol & t & _
FixDate(.Fields("settle Date")) & t2 & FixDate(.Fields("Trade
date")) & t & .Fields("Qty") & String(9, t) & .Fields("MktValue") & t & _
.Fields("OrigCost") & String(10, t) & "n" & t & "65533" &
String(12, t) & "1" & t2 & t & "n" & t & "y" & String(13, t) & "y"
WriteFile sBlotter, sLine
.MoveNext
Loop

.Close
End With

Set rsR = Nothing
Set dbD = Nothing

MsgBox "The file " & sBlotter & " has been created. "

End Sub


TIA

Matt

 

[Marshall Barton]

I wrote some code to loop through a recordset and create an output file from
the data returned from a query. This works fine if I hard code the
parameters in the query, but I need a way to prompt for the input
parameters. I'm not sure how to do it. Given the following code, I'd like to
ask for user input for the portcode parameter. What are the options for
doing this? I haven't created a form to use a textbox. Can I just use
InputBox? If so, how would I code it?

Sub CreateBlotter()

Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim t As String, t2 As String
Dim sLine As String, sPath As String
Dim sSymbol As String, sBlotter As String
Dim prm As Parameter, qdf As QueryDef

t = ","
t2 = ",,"

sPath = GetPath(CurrentDb.Name)
sBlotter = sPath & "TaxLots.trn"
If bFileExists(sBlotter) Then Kill sBlotter

'Initialise
Set dbD = CurrentDb()
Set rsR = dbD.OpenRecordset("DataForExportFile", dbOpenSnapshot)

Replace that initialize code with something more like:

Set dbD = CurrentDb()
Set qdf = dbD.QueryDefs!DataForExportFile
qdf.Parameters!portcode = InputBox(...

Set rsR = qdf.OpenRecordset(dbOpenSnapshot)

 

[Jeff Boyce]

Matt

Are you confident that you cannot do what you want to using set-oriented
operations (i.e, queries)? I believe they generally run much faster than
iterating through recordsets.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Matt Williamson]

Replace that initialize code with something more like:

Set dbD = CurrentDb()
Set qdf = dbD.QueryDefs!DataForExportFile
qdf.Parameters!portcode = InputBox(...

Set rsR = qdf.OpenRecordset(dbOpenSnapshot)

That works great. Thank you Marshall

Matt

 

[Matt Williamson]

Are you confident that you cannot do what you want to using set-oriented
operations (i.e, queries)? I believe they generally run much faster than
iterating through recordsets.

I'm not sure how which is why I did it this way. I'm always open to new
ideas though. The values from the query only fill in about 1/3 of the fields
in the output file. The others are either static or just need to have a
placeholder which I didn't know how to do in a straight up query.