Set Value of Control based on result of Select Case (1 Viewer)

jeran042 · Aug 8, 2018

Good afternoon all,

I am hoping someone can help me with the very beginnings of an idea.
I am trying to test the value in a form control, [POLICY], to see if it begins with "R", "R0", or 0. If it contains any of these 3 variable, I want to strip the characters away, and leave the remainder of [POLICY].

Here is what I have (very basic) for code:

Code:

Private Sub Form_Open(Cancel As Integer)
'strip away "R", "R0", or 0 from the form control me.POLICY
'Set the value of Me.txtTEST with result

Select Case Me.POLICY

    Case Left([POLICY], 1) = "R"
        Me.txtTEST.value = Mid([POLICY],3,Len([POLICY])

    Case Left([POLICY], 2) = "R0"
        Me.txtTEST.value = Mid([POLICY],2,Len([POLICY])

    Case Left([POLICY], 1) = 0
        Me.txtTEST.value = Mid([POLICY],2,Len([POLICY])

    Case Else
    'If none of the above are TRUE, give full value of me.[POLICY]
        me.txtTEST.value = Me.[POLICY].value
        
End Select
'Set the value of me.txtTEST with result of CASE Statement
    Me.txtTEST = result

End Sub

What I am trying to accomplish is a DLookup on a subform. The problem is, in one table the [POLICY] is a data type "Number" and in another table the data type is "Short Text".

I should mention I was able to get the desired result with the formula:

Code:

=IIf(Left([POLICY],1)="R",Mid([POLICY],3,Len([POLICY])),IIf(Left([POLICY],1)=0,Mid([POLICY],2,Len([POLICY])),[POLICY]))

Here is what the DLookup looks like:

Code:

=DLookUp(Replace("NOTES",Chr(13)," "),"tblInvoiceLog","[VOUCHER_NUMBER]= " & [txtTEST] & "")

NOTE: the Replace is used to strip away a carriage return in the [NOTES] field. And as of yet this is not working correctly either.

Any help or suggestions are welcome and appreciated,

Ranman256 · Aug 8, 2018

Change it to:
Select Case True.

BigHappyDaddy · Aug 9, 2018

Why not just two if statements?

Code:

If Left(Me.POLICY,1) = "R" then Me.POLICY = Right(Me.POLICY,len(Me.POLICY)-1)
if Left(Me.POLICY,1) = "0" then Me.POLICY = Right(Me.POLICY,len(Me.POLICY)-1)

This will remove all occurances of any leading "R", "R0" or "0".

arnelgp · Aug 9, 2018

firstly, you should make POLICY field in both tables consistent (i suggest short text so you can have mixed letters and numbers on them).
second, you use the Form's Load event and not the Open event.
Form''s controls (except the subform) have no values when in Open event.

you can create a Public Function for the replacement:

Code:

Public Function fncReplace(byval thisPolicy As Variant) As String
Const Pattern As String = "^(R0|R|0)"
thisPolicy = thisPolicy & ""
With CreateObject("VBScript.RegExp")
     .Pattern = Pattern
     .Global = True
     .IgnoreCase = True
     fncReplace = Trim(.Replace(thisPolicy, ""))
End With
End Function

so the expression on the query or vba:

[fieldToReplace] = fncReplace([fieldToReplace])

jeran042 · Aug 9, 2018

arnelgp said:
firstly, you should make POLICY field in both tables consistent (i suggest short text so you can have mixed letters and numbers on them).
second, you use the Form's Load event and not the Open event.
Form''s controls (except the subform) have no values when in Open event.

you can create a Public Function for the replacement:

Code:

Public Function fncReplace(byval thisPolicy As Variant) As String Const Pattern As String = "^(R0|R|0)" thisPolicy = thisPolicy & "" With CreateObject("VBScript.RegExp") .Pattern = Pattern .Global = True .IgnoreCase = True fncReplace = Trim(.Replace(thisPolicy, "")) End With End Function

so the expression on the query or vba:

[fieldToReplace] = fncReplace([fieldToReplace])

I couldn't agree more, the problem is that one table is a linked table, and I do not have ownership of it.

Secondly, can you help me a little more with your suggestion.
So I would put this function in the parent forms On Load event, or the subforms On Load event. Next, would I put (in my case) the code below in the function?

Code:

[me.txtTEST] = fncReplace([me.txtTEST])

and =fncReplace([me.txtTEST]) in the controls source?

arnelgp · Aug 9, 2018

why don't we just do it once using an Update Query when the form opens/load (whichever event you like).

private sub form_open(cancel as integer)
currentdb.execute "update [yourtable] set [policy]=fncReplace([policy];"
end sub

Set Value of Control based on result of Select Case (1 Viewer)

jeran042

Registered User.

Ranman256

Well-known member

BigHappyDaddy

Coding Monkey Wanna-Be

arnelgp

..forever waiting... waiting for jellybean!

jeran042

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread