Populate a control with a value from a previous record

[Cravaus]

On my billing form I have a text box control showing results called
TotalBilling. I also have a text box showing the invoice number that advances
incrementally by 1. The results of TotalBilling from the last invoice I want
to appear in the control containing the PreviousBallance field when I make a
new record for this client.

I have tried using AfterUpdate on the TotalBilling control:
Me.[PreviousBallance].DefaultValue = """" & Me.[TotalBilling] & """"

This does not work. What can I do that will work. I am thinking I need to
use DMax() and the invoice numbers. I am feeling lost with this. Some tips
would help.
Thanks

 

[Jack Leach]

Generally what I do here is create a variable private to the form's module to
store the value... use the Current event of the form to set/read the value.


Option Compare Database
Option Explicit

Private strBilling As String

Private Sub Form_Current()
'set the control value (from the last record)
If Not Isnull(strBilling) Then
Me.PreviousBalance = strBilling
End If

'set the private var (for the next record)
If Not Isnull(Me.TotalBilling) Then
strBilling = Me.TotalBilling
End If
End Sub



hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Cravaus]

I tried this out per your instructions and it works as long as I do not
navigate back to past invoices. When I navigate backwoards the previous
ballance updates and is inaccurate. Because this is circular it keeps adding
on to the ballance of even previous orders when I cycle back.

What I think I need to do is to use the DLookup() function to reference only
the last TotalBilling on the previous invoice for the particular client id
and I think I need to have it entered as a default value for PreviousBalance
so it will not update with each time I cycle back and look at previous
orders. I am trying write something that will do this with my limitted
understanding. I am not getting it yet.

Generally what I do here is create a variable private to the form's module to
store the value... use the Current event of the form to set/read the value.


Option Compare Database
Option Explicit

Private strBilling As String

Private Sub Form_Current()
'set the control value (from the last record)
If Not Isnull(strBilling) Then
Me.PreviousBalance = strBilling
End If

'set the private var (for the next record)
If Not Isnull(Me.TotalBilling) Then
strBilling = Me.TotalBilling
End If
End Sub



hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

On my billing form I have a text box control showing results called
TotalBilling. I also have a text box showing the invoice number that advances
incrementally by 1. The results of TotalBilling from the last invoice I want
to appear in the control containing the PreviousBallance field when I make a
new record for this client.

I have tried using AfterUpdate on the TotalBilling control:
Me.[PreviousBallance].DefaultValue = """" & Me.[TotalBilling] & """"

This does not work. What can I do that will work. I am thinking I need to
use DMax() and the invoice numbers. I am feeling lost with this. Some tips
would help.
Thanks

 

[Arvin Meyer [MVP]]

On my billing form I have a text box control showing results called
TotalBilling. I also have a text box showing the invoice number that
advances
incrementally by 1. The results of TotalBilling from the last invoice I
want
to appear in the control containing the PreviousBallance field when I make
a
new record for this client.

I have tried using AfterUpdate on the TotalBilling control:
Me.[PreviousBallance].DefaultValue = """" & Me.[TotalBilling] & """"

This does not work. What can I do that will work. I am thinking I need
to
use DMax() and the invoice numbers. I am feeling lost with this. Some
tips
would help.

It should work unless [PreviousBalance] is populated in code, or the value
comes from a calculated query column. In those cases, the AfterUpdate event
doesn't fire. You might try to use the FORM's AfterUpdate event to fill a
public variable, the call it in the form's Current event of the next record.

 

[Cravaus]

OK, I figured it out a partial solution. All I needed to do Jacks example
was add DefaultValue to Me.PreviousBalance and this works as long as I start
the new invoice from the actual last invoice :

Current code is:
Option Compare Database
Option Explicit

Private strBilling As String

Private Sub Form_Current()
'set the control value (from the last record)
If Not Isnull(strBilling) Then
Me.PreviousBalance.DefaultValue = strBilling
End If

'set the private var (for the next record)
If Not Isnull(Me.TotalBilling) Then
strBilling = Me.TotalBilling
End If
End Sub

So, this is a temporary solution. I need to find a way to reference only
the last invoice. I thought the invoice numbers were all sequential by units
of 1 but I am wrong. Some invoices have been deleted due to errors and so
there are occasional gaps. Perhaps DMax() would be best. I will keep
chugging away.

I tried this out per your instructions and it works as long as I do not
navigate back to past invoices. When I navigate backwoards the previous
ballance updates and is inaccurate. Because this is circular it keeps adding
on to the ballance of even previous orders when I cycle back.

What I think I need to do is to use the DLookup() function to reference only
the last TotalBilling on the previous invoice for the particular client id
and I think I need to have it entered as a default value for PreviousBalance
so it will not update with each time I cycle back and look at previous
orders. I am trying write something that will do this with my limitted
understanding. I am not getting it yet.

Generally what I do here is create a variable private to the form's module to
store the value... use the Current event of the form to set/read the value.


Option Compare Database
Option Explicit

Private strBilling As String

Private Sub Form_Current()
'set the control value (from the last record)
If Not Isnull(strBilling) Then
Me.PreviousBalance = strBilling
End If

'set the private var (for the next record)
If Not Isnull(Me.TotalBilling) Then
strBilling = Me.TotalBilling
End If
End Sub



hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

On my billing form I have a text box control showing results called
TotalBilling. I also have a text box showing the invoice number that advances
incrementally by 1. The results of TotalBilling from the last invoice I want
to appear in the control containing the PreviousBallance field when I make a
new record for this client.

I have tried using AfterUpdate on the TotalBilling control:
Me.[PreviousBallance].DefaultValue = """" & Me.[TotalBilling] & """"

This does not work. What can I do that will work. I am thinking I need to
use DMax() and the invoice numbers. I am feeling lost with this. Some tips
would help.
Thanks