Search form for both mainform and subform

[vid]

Hi!

I've been breaking my head over this problem for 2 days now and haven't managed to find a solution! please help..

I am attaching a sample db. The problem is that when I search by a field (eg. Technical Team Feedback) in the subform of the form being opened(search form) the "view all" button doesn't work..

Please help!!! Its driving me crazy...

 

[HiTechCoach]

Hi!

I've been breaking my head over this problem for 2 days now and haven't managed to find a solution! please help..

I am attaching a sample db. The problem is that when I search by a field (eg. Technical Team Feedback) in the subform of the form being opened(search form) the "view all" button doesn't work..

Please help!!! Its driving me crazy...

The reason the view all button does not work is that the search form is a pop up and is taying on top.

You will need to either code the Search form or hide it.

I add one line of code to the View All On Click Evnetand it works:

   ' hide the search form
   Me.Visible = False  ' <<<< added this
   ' view the records
   DoCmd.OpenForm "Edit Claims", , , strWhere

 

[HiTechCoach]

If you will have already filtered the search form before using the View All, then you could use this:

Private Sub view_all_Click()
      
   Me.Visible = False
   DoCmd.OpenForm "Edit Claims", , , Me.Filter
       
End Sub

 

[vid]

Ummm.. I don't think you understood the problem properly..

Try this:

Chose a requestor and click on View All.. It works perfectly.
Then unfilter everything and chose a technical team feedback. Again click on view all.. It has problem... Even though it lists the correct records, it doesn't open them up like it does in the case of requestor or any other..

I think this problem is because the Technical Team Feedback and the product code/ name lie in the subform so it cannot filter directly.. For the others since they are in the main Edit Claims form, its working..

I hope I have explained better...

Any help will be greatly appreciated!! Thanks in advance

 

[HiTechCoach]

Ummm.. I don't think you understood the problem properly..

Try this:

Chose a requestor and click on View All.. It works perfectly.
Then unfilter everything and chose a technical team feedback. Again click on view all.. It has problem... Even though it lists the correct records, it doesn't open them up like it does in the case of requestor or any other..

I think this problem is because the Technical Team Feedback and the product code/ name lie in the subform so it cannot filter directly.. For the others since they are in the main Edit Claims form, its working..

I hope I have explained better...

Any help will be greatly appreciated!! Thanks in advance

I this I figure out the "Tech Check Feedback" (I think that is what you meant by "technical team feedback") issue.

You will not be able to use the Search form's filter to filter the Edit for since they have a different record source.

I have done search forms similar to this before.

What I would suggest is that you will need to append all the filtered unique values for [Warranty ID] into a temp table. Use this table in the [Edit claims] form's record source to filter the records.

I would urge you to avoid using spaces in object and field names.

 

[vid]

Thank you for your response It makes complete sense... I figured I needed to do something on these lines just wasn't sure exactly what I should be doing!

I will try this out and get back to you if I have more doubts Thanks again!!

 

[vid]

Could you tell me how to "append all the filtered unique values for [Warranty ID] into a temp table"... That would be a great help

 

[HiTechCoach]

Could you tell me how to "append all the filtered unique values for [Warranty ID] into a temp table"... That would be a great help

I normally have a table already create with on the primary key field already created.

1) I run a delete query to empty the table.

2) build a SQl statement in VAB code to do the append building the WHERE clause that is the same as the filter.

3) run the append query. Note: since there is already a primary key set in the work table, it will only only put each [Warranty ID] in the table once. The duplicates will be automatically removed.

 

[vid]

Thanks for all your help

this is the query i'm trying to use but its giving me an error.. Could you please suggest why this is happening!

CurrentDb().Execute "INSERT INTO Temp ([Warranty ID]) VALUES (" & Me!WarrantyID & ") WHERE strWhere"

I just don't know how to refer to all the WarrantyIDs that are returned after the filter is applied..

Thanks again!

 

[HiTechCoach]

Thanks for all your help

this is the query i'm trying to use but its giving me an error.. Could you please suggest why this is happening!

CurrentDb().Execute "INSERT INTO Temp ([Warranty ID]) VALUES (" & Me!WarrantyID & ") WHERE strWhere"

I just don't know how to refer to all the WarrantyIDs that are returned after the filter is applied..

Thanks again!

Try:

CurrentDb().Execute "INSERT INTO Temp ([Warranty ID]) SELECT [Warranty Claim].[Warranty ID] FROM [Warranty Claim] LEFT JOIN [Warranty Claim Details] ON [Warranty Claim].[Warranty ID] = [Warranty Claim Details].[Warranty ID]; WHERE " & strWhere

 

[vid]

CurrentDb().Execute "DELETE * FROM [Temp]"
Sql = "(INSERT INTO Temp ([Warranty ID]) SELECT DISTINCT * FROM [Search Results Query] WHERE ' " & strWhere & " ')"
CurrentDb().Execute Sql

This is the code I've managed to come up with.. everything else is working fine now! i just don't know the correct syntax to make the WHERE part of the query as the filter ie StrWhere

Please help!

 

[vid]

Thanks! its works perfectly.. just removed the ;

Thanks a ton!!!

 

[HiTechCoach]

Thanks! its works perfectly.. just removed the ;

Thanks a ton!!!

You're welcome!

Glad to assist.