Subform Requery problem

Kained

Registered User.
Local time
Today, 22:23
Joined
Jul 5, 2005
Messages
12
Hi there,

I hope someone can help... and i hope i can explain this properly.
Some background... I have a form which has a Global ID as one of the fields.
From this form another audit results form can be opened (on the click of a button) that shows the results of Audits based on this ID. The results of audits are in a flat un-normalised table(this is not an issue at this point)

On the top of the audit results is a textbox for the 'GID', the contents of which have been copied from the original form where the button was located. This process uses a macro.

OpenForm
Set Value
[Forms]![frm_Audit Result]![Audit Result GID]
=[Forms]![frm_Call Log]![txt_Global ID]

The audit results form has a subform which displays the result of a query based on the the value that is copied.

The txtbox displays the value fine, but the subform does not display the result of the query.

The query has number of fields but the criteria for filtering the correct ID's is:
[Forms]![frm_Audit Result]![Audit Result GID]

Its just that the subfrom does not display any result as soon as the audit results form is launched. If I alter the number in the text box and hit enter, the subform displays the correct results.

The subforms Record source is the query.

I've tried numerous ways around the problem with various attempt to update the query on various events, but have had no luck.

I guess i need to rerun the query and then refresh the subform once the main form has the GID copied over... but am not sure how to rerun the query and at what point/with which event?

Any pointers are much appreciated.
 
Last edited:
I know how to do it in VBA but not with a macro. Try this:
OpenForm
Set Value
[Forms]![frm_Audit Result]![Audit Result GID]
=[Forms]![frm_Call Log]![txt_Global ID]
Set Value
[Forms]![frm_Audit Result]![SubFormControl]![LinkMasterFields]
="WhatEverItIsSetToNow"]
Basically set the value of the LinkMasterField to whatever it is now and maybe it will force a requery of the SubForm.
 
Hmm...

my problem maybe that I dont have any Linked child or link master fields as I dont want to show any one record. I want to show the results of a query based on a GID copied from the original form as a table (not a form as their maybe more than 1 result)
Am sure it would work if the querys criteria was from the original form because that number would exist when the query needed it.

It must build the query before I copy the data from the original text box to the audit results text box on the audit result form.

Ive set the Source Object for the subform to the query... but still no joy.

my brain is hurting...
 
I'm confused. If you have not utilized the Link Master/Child Fields then how does the following happen?
If I alter the number in the text box and hit enter, the subform displays the correct results.
Is there code in the AfterUpdate event of the 'GID' textbox?
 
Yes.

After update does have a Form.Refresh in its event procedure.

I was fumbling around trying to get the damn thing to refresh once the textbox had a number copied to it and that seemed to work but only once i change the number.

So i know the form works, its just I cant get it to requery/refresh at the right point before it displays.

Perhaps I need to force a requery of the query that is the Source Object of the subform once the textbox in the main form has a value, but not sure where or how?

to recap...

[orginal form]
textbox_GID
textbox_bunch of other stuff
etc..
.
.
[Audit Form] (launched from the orginal form with a button)
textbox_AuditGID (copy of [original form]![textbox_GID] once opened )
subform_Auditresults (source object is audit_result Query)

[Audit Result Query]
GID (criteria based on [form]![textbox_AuditGID])
Location
etc..
.
.

If your wondering why I need the query criteria using the audit forms textbox_GID and not the original form its because I need to be able to edit the number and use the textbox_GID to change the query as the form is used as a stand alone search form.
 
Hi Kained,

I have not seen a reason yet to not use the Link Master/Child Fields of the SubFormControl. When you use this Form/SubForm as a search form do you search for something other than GID?

Executing a Form.Refresh causes all of the controls on the form to Refresh including the SubFormControl.

So far it looks like you are trying to avoid writing any VBA and using macros to accomplish as much as possible. There are limits to macros.
 
Basically the data (a flat table of results [CPM Results FY05]) is not normalised. The fields of interest in this table are


GID (can be duplicate)
Outlet name (Can be duplicate)
Cat 1 Result
Cat 2 Result
etc.
.
.
.

The form [frm_Audit Result] in which the subform resides has no 'record source' as it is essentially used to display a search, so the form isnt bound. As the form isnt bound I cant use child/master links.

The best result i have is to use a SQL query for the subform record source as follows.

SELECT [CPM Results FY05].GID, [CPM Results FY05].[Outlet Status], [CPM Results FY05].[Outlet Name], [CPM Results FY05].[Audit Comment], [CPM Results FY05].[APH Description], [CPM Results FY05].[Results Month] FROM [CPM Results FY05]
WHERE ((([CPM Results FY05].GID)=Forms![frm_Audit Result]![Audit Result GID]));

Ive trunkated this as there were many many fields.

As you can see the critera uses a control [Audit Result GID] on the parent form... which is probalby where its all falling over as am guessing when the query is run, the control hasnt yet had the data copied to it using the macro stated earlier...
Setvalue [Forms]![frm_Audit Result]![Audit Result GID]
=[Forms]![frm_Call Log]![txt_Global ID]
 

Users who are viewing this thread

Back
Top Bottom