Author Topic: Open subForm based on Query with a parameter  (Read 2144 times)

GingerVA

  • Newbie
  • *
  • Posts: 7
Open subForm based on Query with a parameter
« on: February 23, 2012, 04:25:41 PM »
Ok since I can't open a report directly in a subform I am trying a work around.

Using the query to show my data (that would have been in the report) I created a form in datasheet view.
I want the form to open in the UI builder main form and display only the data based on a specific due date.

To accomplish this I have:
Created frmInvoice with and unbound text box
Created the query with this in the criteria box: [Forms]![frmmain_expandlg]![Subform1]![Due Date]
Inserted the query's form as a subform in frmInvoice named: qryfrmfeedue

When I open the frmInvoice from design view to form view outside of the main UI Builder form, a box pops up asking for my criteria, I enter the date and the data appears on the form.

But I when I open frmInvoice and enter data in the unbound text box it doesn't work.

I would prefer the qryfrmfeedue opens on the frmInvoice after I enter the due date in the unbound text box.

But I can't figure out how to get it to recognize the date run the underlying query.

Next I tried creating a button that would open the form, which it did, but in a new window.

Next I tried adding the qryfrmfeedue on the Invoice submenu with submenu button 'Preview Invoices'. It will open the form, but does not ask for the criteria to be opened.

Please help! :)
I have attached a screen shot of the frmInvoice how I want it to look in case that helps.
Thanks! Ginger


opengateadmin

  • Administrator
  • Sr. Member
  • *****
  • Posts: 336
    • Microsoft Access
Re: Open subForm based on Query with a parameter
« Reply #1 on: February 23, 2012, 04:47:55 PM »
Here's what I would recommend:

1) Make qryfrmfeedue Visble = No (in other words, make it hidden when your form loads).
2) Change your query to not use the [Forms]![frmmain_expandlg]![Subform1]![Due Date] criteria.
3) Create a new On_Exit event for your unbound text box with this:

Code: [Select]
If isdate(Me.YourTextBoxName.Value) then
   me.qryfrmfeedue.form.filter = "[Due Date] = #" & Me.YourTextBoxName.Value & "#"
   me.qryfrmfeedue.form.filteron = true
  me.qryfrmfeedue.visible = true
endif
The above assumes that the subform frame for qryfrmfeedue is also named qryfrmfeedue within frmInvoice.  And you'd want to replace [Due Date] with whatever the field name is in qryfrmfeedue for your due date.

This will cause the subform to display and be filtered if they enter a date and then exit the unbound textbox.

let me know if you need any help.  You can also email a copy of your db to support@opengatesw.net if you want me to look at it.

GingerVA

  • Newbie
  • *
  • Posts: 7
Re: Open subForm based on Query with a parameter
« Reply #2 on: February 23, 2012, 05:29:49 PM »
Thank you very much! I did have to tweak it bit based on real names of fields, etc., and it now works beautifully! :)