navigator buttons

[Guest]

I have this form that facilitates data entry into my table. There is a
calculated control on the form, which shows a product of two numbers entered
by the user. I was told that storing calculated control's data in a table is
not wise, but I think in my case it is working perfectly. I also have two
radio buttons that control which field in my table the number from my
calculated control passes into. If I click credit it is sent into the credit
amount field and if i click debit it passes into the debit amount field. To
achieve this I used two hidden textboxes that are each bound to a field.
txtCreditAmount is bound to the Credit_Amount field and txtDebitAmount is
bound to the Debit_Amound field. So if there is a number in my unbound
calculated control by selecting the appropriate radio button the content of
my calculated control is sent to on of the textboxes which are recorded in
the table.

I have 2 questions...
First, is there a simpler way of doing this? It works perfectly, but
something tells me there should be an easier way of doing this.
Secondly, When I use my navigator buttons the stored number does not show up
in the calculated control, only when I delete a record with the help of my
delete button, then the next record's amount shows up, but when I am trying
to navigate nothing happens....

Any ideas????


Here is the code:

Private Sub btnCalculate_Click()
Call RecordedAmount_AfterUpdate
Call frmRad_AfterUpdate
End Sub



Private Sub frmRad_AfterUpdate()
If Me.frmRad = 1 Then
Me.txtDebitAmount = RecordedAmount
Else
Me.txtCreditAmount = RecordedAmount
End If
End Sub

Private Sub RecordedAmount_AfterUpdate()
Me.RecordedAmount = Round(Me.Exchange_Rate * Me.For_Cur_Amount, 2)
End Sub



Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click


DoCmd.GoToRecord , , acNewRec

RecordedAmount.SetFocus
Me.RecordedAmount.Text = ""


Exit_btnAddRecord_Click:
Exit Sub

Err_btnAddRecord_Click:
MsgBox Err.Description
Resume Exit_btnAddRecord_Click

End Sub
Private Sub btnDeleteDecord_Click()
On Error GoTo Err_btnDeleteDecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

RecordedAmount.SetFocus
Me.RecordedAmount.Text = ""


Exit_btnDeleteDecord_Click:
Exit Sub

Err_btnDeleteDecord_Click:
MsgBox Err.Description
Resume Exit_btnDeleteDecord_Click

End Sub
Private Sub btnExitForm_Click()
On Error GoTo Err_btnExitForm_Click


DoCmd.Close

Exit_btnExitForm_Click:
Exit Sub

Err_btnExitForm_Click:
MsgBox Err.Description
Resume Exit_btnExitForm_Click

End Sub
Private Sub btnExitApp_Click()
On Error GoTo Err_btnExitApp_Click


DoCmd.Quit

Exit_btnExitApp_Click:
Exit Sub

Err_btnExitApp_Click:
MsgBox Err.Description
Resume Exit_btnExitApp_Click

End Sub
Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_btnNext_Click:
Exit Sub

Err_btnNext_Click:
MsgBox Err.Description
Resume Exit_btnNext_Click

End Sub
Private Sub btnPrevious_Click()
On Error GoTo Err_btnPrevious_Click


DoCmd.GoToRecord , , acPrevious

Exit_btnPrevious_Click:
Exit Sub

Err_btnPrevious_Click:
MsgBox Err.Description
Resume Exit_btnPrevious_Click

End Sub
Private Sub btnNextRecord_Click()
On Error GoTo Err_btnNextRecord_Click


DoCmd.GoToRecord , , acNext

Exit_btnNextRecord_Click:
Exit Sub

Err_btnNextRecord_Click:
MsgBox Err.Description
Resume Exit_btnNextRecord_Click

End Sub

Private Sub txtDateOfRequisition_AfterUpdate()
Me.txtDateOfRequisition.

End Sub

 

[Guest]

"First, is there a simpler way of doing this?"
Yes.
I do not store calculated values, so cannot really advise about the best way
to do so. The people who say it is not a good idea are correct. There may
be limited reasons for doing so, but your situation suggests no such reason.
How are you calculating the value? Are you performing the calculation in
the After Update event of the second text box the user fills in?
In order to see the correct calculation for each record you could perform
the calculation in the form's Current event. One approach would be to check
for a value in each of the amount text boxes. If they contain values,
perform the calculation and place the result in the Total text box. If they
do not, leave the Total text box blank.
I assume there is a particular reason why you want to separate the values.
If you describe the situation it will be easier to suggest a solution. There
are any number of things you could do to list credits and debits separately,
if that is your intention, but there is no single answer for all situations.

 

[Guest]

Right now, I have everything in VB. I have an Exchange Rate text box and I
have an Amount text box. This database is for my university' summer academic
programs and keep track of payments the students make before going to the
trip and requisitions that faculty members make. For example, the faculty
member acoompanying the students on a summer trip let's say in an European
country incurrs a charge for let's say taking a taxi from the hotel to the
train station. What my form does is that it calculates the amount incurred in
US dollars and stores it in a table.
In one of the textboxes we type in a the amount we spent in foreign currency
and in the other textbox we type in the exchange rate. I have a link that
takes the user to xe.com where exchange rates can be looked up. I have a
calculate button which if pressed performs the multiplication and displays
the product in my calculated control.

Private Sub RecordedAmount_AfterUpdate()
Me.RecordedAmount = Round(Me.Exchange_Rate * Me.For_Cur_Amount, 2)
End Sub
RecordedAmount is my calculated control.

Private Sub btnCalculate_Click()
Call RecordedAmount_AfterUpdate
Call frmRad_AfterUpdate
End Sub

This is the code for the button that performs my calculation.

And as I said before I have radio 2 radio buttons that allows me to select
which field I want to record my calculated control's content to.

Here is the code for that:
Private Sub frmRad_AfterUpdate()
If Me.frmRad = 1 Then
Me.txtDebitAmount = RecordedAmount
Else
Me.txtCreditAmount = RecordedAmount
End If
End Sub

Since my calculated control can not be bound to 2 fields at the same time, I
got around it but using two hidden textboxes that are bound to the fields
DebitAmount and CreditAMount of my table.

Like I said this is working perfectly, the only problem I have is when I
want to navigate among the records in the table. Obviously the the contents
of bound controls will change, but nothing shows up in my calculated control
(RecordedAMount) , which is unbound because I could not get it bound to two
fields at the same time... I just want to be able to see the stored amoung
in one way or another when I navigate through the records using my navigator
buttons that I created.

 

[Guest]

You were quite rightly advised against storing the computed values in the
table. Doing so means that the table is not properly normalized and is
vulnerable to update anomalies by virtue of the fact that the values of
computed field and /or the fields from which it derives its value can be
changed independently of each other. In your case a normalized solution
would be to have fields Exchange_Rate and For_Cur_Amount and another field
which records whether the transaction is a debit or credit. This could be a
text field with values 'Credit' or 'Debit' or it could be a Boolean (Yes/No)
field whose value was True for Credit say and False for Debit. Lets assume
the latter as this makes it possible to have a chack box bound to the field,
in which case the field might be called CreditTransaction.

You can then have two computed controls on the form to show the Credit and
Debit amounts with ControlSource properties of:

=([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],1,0]) for
the Credit control
and:
=([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],0,1]) for
the Debit control

With this normalized design you should have no problem navigating in your
form. For reporting purposes you can use the same expression for computed
columns in a query and base the report on that query with controls in the
report bound to the two computed columns. You can then work with them in the
report exactly as you would with fields from a base table.

 

[Guest]

I'll give it a shot tomorrow at work and see what happens!

Thanks for now!

You were quite rightly advised against storing the computed values in the
table. Doing so means that the table is not properly normalized and is
vulnerable to update anomalies by virtue of the fact that the values of
computed field and /or the fields from which it derives its value can be
changed independently of each other. In your case a normalized solution
would be to have fields Exchange_Rate and For_Cur_Amount and another field
which records whether the transaction is a debit or credit. This could be a
text field with values 'Credit' or 'Debit' or it could be a Boolean (Yes/No)
field whose value was True for Credit say and False for Debit. Lets assume
the latter as this makes it possible to have a chack box bound to the field,
in which case the field might be called CreditTransaction.

You can then have two computed controls on the form to show the Credit and
Debit amounts with ControlSource properties of:

=([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],1,0]) for
the Credit control
and:
=([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],0,1]) for
the Debit control

With this normalized design you should have no problem navigating in your
form. For reporting purposes you can use the same expression for computed
columns in a query and base the report on that query with controls in the
report bound to the two computed columns. You can then work with them in the
report exactly as you would with fields from a base table.

I have this form that facilitates data entry into my table. There is a
calculated control on the form, which shows a product of two numbers entered
by the user. I was told that storing calculated control's data in a table is
not wise, but I think in my case it is working perfectly. I also have two
radio buttons that control which field in my table the number from my
calculated control passes into. If I click credit it is sent into the credit
amount field and if i click debit it passes into the debit amount field. To
achieve this I used two hidden textboxes that are each bound to a field.
txtCreditAmount is bound to the Credit_Amount field and txtDebitAmount is
bound to the Debit_Amound field. So if there is a number in my unbound
calculated control by selecting the appropriate radio button the content of
my calculated control is sent to on of the textboxes which are recorded in
the table.

I have 2 questions...
First, is there a simpler way of doing this? It works perfectly, but
something tells me there should be an easier way of doing this.
Secondly, When I use my navigator buttons the stored number does not show up
in the calculated control, only when I delete a record with the help of my
delete button, then the next record's amount shows up, but when I am trying
to navigate nothing happens....

Any ideas????


Here is the code:

Private Sub btnCalculate_Click()
Call RecordedAmount_AfterUpdate
Call frmRad_AfterUpdate
End Sub



Private Sub frmRad_AfterUpdate()
If Me.frmRad = 1 Then
Me.txtDebitAmount = RecordedAmount
Else
Me.txtCreditAmount = RecordedAmount
End If
End Sub

Private Sub RecordedAmount_AfterUpdate()
Me.RecordedAmount = Round(Me.Exchange_Rate * Me.For_Cur_Amount, 2)
End Sub



Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click


DoCmd.GoToRecord , , acNewRec

RecordedAmount.SetFocus
Me.RecordedAmount.Text = ""


Exit_btnAddRecord_Click:
Exit Sub

Err_btnAddRecord_Click:
MsgBox Err.Description
Resume Exit_btnAddRecord_Click

End Sub
Private Sub btnDeleteDecord_Click()
On Error GoTo Err_btnDeleteDecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

RecordedAmount.SetFocus
Me.RecordedAmount.Text = ""


Exit_btnDeleteDecord_Click:
Exit Sub

Err_btnDeleteDecord_Click:
MsgBox Err.Description
Resume Exit_btnDeleteDecord_Click

End Sub
Private Sub btnExitForm_Click()
On Error GoTo Err_btnExitForm_Click


DoCmd.Close

Exit_btnExitForm_Click:
Exit Sub

Err_btnExitForm_Click:
MsgBox Err.Description
Resume Exit_btnExitForm_Click

End Sub
Private Sub btnExitApp_Click()
On Error GoTo Err_btnExitApp_Click


DoCmd.Quit

Exit_btnExitApp_Click:
Exit Sub

Err_btnExitApp_Click:
MsgBox Err.Description
Resume Exit_btnExitApp_Click

End Sub
Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_btnNext_Click:
Exit Sub

Err_btnNext_Click:
MsgBox Err.Description
Resume Exit_btnNext_Click

End Sub
Private Sub btnPrevious_Click()
On Error GoTo Err_btnPrevious_Click


DoCmd.GoToRecord , , acPrevious

Exit_btnPrevious_Click:
Exit Sub

Err_btnPrevious_Click:
MsgBox Err.Description
Resume Exit_btnPrevious_Click

End Sub
Private Sub btnNextRecord_Click()
On Error GoTo Err_btnNextRecord_Click


DoCmd.GoToRecord , , acNext

Exit_btnNextRecord_Click:
Exit Sub

Err_btnNextRecord_Click:
MsgBox Err.Description
Resume Exit_btnNextRecord_Click

End Sub

Private Sub txtDateOfRequisition_AfterUpdate()
Me.txtDateOfRequisition.

End Sub

 

[Guest]

in your response you are suggesting the use of two seperate computed
controls, one for credits and one for debits and this makes perfect sense as
far as navigation is concerned. My dilemma is over the possibilty of doing
the same with only one calculated control. A control whose content gets
recorded into my table based on which radio button i click on. (debit /
credit) ???

I'll give it a shot tomorrow at work and see what happens!

Thanks for now!

You were quite rightly advised against storing the computed values in the
table. Doing so means that the table is not properly normalized and is
vulnerable to update anomalies by virtue of the fact that the values of
computed field and /or the fields from which it derives its value can be
changed independently of each other. In your case a normalized solution
would be to have fields Exchange_Rate and For_Cur_Amount and another field
which records whether the transaction is a debit or credit. This could be a
text field with values 'Credit' or 'Debit' or it could be a Boolean (Yes/No)
field whose value was True for Credit say and False for Debit. Lets assume
the latter as this makes it possible to have a chack box bound to the field,
in which case the field might be called CreditTransaction.

You can then have two computed controls on the form to show the Credit and
Debit amounts with ControlSource properties of:

=([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],1,0]) for
the Credit control
and:
=([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],0,1]) for
the Debit control

With this normalized design you should have no problem navigating in your
form. For reporting purposes you can use the same expression for computed
columns in a query and base the report on that query with controls in the
report bound to the two computed columns. You can then work with them in the
report exactly as you would with fields from a base table.

I have this form that facilitates data entry into my table. There is a
calculated control on the form, which shows a product of two numbers entered
by the user. I was told that storing calculated control's data in a table is
not wise, but I think in my case it is working perfectly. I also have two
radio buttons that control which field in my table the number from my
calculated control passes into. If I click credit it is sent into the credit
amount field and if i click debit it passes into the debit amount field. To
achieve this I used two hidden textboxes that are each bound to a field.
txtCreditAmount is bound to the Credit_Amount field and txtDebitAmount is
bound to the Debit_Amound field. So if there is a number in my unbound
calculated control by selecting the appropriate radio button the content of
my calculated control is sent to on of the textboxes which are recorded in
the table.

I have 2 questions...
First, is there a simpler way of doing this? It works perfectly, but
something tells me there should be an easier way of doing this.
Secondly, When I use my navigator buttons the stored number does not show up
in the calculated control, only when I delete a record with the help of my
delete button, then the next record's amount shows up, but when I am trying
to navigate nothing happens....

Any ideas????


Here is the code:

Private Sub btnCalculate_Click()
Call RecordedAmount_AfterUpdate
Call frmRad_AfterUpdate
End Sub



Private Sub frmRad_AfterUpdate()
If Me.frmRad = 1 Then
Me.txtDebitAmount = RecordedAmount
Else
Me.txtCreditAmount = RecordedAmount
End If
End Sub

Private Sub RecordedAmount_AfterUpdate()
Me.RecordedAmount = Round(Me.Exchange_Rate * Me.For_Cur_Amount, 2)
End Sub



Private Sub btnAddRecord_Click()
On Error GoTo Err_btnAddRecord_Click


DoCmd.GoToRecord , , acNewRec

RecordedAmount.SetFocus
Me.RecordedAmount.Text = ""


Exit_btnAddRecord_Click:
Exit Sub

Err_btnAddRecord_Click:
MsgBox Err.Description
Resume Exit_btnAddRecord_Click

End Sub
Private Sub btnDeleteDecord_Click()
On Error GoTo Err_btnDeleteDecord_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

RecordedAmount.SetFocus
Me.RecordedAmount.Text = ""


Exit_btnDeleteDecord_Click:
Exit Sub

Err_btnDeleteDecord_Click:
MsgBox Err.Description
Resume Exit_btnDeleteDecord_Click

End Sub
Private Sub btnExitForm_Click()
On Error GoTo Err_btnExitForm_Click


DoCmd.Close

Exit_btnExitForm_Click:
Exit Sub

Err_btnExitForm_Click:
MsgBox Err.Description
Resume Exit_btnExitForm_Click

End Sub
Private Sub btnExitApp_Click()
On Error GoTo Err_btnExitApp_Click


DoCmd.Quit

Exit_btnExitApp_Click:
Exit Sub

Err_btnExitApp_Click:
MsgBox Err.Description
Resume Exit_btnExitApp_Click

End Sub
Private Sub btnNext_Click()
On Error GoTo Err_btnNext_Click


Screen.PreviousControl.SetFocus
DoCmd.FindNext

Exit_btnNext_Click:
Exit Sub

Err_btnNext_Click:
MsgBox Err.Description
Resume Exit_btnNext_Click

End Sub
Private Sub btnPrevious_Click()
On Error GoTo Err_btnPrevious_Click


DoCmd.GoToRecord , , acPrevious

Exit_btnPrevious_Click:
Exit Sub

Err_btnPrevious_Click:
MsgBox Err.Description
Resume Exit_btnPrevious_Click

End Sub
Private Sub btnNextRecord_Click()
On Error GoTo Err_btnNextRecord_Click


DoCmd.GoToRecord , , acNext

Exit_btnNextRecord_Click:
Exit Sub

Err_btnNextRecord_Click:
MsgBox Err.Description
Resume Exit_btnNextRecord_Click

End Sub

Private Sub txtDateOfRequisition_AfterUpdate()
Me.txtDateOfRequisition.

End Sub

 

[Guest]

Whether you have one or two controls is immaterial. The point is that you
should not be storing the computed value in the table for the reasons I
outlined, but instead should have a column in the table which indicates
whether the row records a credit or debit transaction. If you then have just
one computed control its ControlSource does not need to reference the
CreditTransaction value, but is a straightforward product of the values of
the other two columns:

=[Exchange_Rate] * [For_Cur_Amount]

Should you wish to aggregate the computed values, in a report for instance,
you can still do this. Say you have a report which lists all the
transactions and you want to show the net sum of the transactions in a
footer, i.e. the sum of the credits less the sum of the debits the expression
for the control in the footer would be:

=Sum(([Exchange_Rate] * [For_Cur_Amount]) * IIf([CreditTransaction],1,-1))

In this expression the IIf function returns a value of 1 or -1 depending on
whether the transaction is a credit or debit. By multiplying the product of
the two stored values by this the debits are converted to a negative value,
so when you sum the values you end up with the net balance of all credits
less all debits.