Here is a bit of code from the middle of a long module for creating a Mail
merge data file

If Left(strSQL, 10) = "PARAMETERS" Then
Stop ' ?????????????
End If

Set rstOutput = CurrentDb.OpenRecordset(strSQL, , dbSeeChanges)

The strSQL comes from a variely of sources such as a query, a form or
report's record source or a table . If there are no parameters, everything
works perfectly. So we must assume there is no QueryDef associared with the
strSQL string

Here is a sample of an actual strSQL string

PARAMETERS [Enter date of last New Member's Evening] DateTime;
SELECT DISTINCT QAddressLabelsFamily.*
FROM Member INNER JOIN QAddressLabelsFamily ON Member.[MemHeadOfHouseID] =
QAddressLabelsFamily.[MemHeadOfHouseID] WHERE (((Member.MemJoinDate)>[Enter
date of last New Member's Evening])) ORDER BY
QAddressLabelsFamily.MemSurName;

How do I get the "Enter parameter" box to open and pass the parameter to the
recordset?

Thanks

Phil

On Sun, 12 Dec 2010 17:14:25 GMT, "Phil" <phil (AT) stantonfamily (DOT) co.uk>
wrote:

Via the parameters collection of the Querydef object. Off the cuff:
dim qd as dao.querydef
set qd=currentdb.createquerydef("",sql)
debug.print qd.parameters.count
qd![Enter date of last New Member's Evening].Value = #1/1/2010#
set rs = qd.openrecordset(dbOpenSnapshot,dbSeeChanges)

-Tom.


Microsoft Access MVP

On 12/12/2010 20:02:41, Tom van Stiphout wrote:
Thanks, Tom

Actually sorted it before I looked at your reply

ended up with

On Error GoTo CreateMMDate_Err ' if sql is bad...simply exit...

If Left(strSQL, 10) = "PARAMETERS" Then
Set MyDb = CurrentDb
Set QDF = MyDb.CreateQueryDef("", strSQL)
For Each Prm In QDF.Parameters
i = InStr(Prm.Name, "[")
If i > 0 Then
Stg Stg = Right(Prm.Name, Len(Prm.Name) - i) ' Strip the left [
End If
i = InStr(Stg, "]")
If i > 0 Then
Stg Stg = Left(Stg, i - 1) ' Strip the right ]
End If
EnterParam:
' ' We may get an error so the error routine asks for the correct type of
entry Prm.Value = InputBox(Stg)
Next Prm
Set rstOutput = QDF.OpenRecordset(, dbSeeChanges)
Set QDF = Nothing
Set MyDb = Nothing
Else
Set rstOutput = CurrentDb.OpenRecordset(strSQL, , dbSeeChanges)
End If

CreateMMDate_Err:
If Err = 0 Then ' Empty recordset
MsgBox "No data was created for this merge" & vbCrLf & _
"Make sure the sql is correct" & vbCrLf & _
"sql was " & vbCrLf & vbCrLf & strSQL, _
vbCritical, "no data for this merge"
ElseIf seIf Err = 3421 Then ' Wrong data entered in input box
If Prm.Type = dbDate Then
Msg = "date"
ElseIf Prm.Type = dbChar Then
Msg = "single character"
ElseIf Prm.Type = dbText Then
Msg = "text string"
Else
Msg = "number"
End If
MsgBox "The information we are looking for is a " & Msg
Resume EnterParam
MsgBox Err.Description
End If

Thanks agian

Phil