Input Box Bound to Expression

[CementCarver]

I have an input box that display a number field which is bound to an expression =IIf(IsNull([SMLicence]),"","SM" & [SMLicence] & " -14")

But I am not able to input any numbers into this field, since it's bound to this expression.

Is there anyway that a number can be input, regardless if it has an expression or not?

CementCarver

[ItsMe]

Create a new text box to handle your User Input and then add the new control name/value to your aggregate expression.

[CementCarver]

ItsMe,

Don't quite understand what you're saying... Is it possible for you to provide an example?

=IIf(IsNull([SMLicence]),"","SM" & [SMLicence] & " -14")

So, you'd place the new textbox control name inside the expression above?

CementCarver

[ItsMe]

What is the name of the field that contains your expression? This field is displaying information as a result of your expression, ie SM True -14

Are you trying to add additional information in this text box control?

[CementCarver]

I patch together the correct licence number in the format that my client has asked for. But in the column, there is only a 2x digit number. So, if the SMLicence is 10, the output should display SM10-14. All I want to do is allow the client to input a number in this existing field.

CementCarver

[ItsMe]

All I want to do is allow the client to input a number in this existing field.

CementCarver

I understood everything in post # 5, except this part.

What is the name of the control that has your expression?

Do you want to incorporate the user input within the expression?

[CementCarver]

The name of the control is SelfMLicence and yes, it would like to incorporate an input into the expression.

CC

[ItsMe]

You need to create another control to collect your user input. You also need a way to fire some code to update the field named SelfMLicence.

Here is one way to go about it.

Erase the expresion within SelfMLicence. It will now be unbound. Create another unbound textbox named txtAddInput.

In the form's "Current" event place the following. I took the liberty to edit the original expresion to add a space after SM.

Dim strLicense As String
strLicense = IIf(IsNull([SMLicence]), "", "SM " & [SMLicence] & " -14")
Me.SelfMLicence.Value = strLicense

In the new control's AfterUpdate event (the one named "txtAddInput") place the following

Dim strLicense As String
If Not IsNull(Me.txtAddInput) Then
strLicense = Me.txtAddInput & "_"
Else
strLicense = ""
End If
strLicense = strLicense & IIf(IsNull([SMLicence]), "", "SM " & [SMLicence] & " -14")
Me.SelfMLicence.Value = strLicense

With that, when the user exits the new text box, it will add whatever was typed into txtAddInput.

Let me know if you need to change anything around, like the underscore.

[June7]

Why save concatenated data? Why save two discrete values into same field? Why save calculated value at all? Saving calculated data is usually ill-advised.

Why save the SM and -14 into the field? If this is same for all records, can calculate concatenated value when needed for display.

[CementCarver]

Thanks ItsMe

Your code worked perfectly. I can now have the client input the numerical value required.

Because their old licencing system worked for the format required I needed a method to continue with their format is. Self Marketer Licence numbers are SM30-14, meaning the 30th licenced issued within the year 2014.

Thank you for everyone's input.

CementCarver

[ItsMe]

Alright, as long as your client is happy. The code I gave you gives you two unbound text boxes. With the value's being held in a string variable you may not need both textboxes. But if you got things working to your liking......

[June7]

Okay, I can understand that, have similar situation (20yyA-XXXX). Then the -14 needs to be dynamic? I have code that determines the current year and uses that value to construct the identifier.

[ItsMe]

Okay, I can understand that, have similar situation (20yyA-XXXX). Then the -14 needs to be dynamic? I have code that determines the current year and uses that value to construct the identifier.

Yeah, I think more can be done to remove the User interaction. Limit the chance for error because the user typed something incorrectly. Anytime there is a user input, there is a chance for error. The more the program dictates, the more consistent the data.

[CementCarver]

Gentlemen,

When a new marketer gets created, I automatically assign the next highest value for the marketing licence number (M24 - 14), but for the self marketing number, not all marketers have this distinction, therefore, the user needs to assign it when the time comes. Or else I would have done the same as the marketing licence number.

I do agree though, that any time a user needs to input something, it does introduce the possibility of errors. Again, thank you for your input.

CementCarver