Passing parameter value to a Form created from a Query

[jstevens]

I'm trying to pass a parameter to a form that was created from a query and am having trouble. Below is what I'm attempting, but it still prompts me to enter a value when the form opens.

DoCmd.OpenForm "frm_AllocationList", acNormal, , , , acWindowNormal
CurrentDb.QueryDefs("qry_AllocationList").Parameters("@Internal ID") = "41"

Thanks,
Jason

 

[JHB]

Could you explain in another way what you're trying to do, then it is not clear?

 

[arnelgp]

you need code to do that through vba.

dim db as DAO.Database
dim qd as dao.querydef

set db=currentdb
' create temporary query definition
' same sql statement as qry_AllocationList"
set qd=db.CreateQueryDef("zz_frm_AllocationList", db.QueryDefs("qry_AllocationList").SQL)
set qd=nothing

' call our parameter modifier
Call fnModifyParameterOfQuery("frm_AllocationList", "@Internal ID", "41")

DoCmd.OpenForm "frm_AllocationList", acNormal, , , , acWindowNormal


' reinstate original query statement
set qd = db.QueryDefs("frm_AllocationList")
qd.SQL = db.QueryDefs("zz_frm_AllocationList").SQL

set qd=nothing

' delete temporary query
db.QueryDefs.Delete "zz_frm_AllocationList"
set db=nothing

on separate module, paste this code:

Public Sub fnModifyParameterOfQuery(strQueryName As String, strParameterName As String, v As Variant)

Dim db As DAO.Database
Dim qd As DAO.QueryDef

Dim strSelect As String
Dim strParameter As String
Dim strSQL As String
Dim nNumStartParam As Integer
Dim nNumEndParam As Integer
Dim iLoop As Integer

Dim arrParameters() As String

Set db = CurrentDb

Set qd = db.QueryDefs(strQueryName)
strSQL = qd.SQL

nNumStartParam = InStr(strSQL, "PARAMETERS")

If nNumStartParam <> 0 Then
    ' there is parameter
    nNumEndParam = InStr(strSQL, "SELECT")
    
    strSelect = Mid(strSQL, nNumEndParam, Len(strSQL))
    strParameter = Left(strSQL, nNumEndParam - 1)
    strParameter = Replace(strParameter, "PARAMETERS", "")
    strParameter = Replace(strParameter, ";", "")
    
    arrParameters = Split(strParameter, ",")
    
    For iLoop = LBound(arrParameters) To UBound(arrParameters)
        If InStr(arrParameters(iLoop), strParameterName) Then
            If VarType(v) = vbInteger Or VarType(v) = vbLong Then
                arrParameters(iLoop) = arrParameters(iLoop) & " = " & v
            Else
                arrParameters(iLoop) = arrParameters(iLoop) & " = '" & v & "'"
            End If
        End If
    Next iLoop
    strSQL = "Parameters "
    For iLoop = LBound(arrParameters) To UBound(arrParameters)
        strSQL = strSQL & arrParameters(iLoop) & ","
    Next iLoop
    strSQL = strSQL & ";"
    strSQL = Replace(strSQL, ",;", ";")
    strSQL = strSQL & strSelect
End If
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing
End Sub

 

[spikepl]

As JHB said - what are you trying to do? Your title is confusing.

IF your form is based on that query and you want to change the query's parameter, then you need to change the parameter FIRST, and THEN open the form.

Also, an Internal ID is probably a Long and not String, so you should assign 41 and not "41". Further, do not use spaces or nonalpha caharcters in object and field names, because that gives a lot of PITA.