QueryDefs Question

[xRoachx]

What does the following line of code do?

CurrentDb.QueryDefs("Assignment Sheet Query").Parameters
("App Type").Value

Does it show the value of the parameter for the "App Type"
field, or does it allow me to change the value of the
parameter? Or does it do both or nothing?

I used a similar line of code to set the default value of
a table:

CurrentDb.TableDefs("Assignment Sheet").Fields("App
Type").DefaultValue

What I am trying to do is update the parameter prior to
running the query or at run-time so the user doesn't get
the parameter input box. The parameter is based on user
input. However, when I try to use the code for the
QueryDef I get the error "Item not found in the
collection." I've verified the spelling of the query and
the field name. Thanks.

 

[Douglas J. Steele]

By itself, that line does nothing.

You can either have

strCurrValue = CurrentDb.QueryDefs("Assignment Sheet Query").Parameters("App
Type").Value

to determine what the current value of the parameter is, or

CurrentDb.QueryDefs("Assignment Sheet Query").Parameters("App Type").Value =
strCurrValue

to set the value of the parameter.

 

[xRoachx]

Doug, thanks for the quick reply.

I have the code as written in your second example because
I figured it worked the same as TableDefs. But I am still
getting the error (run-time 3265). I looked up the error
and it wasn't very helpful. Any thoughts? The rest of
the code is below:

Private Sub cmdOK_Click()

Dim strAppType As String 'App type to determine
which query to run
Dim strTempAppType As String
Dim strClassDate As String 'Class Start Date to
pull accounts for
Dim strTempClassDate As String


'Check to ensure the user chose an app type
'and entered the class start date
'
If IsNull(cboAppTypes) Then
MsgBox "You must choose an app type." _
& vbCrLf & "Please try again.", vbExclamation,
_
"ADDITIONAL INFO REQUIRED"
cboAppTypes.SetFocus
Exit Sub
End If

If IsNull(tboClassDate) Then
MsgBox "You must enter a class date." _
& vbCrLf & "Please try again.", vbExclamation,
_
"ADDITIONAL INFO REQUIRED"
tboClassDate.SetFocus
Exit Sub
End If

'Assign default App Type
'
cboAppTypes.SetFocus
strAppType = cboAppTypes.Text
strTempAppType = """" & cboAppTypes.Text & """"
CurrentDb.TableDefs("Assignment Sheet").Fields("App
Type").DefaultValue = strTempAppType

'Assign default Class Date
'
tboClassDate.SetFocus
strClassDate = "#" & tboClassDate.Text & "#"
CurrentDb.TableDefs("Assignment Sheet").Fields("Class
Date").DefaultValue = strClassDate


'Turn off warnings for the queries
'
DoCmd.SetWarnings False

'Select the appropriate query to run
'
' Select Case strAppType
' Case "New App"
' DoCmd.OpenQuery "New App Accts Test",
acViewNormal, acReadOnly
' Case "Web App"
' DoCmd.OpenQuery "Web App Accts", acViewNormal,
acReadOnly
' Case "Renewal"
' DoCmd.OpenQuery "Renewal Accts", acViewNormal,
acReadOnly
' Case Else
' MsgBox "Please only enter New App, Web App, SKIP
App, or Renewal!", vbOKOnly + vbExclamation, "WRONG VALUE"
' cboAppTypes.SetFocus
' End Select
'
CurrentDb.QueryDefs("Assignment Sheet
Query").Parameters("App Type").Value = strAppType
DoCmd.OpenReport "Assignment Sheet II",
acViewPreview, "Assignment Sheet Query"
DoCmd.Maximize

'Turn on warnings
'
DoCmd.SetWarnings True

'Close the form
'
DoCmd.Close acForm, "frmGetApplications", acSaveNo

End Sub

 

[Douglas J. Steele]

What line is raising the error? The error message ("Item not found in this
collection.") implies that you may have mistyped a name.

 

[xRoachx]

It's the line I started the post with:

CurrentDb.TableDefs("Assignment Sheet").Fields("App
Type").DefaultValue = strTempAppType