Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

Openform subform filter

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
Sasha42
Asked:
Sasha42
  • 9
  • 8
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this instead - using the WHERE clause of the Form Open:

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

mx
0
 
Sasha42Author Commented:
The button is on frmMain.  I need it to reopen frmMain with the data filtered in the subform based on the PO Number.
0
 
Sasha42Author Commented:
The PO Number is text
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeffrey CoachmanCommented:
< 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
 
Jeffrey CoachmanCommented:
...in other words, why re-open it when you can filter it in-place?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"The PO Number is text"

Then

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

mx
0
 
Sasha42Author Commented:
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
 
Sasha42Author Commented:
Thanks, DatabaseMX.  But, the PO Number field is in the subform.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this:

Me.sfrmShipped.Form.Filter = "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
Me.sfrmShipped.Form.FilterOn = True
0
 
Sasha42Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is [PO Number] also on the main Form?

mx
0
 
Sasha42Author Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... that should be working then, assuming PO Number is the correct spelling (?) ...

Can you upload the db?

mx
0
 
Sasha42Author Commented:
I really appreciate it.  Is there an upload component on this site?
0
 
Sasha42Author Commented:
The find PO combo box is on the top left.  
Shipping-Database-for-Experts.accdb
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
 
Sasha42Author Commented:
Thanks so much for your help.  I just worked around it.  Not very elegant, but I came up with a solution.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 9
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now