Solved

Openform subform filter

Posted on 2011-03-07
19
354 Views
Last Modified: 2012-08-13
I have a main form, frmMain, with a subform, sfrmJobInfo.  On the main form, I have a combo box that says find PO Number (it queries the data in PO number field in the subform). On the afterupate event for the combobox I want the code to pull up the records in the subform.  This does not work.

    stDocName = "frmMain"
    stLinkCriteria = Me.[sfrmJobInfo].Form.Filter = "[PO Number]=" & Me.[PO Number]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

0
Comment
Question by:Sasha42
  • 9
  • 8
  • 2
19 Comments
 
LVL 75
ID: 35061831
Try this instead - using the WHERE clause of the Form Open:

    stDocName = "frmMain"
    DoCmd.OpenForm stDocName,,, "[PO Number]=" & Me.[PO Number]

mx
0
 

Author Comment

by:Sasha42
ID: 35061857
The button is on frmMain.  I need it to reopen frmMain with the data filtered in the subform based on the PO Number.
0
 

Author Comment

by:Sasha42
ID: 35062036
The PO Number is text
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35062136
< I need it to reopen frmMain>
Why?
Just filter it:

Try code like this on the main form combobox after update event:

[YourSubform].Form.Filter = "[PO Number]= " & Me.[PO Number]
[OrderDetails Subform].Form.FilterOn = True



JeffCoachman
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35062143
...in other words, why re-open it when you can filter it in-place?
0
 
LVL 75
ID: 35062182
"The PO Number is text"

Then

    stDocName = "frmMain"
    DoCmd.OpenForm stDocName,,, "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)

mx
0
 

Author Comment

by:Sasha42
ID: 35062324
I didn't know how else to approach it.

This is what I have.  The cboPoNumbers is returning the correct value and that value exists in the subform data.
The Main form and sub form are linked by Master and Child.  This looks like it should work, but it doesn't. Thanks for your help.

[sfrmShipped].Form.Filter = "[PO Number]= '" & Me.[cboPONumbers] & "'"
[sfrmShipped].Form.FilterOn = True
0
 

Author Comment

by:Sasha42
ID: 35062373
Thanks, DatabaseMX.  But, the PO Number field is in the subform.
0
 
LVL 75
ID: 35062381
Did you see my post just above ?

This is all you should need - one line of code to replace your original code:

    DoCmd.OpenForm "frmMain" ,,, "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)

mx
0
 
LVL 75
ID: 35062402
Try this:

Me.sfrmShipped.Form.Filter = "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
Me.sfrmShipped.Form.FilterOn = True
0
 

Author Comment

by:Sasha42
ID: 35062573
DatabaseMX, I get a "it can't in the field PO Number" error message.  I rebuilt it in case it had a snake bite and it still doesn't work.   Can you help me with a work around. Thanks!

0
 
LVL 75
ID: 35062612
Is [PO Number] also on the main Form?

mx
0
 

Author Comment

by:Sasha42
ID: 35062677
This is what you wrote:

Me.sfrmShipped.Form.Filter = "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
Me.sfrmShipped.Form.FilterOn = True

I changed the me.[PO Number] to me.cboPONumber because it is actually pulling from the combobox:

Me.sfrmShipped.Form.Filter = "[PO Number]=" & Chr(34) & Me.cboPONumber & Chr(34)
Me.sfrmShipped.Form.FilterOn = True

The cboPONumber is on frmMain.  The PO Number field is in the subform.  Thanks again.
0
 
LVL 75
ID: 35062745
ok ... that should be working then, assuming PO Number is the correct spelling (?) ...

Can you upload the db?

mx
0
 

Author Comment

by:Sasha42
ID: 35063050
I really appreciate it.  Is there an upload component on this site?
0
 

Author Comment

by:Sasha42
ID: 35063131
The find PO combo box is on the top left.  
Shipping-Database-for-Experts.accdb
0
 
LVL 75
ID: 35063146
Right on this page .. File >> Add File ..
Please compact & Repair, then Zip up to reduce size.  Explain exactly how to reproduce the issue.
Remove any sensitive data ....

mx
Capture1.gif
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 500 total points
ID: 35065945
sorry for the delay.  As you have probably noticed, EE site has been up/down all day.

The attached db 'seems' to now do what you want, as I understand it.  The main issue was the Link Child/Master fields on the subform control.  You need brackets around the Job Number because of the Space in the name, which is on of the 100,000 reasons not ... to use Spaces in *any* object Name.   Instead, you Proper Case naming for some like job number eg:

JobNumber  or JobNum

PONumber ... etc

Note... I  have an all day class tomorrow so online activity will be limited until tomorrow night.

mx
Shipping-Database-for-Experts-MX.zip
0
 

Author Comment

by:Sasha42
ID: 35074114
Thanks so much for your help.  I just worked around it.  Not very elegant, but I came up with a solution.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Office 365 home questions 7 65
How can you open the FORM2 2 31
Access 2016 - Labels prompt to print 4 31
Cascading Combo boxes between 2 sub navigation forms 1 15
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question