Sharon
asked on
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.Filt er = "[PO Number]=" & Me.[PO Number]
DoCmd.OpenForm stDocName, , , stLinkCriteria
stDocName = "frmMain"
stLinkCriteria = Me.[sfrmJobInfo].Form.Filt
DoCmd.OpenForm stDocName, , , stLinkCriteria
ASKER
The button is on frmMain. I need it to reopen frmMain with the data filtered in the subform based on the PO Number.
ASKER
The PO Number is text
< 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
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
...in other words, why re-open it when you can filter it in-place?
"The PO Number is text"
Then
stDocName = "frmMain"
DoCmd.OpenForm stDocName,,, "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
mx
Then
stDocName = "frmMain"
DoCmd.OpenForm stDocName,,, "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
mx
ASKER
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.FilterO n = True
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.FilterO
ASKER
Thanks, DatabaseMX. But, the PO Number field is in the subform.
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
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
Try this:
Me.sfrmShipped.Form.Filter = "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
Me.sfrmShipped.Form.Filter On = True
Me.sfrmShipped.Form.Filter
Me.sfrmShipped.Form.Filter
ASKER
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!
Is [PO Number] also on the main Form?
mx
mx
ASKER
This is what you wrote:
Me.sfrmShipped.Form.Filter = "[PO Number]=" & Chr(34) & Me.[PO Number] & Chr(34)
Me.sfrmShipped.Form.Filter On = 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.Filter On = True
The cboPONumber is on frmMain. The PO Number field is in the subform. Thanks again.
Me.sfrmShipped.Form.Filter
Me.sfrmShipped.Form.Filter
I changed the me.[PO Number] to me.cboPONumber because it is actually pulling from the combobox:
Me.sfrmShipped.Form.Filter
Me.sfrmShipped.Form.Filter
The cboPONumber is on frmMain. The PO Number field is in the subform. Thanks again.
ok ... that should be working then, assuming PO Number is the correct spelling (?) ...
Can you upload the db?
mx
Can you upload the db?
mx
ASKER
I really appreciate it. Is there an upload component on this site?
ASKER
The find PO combo box is on the top left.
Shipping-Database-for-Experts.accdb
Shipping-Database-for-Experts.accdb
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
Please compact & Repair, then Zip up to reduce size. Explain exactly how to reproduce the issue.
Remove any sensitive data ....
mx
Capture1.gif
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks so much for your help. I just worked around it. Not very elegant, but I came up with a solution.
stDocName = "frmMain"
DoCmd.OpenForm stDocName,,, "[PO Number]=" & Me.[PO Number]
mx