auto-fill a textbox

[Guest]

Can I have a textbox auto-filled with the combined content of other 2
textboxes?

ie. textbox1 has "Color", textbox2 has "Size" --> fill textbox3 with
"Color.Size"

Regards,
Sam

 

[Guest]

Yes. Using the AFTERUPDATE method, you can write a line of code like:

Textbox3.value = Textbox1.value + "." Textbox2.value

 

[Guest]

Sam, if you're not trying to *store* this concatenated string into an
underlying table field (and you shouldn't, since you can recreate it
anytime), simply set the textbox' Control Source property:

=[TextBox1] + "." + [TextBox2]

 

[Guest]

Many thanks to both.
I wonder if this can be expand further? Can I have the Textbox displaying
the value in Textbox1 plus only the 1st letter of Textbox2? Thanks

e.g.
Textbox1 = Blue
Textbox2 = M - Medium
Textbox then displays Blue.M

Sam, if you're not trying to *store* this concatenated string into an
underlying table field (and you shouldn't, since you can recreate it
anytime), simply set the textbox' Control Source property:

=[TextBox1] + "." + [TextBox2]

Can I have a textbox auto-filled with the combined content of other 2
textboxes?

ie. textbox1 has "Color", textbox2 has "Size" --> fill textbox3 with
"Color.Size"

Regards,
Sam

 

[Jeff Boyce]

Sam

Look into the use of the Left$() function. Left$([Textbox2],1) would give
the leftmost character of Textbox2.

--
Good luck

Jeff Boyce
<Access MVP>

Many thanks to both.
I wonder if this can be expand further? Can I have the Textbox displaying
the value in Textbox1 plus only the 1st letter of Textbox2? Thanks

e.g.
Textbox1 = Blue
Textbox2 = M - Medium
Textbox then displays Blue.M

Sam, if you're not trying to *store* this concatenated string into an
underlying table field (and you shouldn't, since you can recreate it
anytime), simply set the textbox' Control Source property:

=[TextBox1] + "." + [TextBox2]

Can I have a textbox auto-filled with the combined content of other 2
textboxes?

ie. textbox1 has "Color", textbox2 has "Size" --> fill textbox3 with
"Color.Size"

Regards,
Sam

 

[Guest]

Thanks for your prompt reply, Jeff. That's very helpful. But I just realise I
still need a bit more on this auto-fill action for another example (sorry).

e.g.
Textbox1 = drawing number
Textbox2 = status (a value list of "D - in development","J - job with number)
Textbox3 might then be A123.D or A123.J001 etc

I think this may require the use of IF...ELSE function, but don't know how
this would be done. Can you please help?

Sam

Look into the use of the Left$() function. Left$([Textbox2],1) would give
the leftmost character of Textbox2.

--
Good luck

Jeff Boyce
<Access MVP>

Many thanks to both.
I wonder if this can be expand further? Can I have the Textbox displaying
the value in Textbox1 plus only the 1st letter of Textbox2? Thanks

e.g.
Textbox1 = Blue
Textbox2 = M - Medium
Textbox then displays Blue.M

Sam, if you're not trying to *store* this concatenated string into an
underlying table field (and you shouldn't, since you can recreate it
anytime), simply set the textbox' Control Source property:

=[TextBox1] + "." + [TextBox2]

:

Can I have a textbox auto-filled with the combined content of other 2
textboxes?

ie. textbox1 has "Color", textbox2 has "Size" --> fill textbox3 with
"Color.Size"

Regards,
Sam

 

[Guest]

Hi, Sam.

Refer to VBA Help on the IIf() function. BTW, to access VBA Help, you must
be in module edit mode. Pressing <Ctrl-G> from the database window will do
it also.

IIf() returns a single value. The syntax is:

=IIf({True/False statement}, {Value if True}, {Value if False})

So, for your case for 2 possible values of textbox 2,

=IIf([textbox2]="D",[textbox1]+"."+[textbox2],[textbox1]+"."+[textbox2]+[textbox3])

If you have more than 2 possible values, you can either use nested IIf's,
or, preferably write a new function using a Select Case statement and store
it in a public module (New from the Modules tab). I've assumed that the
'number' you're referring to is really a string value.

Public Function MyTextBoxValue(strTBox1 as String, strTBox2 As String) As
String
Select Case strTBox2
Case "D"
MyTextBoxValue = [textbox1]+"."+[textbox2]
Case "J"
MyTextBoxValue = [textbox1]+"."+[textbox2]+[textbox3]
' Put any other cases here
End Function

Then in the Control Source of textbox 3, place a call to the function,
passing the values from Textbox1 and Textbox2 to the function:

=MyTextBoxValue([textbox1], [textbox2])

Hope that helps.
Sprinks

Thanks for your prompt reply, Jeff. That's very helpful. But I just realise I
still need a bit more on this auto-fill action for another example (sorry).

e.g.
Textbox1 = drawing number
Textbox2 = status (a value list of "D - in development","J - job with number)
Textbox3 might then be A123.D or A123.J001 etc

I think this may require the use of IF...ELSE function, but don't know how
this would be done. Can you please help?

Sam

Look into the use of the Left$() function. Left$([Textbox2],1) would give
the leftmost character of Textbox2.

--
Good luck

Jeff Boyce
<Access MVP>

Many thanks to both.
I wonder if this can be expand further? Can I have the Textbox displaying
the value in Textbox1 plus only the 1st letter of Textbox2? Thanks

e.g.
Textbox1 = Blue
Textbox2 = M - Medium
Textbox then displays Blue.M

:

Sam, if you're not trying to *store* this concatenated string into an
underlying table field (and you shouldn't, since you can recreate it
anytime), simply set the textbox' Control Source property:

=[TextBox1] + "." + [TextBox2]

:

Can I have a textbox auto-filled with the combined content of other 2
textboxes?

ie. textbox1 has "Color", textbox2 has "Size" --> fill textbox3 with
"Color.Size"

Regards,
Sam

 

[Guest]

Thanks, Sprinks. Here is my code using your IIf() method. But I believe my
attempt here still needs improvement. Can you please help me again?

Here’s all the fields I have in the form:
1) Version textbox (text value)
2) Status combobox (with a value list of "O - All";"D-In Development";"P-In
Production";"A-An Assembly";"C-A Concept";"J-Job with #")
3) JobNo textbox (numeric value)
4) VersionStatus textbox

Now VersionStatus returns the combined values entered in Version, Status and
JobNo. However, I was trying to have JobNo textbox enabled only when “J-Job
with #†is selected in the Status combo box, otherwise JobNo stays disabled
and is skipped (ie. send focus straight to VersionStatus textbox and returns
only combined values of Version and Status) after an “non-J†Status value is
chosen. How do I make this work?


--- start of my code ---
Private Sub Version_AfterUpdate()
VersionStatus.Value = [Version]
End Sub

Private Sub Status_AfterUpdate()
VersionStatus.Value = IIf([Status] = "J-Job with #", [Version] + "." +
[Status] + [JobNo], [Version] + "." + Left$([Status], 1))
End Sub

Private Sub JobNo_AfterUpdate()
VersionStatus.Value = [Version] + "." + Left$([Status], 1) + [JobNo]
End Sub
--- end of my code ---

[NB. AfterUpdate, instead of VersionStatus’s ControlSource, is used here
because the VersionStatus value needs to be displayed in another
VersionStatus field in a subform]

 

[Guest]

Hi, Sam.

Firstly, IIf is a function that provides a simple way to assign a value
without VBA event code. If you're going to use a VBA procedure anyway, your
code will be more readable and flexible by using an If..Then..Else...End If
construct. You are also missing a call to the Left$ function in the Status
AfterUpdate event. Rewriting:

If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status],1) + [JobNo]
Else
[VersionStatus] = [Version] + "." + Left$([Status],1)
End If

The parentheses are just a reminder that what's enclosed evaluates to a
single boolean value.

Re: toggling on [JobNo] when [Status] = "J": set the JobNo textbox'
Enabled property (and, optionally, its Visible property) to No in Form Design
View. Then toggle them on in the Status AfterUpdate event.

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If

If this form is being used in Data Entry mode only, this is all you'll need.
If the user can scroll between records, however, you'd want [JobNo] to
reflect what's ever in the [Status] field. In that case, add the following
to the form's On Current event:

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
Else
[JobNo].Enabled = False
[JobNo].Visible = False
End If

One final note: since you'd really like [VersionStatus] to update its value
regardless of which of the other 3 fields change, this suggests writing a
general procedure,

Private Sub UpdateVersionStatus()
' Put code here
End Sub

and calling it in the AfterUpdate event of each control:

Call UpdateVersionStatus

Hope that solves it for you.
Sprinks

 

[Guest]

Thanks Sprinks. Your code and description is very clear and easy to follow.
Everything works well except this one thing with your general procedure that
I adopt -- the Version AfterUpdate event triggers a run-time error 94 Invalid
use of Null, with the line [VersionStatus] = [Version] + "." +
Left$([Status], 1)

I was thinking is it because Status is still blank (as it should) after
filling Version textbox so there's no Status value to update? Can you see any
way around this? Thanks

--- start of my code ---
Private Sub Version_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub Status_AfterUpdate()
If Left$([Status], 1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If
Call UpdateVersionStatus
End Sub

Private Sub JobNo_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub UpdateVersionStatus()
If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status], 1) + [JobNo]
Else
' this line triggers a run-time error
[VersionStatus] = [Version] + "." + Left$([Status], 1)
End If
End Sub
--- end of my code ---

Hi, Sam.

Firstly, IIf is a function that provides a simple way to assign a value
without VBA event code. If you're going to use a VBA procedure anyway, your
code will be more readable and flexible by using an If..Then..Else...End If
construct. You are also missing a call to the Left$ function in the Status
AfterUpdate event. Rewriting:

If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status],1) + [JobNo]
Else
[VersionStatus] = [Version] + "." + Left$([Status],1)
End If

The parentheses are just a reminder that what's enclosed evaluates to a
single boolean value.

Re: toggling on [JobNo] when [Status] = "J": set the JobNo textbox'
Enabled property (and, optionally, its Visible property) to No in Form Design
View. Then toggle them on in the Status AfterUpdate event.

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If

If this form is being used in Data Entry mode only, this is all you'll need.
If the user can scroll between records, however, you'd want [JobNo] to
reflect what's ever in the [Status] field. In that case, add the following
to the form's On Current event:

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
Else
[JobNo].Enabled = False
[JobNo].Visible = False
End If

One final note: since you'd really like [VersionStatus] to update its value
regardless of which of the other 3 fields change, this suggests writing a
general procedure,

Private Sub UpdateVersionStatus()
' Put code here
End Sub

and calling it in the AfterUpdate event of each control:

Call UpdateVersionStatus

Hope that solves it for you.
Sprinks

 

[Guest]

Hi, Sam. Sorry about that.

Use the concatenate operator (&) on strings rather than the + operator.
They behave differently with Null values.

[VersionStatus] = [Version] & "." & Left$([Status], 1)

Hope that helps.
Sprinks

Thanks Sprinks. Your code and description is very clear and easy to follow.
Everything works well except this one thing with your general procedure that
I adopt -- the Version AfterUpdate event triggers a run-time error 94 Invalid
use of Null, with the line [VersionStatus] = [Version] + "." +
Left$([Status], 1)

I was thinking is it because Status is still blank (as it should) after
filling Version textbox so there's no Status value to update? Can you see any
way around this? Thanks

--- start of my code ---
Private Sub Version_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub Status_AfterUpdate()
If Left$([Status], 1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If
Call UpdateVersionStatus
End Sub

Private Sub JobNo_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub UpdateVersionStatus()
If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status], 1) + [JobNo]
Else
' this line triggers a run-time error
[VersionStatus] = [Version] + "." + Left$([Status], 1)
End If
End Sub
--- end of my code ---

Hi, Sam.

Firstly, IIf is a function that provides a simple way to assign a value
without VBA event code. If you're going to use a VBA procedure anyway, your
code will be more readable and flexible by using an If..Then..Else...End If
construct. You are also missing a call to the Left$ function in the Status
AfterUpdate event. Rewriting:

If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status],1) + [JobNo]
Else
[VersionStatus] = [Version] + "." + Left$([Status],1)
End If

The parentheses are just a reminder that what's enclosed evaluates to a
single boolean value.

Re: toggling on [JobNo] when [Status] = "J": set the JobNo textbox'
Enabled property (and, optionally, its Visible property) to No in Form Design
View. Then toggle them on in the Status AfterUpdate event.

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If

If this form is being used in Data Entry mode only, this is all you'll need.
If the user can scroll between records, however, you'd want [JobNo] to
reflect what's ever in the [Status] field. In that case, add the following
to the form's On Current event:

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
Else
[JobNo].Enabled = False
[JobNo].Visible = False
End If

One final note: since you'd really like [VersionStatus] to update its value
regardless of which of the other 3 fields change, this suggests writing a
general procedure,

Private Sub UpdateVersionStatus()
' Put code here
End Sub

and calling it in the AfterUpdate event of each control:

Call UpdateVersionStatus

Hope that solves it for you.
Sprinks

 

[Guest]

Hi Sprinks,
It still says Invalid use of Null with the line

[VersionStatus] = [Version] & "." & Left$([Status], 1)

if Version is entered first, then Status.

However, as long as Status isn't null before entering value in Version (i.e.
fill Status with something first before filling Version) , it works fine
without the error message poping up. How can I fix this?

Regards,
Sam

Hi, Sam. Sorry about that.

Use the concatenate operator (&) on strings rather than the + operator.
They behave differently with Null values.

[VersionStatus] = [Version] & "." & Left$([Status], 1)

Hope that helps.
Sprinks

Thanks Sprinks. Your code and description is very clear and easy to follow.
Everything works well except this one thing with your general procedure that
I adopt -- the Version AfterUpdate event triggers a run-time error 94 Invalid
use of Null, with the line [VersionStatus] = [Version] + "." +
Left$([Status], 1)

I was thinking is it because Status is still blank (as it should) after
filling Version textbox so there's no Status value to update? Can you see any
way around this? Thanks

--- start of my code ---
Private Sub Version_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub Status_AfterUpdate()
If Left$([Status], 1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If
Call UpdateVersionStatus
End Sub

Private Sub JobNo_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub UpdateVersionStatus()
If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status], 1) + [JobNo]
Else
' this line triggers a run-time error
[VersionStatus] = [Version] + "." + Left$([Status], 1)
End If
End Sub
--- end of my code ---

Hi, Sam.

Firstly, IIf is a function that provides a simple way to assign a value
without VBA event code. If you're going to use a VBA procedure anyway, your
code will be more readable and flexible by using an If..Then..Else...End If
construct. You are also missing a call to the Left$ function in the Status
AfterUpdate event. Rewriting:

If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status],1) + [JobNo]
Else
[VersionStatus] = [Version] + "." + Left$([Status],1)
End If

The parentheses are just a reminder that what's enclosed evaluates to a
single boolean value.

Re: toggling on [JobNo] when [Status] = "J": set the JobNo textbox'
Enabled property (and, optionally, its Visible property) to No in Form Design
View. Then toggle them on in the Status AfterUpdate event.

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If

If this form is being used in Data Entry mode only, this is all you'll need.
If the user can scroll between records, however, you'd want [JobNo] to
reflect what's ever in the [Status] field. In that case, add the following
to the form's On Current event:

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
Else
[JobNo].Enabled = False
[JobNo].Visible = False
End If

One final note: since you'd really like [VersionStatus] to update its value
regardless of which of the other 3 fields change, this suggests writing a
general procedure,

Private Sub UpdateVersionStatus()
' Put code here
End Sub

and calling it in the AfterUpdate event of each control:

Call UpdateVersionStatus

Hope that solves it for you.
Sprinks

 

[Guest]

That's very strange, Sam. I tried duplicating your error, and couldn't. Try
converting each potentially Null value to a blank string with the Nz()
function:

[VersionStatus] = Nz([Version]) & "." & Left$(Nz([Status]), 1)

Sprinks

Hi Sprinks,
It still says Invalid use of Null with the line

[VersionStatus] = [Version] & "." & Left$([Status], 1)

if Version is entered first, then Status.

However, as long as Status isn't null before entering value in Version (i.e.
fill Status with something first before filling Version) , it works fine
without the error message poping up. How can I fix this?

Regards,
Sam

Hi, Sam. Sorry about that.

Use the concatenate operator (&) on strings rather than the + operator.
They behave differently with Null values.

[VersionStatus] = [Version] & "." & Left$([Status], 1)

Hope that helps.
Sprinks

Thanks Sprinks. Your code and description is very clear and easy to follow.
Everything works well except this one thing with your general procedure that
I adopt -- the Version AfterUpdate event triggers a run-time error 94 Invalid
use of Null, with the line [VersionStatus] = [Version] + "." +
Left$([Status], 1)

I was thinking is it because Status is still blank (as it should) after
filling Version textbox so there's no Status value to update? Can you see any
way around this? Thanks

--- start of my code ---
Private Sub Version_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub Status_AfterUpdate()
If Left$([Status], 1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If
Call UpdateVersionStatus
End Sub

Private Sub JobNo_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub UpdateVersionStatus()
If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status], 1) + [JobNo]
Else
' this line triggers a run-time error
[VersionStatus] = [Version] + "." + Left$([Status], 1)
End If
End Sub
--- end of my code ---

:

Hi, Sam.

Firstly, IIf is a function that provides a simple way to assign a value
without VBA event code. If you're going to use a VBA procedure anyway, your
code will be more readable and flexible by using an If..Then..Else...End If
construct. You are also missing a call to the Left$ function in the Status
AfterUpdate event. Rewriting:

If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status],1) + [JobNo]
Else
[VersionStatus] = [Version] + "." + Left$([Status],1)
End If

The parentheses are just a reminder that what's enclosed evaluates to a
single boolean value.

Re: toggling on [JobNo] when [Status] = "J": set the JobNo textbox'
Enabled property (and, optionally, its Visible property) to No in Form Design
View. Then toggle them on in the Status AfterUpdate event.

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If

If this form is being used in Data Entry mode only, this is all you'll need.
If the user can scroll between records, however, you'd want [JobNo] to
reflect what's ever in the [Status] field. In that case, add the following
to the form's On Current event:

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
Else
[JobNo].Enabled = False
[JobNo].Visible = False
End If

One final note: since you'd really like [VersionStatus] to update its value
regardless of which of the other 3 fields change, this suggests writing a
general procedure,

Private Sub UpdateVersionStatus()
' Put code here
End Sub

and calling it in the AfterUpdate event of each control:

Call UpdateVersionStatus

Hope that solves it for you.
Sprinks

 

[Guest]

Splendid! the Nz() function does the trick beautifully
Many thanks!

Sam

That's very strange, Sam. I tried duplicating your error, and couldn't. Try
converting each potentially Null value to a blank string with the Nz()
function:

[VersionStatus] = Nz([Version]) & "." & Left$(Nz([Status]), 1)

Sprinks

Hi Sprinks,
It still says Invalid use of Null with the line

[VersionStatus] = [Version] & "." & Left$([Status], 1)

if Version is entered first, then Status.

However, as long as Status isn't null before entering value in Version (i.e.
fill Status with something first before filling Version) , it works fine
without the error message poping up. How can I fix this?

Regards,
Sam

Hi, Sam. Sorry about that.

Use the concatenate operator (&) on strings rather than the + operator.
They behave differently with Null values.

[VersionStatus] = [Version] & "." & Left$([Status], 1)

Hope that helps.
Sprinks

:

Thanks Sprinks. Your code and description is very clear and easy to follow.
Everything works well except this one thing with your general procedure that
I adopt -- the Version AfterUpdate event triggers a run-time error 94 Invalid
use of Null, with the line [VersionStatus] = [Version] + "." +
Left$([Status], 1)

I was thinking is it because Status is still blank (as it should) after
filling Version textbox so there's no Status value to update? Can you see any
way around this? Thanks

--- start of my code ---
Private Sub Version_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub Status_AfterUpdate()
If Left$([Status], 1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If
Call UpdateVersionStatus
End Sub

Private Sub JobNo_AfterUpdate()
Call UpdateVersionStatus
End Sub

Private Sub UpdateVersionStatus()
If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status], 1) + [JobNo]
Else
' this line triggers a run-time error
[VersionStatus] = [Version] + "." + Left$([Status], 1)
End If
End Sub
--- end of my code ---

:

Hi, Sam.

Firstly, IIf is a function that provides a simple way to assign a value
without VBA event code. If you're going to use a VBA procedure anyway, your
code will be more readable and flexible by using an If..Then..Else...End If
construct. You are also missing a call to the Left$ function in the Status
AfterUpdate event. Rewriting:

If ([Status] = "J-Job with #") Then
[VersionStatus] = [Version] + "." + Left$([Status],1) + [JobNo]
Else
[VersionStatus] = [Version] + "." + Left$([Status],1)
End If

The parentheses are just a reminder that what's enclosed evaluates to a
single boolean value.

Re: toggling on [JobNo] when [Status] = "J": set the JobNo textbox'
Enabled property (and, optionally, its Visible property) to No in Form Design
View. Then toggle them on in the Status AfterUpdate event.

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
End If

If this form is being used in Data Entry mode only, this is all you'll need.
If the user can scroll between records, however, you'd want [JobNo] to
reflect what's ever in the [Status] field. In that case, add the following
to the form's On Current event:

If Left$([Status],1) = "J" Then
[JobNo].Enabled = True
[JobNo].Visible = True
Else
[JobNo].Enabled = False
[JobNo].Visible = False
End If

One final note: since you'd really like [VersionStatus] to update its value
regardless of which of the other 3 fields change, this suggests writing a
general procedure,

Private Sub UpdateVersionStatus()
' Put code here
End Sub

and calling it in the AfterUpdate event of each control:

Call UpdateVersionStatus

Hope that solves it for you.
Sprinks