Search form for both mainform and subform (1 Viewer)

V

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
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...
 

Attachments

  • Sample db.zip
    133 KB · Views: 187
HiTechCoach

HiTechCoach

Well-known member
Local time
Yesterday, 20:18
Joined
Mar 6, 2006
Messages
4,357
vid said:
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...
Click to expand...

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:

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

HiTechCoach

Well-known member
Local time
Yesterday, 20:18
Joined
Mar 6, 2006
Messages
4,357
If you will have already filtered the search form before using the View All, then you could use this:

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

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
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

HiTechCoach

Well-known member
Local time
Yesterday, 20:18
Joined
Mar 6, 2006
Messages
4,357
vid said:
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 :)
Click to expand...

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.
 
V

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
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!!
 
V

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
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

HiTechCoach

Well-known member
Local time
Yesterday, 20:18
Joined
Mar 6, 2006
Messages
4,357
vid said:
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 :)
Click to expand...

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.
 
V

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
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

HiTechCoach

Well-known member
Local time
Yesterday, 20:18
Joined
Mar 6, 2006
Messages
4,357
vid said:
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!
Click to expand...

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
 
Last edited:
V

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
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!
 
V

vid

Registered User.
Local time
Yesterday, 18:18
Joined
Feb 4, 2010
Messages
62
Thanks! its works perfectly.. just removed the ;

Thanks a ton!!! :D
 
You must log in or register to reply here.

Similar threads

MattBaldry
Solved Open Form and Apply Filter to Subform
Replies
3
Views
319
theDBguy
theDBguy
B
Update main form text boxes as subform is filled in? Also, subforms not linking?
Replies
4
Views
675
gemma-the-husky
gemma-the-husky
C
THe dreaded #Name? is ruining my form!!!!
Replies
9
Views
372
theDBguy
theDBguy
M
Solved Multi combo box and textbox search
Replies
14
Views
1,065
mounty76
M
M
Solved Search and find records on a continuous subform
2
Replies
33
Views
1,479
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom