Open Form Linked to Main Form?

[Dave Elliott]

I have a main form named TimeCards with a sub-form on it named Time and
Hours
I have another form with the same record source as the form Time and Hours
that I wish to open showing the same records as the sub-form Time and Hours
which is linked to the main form with a master/child relationship. This form
is named EmpCalc
The table where the sub-form is based on is named Hours with a HoursID as
the primary key
It also has a TimeID that is used to link it to the main form TimeCards

How can I make the new form, EmpCalc show the records that the main form
TimeCards via the sub-form shows, i.e. Time and Hours?

I tried but it didnt work!

Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm "EmpCalc", "", "[HoursID]=[Forms]![Time_Hours]![HoursID]"

'stDocName = "EmpCalc"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Steve Schapel]

Dave,

There are syntax errors in both your examples. Try it like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" &
[Forms]![TimeCards]![Time_Hours].Form![HoursID]

Or, if the EmpCalc from is being opened, as I imagine may be the case,
via an event on the Time_Hours subform, it would be more like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" & Me.HoursID

By the way, is it supposed to be [Time_Hours] or [Time and Hours]?

 

[Dave Elliott]

Ok getting closer, but no cigar. It opens now starting with the right
record, just too many records for the main record.
example: it should show only 49 records for the one record on the main form,
but instead it shows ALL the records in the table.
Example; TimeCards form (Main Form) composes one record with the sub-form
(Time and Hours) for this record having many records. I am using another
form named EmpCalc which I want to ONLY show the same records as the
sub-form Time and Hours that makes up ONE record via the main form
TimeCards.



'Set Me.Form.Recordset = Forms("Time and Hours").Form.Recordset
DoCmd.OpenForm "EmpCalc", , , "[Employee ID]=" &
[Forms]![TimeCards]![Time_Hours].Form![Employee ID]

Dave,

There are syntax errors in both your examples. Try it like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" &
[Forms]![TimeCards]![Time_Hours].Form![HoursID]

Or, if the EmpCalc from is being opened, as I imagine may be the case, via
an event on the Time_Hours subform, it would be more like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" & Me.HoursID

By the way, is it supposed to be [Time_Hours] or [Time and Hours]?

--
Steve Schapel, Microsoft Access MVP

I have a main form named TimeCards with a sub-form on it named Time and
Hours
I have another form with the same record source as the form Time and
Hours that I wish to open showing the same records as the sub-form Time
and Hours which is linked to the main form with a master/child
relationship. This form is named EmpCalc
The table where the sub-form is based on is named Hours with a HoursID as
the primary key
It also has a TimeID that is used to link it to the main form TimeCards

How can I make the new form, EmpCalc show the records that the main form
TimeCards via the sub-form shows, i.e. Time and Hours?

I tried but it didnt work!

Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm "EmpCalc", "", "[HoursID]=[Forms]![Time_Hours]![HoursID]"

'stDocName = "EmpCalc"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Steve Schapel]

Dave,

In that case, if I understand your earlier psot correctly, you should be
using the TimeID in the Where Condition, not the Employee ID. Maybe
like this?...
DoCmd.OpenForm "EmpCalc", , , "[TimeID]=" &
[Forms]![TimeCards]![Time_Hours].Form!TimeID

Just try and work out what criteria will determine the records you want.
Obviously if you use the Employee ID as the criteria, you will get all
the records for that Employee.

 

[Dave Elliott]

tried TimeID instead but it still shows the same amount of records.Too many
Would I instead have to refer to the main/sub-form somehow?

Ok getting closer, but no cigar. It opens now starting with the right
record, just too many records for the main record.
example: it should show only 49 records for the one record on the main
form, but instead it shows ALL the records in the table.
Example; TimeCards form (Main Form) composes one record with the sub-form
(Time and Hours) for this record having many records. I am using another
form named EmpCalc which I want to ONLY show the same records as the
sub-form Time and Hours that makes up ONE record via the main form
TimeCards.



'Set Me.Form.Recordset = Forms("Time and Hours").Form.Recordset
DoCmd.OpenForm "EmpCalc", , , "[Employee ID]=" &
[Forms]![TimeCards]![Time_Hours].Form![Employee ID]

Dave,

There are syntax errors in both your examples. Try it like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" &
[Forms]![TimeCards]![Time_Hours].Form![HoursID]

Or, if the EmpCalc from is being opened, as I imagine may be the case,
via an event on the Time_Hours subform, it would be more like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" & Me.HoursID

By the way, is it supposed to be [Time_Hours] or [Time and Hours]?

--
Steve Schapel, Microsoft Access MVP

I have a main form named TimeCards with a sub-form on it named Time and
Hours
I have another form with the same record source as the form Time and
Hours that I wish to open showing the same records as the sub-form Time
and Hours which is linked to the main form with a master/child
relationship. This form is named EmpCalc
The table where the sub-form is based on is named Hours with a HoursID
as the primary key
It also has a TimeID that is used to link it to the main form TimeCards

How can I make the new form, EmpCalc show the records that the main form
TimeCards via the sub-form shows, i.e. Time and Hours?

I tried but it didnt work!

Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm "EmpCalc", "", "[HoursID]=[Forms]![Time_Hours]![HoursID]"

'stDocName = "EmpCalc"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Steve Schapel]

Dave,

I am not really able to answer that question, on the basis of the
information I have at present. But here's the concept... Think about
the table or query that is the Record Source of the EmpCalc form. Ok,
which field(s) need to have a criteria applied in order for you to
obtain the records you require? Ok, which form(s) have data in the
current record which will determine these criteria? Ok, so that's what
you have to use. As I mentioned before, the syntax of your code will
also depend on the where the code is being called from, which only you know.

 

[Dave Elliott]

How can I make the form (EmpCalc) ONLY show the same records as the form
Time and Hours located on my main form TimeCards?
Both Time and Hours and EmpCalc have the same recordsource, that is they use
the same query.
Tried the bellow code for the EmpCalc form on the load event, ALL records
showed.

Set Me.Form.Recordset = Forms("Time and Hours").Form.Recordset

tried TimeID instead but it still shows the same amount of records.Too
many
Would I instead have to refer to the main/sub-form somehow?

Ok getting closer, but no cigar. It opens now starting with the right
record, just too many records for the main record.
example: it should show only 49 records for the one record on the main
form, but instead it shows ALL the records in the table.
Example; TimeCards form (Main Form) composes one record with the sub-form
(Time and Hours) for this record having many records. I am using another
form named EmpCalc which I want to ONLY show the same records as the
sub-form Time and Hours that makes up ONE record via the main form
TimeCards.



'Set Me.Form.Recordset = Forms("Time and Hours").Form.Recordset
DoCmd.OpenForm "EmpCalc", , , "[Employee ID]=" &
[Forms]![TimeCards]![Time_Hours].Form![Employee ID]

Dave,

There are syntax errors in both your examples. Try it like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" &
[Forms]![TimeCards]![Time_Hours].Form![HoursID]

Or, if the EmpCalc from is being opened, as I imagine may be the case,
via an event on the Time_Hours subform, it would be more like this...
DoCmd.OpenForm "EmpCalc", , , "[HoursID]=" & Me.HoursID

By the way, is it supposed to be [Time_Hours] or [Time and Hours]?

--
Steve Schapel, Microsoft Access MVP


Dave Elliott wrote:
I have a main form named TimeCards with a sub-form on it named Time and
Hours
I have another form with the same record source as the form Time and
Hours that I wish to open showing the same records as the sub-form Time
and Hours which is linked to the main form with a master/child
relationship. This form is named EmpCalc
The table where the sub-form is based on is named Hours with a HoursID
as the primary key
It also has a TimeID that is used to link it to the main form TimeCards

How can I make the new form, EmpCalc show the records that the main
form TimeCards via the sub-form shows, i.e. Time and Hours?

I tried but it didnt work!

Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.OpenForm "EmpCalc", "",
"[HoursID]=[Forms]![Time_Hours]![HoursID]"

'stDocName = "EmpCalc"
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Steve Schapel]

Dave,

I imagine the records shown in the Time and Hours subform are
restricted, not via the query that is the record source of the form, but
via the link to the TimeCards form. If you have a look at the Link
Master Fields and Link Child Fields properties of the subform, you will
see what field(s) are involved.

As regards controlling the records included in the EmpCalc form, you can
do this via a criteria in the query that it is based on. This is
probably the way I would do it. Or you can do it, as discussed
previously, via the Where Condition argument of the OpenForm method when
opening the EmpCalc form. If you need more specific help with these,
you will need to supply more detailed information, as I indicated
previously.

 

[Albert D.Kallal]

Since you are talking about the "child" records, and want to show the "many"
side.

Then, you should use:

DoCmd.OpenForm "EmpCalc", , , "[Employee ID] = " & me![Employee ID]

Remember, the 'where' clause is simply standard sql. The above says:

for all records in form EmpCalc, restrict the data to a field called
[Employee ID] to the current employee id in our main form (me!Employee ID]

By the way, in the future...stay away from putting spaces in field
names..they are VERY nasty, and can cause all kinds of problems. (and, sql
server, oracle, and most database systems do not support spaces anyway).

Further:

"[Employee ID] = " & me![Employee ID]

^^^^ above is name of field in the table EmpCalc is attached to
So, if the name of the field in the EmpCalc is NOT [Employee ID], then
change the above to whatever it is supposed to be

"[Employee ID] = " & me![Employee ID]

^^^^ above is name of field in
our main form here. Again, if the name of the employeeID field in our main
form is not as above, then change it.

it is not clear if the button you are talking about is on the main form, or
the sub-form. The above assumes your button is on the main form.

There is NO need to reference, or look at the values in the sub-form here,
since all you are doing is restricting the new form to all records that
belong
to our "parent" record.

One more thing, if you allow editing in your sub-form, and then open another
form with all those sub-form records, you can get a conflict. This means
BEFORE you open that new form, you need to FORCE a disk write of the
sub-form records. Hence, you need:

me.MySubFormContorlName.Form.Refresh
DoCmd.OpenForm "EmpCalc", , , "[Employee ID] = " & me![Employee ID]

 

[Steve Schapel]

PMFJI here Albert, and just to try and avoid further confusion in an
already confused situation... Dave does not want the EmpCalc form to
show all records for the [Employee ID]. He wants the records as per
those displayed in his [Time and Hours] subform, which is a subset of
the Employee's records. So far, he has been unable to tell us what
field(s) is the basis for this data selection, and also he has not
responded to my requests for further information about a number of other
things, so specific advice is difficult, but I feel sure that opening
the EmpCalc form based on the current Employee ID is not what is
required.

 

[Dave Elliott]

Thanks all for your hlep, but I approached the problem from a different
aspect.
Tried to describe the best I could.
I just made a copy of the form Time and Hours (EmpCalc) and linked it to
the main form. Now I am back to the original quest of counting the records
on this form where on any day there are more than 2 employees who worked on
it.
EmployeeID: which looks up the employee
Work Date: which is the date the employee worked
Text34: which show the day =Format([Work Date],"dddd")
Hours: which count the regular hours worked
Overtime: which shows the overtime hours worked.

PMFJI here Albert, and just to try and avoid further confusion in an
already confused situation... Dave does not want the EmpCalc form to show
all records for the [Employee ID]. He wants the records as per those
displayed in his [Time and Hours] subform, which is a subset of the
Employee's records. So far, he has been unable to tell us what field(s)
is the basis for this data selection, and also he has not responded to my
requests for further information about a number of other things, so
specific advice is difficult, but I feel sure that opening the EmpCalc
form based on the current Employee ID is not what is required.

--
Steve Schapel, Microsoft Access MVP

Since you are talking about the "child" records, and want to show the
"many"
side.

Then, you should use:

DoCmd.OpenForm "EmpCalc", , , "[Employee ID] = " & me![Employee ID]

Remember, the 'where' clause is simply standard sql. The above says:

for all records in form EmpCalc, restrict the data to a field called
[Employee ID] to the current employee id in our main form (me!Employee
ID]

By the way, in the future...stay away from putting spaces in field
names..they are VERY nasty, and can cause all kinds of problems. (and,
sql
server, oracle, and most database systems do not support spaces anyway).

Further:

"[Employee ID] = " & me![Employee ID]

^^^^ above is name of field in the table EmpCalc is attached to
So, if the name of the field in the EmpCalc is NOT [Employee ID], then
change the above to whatever it is supposed to be

"[Employee ID] = " & me![Employee ID]

^^^^ above is name of field
in
our main form here. Again, if the name of the employeeID field in our
main form is not as above, then change it.

it is not clear if the button you are talking about is on the main form,
or
the sub-form. The above assumes your button is on the main form.

There is NO need to reference, or look at the values in the sub-form
here,
since all you are doing is restricting the new form to all records that
belong
to our "parent" record.

One more thing, if you allow editing in your sub-form, and then open
another form with all those sub-form records, you can get a conflict.
This means BEFORE you open that new form, you need to FORCE a disk write
of the sub-form records. Hence, you need:

me.MySubFormContorlName.Form.Refresh
DoCmd.OpenForm "EmpCalc", , , "[Employee ID] = " & me![Employee ID]

 

[Dave Elliott]

Form: EmpCalc
6 fields
EmployeeID: shows the employee
TimeCounter: shows the invoice number
work date: shows the date the employee worked
Text34 =Format([Work Date],"dddd") shows the work date in day format
hours: shows the hours worked for each employee
overtime: shows the overtime worked for each employee
Both fields below are located in the form footer...........................
Text95: shows the total hours for all employees;=Sum([Hours])
SumOfoVERTIME: SHOWS the total overtime for all employees;=Sum([Overtime])

The idea is too count how many days where there are more than 2 employees
who worked on the same job,i.e. TimeCounter (Invoice Number) of course the
regular rate for 2 or less
employees still counts as well and has to be figured in.
this is necessary because the rate changes at which the customer is billed
when the count of employees
is more than 2 on any given day. For one or two employees the rate 65.00 a
hour, for each employee after
that the rate becomes 1/2 the rate added to the original rate (32.50). Now
the rate becomes 97.50 for each hour
that 3 employees worked on for the same day.so the calculation gets
complicated for me as you can see.
Example: Forest Gump works on Monday 05/30/05 as well as Bubba Gump and
Billy Bob and the total hours worked
is 24. Then the customer will be billed for 24 hours @ 97.50 AN HOUR.
If on Tuseday for the same Invoice Number, i.e. TimeCounter only 2 employees
worked 8 hours each, then the rate of 65.00 a
hour will be used to calculate at 16 hours * 65.00. this will be added to
the existing time for Monday and the
final total will be billed to the customer.

So the calculation must be performed for each day;
Lost as to how to proceed!
I have the data on the form, just need to sort it out so it can be
calculated.

Thanks,

Dave

 

[Steve Schapel]

Dave,

I definitely would not be trying to perform this type of calculation on
a form. I would be doing this in a query, or else writing a
user-defined function specific to the purpose. Do you have a table with
the same fields as you described, i.e. EmployeeID, WorkDate, Hours,
Overtime?

 

[Dave Elliott]

Yes, the query is named EmpCalc
it has 3 tables in it linked by ID
Hours Table
Employees Table
TEmpOrCon Table
Hours and employees tablelinked by EmployeeID and Employees to TEmpOrCon
linked by EmpOrCon

SQL is below:

SELECT Hours.SinMar, Hours.NoAllow, Hours.PayRate, Hours.Hours,
IIf([TEmpOrCon].[EmpOrCon]=0.2,1*[PayRate],[Hours]*[PayRate]) AS RegPay,
Hours.OTRate, Hours.Overtime, [Hours].[OTRate]*[Overtime]*[PayRate] AS
OTPay, [RegPay]+[OTPay] AS ExpPayPerDay, Hours.TimeID, Hours.HoursID,
Hours.EmployeeID, Employees.[First Name], Hours.[Work Date], Employees.[Last
Name], Hours.ChkNoID, IIf(IsNull([ChkNoID]),0,-1) AS Paid
FROM (Hours LEFT JOIN Employees ON Hours.EmployeeID = Employees.[Employee
ID]) LEFT JOIN TEmpOrCon ON Employees.EmpOrCon = TEmpOrCon.EmpOrConID
ORDER BY Hours.[Work Date] DESC;

Dave,

I definitely would not be trying to perform this type of calculation on a
form. I would be doing this in a query, or else writing a user-defined
function specific to the purpose. Do you have a table with the same
fields as you described, i.e. EmployeeID, WorkDate, Hours, Overtime?

--
Steve Schapel, Microsoft Access MVP

Form: EmpCalc
6 fields
EmployeeID: shows the employee
TimeCounter: shows the invoice number
work date: shows the date the employee worked
Text34 =Format([Work Date],"dddd") shows the work date in day format
hours: shows the hours worked for each employee
overtime: shows the overtime worked for each employee
Both fields below are located in the form
footer...........................
Text95: shows the total hours for all employees;=Sum([Hours])
SumOfoVERTIME: SHOWS the total overtime for all
employees;=Sum([Overtime])

The idea is too count how many days where there are more than 2 employees
who worked on the same job,i.e. TimeCounter (Invoice Number) of course
the regular rate for 2 or less
employees still counts as well and has to be figured in.
this is necessary because the rate changes at which the customer is
billed when the count of employees
is more than 2 on any given day. For one or two employees the rate 65.00
a hour, for each employee after
that the rate becomes 1/2 the rate added to the original rate (32.50).
Now the rate becomes 97.50 for each hour
that 3 employees worked on for the same day.so the calculation gets
complicated for me as you can see.
Example: Forest Gump works on Monday 05/30/05 as well as Bubba Gump and
Billy Bob and the total hours worked
is 24. Then the customer will be billed for 24 hours @ 97.50 AN HOUR.
If on Tuseday for the same Invoice Number, i.e. TimeCounter only 2
employees worked 8 hours each, then the rate of 65.00 a
hour will be used to calculate at 16 hours * 65.00. this will be added to
the existing time for Monday and the
final total will be billed to the customer.

So the calculation must be performed for each day;
Lost as to how to proceed!
I have the data on the form, just need to sort it out so it can be
calculated.

Thanks,

Dave

 

[Dave Elliott]

NEW APPROACH TO THIS, IT IS NOW LINKED TO THE MAIN FORM AND SHOWS
OR IS INVISIBLE VIA CODE. THE CODE STILL NEEDS TO BE FIGURED OUT, BUT
THE FORM AND RECORD COUNT IS NOW SOLVED.

THANKS,

DAVE

Yes, the query is named EmpCalc
it has 3 tables in it linked by ID
Hours Table
Employees Table
TEmpOrCon Table
Hours and employees tablelinked by EmployeeID and Employees to TEmpOrCon
linked by EmpOrCon

SQL is below:

SELECT Hours.SinMar, Hours.NoAllow, Hours.PayRate, Hours.Hours,
IIf([TEmpOrCon].[EmpOrCon]=0.2,1*[PayRate],[Hours]*[PayRate]) AS RegPay,
Hours.OTRate, Hours.Overtime, [Hours].[OTRate]*[Overtime]*[PayRate] AS
OTPay, [RegPay]+[OTPay] AS ExpPayPerDay, Hours.TimeID, Hours.HoursID,
Hours.EmployeeID, Employees.[First Name], Hours.[Work Date],
Employees.[Last Name], Hours.ChkNoID, IIf(IsNull([ChkNoID]),0,-1) AS Paid
FROM (Hours LEFT JOIN Employees ON Hours.EmployeeID = Employees.[Employee
ID]) LEFT JOIN TEmpOrCon ON Employees.EmpOrCon = TEmpOrCon.EmpOrConID
ORDER BY Hours.[Work Date] DESC;

Dave,

I definitely would not be trying to perform this type of calculation on a
form. I would be doing this in a query, or else writing a user-defined
function specific to the purpose. Do you have a table with the same
fields as you described, i.e. EmployeeID, WorkDate, Hours, Overtime?

--
Steve Schapel, Microsoft Access MVP

Form: EmpCalc
6 fields
EmployeeID: shows the employee
TimeCounter: shows the invoice number
work date: shows the date the employee worked
Text34 =Format([Work Date],"dddd") shows the work date in day format
hours: shows the hours worked for each employee
overtime: shows the overtime worked for each employee
Both fields below are located in the form
footer...........................
Text95: shows the total hours for all employees;=Sum([Hours])
SumOfoVERTIME: SHOWS the total overtime for all
employees;=Sum([Overtime])

The idea is too count how many days where there are more than 2
employees
who worked on the same job,i.e. TimeCounter (Invoice Number) of course
the regular rate for 2 or less
employees still counts as well and has to be figured in.
this is necessary because the rate changes at which the customer is
billed when the count of employees
is more than 2 on any given day. For one or two employees the rate 65.00
a hour, for each employee after
that the rate becomes 1/2 the rate added to the original rate (32.50).
Now the rate becomes 97.50 for each hour
that 3 employees worked on for the same day.so the calculation gets
complicated for me as you can see.
Example: Forest Gump works on Monday 05/30/05 as well as Bubba Gump and
Billy Bob and the total hours worked
is 24. Then the customer will be billed for 24 hours @ 97.50 AN HOUR.
If on Tuseday for the same Invoice Number, i.e. TimeCounter only 2
employees worked 8 hours each, then the rate of 65.00 a
hour will be used to calculate at 16 hours * 65.00. this will be added
to the existing time for Monday and the
final total will be billed to the customer.

So the calculation must be performed for each day;
Lost as to how to proceed!
I have the data on the form, just need to sort it out so it can be
calculated.

Thanks,

Dave