Solved Filter form Only (1 Viewer)

[theinviter]

Dears:
i have created a form that contain a search textbox, once user update then it will filter the form.
i have applied the below code but it apply the filter on both form and subform .
so can you please advise me how to correct to filter form only.
Private Sub FindCode_AfterUpdate()

On Error GoTo errhandler

''n = 1 / 0 ' cause an error



Dim strFilter As String

strFilter = ""

If Len(Me.FindCode & "") <> 0 Then

strFilter = " or '' &

 Like '" & Me.FindCode & "'"

End If



If Len(Me.FindCode & "") <> 0 Then

strFilter = strFilter & " or '' & [Other Code] Like '" & Me.FindCode & "'"

End If





If Len(strFilter) > 0 Then

strFilter = Mid(strFilter, 4)

With Me

.Filter = strFilter

.FilterOn = True
Me.Table1_Subform.SetFocus
DoCmd.GoToControl ("[Qnty_IN]")
DoCmd.GoToRecord , , acNewRec
End With

Else

'Me.Filter = ""

Me.FilterOn = False

End If



Exit Sub

errhandler:

' error handling code

Resume Next
End Sub

 

[The_Doc_Man]

If the sub-form is linked to the main form via the Parent/Child links that reflect a relationship between parent and child, then the sub-form WILL be affected by the filter when the main form requeries and that requery is propagated down to the sub-form. Access does that requery because ignoring a declared parent/child relationship would violate referential integrity.

The question is, why would you NOT want the sub-form to react to changes in its parent that change what is visible? Stated another way, if you truly have a relationship between parent form and child form, why would you wish to ignore the relationship?

If this is not what you meant by "i have applied the below code but it apply the filter on both form and subform so can you please advise me how to correct to filter form only" then please explain in a bit more detail.

ADDENDUM: If the sub-form DOES NOT have anything in the Parent/Child links AND does not have a declared relationship - but you still see that change, then we would have to see the SQL version of the .Recordsource used for each of the two forms to figure out why.

 

[theinviter]

If the sub-form is linked to the main form via the Parent/Child links that reflect a relationship between parent and child, then the sub-form WILL be affected by the filter when the main form requeries and that requery is propagated down to the sub-form. Access does that requery because ignoring a declared parent/child relationship would violate referential integrity.

The question is, why would you NOT want the sub-form to react to changes in its parent that change what is visible? Stated another way, if you truly have a relationship between parent form and child form, why would you wish to ignore the relationship?

If this is not what you meant by "i have applied the below code but it apply the filter on both form and subform so can you please advise me how to correct to filter form only" then please explain in a bit more detail.

ADDENDUM: If the sub-form DOES NOT have anything in the Parent/Child links AND does not have a declared relationship - but you still see that change, then we would have to see the SQL version of the .Recordsource used for each of the two forms to figure out why.

Thanks for explanation.
Yes the form is linked to subform. As there is 2 criteria to filter the for by ited code and other code.
When the user enter the code in text field then, will filter the form to that record. As the subform has other data related to item code.
What I want is that when user enter the code in text field then. It will for that code in ( code, and other code) 2 field. And then filter the form. If not found then show a msgbox ( Record not found).
I will share the database later.

 

[theinviter]

Find the database in link below

Solved - Filter Between Dates

Dears; I have a sub form that contain Date Filter filed ( Date_From and Date_To). so i tried to filter the form with below code and work fine. but i clear the filter by clicking on button, got error message " The Search key was not found in any record" so how to avoid this error. Private...

www.access-programmers.co.uk

 

[The_Doc_Man]

OK, it is clear that we have a minor language issue here. So let me repeat what I got out of that.

You have 2 fields that will be used to filter some table. You want to put the filter strings in one or two text boxes and have the form test for whether there is a record matching the pair of field values. If you find a match, display the record. If you don't find a match, display a message.

Did I understand that correctly?

If I got that right, this whole situation depends on building the "criteria" string, which is a WHERE clause except you don't include the word "WHERE" in the clause. I opened the DB in the linked post but I didn't see enough to help me understand where that was going. So I'll try to describe this in English and hope you can follow it.

The criteria string might resemble

strCriteria = "( [Date_] >= #" & Me.StartDate & "# ) AND ( [Date_] <= #" & Me.EndDate & "# )"

You build that string as step 1. Step 2 would be to use

lRecordCount = DCount( "*", "Stock", strCriteria )

Then you would test whether lRecordCount is 0.

IF lRecordCount = 0 Then
    MsgBox "No records found", vbOKOnly, "No Records"
Else
    Me.Filter = strCriteria
    Me.FilterOn = TRUE
    Me.ReQuery
End if

There would obviously be more to it than this, but this is showing the basics. The problem is, your description from the start of the problem to the most recent description does not seem consistent. Which makes me wonder if there is more going on than you have described. We all want to help folks here, but the difficulty will always be whether our language differences become a barrier to understanding. Inconsistent descriptions can be a problem that makes help harder to offer. Don't give up, but we need to have as much clarity as possible in understanding what you REALLY want to do.

 

[theinviter]

Please find the correct file attached, as i Update the filed "Code" so need the form to filer for item code and other code. as i need the filter to be applied in the form only.

 

[arnelgp]

test and check.

 

[theinviter]

test and check.

thanks , but i need also to search also in "Other Code" . and is there a way to filter after update.

 

[theinviter]

thanks , but i need also to search also in "Other Code" . and is there a way to filter after update.

i got it, , but i want now to apply this after update.

 

[arnelgp]

here on AfterUpdate of combo/textbox