Using and InputBox in OpenForm WHERE clause

[shaggles]

I am trying to use an inputbox to determine which form
should open on startup. I also want to use the string
derived from the input to filter the form that opens.
This is where I am running into trouble. The closest I've
gotten is the form below. With this I get a second pop-up
with whatever I've entered in the inputbox. Entering it
again gives me the filtered form. Any advice?
Dim strDept As String
strDept = InputBox("Enter Dept ID")
If strDept = "Compliance" Then
DoCmd.OpenForm "Reg_Notice_Main"
Else:
DoCmd.OpenForm "frmBusiness_Line",,,"[frmBusiness_line].
[DeptID]= " & strDept
End If

 

[Marshall Barton]

I am trying to use an inputbox to determine which form
should open on startup. I also want to use the string
derived from the input to filter the form that opens.
This is where I am running into trouble. The closest I've
gotten is the form below. With this I get a second pop-up
with whatever I've entered in the inputbox. Entering it
again gives me the filtered form. Any advice?
Dim strDept As String
strDept = InputBox("Enter Dept ID")
If strDept = "Compliance" Then
DoCmd.OpenForm "Reg_Notice_Main"
Else:
DoCmd.OpenForm "frmBusiness_Line",,,"[frmBusiness_line].
[DeptID]= " & strDept
End If

If the DeptID field is a text field, then you need to use
quotes around the value:

DoCmd.OpenForm "frmBusiness_Line",,, _
"DeptID= '" & strDept & "'"

 

[Albert D. Kallal]

Try:

DoCmd.OpenForm "frmBusiness_Line",,,"[DeptID]= " & strDept


You do not need to include, or refernce the form name. You simply want
DeptID = to some number.

As Marshal mentoned....if the DeptID is a string, then you need:

DoCmd.OpenForm "frmBusiness_Line",,,"[DeptID]= '" & strDept & "'"

 

[shaggles]

That did it. Thanks guys.