If a control contains this value, do this...

[CW]

I want to set the value in a control on a form depending on the value in a
preceding control.
Specifically, if Text1 contains the word Insurance, then I want the value in
Text2 to be a particular numeric value (4005, which is an accounting analysis
code).
Text1 can be populated by users in various ways (it needs to be free text
and not a combo or a value list) so I cannot make Text2 react to the
selection of a certain value.
I was thinking of using the AfterUpdate or OnExit event on Text1, but there
comes my question - how do I code this: "If Text1 contains Insurance then
Text2 = 4005" ??
I have tried several variations along those lines without success - help,
please!
Many thanks
CW

 

[Tom Wickerath]

Hi CW,

Can you help us understand why a combo box would not work for you? You can,
after all, use the not-in-list event procedure to allow users to add new
items to a combo box. The chances of spelling typos are greatly reduced when
people can select from a list of values. And, as you have indicated, it is
pretty easy to populate Text2 based on a hidden column included in the row
source for a combo box.

If you must use a text box, then I'd be tempted to call a custom procedure
from both the Current Event of the form, and the After Update event of Text1,
so that Text2 will show whatever values it needs to, assuming the entry in
Text1 is spelled correctly. The easiest method might involve hard-coding the
values into a SELECT Case statement. Something like this:

Option Compare Database
Option Explicit

Private Sub Form_Current()
RecalcText2
End Sub

Private Sub Text1_AfterUpdate()
RecalcText2
End Sub

Private Sub RecalcText2()
On Error GoTo ProcError

Select Case Me.Text1
Case "Insurance"
Me.Text2 = 4005
Case "Prepaid"
Me.Text2 = 5004
Case "Something Else"
Me.Text2 = "Whatever"
Case Else ' No matching values
Me.Text2 = Null
End Select

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure RecalcText2..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[CW]

Tom,
Many thanks for your detailed, very helpful response.
It's getting a bit late here in UK now but I shall try your suggested code
tomorrow.

The reason it isn't really practical to use a combo is because the control
source in Text1 is a string of concatenated data, each part of which is drawn
in from another part of the database using DLookup - basically it consists of:

"Insurance premium on <value> at a rate of <percentage> for a period of <#>
months". All those variables come in from other places.

On the other hand, if insurance isn't applicable, then that line may carry
some other chargeable service name, or may be empty.

But if it is used, and insurance is the service being charged for on that
line, that's when I want the subsequent control (which is the accounting
code) to be automatically populated, to avoid user error.

Sorry that's a bit lengthy but since you asked I felt I should explain
adequately what I am after, and why!

Thanks again
CW

Text1 is the first part of a line on our sales invoices

 

[Tom Wickerath]

Hi CW,

The example I provided will work only for exact matches of text in the Text1
control. If you have concatenated data, then you likely will not have just
"Insurance" showing in control named "Text1". You would likely need to modify
the procedure in that case, to compare with just the first word.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[CW]

Tom -
Yes, I will need to do that (compare with just the first word). How would I
code that, please?
Many thanks
CW

 

[Tom Wickerath]

Hi CW,

Yes, I will need to do that (compare with just the first word). How would I
code that, please?

I think this modified version will work. It may not be the best way or most
efficient manner of coding this, but it seems to work when I'm testing it now.

Option Compare Database
Option Explicit

Private Sub Form_Current()
RecalcText2
End Sub

Private Sub Text1_AfterUpdate()
RecalcText2
End Sub

Private Sub RecalcText2()
On Error GoTo ProcError

Dim strText As String
Dim strFirstWord As String
Dim intPosition As Integer ' Position of first space character.

strText = Nz(Me.Text1, "")
intPosition = InStr(1, strText, Chr(32)) - 1

If intPosition = -1 Then ' Possible if Text1 contains a single word.
intPosition = InStr(1, strText & " ", Chr(32))
End If

strFirstWord = Left(strText, intPosition)
Debug.Print strFirstWord

Select Case strFirstWord
Case "Insurance"
Me.Text2 = 4005
Case "Prepaid"
Me.Text2 = 5004
Case "Something Else"
Me.Text2 = "Whatever"
Case Else ' No matching values
Me.Text2 = Null
End Select

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure RecalcText2..."
Resume ExitProc
End Sub


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________