I have a continuous form (Takings) based on dates and I want to synchronise a
second form (TakingsWages) based on a crosstab query pulling information from
the date in the Takings form.
I can’t use subforms because of space problems, and I can’t use a tabbed
form, as both forms need to be seen together. The continuous form shows
takings on a particular day, and the CrossTab form shows staff on duty that
day with their wages.

Main form synchronising field is Takings!TakingsDate and the CrossTab form
synchronising field is DatePaid I have set a parameter in the CrossTab Query
and this runs OK The CrossTab form in design view shows an error condition on
all the fields, (this control has an invalid ControlSource) but works OK in
spite of that.

The OnCurrent Event of the Takings form has
Private Sub Form_Current()
Dim Frm As Form
Set Frm = Forms!TakingsWages
Frm.Requery
End Sub

Trouble is that the wages form only shows the correct record if you close the
TakingsWages form and re-open it with the appropriate record selected in the
Takings form. The Requery does nothing
Any ideas
Phil

On 02/02/2012 18:12:48, "Phil" wrote:
Not to worry - problem sorted

How'd you 'sort it'? Someone else might benefit from your approach.

--
Larry Linson
Microsoft Office Access MVP
Co-Author, Microsoft Access Small Business Solutions, Wiley 2010

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote

On 03/02/2012 17:42:17, "Access Developer" wrote:
Hi Larry

That's what the group is for, I should have posted the solution on my
previous post.

First thing was to get the SQL for the TakingWages subsidiary form correct,
and this is the form's recordset

PARAMETERS [Forms]![Takings]![TakingsDate] DateTime;
TRANSFORM Sum(Nz([WeightedWage],0)) AS SumOfWage
SELECT QWages.EmployeeName, QWages.DatePaid, QWages.HoursWorked
FROM QWages WHERE
(((DDiff("d",CStr([Forms]![Takings]![TakingsDate]),CStr([DatePaid])))=0))
GROUP BY QWages.EmployeeName, QWages.DatePaid, QWages.HoursWorked ORDER BY
QWages.EmployeeName, QWages.HoursWorked DESC PIVOT QWages.GroupName In
('Bar','Galley');

2 things of interest, 1 is I use a DDiff function (similar to DateDiff() but
using strings as input rather than dates. Seems to get over the problem of
different date formats in UK & US. Secondly, I had to declare the column
names (there are only 2, Bar & Galley) though it would be simple
programmatically to add as many as you like. Unless you do this, if there is
no data for a column (No Galley takings on a particular day) you get a #Name
error.

The OnCurrent Event on the main takings form is outlined in the original post

If Isloaded("TakingsWages") Then
Set Frm = Forms!TakingsWages
Frm.Requery
Frm!SumHoursWorked.Requery ' Totals
Frm!SumBar.Requery
Frm!SumGalley.Requery
End If

I think my problem in the first place was caused by not getting the
parameters correct and not defining the column names. Anyway, problem
solved!!!!!!

Phil