DoCmd OpenForm - with WHERE clause not working ?

[luppi13]

Well, I want to thank you all for your help and input!! I have figured out that the OpenForm just doesn't work as it should when there is a WHERE statement present. I ran the openForm twice in a row and the data showed up. So, I replaced the second OpenForm with:
frmFDRInput.Filter = "tblLoanData.LOAN_NUM = '" & Me.cboSearchLoans.Column(0) & "'"
frmFDRInput.FilterOn = True

And it worked. I think this may be a bug from the latest update for Microsoft, but at least I got around it.

Thanks again!!!

 

[gta3005]

Reviving this old thread where I'm dealing with the same issue.

I created a search button where I'm using an input box to record an input and then opening the form filtered by the search criteria using the where condition in DoCmd.OpenForm

Here's the code I currently have for code:

Dim strInput As String

strInput = InputBox("Enter Manufacturing PN", "MFP Search", "MFP*")
If strInput = "" Then Exit Sub
MsgBox (strInput)

DoCmd.OpenForm "Mfg Part Management", , , "MFP = strInput"

The button almost works. It will prompt for strInput properly and store it (confirmed with msgbox) but then is unable to find the strInput valude in the where condition as it asks for strInput again. What am I missing here?

 

[Gasman]

You need to concatenate your variable.

"MFP = " & strInput

if numeric, else

"MFP = '" & strInput & "'"

if text

 

[gta3005]

MFP is a field from the Form that I'm trying to filter by. It is a field with the AutoNumber data type in the form.

Do I need to clarify it as a string to use it in the code?

 

[Gasman]

MFP is a field from the Form that I'm trying to filter by. It is a field with the AutoNumber data type in the form.

Do I need to clarify it as a string to use it in the code?

Well that would be numeric then?

 

[gta3005]

I see what you mean. This does remove the second input screen, but it takes me back a step.

When formatting it as

"MFP = " & strInput

It opens the form to a new record like the filter failed.

And as I just typed this response, I believe I figured it out. When searching, I'm searching for MFP*3, so I'm looking for part number 3. Well the MFP was added as a prefix so it doesn't appear to be actually being saved. So when I search for just 3, it properly filters and gives me the correct response. I will play with this more, but thank you for indirectly solving the issue! Seems to be how it usually happens...

 

[Gasman]

Well you have to compare like for like?
A human might know what you mean, but not a computer.

 

[Pat Hartman]

The string is not being concatenated to the autonumber so you can't use like. You have to search for "= 3" NOT "Like MFP3"

Also, NEVER use like unless you are working with a string and it is incomplete so your expression, if you use like MUST always include at least one wildcard.