Creating Calculated Field

  • Thread starter Thread starter bagia
  • Start date Start date
B

bagia

Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!
 
This function will get the value from the previous row of the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function
 
Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.


-----Original Message-----
This function will get the value from the previous row of the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bagia said:
Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!
Click to expand...


.
Click to expand...
 
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--
bagia said:
Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.


-----Original Message-----
This function will get the value from the previous row of the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

bagia said:
Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Allen,

Sorry for being so ignorant about the this. I went ot the
Module tab and created a new one with the code...copied
and pasted.

I open the form and received an error msg...it brought up
the MS Visual Basic window "Compile Error: User-defined
type not defined". "rs As DAO.Recordset" is highlighted.

Is there a way for me to send you the database and have
you take a look at it if it's easier for you to see what
i'm doing wrong?

-----Original Message-----
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--
bagia said:
Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.


-----Original Message-----
This function will get the value from the previous row
Click to expand...
of
the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text
Click to expand...
box
to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row
Click to expand...
of
the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading
Click to expand...
(from
the
previous record)from today's reading (today number field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!
Click to expand...
Click to expand...


.
Click to expand...
 
The message means you do not have a reference to the DAO library.

From the code window (where you got the error), choose References from the
Tools menu. Then check the box beside:
Microsoft DAO 3.6

To check everything is okay, choose Compile from the Debug menu.

If you want more information about references, what they are, and which ones
you need see:
http://allenbrowne.com/ser-38.html


bagia said:
Allen,

Sorry for being so ignorant about the this. I went ot the
Module tab and created a new one with the code...copied
and pasted.

I open the form and received an error msg...it brought up
the MS Visual Basic window "Compile Error: User-defined
type not defined". "rs As DAO.Recordset" is highlighted.

Is there a way for me to send you the database and have
you take a look at it if it's easier for you to see what
i'm doing wrong?

-----Original Message-----
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--
bagia said:
Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.



-----Original Message-----
This function will get the value from the previous row of
the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a text box
to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As
String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous row of
the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and
not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from
the
previous record)from today's reading (today number
field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly appreciated!
Click to expand...
Click to expand...
Click to expand...
 
Thank you very much Allen!
-----Original Message-----
The message means you do not have a reference to the DAO library.

From the code window (where you got the error), choose References from the
Tools menu. Then check the box beside:
Microsoft DAO 3.6

To check everything is okay, choose Compile from the Debug menu.

If you want more information about references, what they are, and which ones
you need see:
http://allenbrowne.com/ser-38.html


bagia said:
Allen,

Sorry for being so ignorant about the this. I went ot the
Module tab and created a new one with the code...copied
and pasted.

I open the form and received an error msg...it brought up
the MS Visual Basic window "Compile Error: User-defined
type not defined". "rs As DAO.Recordset" is highlighted.

Is there a way for me to send you the database and have
you take a look at it if it's easier for you to see what
i'm doing wrong?

-----Original Message-----
1. In the Database window, select the Module tab.
2. Click New
3. Paste the code.
4. Save the module. "Module1" will do for a name.

You can now use this function anywhere in your program, just like Date() or
any of the built-in functions.

--
Allen,

Thanks so much for helping. I set teh Control Source for
a text box to:
=GetPreviousValue ([Form], "NameOfYourFieldHere")

BUT I'm not sure where to place the function. Where should
I place the function? Do I place it in the Control Source
as well? Sorry I don't know anything about programming.



-----Original Message-----
This function will get the value from the previous
Click to expand...
row
of
the form,
regardless of how the form is sorted or filtered.

To use it in a form, set the Control Source of a
Click to expand...
text
box
to:
=GetPreviousValue([Form], "NameOfYourFieldHere")

Function GetPreviousValue(frm As Form, strField As
String) As Variant
On Error GoTo Err_Handler
'Purpose: Return the value from the previous
Click to expand...
row
of
the form.
Dim rs As DAO.Recordset

Set rs = frm.RecordsetClone
rs.Bookmark = frm.Bookmark
rs.MovePrevious
GetPreviousValue = rs(strField)

Set rs = Nothing
Exit_Handler:
Exit Function

Err_Handler:
If Err.Number <> 3021& Then 'No current record
Debug.Print Err.Number, Err.Description
End If
GetPreviousValue = Null
Resume Exit_Handler
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi,

I'm trying to create a calculated field in a form and
not
sure where to start. I have a water reading database.
What I want to do is to come up with today's water meter
reading. I want to subtract yesterday's reading (from
the
previous record)from today's reading (today number
field).

Water reading = [today number]- how can i pull previous
record info into this function?

Any suggestions or help would be greatly
Click to expand...
Click to expand...
appreciated!


.
Click to expand...
 
Back
Top