Solved

Openform subform filter

Posted on 2011-03-07
19
331 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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
The PO Number is text
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
< 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
Comment Utility
...in other words, why re-open it when you can filter it in-place?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
"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
Comment Utility
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
Comment Utility
Thanks, DatabaseMX.  But, the PO Number field is in the subform.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Is [PO Number] also on the main Form?

mx
0
 

Author Comment

by:Sasha42
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
ok ... that should be working then, assuming PO Number is the correct spelling (?) ...

Can you upload the db?

mx
0
 

Author Comment

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

Author Comment

by:Sasha42
Comment Utility
The find PO combo box is on the top left.  
Shipping-Database-for-Experts.accdb
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks so much for your help.  I just worked around it.  Not very elegant, but I came up with a solution.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

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…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now