Problem concerning Time

[Jphillis]

I have Two text boxes on one form, Time purchased, and Time available, and another text box on a different form, Total Time.

I want to be able to input the Time Purchased (which i can do), I then want to up date the Total Time when a new call is taken (which i can also do) The problem is i want to beable to show the time that is remaining after having subtracted the Total Time (which is located on one form) from the Time Purchased (located on a different form)and show the result in the Time Available test box.

Will i have problems if the Time Purchased box is null?

 

[Aivars]

Hi!

Both forms must be opened.

private sub txtTimePurchased_AfterUpdate()
dim frm as form

set frm=forms("AnotherFormName&quot
if not isnull(me.txtTimePurchased) then
me.txtTimeAvailable = iif(not isnull(frm!txtTotalTime),frm!txtTotalTime-me.txtTimePurchased,0)
else
me.txtTimeAvailable =0 'Or other value e.g. "No purchased time"
endif
end sub

Aivars
LOL My holidays will be started next week!

 

[Jphillis]

Excuse my ignorance, but where do i put this code? I'm still a novice at access.

 

[Aivars]

Into procedure (Event) AfterUpdate of Time purchased text box.

"AnotherFormName" is form name what contain Total Time.

Aivars

 

[Jphillis]

I have modified the code as follows:

private sub TimePurchased_AfterUpdate()
dim frm as form

set frm=forms(Calls)
if not isnull(me.TimePurchased) then
me.TimeAvailable = iif(not isnull(frm!TotalTime),frm!TotalTime-me.TimePurchased,0)
else
me.TimeAvailable =0
endif
end sub

but an error is displayed "The expression you entered contains invalid syntax" ??

 

[oltremari]

Could this not be handled easier using the NZ function which converts null to zero? Also you do not have to create a form object to reference a control on another form.

Try this...

Private Sub TimePurchased_AfterUpdate()

me.TimeAvailable = NZ([Forms]![Calls]![TotalTime])-
NZ(me.TimePurchased)

End Sub


OR if you need to always have TimeAvailable = 0 if TimePurchased = null

Private Sub TimePurchased_AfterUpdate()
me.TimeAvailable = NZ([Forms]![Calls]![TotalTime]-
me.TimePurchased)

End Sub

Notice change in placement of parenthesis and use of a single NZ instead of two.

X-)

 

[oltremari]

In your modified code

set frm=forms(Calls)


should read

set frm=forms("Calls&quot

 

[Jphillis]

The syntax error is still coming up, is there anyway i can find out more information about what type of error it is or where it is happening? is there a compiler on access?

 

[oltremari]

JPhillis,

What version of Access are you running.

No there is not a "compiler" for Access in the sense that you can create an executable from an Access database.

There is a debugger which allows you to examine/monitor the execution of your code.

You can place a breakpoint on the first line of executable code in the AfterUpdate event procedure (DIM statements are examples on non-executable code). The debugger will display showing your position in code. You can check variable values by positioning you mouse over them while your code is executing. You can set up a watch list of variables and objects to display their values during code execution. You can also step through each line of code by pressing F8.

You may also have a missing reference. Open any module or event procedure, click on Tools, click on References. Look for references which indicate that they are missing (not to be confused with references which are not checkmarked, do not checkmark all of the available references in the list). A missing reference is one that is checkmarked but has the word MISSING in the title of the reference.

 

[Jphillis]

Access 2000

Is the code that you have suggested supposed to be written in the Expression builder or in the Code builder, (which i presume is VB code?)

 

[oltremari]

The code should be written in the Code Builder.

The easiest way to put the code in the proper place is:
(and please don't be offended if I sould too simple)

in design view of the form,
select the TimePurchased text box control,
in the properties of the control click on the EVENT tab,
in the box for the AfterUpdate event set it to [EventProcedure] (pick it from the combo box or just double click)

Once the AfterUpdate is set to [EventProcedure] click on the ... button (a.k.a. elipse button) which appears just to the right of [EventProcedure]. This will position you in the section of code for this event.

If the event procedure does not already exist, an empty TimePurchased_AfterUpdate Sub will automatically be created. If the procedure already exists, you can delete any unwanted code and replace it the code above.