Unbound Master with bound detail subform based on a query

[vegeta456]

I am trying do display some data in a continous subform which is part of an unbound form.

The master form is supposed to display a error discription window. To do this I call the following sub procedure

 
Public Sub display_error_payadvice( LegacyEmpNumber As String, FullName As String, EmployeeNumber As String )
 
   DoCmd.OpenForm "ErrorPayAdvice"
   Forms!ErrorPayAdvice.[employee Number] = EmployeeNumber
   Forms!ErrorPayAdvice.[Full Name] = FullName
   Forms!ErrorPayAdvice.[Legacy Employee Number] = LegacyEmpNumber
   DoCmd.OpenForm "ErrorPayAdvice"
 
 

The 2 continous sub forms are based on the following queries

 
SELECT PAY_RUN_RESULTS.ELEMENT_NAME, Sum(PAY_RUN_RESULTS.AMOUNT) AS AMOUNT
FROM PAY_RUN_RESULTS
GROUP BY PAY_RUN_RESULTS.ELEMENT_NAME, PAY_RUN_RESULTS.PAY_ADVICE, PAY_RUN_RESULTS.EMPLOYEE_NUMBER
HAVING (((PAY_RUN_RESULTS.PAY_ADVICE)='D') And ((PAY_RUN_RESULTS.EMPLOYEE_NUMBER)=Forms!ErrorPayAdvice![Employee Number]));
 
 

The Master form displays the employee number, name details and legacy employee number Ok but the subforms do not. If I open the subforms separately, after entering a value for the employee number parameter, the forms display the correct data. If I right click into the subform rectangle on the screen and select the 'Remove Filter/Sort' the data then appears. I have looked through the properties for the subform but I cannot find a Filter/Sort property. What do I need to do in order to get the subforms to work?

The version of Access I am using is 2003 on Windows XP.

[nspader]

I am very confused as to what you are doing or trying to do.

Also, what you are trying to display.

Maybe it is just me, but I could use some more specific explanations.

Nick

[vegeta456]

Ok.
What should happen is that the sub procedure
sets some text items on on the form 'ErrorPayAdvice '

The form is then opened and the and the records in the subform displayed. The subform is based on the query shown and returns rows related to the the employee number in the master form.

[Stewart Ross]

Hi. I suspect that you are running into difficulties because at the time the form/subform combination is opened the unbound field Employee Number has no value set, so the underlying query you list returns no records. You should be aware that the sequence of events that occur when a form opens means that the recordsource for the subforms (the query you list) will be evaluated before your sub display_error_p ayadvice updates the fields which are supposed to filter the subforms.

Your second call to the OpenForm method in line 7 will not help you here, as the form is already open from the call in line 3. The second call is simply redundant.

To resolve this you will need to requery each subform after you set the field values in the main form within your subroutine. This refreshes the recordset which should now filter according to the employee number that is now present in the form.

Forms!ErrorPayAdvice.[employee Number] = EmployeeNumber
...
Forms![First Subform Name].Form.Requery 
Forms![Second Subform Name].Form.Requery
 

You will need to substitute the actual names of your subforms in the form field references for the requery calls above.

There are other ways to tackle this (by setting the recordsource for each subform to an SQL statement built in your code, for instance) but the requery method should at least resolve why you cannot see any records at present.

-Stewart

[vegeta456]

Thanks Stewart for your reply

Tried your suggestion but I am getting a Run time Error 2450

'Microsoft Office Access Cannont find the form 'Pay Run Earnings New' (this is one of the subforms) I think this is because the subform is not open at the time because I got the same error when I tried setting the values of the text items in the main form before the 'DoCmd.Openform

[Stewart Ross]

Thanks Stewart for your reply

Tried your suggestion but I am getting a Run time Error 2450

'Microsoft Office Access Cannont find the form 'Pay Run Earnings New' (this is one of the subforms) I think this is because the subform is not open at the time because I got the same error when I tried setting the values of the text items in the main form before the 'DoCmd.Openform

Apologies, I missed out the form name from the skeletons:

Forms![ErrorPayAdvice]![Pay Run Earnings New].Form.Requery
Forms![ErrorPayAdvice]![Second subform name].Form.Requery

-Stewart