Need help passing parameter to Select query

[utzja1]

I have form with a button on it that launches a parameter-based Select query (which served as the source for a report). I didn't have any validation measure in place, so if the User supplied a bogus value, a blank report was generated. While not technically an error, it would seem more polished to generate a warning message if the User supplies a bad query value and prompt them to re-enter.

Having read other posts along these lines, I've added an unbound text box to the form which the User fills in first before clicking the button. When the button is clicked, it executes code that uses the DCount function to make sure the text box value is in the source table, and if it is then it runs the parameter-based Select query. My question is how to pass the value in the text box to the query as a parameter. Below is a sanitized version of the code that I've generated so far. All help is appreciated!

Private Sub SingleItemRptB_Click()
If DCount("[FieldName]", "

", "[FieldName]=[TextBoxValue]") = 0 Then
MsgBox "Item not in database. Please check value and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
End If

End Sub

 

[pr2-eugin]

Open your Query in Design view, and look for the Parameter value, replace it with the Forms!FormName!ControlName.... Example.. If you have initially..

SELECT allTheFieldsYouWant FROM theTable
WHERE someField = [EnterTheParameter:];

Change it to..

SELECT allTheFieldsYouWant FROM theTable
WHERE someField = Forms![[B]yourFormName[/B]]![[B]yourTextBoxName[/B]];

Change it it suit your needs..

 

[utzja1]

Thank you for such a prompt reply! Your advice worked like a charm; I'm going to bump my status up to mixed success. Could I impose on you again to double-check my use of the DCount function? I can verify that the proper query is being run if the User-supplied value is in the table, but if it's not, I'm not getting the error message. It's also not launching the report regardless of whether the User value is in the table or not. Thanks!

Private Sub SingleItemB_Click()

Dim NumRecord As Integer

NumRecord = DCount("[Field1]", "Table", "[Field1] = " & Forms!FormName!Control)

If NumRecord = 0 Then
MsgBox "Structure ID not in database. Please check and re-enter."
Else
DoCmd.OpenQuery "SingleItemQ", acViewNormal, acReadOnly
DoCmd.OpenReport "SingleItemR", acViewNormal, , , acWindowNormal
End If

End Sub

 

[utzja1]

Success! Thanks for your help.

Private Sub SingleCulvertRptB_Click()

Dim NumRecord As Integer

NumRecord = DCount("[StateStructureNum]", "NbiCulvert", "[StateStructureNum] = " & Forms!CulvertRptsF!SingleCulvID)

If NumRecord = 0 Then
MsgBox "Structure ID not in database. Please check and re-enter."
Forms!CulvertRptsF!SingleCulvID = Null
Else
DoCmd.OpenQuery "SingleNbiCulvQ", acViewNormal, acReadOnly
DoCmd.OpenReport "SingleNbiCulvR", acViewReport, "SingleNbiCulvQ", ,acWindowNormal
DoCmd.Close acQuery, "SingleNbiCulvQ", acSaveNo

End If

End Sub

 

[pr2-eugin]

Glad you have it sorted..