How to place a suffix on a textbox entry?

[Turb]

Hello all,
I have a textbox control on my form that is populated by a query when the form opens. I have been trying to figure out how to attach a suffix to the data in the textbox based on what the data itself is.
For example: if the query populates the textbox field with data starting with "FR" (FR123456), I need the suffix "L" to be tacked on (FR123456L); if not then I need no suffix.
Please help!

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[dRahme]

Hello, one way is to do it in your form's query:

SELECT IIf(Mid([YourTextField],2,2)="FR",[YourTextField] & "L",[YourTextField]) AS TestforFR, Table1.YourTextField
FROM Table1;

and use that for your control's recordsource.

Hope that helps.

 

[Turb]

dRahme,
Thank you for your response.
Will this work if the user has to input the data to the query for this control: Criteria = "Like [Enter Model Number]"?

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[Turb]

This is what I had in mind but, I can't figure out the "Then" part of this statement that is on the OnChange event of the textbox control:

Private Sub MODEL_Change()
On Error GoTo Err_MODEL_Change
 
    If Me!tbxControl.Value Like "FR*" Then
[b]  >>>What can I place here?<<<   [/b]
    Else
        'Do nothing
    End If
    
Exit_MODEL_Change:
    Exit Sub

Err_MODEL_Change:
    MsgBox Err.Description
    Resume Exit_MODEL_Change
End Sub

Will this approach even work?
Thanks for your help!

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[dRahme]

Hi, try the afterupdate event:

Private Sub txtField_AfterUpdate()
If Mid(Me.txtField, 2, 2) = "FR" Then
Me.txtField = Me.txtField & "L"
End If

End Sub

Is that what you had in mind?

 

[PHV]

Something like this ?
Private Sub MODEL_AfterUpdate()
If Left(Me!MODEL, 2) = "FR" And Right(Me!MODEL, 1) <> "L" Then
Me!MODEL = Me!MODEL & "L"
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Turb]

Thank you both but, this doesn't seem to be working.
I have tried dRahme's:

Private Sub MODEL_AfterUpdate()
   If Mid(Me.MODEL, 2, 2) = "FR" Then
    Me.MODEL = Me.MODEL & "L"
    End If
End Sub

and your suggestion PHV:

Private Sub MODEL_AfterUpdate()
  If Left(Me!MODEL, 2) = "FR" And Right(Me!MODEL, 1) <> "L" Then
    Me!MODEL = Me!MODEL & "L"
  End If
End Sub

Both placed on the textbox's AfterUpdate event.
When the query completes and the form opens, the data still reads "FR123456".
What am I'm doing wrong?


Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[PHV]

Put the same code in the Current event procedure of the form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Turb]

PHV,
Done.
I now get the following error:
"Run-time error '3226'
Recordset is not updateable."
The debugger is highlighting
Me!MODEL = Me!MODEL & "L"
Could this be an Access version thing?
I'm using 97.

Ind. Engineering Tech.

http://www.cdstandbypower.com/

 

[PHV]

Recordset is not updateable
Have a look here:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dRahme]

How about:

If Me.txtField Like "*" & fr & "*" Then
Me.txtField = Me.txtField & "L"
End If

 

[TheAceMan1]

How are ya Turb . . . . .

You have what you need in this thread, your just not using the code properly as far as form operations are concerned. Realize a controls [blue]AfterUpdate event doesn't trigger unless the control is edited/updated.[/blue] Also, [blue]writing to a textbox thru VBA does not trigger AfterUpdate![/blue] So when you open your form, nothing happens, and it won't until you edit/update the textbox.

What you need to do is run a [blue]one time Update query[/blue] to take care of all the current values in the table that don't have and require the suffix.

There after, [blue]PHV's[/blue] code in the AfterUpdate event will take care of any editing in the textbox, wether new record or previously saved . . .

See Ya! . . . . . .

 

[Turb]

All,
Thank you for all your help!
I'm sorry but, I believe I need to "drop back 10 and punt".
I just realized that there are other things working against me on this: the query I spoke of that underlies this form is pulling data from six different linked tables to create the record, the 'MODEL' textbox control on the form is "locked" and needs to remain so.
I guess I need to think about this some more and find a different approach.
Once again, thank you all for your insights and help!
Turb

Ind. Engineering Tech.

http://www.cdstandbypower.com/