Concatenating text and strings as controlsource for a text box

[WalkieTalkie]

I always have trouble with concatentaing strings and text and can never work out whether and/or when to use a single or double apostrophe!!! I know this is simple but I just can't get it right...

txtInstructions is a textbox on a form in a MS Access 2003 database.

I am trying to change the control source for the the text box using the following code:

Dim strNewClient As String
strNewClient = Me.txtNewClient

[Forms]![frmCompanionWizard]![txtInstructions].ControlSource = "= '" & strNewClient & "' is now a member of '& txtOldClient &'`s travelling party.'"

I am getting a syntax error. I have isolated it down to a problem with the syntax around strNewClient but despite having tried what must be every variation of combinations of single and double apostrophes I just can't get it to work.

I will be grateful for any help offered!

 

[strongm]

Either of these should do the trick:

[Forms]![frmCompanionWizard]![txtInstructions].ControlSource = "= '" & strNewClient & " is now a member of ' & [txtOldClient] & '`s travelling party.'"


or

[Forms]![frmCompanionWizard]![txtInstructions].ControlSource = "= """ & strNewClient & " is now a member of "" & [txtOldClient] & ""'s travelling party.

 

[Andrzejek]

To help you solve the over-all problem with "when to use a single or double apostrophe", do this:

Dim strNewClient As String
[blue]Dim strCS As String[/blue]

strNewClient = Me.txtNewClient
[blue]
strCS = "= '" & strNewClient & "' is now a member of '" & [txtOldClient] & "'`s travelling party."

Debug.Print strCS[/blue]

[Forms]![frmCompanionWizard]![txtInstructions].ControlSource =[blue] strCS[/blue]

This way you can see in Immediate Window the value of your strCS, or - if you don't like [tt]Debug.Print[/tt], you can stop on line after [tt]strCS = ...[/tt] and type in Immediate Window:
[tt]? strCS[/tt]
and - again - you can see what you have in the String.

Have fun.

---- Andy

 

[WalkieTalkie]

Hi and thank you for such prompt replies.

Here are the results for the three suggestions:

"= '" & strNewClient & " is now a member of ' & [txtOldClient] & '`s travelling party.'"

results in...

= 'Practice Client' is now a member of ' Another Client'`s travelling party.

- with unwanted apostrophes.

...next...

"= """ & strNewClient & " is now a member of "" & [txtOldClient] & ""'s travelling party."""

results in...

= 'Practice Client is now a member of ' & [txtOldClient] & '`s travelling party.'

...and, finally:

"= '" & strNewClient & "' is now a member of '" & [txtOldClient] & "'`s travelling party."

results in...

= "Practice Client is now a member of " & [txtOldClient] & "'s travelling party."

I suppose at least I feel a little vindicated that its not just me! How annoying is this???

Any other suggestions?

 

[Andrzejek]

Well, it would be very helpful to know [red]what do you want to have as an outcome[/red] of this “string building exercise”? All what you got so far is our best guess what you want.

In your original post you presented the sample of your code that does not work for you, but we have no idea what you want to happen.


Have fun.

---- Andy

 

[strongm]

>vindicated

Don't be. I know exactly what I'm doing here. Both of my suggestions work fine as ControlSource strings given your original post. Sadly it now appears that yopur OP isn't what you actually meant.

Perhaps you'd like to figure out exactly what it is you want, and then come back and tell us sop we can help you properly.

 

[MajP]

Assume I have a txtOldClient on the form. It has a value of "smith" in it

Public Sub SetControlSource()
  Dim cs As String
  Dim strNewClient As String
  strNewClient = "Brown"
  
  cs = "= """ & strNewClient & " is now a member of "" & [txtOldClient] & "" 's traveling party"""
  Debug.Print cs
  Forms("frmClient").txtUnbound.controlSource = cs
End Sub

This looks like this in the control source
="Brown is now a member of " & [txtOldClient] & " 's traveling party"

and resolves to this
Brown is now a member of Smith 's traveling party


However, I can not think of a worse way to do this. This is probably the most complicated use of string concatenation I have ever seen. You are changing the control source with a literal and a concatenated control to build a complicated string to get resolved by the form. If you are doing that why not just set the value of the control through code, or build a UDF that does the concatenation.

 

[MajP]

form with
txtOldClient
txtNewClient
txtInstructions

txtOldClient has value of Jones
txtnewClient has value of smith
txtInstructions has control source of
=getInstructions([txtOldClient],[txtNewClient])

Public Function getInstructions(oldClient As Variant, newClient As Variant) As Variant
  If Not IsNull(newClient) And Not IsNull(oldClient) Then
    getInstructions = newClient & " is now a member of " & oldClient & "'s traveling Party"
  End If
End Function

value of txtInstructions is:
Jones is now a member of Smith's traveling Party

If there are some other cases where the control source is completely different then you can put that in the udf. I checked for existence of client but it could be any check.

Public Function getInstructions(oldClient As Variant, newClient As Variant) As Variant
  If Not IsNull(newClient) And Not IsNull(oldClient) Then
    getInstructions = newClient & " is now a member of " & oldClient & "'s traveling Party"
  ElseIf IsNull(newClient) And Not IsNull(oldClient) Then
    getInstructions = oldClient & " is cool"
  ElseIf IsNull(oldClient) And Not IsNull(newClient) Then
    getInstructions = newClient & " is in his own party"
  End If
End Function

 

[WalkieTalkie]

Thanks for your help and patience so far and I'm sorry I didn't make myself more clear to start with - I guess I didn't know what I don't know.

I want to set the value of the control through code, but don't know how to. I want to do this because I want the value to alter when I click a command button. If I set the control source using the property sheet then can I make it change when I click the button?

Does that make sense? I expect that I am making this more complicated than it should be - that is because I am learning as I go along, which is why I am seeking help, which I really appreciate.

MajP, your last suggestion works fine until I try to make the controlsource change by clicking the button, then I run into the same problem as before.

What is the simple way to make a text box change its value when the user clicks a command button? eg:

The text box starts out containing the text "Select a person from the list" and after the user selects 'Mr Smith' from a listbox, the text in the text box changes to "Mr Smith has been selected" (for example).

Thanks for your time and I hope you haven't given up on me!

 

[MajP]

I would simply use a user defined function. This will handle a single form view or a continuous form view.

I will assume your listbox is bound to a field "fieldClient" and is called lstClient

public function getInstructions(client as variant) as string
'check if fieldClient is empty or null
if trim(client & " ") = "" then
getInstructions = "Select from list"
else
getInstructions = Client & " has been selected"
end if
end function

in the txtBxInstructions control source

=getInstructions([lstClient])

For each record, the control source calls the function and passes the value of the list box to the function. That is why this method will work on a continous form.

If instead of using a function you just call a procedure to set the value of the txtBxInstructions, and since it is an unbound control all instantiations of that control would get the same value. Would work fine on a single form view but not on a continous form view. This method however is a little simpler if using a single form view. it would simply be

private sub setInstructions()
'check if fieldClient is empty or null
if trim(me.lstCclient & " ") = "" then
me.txtInstructions = "Select from list"
else
me.txtInstructions = Client & " has been selected"
end if
end sub

Then just leave the control source blank and call this on the forms current event and the listbox afterupdate event

private sub Form_change()
setInstructions
end sub

 

[MajP]

btw the function can have multiple parameters like

public function getInstructions(oldClient as variant, newClient as variant) as string
code here
end function

with function calls in access I always make the parameters variants and then check to see if they are null or empty.

to call from a control source

=getInstructions([txtBxOldCleint],[lstNewClient])

that would pass the value contained in the old client textbox and the new client listbox to the function.

 

[strongm]

>I want to set the value of the control through code

Then either of my solutions should work. Your problem is that you are expecting debug or MsgBox to produce the same result as wahtever you expect your control to output. They won't, since the control performs an additional evaluation of the string that neither debug or MsgBox carry out.

 

[MajP]

As StrongM and I have previously shown if you can make a string for the control source that looks like this:

= "Smith is now a member of " & [txtOldClient] & "'s travelling party."

or this

= 'Smith is now a member of ' & [txtOldClient] & '`s travelling party.'

Then the form will resolve the control source to
Smith is now a member of Brown's travelling party.
assuming Brown is in txtOldClient

But I still think it is silly to write code to basically write code that has to be resolved by the form. Lot easier to do in one step

either use a UDF
Set the value from code

another simple way would be
=iif(isnull([lstClient],"Select from list", [lstClient] & " is now a member of " & [txtOldClient] & "`s travelling party.")

But I would not write code that makes a new line of code.