Second textbox result based on first textbox -- SetFocus

[vbaprogammer]

I have a form with an interest rate (Loan_Rate) textbox, masked 0#.###.
The user enters the interest rate, and it must be converted to words.
The words are placed into another textbox so the user can see the text (Loan_RateSO1 and PctTable array), and supposedly would be saved to the table (must be saved in text format as well as numeric for later merge directly from query).

All is well until SetText sub, where the errors "You must save the field ... the GoToControl ... or SetFocus method."

I feel the code below is very close to what I need (converting the integer to words works, converting the decimal to selected text works, but I can't get the spelled out portion into the textbox (Loan_RateSO) on the form. Can someone put me on the right track with the right stuff?

Private Sub Loan_Rate_BeforeUpdate(Cancel As Integer)
'Variables dimmed in General Declarations
Dim Pct As Integer
Dim Loan_RateTxt As String
Loan_RateSO1 = ""
Loan_RateSO2 = ""
PctTable(0) = " Percent"
PctTable(1) = " and One Eighth Percent"
PctTable(2) = " and One Quarter Percent"
PctTable(3) = " and Three Eighths Percent"
PctTable(4) = " and One Half Percent"
PctTable(5) = " and Five Eights Percent"
PctTable(6) = " and Three Quarters Percent"
PctTable(7) = " and Seven Eights Percent"
If Me.Loan_Rate.Text <= 3# Or Me.Loan_Rate.Text >= 20# Then
MsgBox &quot;Invalid range. Interest rate must be between 3 and 20.&quot;, vbOKOnly, &quot;Interest Rate&quot;
Me.Undo
GoTo EndIt
End If
If InStr(Me.Loan_Rate.Text, &quot;.&quot = 0 And Len(Me.Loan_Rate.Text) = 1 Or Len(Me.Loan_Rate.Text) = 2 Then
Loan_RateTxt = Me.Loan_Rate.Text & &quot;.000&quot;
Else
Loan_RateTxt = Me.Loan_Rate.Text
End If
Loan_Rate1 = Mid(Loan_RateTxt, 1, InStr(1, Loan_RateTxt, &quot;.&quot - 1)
Loan_RateSO1 = Number2Words(Val(Loan_Rate1))
Loan_Rate2 = Mid(Loan_RateTxt, InStr(1, Loan_RateTxt, &quot;.&quot + 1, 3)
Select Case Mid(Loan_Rate2, 1, 3)
Case &quot;0&quot;
SetText Loan_RateSO1, 0
Case &quot;125&quot;
SetText Loan_RateSO1, 1
Case &quot;250&quot;
SetText Loan_RateSO1, 2
Case &quot;375&quot;
SetText Loan_RateSO1, 3
Case &quot;500&quot;
SetText Loan_RateSO1, 4
Case &quot;625&quot;
SetText Loan_RateSO1, 5
Case &quot;750&quot;
SetText Loan_RateSO1, 6
Case &quot;875&quot;
SetText Loan_RateSO1, 7
Case Else
MsgBox &quot;Invalid entry. Please check decimal and reenter.&quot; & vbCr & vbCr & &quot;Options are:&quot; & vbCr & vbCr & &quot; .125, .250, .375&quot; & vbCr & vbCr & &quot; .500, .625, .750, .875&quot;, vbOKOnly, &quot;Interest Rate&quot;
Me.Undo
End Select
EndIt:

End Sub

Public Sub SetText(Loan_RateSO1, Pct)
Me.Loan_RateSO.Locked = False
Me.Loan_RateSO.SetFocus
Me.Loan_RateSO.Text = Loan_RateSO1 & PctTable(Pct)
Me.Loan_RateSO.Locked = True
Me.Loan_RequestDate.SetFocus
End Sub

 

[Rohdem]

The way I understand it, you are getting the right information into the table, but when you assign the information to a text box on the form, it doesn't show up. If this is the case, you need a me.refresh or me.repaint after the assignment statement.

Mike Rohde
rohdem@marshallengines.com

 

[vbaprogammer]

Actually the error occurs when trying to put the spelled out interest rate into the Loan_RateSO textbox on the same form. This is attempted in the subroutine SetText.

(Actually I tried it directly in the BeforeUpdate subroutine, and it doesn't work there either.)

The error is basically that I cannot change focus until the current field is saved.

Run-time error '2108':

'You must save the field ...'

'Set the ... method to the AfterUpdate property ... instead of BeforeUpdate ... so it saves the field before changing the focus.'

Can't refresh or repaint until I get focus and put in the data.

Hope this helps.

Dan