Setting form control value to a variable in VBA

[Guest]

I am very new to VBA. I have had only a brief introduction in my MS App
college course. I am trying to get a value in an unbound calculated control
to round up or down depending on the value. This is a timesheet database.

The line giving me a problem is:

lngDlyHrsWrked = Forms!subfrmDailyTimeWTotalsWeek1.DailyHoursWorked.Value

"The error is Run-Time Error: 2450

Timesheets - QED Management can't find the form 'subfrmDailyTimeWeek1'
referred to in a macro expression or Visual Basic Code."

I feel like this is a simple syntax problem, but I have not been able to fix
it.

Form Information:

The main form is based on a query that limits the dates to the current pay
period. There are two sub forms on the main form. The first sub form is
limited by a query that shows week 1 in the current pay period. The second
form is limited by a query that shows week 2 in the current pay period. The
main form’s header houses the Employee Name, EmployeeID, Department, Team,
and CostCenter fields (all fields from tblEMpInfo). The DailyHoursWorked
field is calculated by the sub form’s query (additional info below).

I have tried several different things to get the unbound control
(DailyHoursWorked) to display the rounded value. Originally, I did not have
the line above in my code at all. I am not sure what I am doing wrong. I
have the code in an Access module. I have tried the same code as a Public
Sub, but I read that a Sub cannot return a value, so I really didn’t expect
that to work. I am calling this Function from the AfterUpdate Events of the
TimeIn, and Time Out Fields on my form in order for it to recalculate the
DailyHoursWorked field after the employee changes the values in the TimeIn
and TimeOut fields. There are 4 TimeIn and 4 TimeOut fields (TimeIn1,
TimeOut1, Time In2, etc.) that are stored in a table named tblTimeCard. The
form is based on a query, that combines fields from tblTimeCard and
tblEmpInfo. The EmployeeID is the Primary Key in tblEmpInfo. The primary
key in tblTimeCard is EmployeeID and Date. These tables have a one-to-many
relationship based on EmployeeID, with referential integrity enforced. The
one side of the relationship is, of course, the tblEmpInfo. Any help is
greatly appreciated.

My code is as follows:

Public Function RoundDailyHrsWorked()

Dim lngDlyHrsWrked As Long

lngDlyHrsWrked = Forms!subfrmDailyTimeWTotalsWeek1.DailyHoursWorked.Value

Select Case lngDlyHrsWrked

Case Is <= 0.1167
lngDlyHrsWrked = 0#
Case Is <= 0.35
lngDlyHrsWrked = 0.25
Case Is <= 0.6167
lngDlyHrsWrked = 0.5
Case Is <= 0.8667
lngDlyHrsWrked = 0.75
Case Is <= 1.1167
lngDlyHrsWrked = 1#
Case Is <= 1.35
lngDlyHrsWrked = 1.25
Case Is <= 1.6167
lngDlyHrsWrked = 1.5
Case Is <= 1.8667
lngDlyHrsWrked = 1.75
Case Is <= 2.1167
lngDlyHrsWrked = 2#
Case Is <= 2.35
lngDlyHrsWrked = 2.25
Case Is <= 2.6167
lngDlyHrsWrked = 2.5
Case Is <= 2.8667
lngDlyHrsWrked = 2.75
Case Is <= 3.1167
lngDlyHrsWrked = 3#
Case Is <= 3.35
lngDlyHrsWrked = 3.25
Case Is <= 3.6167
lngDlyHrsWrked = 3.5
Case Is <= 3.8667
lngDlyHrsWrked = 3.75
Case Is <= 4.1167
lngDlyHrsWrked = 4#
Case Is <= 4.35
lngDlyHrsWrked = 4.25
Case Is <= 4.6167
lngDlyHrsWrked = 4.5
Case Is <= 4.8667
lngDlyHrsWrked = 4.75
Case Is <= 5.1167
lngDlyHrsWrked = 5#
Case Is <= 5.35
lngDlyHrsWrked = 5.25
Case Is <= 5.6167
lngDlyHrsWrked = 5.5
Case Is <= 5.8667
lngDlyHrsWrked = 5.75
Case Is <= 6.1167
lngDlyHrsWrked = 6#
Case Is <= 6.35
lngDlyHrsWrked = 6.25
Case Is <= 6.6167
lngDlyHrsWrked = 6.5
Case Is <= 6.8667
lngDlyHrsWrked = 6.75
Case Is <= 7.1167
lngDlyHrsWrked = 7#
Case Is <= 7.35
lngDlyHrsWrked = 7.25
Case Is <= 7.6167
lngDlyHrsWrked = 7.5
Case Is <= 7.8667
lngDlyHrsWrked = 7.75
Case Is <= 8.1167
lngDlyHrsWrked = 8#
Case Is <= 8.35
lngDlyHrsWrked = 8.25
Case Is <= 8.6167
lngDlyHrsWrked = 8.5
Case Is <= 8.8667
lngDlyHrsWrked = 8.75
Case Is <= 9.1167
lngDlyHrsWrked = 9#
Case Is <= 9.35
lngDlyHrsWrked = 9.25
Case Is <= 9.6167
lngDlyHrsWrked = 9.5
Case Is <= 9.8667
lngDlyHrsWrked = 9.75
Case Is <= 10.1167
lngDlyHrsWrked = 10#
Case Is <= 10.35
lngDlyHrsWrked = 10.25
Case Is <= 10.6167
lngDlyHrsWrked = 10.5
Case Is <= 10.8667
lngDlyHrsWrked = 10.75
Case Is <= 11.1167
lngDlyHrsWrked = 11#
Case Is <= 11.35
lngDlyHrsWrked = 11.25
Case Is <= 11.6167
lngDlyHrsWrked = 11.5
Case Is <= 11.8667
lngDlyHrsWrked = 4.75
Case Is <= 12.1167
lngDlyHrsWrked = 12#
Case Is <= 12.35
lngDlyHrsWrked = 12.25
Case Is <= 12.6167
lngDlyHrsWrked = 12.5
Case Is <= 12.8667
lngDlyHrsWrked = 12.75
Case Is <= 13.1167
lngDlyHrsWrked = 13#
End Select

End Function

Thanks in advance!

Tiffany

 

[Guest]

hi,
try this:
lngdlyhrswrked = [forms]![subfrmDailyTimeWTotalsWeek1]!
[DailyHoursWorked]
this should be the correct syntax

 

[Lynn Trapp]

Try this instead:

Forms!YourMainFormName.Form!subfrmDailyTimeWTotalsWeek1!DailyHoursWorked

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Thank you very much for your help! I am no longer getting the error I
described below. The only problem I am having now is that the
DailyHoursWorked field is not rounding as the code intends for it to. The
DailyHoursWorked field is just showing the actual number and not the rounded
number. Any suggestions?

 

[Lynn Trapp]

Try wrapping the Round() function around right hand side of the assignment
statement.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Guest]

Thank you for your response. I appreciate you taking the time to help me. I
placed this in the Module where the procedure is:

Public Function RoundDailyHrsWorked(lngDlyHrsWrked As Long)

lngDlyHrsWrked =
Forms!frmDailyTimeWTotals.Form!subfrmDailyTimeWTotalsWeek1!DailyHoursWorked

Doing just that did not help. So, I tried the following items in the the
After_Update Event:

RoundDailyHrsWorked(lngDlyHrsWrked)
Call RoundDailyHrsWorked(lngDlyHrsWrked)

I have also tried plaing this in the After_Update field (not using the
[Event Procedure]:

=RoundDailyHrsWorked(lngDlyHrsWrked)


The form is still not displaying the rounded numbers. Did I understand what
you meant by "wrapping the function around....the assignment statement? This
is driving me crazy. I have been reading Access 97 Macro & VBA Handbook by
Susann Novalis and have tried several things in that book, but it flat will
not display the rounded figure. Thanks for your help!

 

[Guest]

I have been trying to debug the code submitted. I used a breakpoint of:

lngDlyHrsWrked =
Forms!frmDailyTimeWTotals.Form!subfrmDailyTimeWTotalsWeek1!DailyHoursWorked

The Immediate window shows me the following at the breakpoint shown above:

Expression Value Type

lngDlyHrsWrked 0 Double
RoundDailyHrsWorked Empty Variant/Empty


Then when I Step Over the above breakpoint it shows me:

Expression Value Type

lngDlyHrsWrked 7.98333333333334 Double
RoundDailyHrsWorked Empty Variant/Empty

So, I changed my code as follows:

Public Function RoundDailyHrsWorked(dblDlyHrsWrked As Double) As Double

dblDlyHrsWrked =
Forms!frmDailyTimeWTotals.Form!subfrmDailyTimeWTotalsWeek1!DailyHoursWorked

Select Case dblDlyHrsWrked

Case Is <= 0.1167
dblDlyHrsWrked = 0#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 0.35
dblDlyHrsWrked = 0.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 0.6167
dblDlyHrsWrked = 0.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 0.8667
dblDlyHrsWrked = 0.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.1167
dblDlyHrsWrked = 1#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.35
dblDlyHrsWrked = 1.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.6167
dblDlyHrsWrked = 1.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 1.8667
dblDlyHrsWrked = 1.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.1167
dblDlyHrsWrked = 2#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.35
dblDlyHrsWrked = 2.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.6167
dblDlyHrsWrked = 2.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 2.8667
dblDlyHrsWrked = 2.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.1167
dblDlyHrsWrked = 3#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.35
dblDlyHrsWrked = 3.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.6167
dblDlyHrsWrked = 3.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 3.8667
dblDlyHrsWrked = 3.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.1167
dblDlyHrsWrked = 4#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.35
dblDlyHrsWrked = 4.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.6167
dblDlyHrsWrked = 4.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 4.8667
dblDlyHrsWrked = 4.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.1167
dblDlyHrsWrked = 5#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.35
dblDlyHrsWrked = 5.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.6167
dblDlyHrsWrked = 5.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 5.8667
dblDlyHrsWrked = 5.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.1167
dblDlyHrsWrked = 6#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.35
dblDlyHrsWrked = 6.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.6167
dblDlyHrsWrked = 6.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 6.8667
dblDlyHrsWrked = 6.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.1167
dblDlyHrsWrked = 7#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.35
dblDlyHrsWrked = 7.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.6167
dblDlyHrsWrked = 7.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 7.8667
dblDlyHrsWrked = 7.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.1167
dblDlyHrsWrked = 8#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.35
dblDlyHrsWrked = 8.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.6167
dblDlyHrsWrked = 8.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 8.8667
dblDlyHrsWrked = 8.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.1167
dblDlyHrsWrked = 9#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.35
dblDlyHrsWrked = 9.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.6167
dblDlyHrsWrked = 9.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 9.8667
dblDlyHrsWrked = 9.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.1167
dblDlyHrsWrked = 10#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.35
dblDlyHrsWrked = 10.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.6167
dblDlyHrsWrked = 10.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 10.8667
dblDlyHrsWrked = 10.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.1167
dblDlyHrsWrked = 11#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.35
dblDlyHrsWrked = 11.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.6167
dblDlyHrsWrked = 11.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 11.8667
dblDlyHrsWrked = 4.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.1167
dblDlyHrsWrked = 12#
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.35
dblDlyHrsWrked = 12.25
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.6167
dblDlyHrsWrked = 12.5
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 12.8667
dblDlyHrsWrked = 12.75
RoundDailyHrsWorked = dblDlyHrsWrked
Case Is <= 13.1167
dblDlyHrsWrked = 13#
RoundDailyHrsWorked = dblDlyHrsWrked
End Select

End Function

Now the Immediate window shows:

Expression Value Type

dblDlyHrsWrked 7.98333333333334 Double
RoundDailyHrsWorked 0 Double

Why isn’t it pulling the value of dblDlyHrsWrked? I have the following code
in the After_Update events of the TimeIn and TimeOut fields:

RoundDailyHrsWorked (DailyHoursWorked)

I realize that the above is incorrect, but when I put anything else I get an
error about the Type or and error that says Expected: list separator.

I have tried the following code in the AfterUpdate events:

RoundDailyHrsWorked (dblDlyHrsWrked)
RoundDailyHrsWorked (dblDlyHrsWrked As Double)
RoundDailyHrsWorked (dblDlyHrsWrked As Double) As Double
Call RoundDailyHrsWorked (dblDlyHrsWrked)

What am I missing? I really need to get past this so I can move on to other
issues. My deadline for having this timesheet database ready to roll out to
users is 05/20/05. Any help is greatly appreciated. Do I need additional
code in my After_Update events? Is it possible to display the rounded
numbers in a calculated control using code?

Tiffany