Full Version: SubForm Requery (Revisited)
UtterAccess Forums > Microsoft® Access > Access Forms
Bemmy
I have managed to Requery a SubForm from an unbound main form after some excellent advice from Anne using the following Code: -
orms!Calendar![Appointments Summary].[Form].Requery
However after then applying and removing FilterBySelection the SubForm in question appears to freeze and will not Requery.
Any suggestions anyone...please?
Thanks
Jack Cowley
I have no idea how you are filtering your subform, but you can 'filter' your subform by linking it to a combo box or text box on the main form. The user makes a selection from the combo box and then the related record is shown in the subform. More details on how you are going about filtering your subform should help someone here to help you find a solution.
ack
Bemmy
Hi Jack
Thanks for the reply.
I am filtering by attaching code to the on click event of a Text box on the SubForm - DoCmd.RunCommand acCommand FilterBySelection.
I am trying to remove this by a double click on the same text box - DoCmd.RunCommand acCommand RemoveFilterSort - or something very similar!
Steve
bakersburg9
Steve - Did you get a solution ?
I have a situation where my client wants to see on his input form the results of the daily input, typically about 5-20 records only per day - he wants to see key fields from the table that the input form has as it's underlying source - I'm actually using a query, because I'm linking some fields, but nonetheless...
When I use the subform wizard, it looks great initially, but the data goes away when I start using the form for input - even when I open the form again after closing it, it doesn't seem to work - the only time it looks the way I want it is when I first set it up.
Steve from California, USA
Jack Cowley
Hmmm. I have never used that constant of the RunCommand method. In the forms Filter property there is probably the filter string and you need to get rid of it. Something like should do the trick:
e.SubformControlName.Form.FilterOn = False
hth,
Jack
Jack Cowley
Steve from California, USA -
That is the Record Source of your subform? What is the purpose of the main form?
Jack
bakersburg9
Jack -
The purpose of the main form is to input daily production runs of a parts manufacturing company - there are only 5-20 runs per day - I have set up a form the user is happy with, but he would like to see the results of the input to the underlying table right on the bottom of the form itself - he doesn't have to see it dynamically, but would like to be able to click a button on the form and see the daily input on the bottom of the screen.
So the form is frm_DailyProd the subform is called sfm_DailyProd, and is based on a query called, guess what ? qry_DailyProd
He knows a little bit about VBA and thinks you could set up an event which could have some code associated with it that would carry out this function - can this be done ?
Otried playing with subforms (HORROR !) and it worked ( or seemed to ) right away - but then the detail went away and never came back (really wierd)
Steve from CA
Jack Cowley
Your subform needs to be filtered by the current date or whatever way you have of knowing which records belong to the day they were entered. With that your subform should show the records that meet that criteria.
Create a form in Datasheet or Continuous view based on this query. Add it to the main form where you enter the data. It should show you the data that you want to see.
I do not understand what you mean when you say, "...the detail went away..."
Can you post a demo if you cannot get it to work so we can see what you are trying to do and how you are trying to do it?
Jack
bakersburg9
I'll try to attach - when I view the subform, it looks great - when I try to attach it to the form, there are just headers - I wonder how I did it right once... strange
Jack Cowley
Steve -
Well me which form is your subform, the subform query that you want to filter on and the form that has the subform...
Jack
bakersburg9
everything is (PREFIX) DailyProd...
bl_DailyProd
qry_DailyProd (the subform is based on this)
frm_DailyProd
sfm_DailyProd
Jack Cowley
There are a number of things going on... You are saving the date as Now() which is a Date and Time and can confuse Access if you are only looking for the Date. Use Date() instead of now and if you need to save the time a record was entered then that is another kettle of fish.
pen the attached and look at your input form and you should see records as you want. Notice the changes made in query "qry_DailyProd".
You really need to normalize your data before you go any further with your database, in my humble opinion...
Jack
Bemmy
Hi
That worked as far as getting rid of the filter. However I was still unable to Requery my SubForm afterwards.
Grateful for any suggestions!
Steve
Jack Cowley
Steve -
How do you know the filter was removed? Did the subform then show all the records? What is supposed to happen when you do your requery? Give as much detail as you can on what you are doing and what you expect to happen with the filter, without the filter, etc.
Jack
bakersburg9
Jack,
don't know what you mean by filter - I simply have a form used for input to write to a table - the user wants to see the results on the screen - he realizes this can't be done dynamically (at least I don't think it can be done) but maybe by clicking a button, something - I know it can be done, but I can't get it to look like a table/datasheet view - it looks really sloppy, and you have to scroll down to see it, etc.
Again, I have a simple query based on the table the tbl name is tbl_DailyProd and the query is qry_DailyProd - the input form is frm_DailyProd and the subform based on the query (source) is sfm_DailyProd
Jack Cowley
Steve -
think I sent you a copy of your database with the subform showing the records that were for a specific day. What part of what I sent you is not doing what you want? If you need more assistance then post the db back, as I did not keep a copy, and I will take a look.
Jack
bakersburg9
Jack,
Can u please look at my db and tell me why my 'PART NAME SHIP' field in my tbl_Shipping table does not update with the value in the text box in my input form ? The control name is "txtPartNameShipOut"
Jack Cowley
Steve -
You do not want to save 'Part Name Ship' in your table "tbl_Shipping". That data already exists in your table "tbl_PartList". Since it already exists in another table you can display the name at any time using a query.
Your txtPartNumberShipOut is an unbound control on the form and that is why it is not being saved, but you do not want to save that data in your table anyway. Remove that field from your shipping table...
HAs an aside I would strongly suggest that you normalize your structure and add Primary keys to your tables and link your related tables properly.
hth,
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.