473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

dynamically set a textbox control form...apostrophe gets in the way

Hi,

I currently have the following code in a subform.

If Not rstPDAddresses.EOF And Not rstPDAddresses.BOF Then
With rstPDAddresses
txtautoAddressID.ControlSource = "='" & Nz(!autoAddressID, "")
& "'"
txtlngAddressTypeID.ControlSource = "='" & Nz(!strAddressType,
"") & "'"
txtstrStreet.ControlSource = "='" & Nz(!strStreet, "") & "'"
txtstrStreet2.ControlSource = "='" & Nz(!strStreet2, "") & "'"
txtstrCity.ControlSource = "='" & Nz(!strCity, "") & "'"
txtstrState.ControlSource = "='" & Nz(!strState, "") & "'"
txtstrZip.ControlSource = "='" & Nz(!strZip, "") & "'"
End With
This code has been working wonderfully at setting the subforms textbox
values to the recordset results based on the main form display.

However, when you introduce an apostrophe into the street name, it will
not display at all.

txtstrStreet.ControlSource = "='" & Nz(!strStreet, "") & ""

is the offending line of code. I'm mostly a web guy, so I'm not
familiar with how to get access to do a workaround for displaying
apostrophes in the text box.

So when !strStreet = Ferrys Ridge RD it works. But when !strStreet =
Ferry's Ridge Rd it dies.

There has to be a work around for this, other than telling the users to
"not use apostrophes in street names"? I can't seem to find one yet.

Thanks for the help

-Trevor

Nov 13 '05 #1
11 3984
Replace each single quote in your code with 2 double-quotes. This will still
cause a problem if there is a street name with a double-quote, but that's
rarer.

As you know, VBA uses the double-quote to delimit the string. So, you can'
just embed a double-quote character in a string because VBA would think it
is the end of the string, and not know what to do with the rest. The
convention is to double-up the character in the string.

So, to get:
This string has a "word" in quotes
you would write:
"This string has a ""word"" in quotes"
If it is the last word, you end up with 3 quote characters at the end:
"This string has a ""word"""

So, you are going to code:
txtautoAddressID.ControlSource = "=""" & Nz(!autoAddressID, """)
and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Trevor" <zi******@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...

I currently have the following code in a subform.

If Not rstPDAddresses.EOF And Not rstPDAddresses.BOF Then
With rstPDAddresses
txtautoAddressID.ControlSource = "='" & Nz(!autoAddressID, "")
& "'"
txtlngAddressTypeID.ControlSource = "='" & Nz(!strAddressType,
"") & "'"
txtstrStreet.ControlSource = "='" & Nz(!strStreet, "") & "'"
txtstrStreet2.ControlSource = "='" & Nz(!strStreet2, "") & "'"
txtstrCity.ControlSource = "='" & Nz(!strCity, "") & "'"
txtstrState.ControlSource = "='" & Nz(!strState, "") & "'"
txtstrZip.ControlSource = "='" & Nz(!strZip, "") & "'"
End With
This code has been working wonderfully at setting the subforms textbox
values to the recordset results based on the main form display.

However, when you introduce an apostrophe into the street name, it will
not display at all.

txtstrStreet.ControlSource = "='" & Nz(!strStreet, "") & ""

is the offending line of code. I'm mostly a web guy, so I'm not
familiar with how to get access to do a workaround for displaying
apostrophes in the text box.

So when !strStreet = Ferrys Ridge RD it works. But when !strStreet =
Ferry's Ridge Rd it dies.

There has to be a work around for this, other than telling the users to
"not use apostrophes in street names"? I can't seem to find one yet.

Thanks for the help

-Trevor

Nov 13 '05 #2
MLH
How, exactly, does that create the apostrophe that he wants to see
in the control?
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Replace each single quote in your code with 2 double-quotes. This will still
cause a problem if there is a street name with a double-quote, but that's
rarer.

As you know, VBA uses the double-quote to delimit the string. So, you can'
just embed a double-quote character in a string because VBA would think it
is the end of the string, and not know what to do with the rest. The
convention is to double-up the character in the string.

So, to get:
This string has a "word" in quotes
you would write:
"This string has a ""word"" in quotes"
If it is the last word, you end up with 3 quote characters at the end:
"This string has a ""word"""

So, you are going to code:
txtautoAddressID.ControlSource = "=""" & Nz(!autoAddressID, """)
and so on.


Nov 13 '05 #3
MLH
Oops. Sorry.
I get it. The apostrophe's already there. Misread the OP.
Nov 13 '05 #4
Ok, that didn't compile actually. I get "expected: list seperator or )

I'm not trying to view double quotes tho, I'm looking for apostrophe's.

for instance. Say we have

---------------------------
dim strAddress as string
strAddress = "O'malley rd."

txtStreetAddress.controlsource = "=' " & Nz(strAddress, "") & " ' "

---------------------------
Since we know strAddress has an apostrophe in it, we know that it's
going to fail. I've tried setting it with 2 apostrophe's like you
would do with inserting it into SQL but that doesn't seem to work.

So, if you understood me correctly, and your solution does indeed
address my problem, then I'm not following you yet. Can we try one
more time?

Thanks,

-Trevor

Nov 13 '05 #5
Ok, I just tried the above code with replacing the strAddress with 2
apostrophe's again. I don't know why it bombed the first time, but
this time it displayed correctly (as SQL would do it)

so...
---------------------------
dim strAddress as string
strAddress = "O''malley rd."

txtStreetAddress.controlsource = "=' " & Nz(strAddress, "") & " ' "
---------------------------

displays O'malley Rd. In the text box.

This works great. Why it didn't the first time I tried that I have no
idea. I must have had a typeo that was killing me. Thanks for the
help

-Trevor

Nov 13 '05 #6
"Trevor" <zi******@gmail.com> wrote in
news:11**********************@g14g2000cwa.googlegr oups.com:
Ok, that didn't compile actually. I get "expected: list seperator
or )

I'm not trying to view double quotes tho, I'm looking for
apostrophe's.

for instance. Say we have

---------------------------
dim strAddress as string
strAddress = "O'malley rd."

txtStreetAddress.controlsource = "=' " & Nz(strAddress, "") & " '
"

---------------------------
Since we know strAddress has an apostrophe in it, we know that
it's
going to fail. I've tried setting it with 2 apostrophe's like you
would do with inserting it into SQL but that doesn't seem to work.

So, if you understood me correctly, and your solution does
indeed
address my problem, then I'm not following you yet. Can we try
one more time?


I've not been following this thread, but it makes no sense to to
NZ() on a string variable.

The single quotes are not necessary at all:

txtStreetAddress.controlsource = "=" & Chr(34) & strAddress &
Chr(34)

Now, if strAddress has a double quote in it, that will fail.

I don't quite understand the issue here. Why are literal strings
being placed in control sources? Why not just set the value of the
textboxes to the string value, then you have nothing to worry about?

Controlsources are for bound controls, i.e., bound to a field in a
recordset or to a value from another control or function. In that
case, quotes won't matter.

The only reason I could imagine to use the Controlsource for this is
to make the controls non-editable, but that can be accomplished with
a value assigned to the textbox by simply locking the control.

So, I just don't understand the issue here at all.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #7
David W. Fenton wrote:
The single quotes are not necessary at all:

txtStreetAddress.controlsource = "=" & Chr(34) & strAddress &
Chr(34)

Now, if strAddress has a double quote in it, that will fail.


In which case:

txtStreetAddress.controlsource = "=" & Chr(34) &
replace(strAddress,chr(34), chr(34) & chr(34)) &
Chr(34)

should do the trick.

I'm not sure what the single quotes are about, myself.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #8
Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:
David W. Fenton wrote:
The single quotes are not necessary at all:

txtStreetAddress.controlsource = "=" & Chr(34) & strAddress &
Chr(34)

Now, if strAddress has a double quote in it, that will fail.


In which case:

txtStreetAddress.controlsource = "=" & Chr(34) &
replace(strAddress,chr(34), chr(34) & chr(34)) &
Chr(34)

should do the trick.

I'm not sure what the single quotes are about, myself.


I'm not sure what the point is entirely. I see no justification for
assigning a *controlsource* with a literal value in code. Just set
the value of the control and be done with it.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9
To overcome this problem, I have created a function which duplicates
any embedded single quote marks:

Public Function duplicateEmbeddedQuotes(aString)
' duplicates embedded quotes for use in SQL searching
Dim thePos
thePos = InStr(1, aString, "'")
Dim theResult
If thePos = 0 Then ' not found
theResult = aString
Else
Dim rightBit
rightBit = Right(aString, Len(aString) - thePos)
theResult = Left(aString, thePos) & "'" &
duplicateEmbeddedQuotes(rightBit)
End If

duplicateEmbeddedQuotes = theResult
End Function

You could then write:

txtstrStreet.ControlSource = "='" & duplicateEmbeddedQuotes(!strStreet)
& "'"

Jim

Trevor wrote:
Hi,

I currently have the following code in a subform.

If Not rstPDAddresses.EOF And Not rstPDAddresses.BOF Then
With rstPDAddresses
txtautoAddressID.ControlSource = "='" & Nz(!autoAddressID, "")
& "'"
txtlngAddressTypeID.ControlSource = "='" & Nz(!strAddressType,
"") & "'"
txtstrStreet.ControlSource = "='" & Nz(!strStreet, "") & "'"
txtstrStreet2.ControlSource = "='" & Nz(!strStreet2, "") & "'"
txtstrCity.ControlSource = "='" & Nz(!strCity, "") & "'"
txtstrState.ControlSource = "='" & Nz(!strState, "") & "'"
txtstrZip.ControlSource = "='" & Nz(!strZip, "") & "'"
End With
This code has been working wonderfully at setting the subforms textbox
values to the recordset results based on the main form display.

However, when you introduce an apostrophe into the street name, it will
not display at all.

txtstrStreet.ControlSource = "='" & Nz(!strStreet, "") & ""

is the offending line of code. I'm mostly a web guy, so I'm not
familiar with how to get access to do a workaround for displaying
apostrophes in the text box.

So when !strStreet = Ferrys Ridge RD it works. But when !strStreet =
Ferry's Ridge Rd it dies.

There has to be a work around for this, other than telling the users to
"not use apostrophes in street names"? I can't seem to find one yet.

Thanks for the help

-Trevor


Nov 13 '05 #10
David W. Fenton wrote:
I'm not sure what the point is entirely. I see no justification for
assigning a *controlsource* with a literal value in code. Just set
the value of the control and be done with it.


I've actually done this a number of times. Scenario being a report/form
for which the user picks and chooses what s/he wants displayed. Say a
form can display up to 8 different fields of information but there are
several dozen bits of information that can be displayed. A pop up or
something generated by a right click menu or button somewhere lists the
possible fields that can be displayed, ex:

Mileage
Licence/Tag
Owner
Location ID
Department
Building
etc, etc, including "Show Nothing"

USer picks what s/he wants to see and the code reassigns the
controlsource of the text box in question. Perhaps also throw in some
code to adjust the display width of the text boxes if a number of "Show
Nothings" have been selected.

But, in any event, I think the OP may be a relative newbie at this and I
know when I'm not familiar with a program, I often do things in the most
difficult way possible... 8)
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #11
Tim Marshall <TI****@PurplePandaChasers.Moertherium> wrote in
news:dj**********@coranto.ucs.mun.ca:
David W. Fenton wrote:
I'm not sure what the point is entirely. I see no justification
for assigning a *controlsource* with a literal value in code.
Just set the value of the control and be done with it.
I've actually done this a number of times. Scenario being a
report/form for which the user picks and chooses what s/he wants
displayed. Say a form can display up to 8 different fields of
information but there are several dozen bits of information that
can be displayed. A pop up or something generated by a right
click menu or button somewhere lists the possible fields that can
be displayed, ex:

Mileage
Licence/Tag
Owner
Location ID
Department
Building
etc, etc, including "Show Nothing"

USer picks what s/he wants to see and the code reassigns the
controlsource of the text box in question. Perhaps also throw in
some code to adjust the display width of the text boxes if a
number of "Show Nothings" have been selected.


But you're assigning a field in the recordsource as the
controlsource, right? That I can understand.

What I can't understand is setting the controlsource to a literal
value, which of necessity causes problems when there are quation
marks in the literal value being assigned, unless you process it to
get around the problem.

But if you just set the value of the control, rather than the
controlsource, the quotes wouldn't matter.
But, in any event, I think the OP may be a relative newbie at this
and I know when I'm not familiar with a program, I often do things
in the most difficult way possible... 8)


The OP is someone who has been programming in Access 2 for many
years and has just switched to A97. I'm not sure whether there are
differences in quote handling between the two versions.

But I am still unable to conceive of a sitaution where there is any
utility of assigning a string value as a control source.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: kea | last post by:
I have a checkbox and a textbox in a continuous taborder on a form along with a bunch of other controls. The textbox is disabled. When I leave the checkbox I want to enable the textbox and move...
2
by: Dica | last post by:
i want to allow my "Cut" command to dynamically determine the active control and cut any selected text from it (assuming it's a textBox control). so far i've got this: private void...
7
by: Grant | last post by:
I have this table on my form which gets populated with data from my database at runtime. I would like to place some controls (linkbutton, textbox etc.) beneath the table when it is complete - at...
8
by: Donald Xie | last post by:
Hi, I noticed an interesting effect when working with controls that are dynamically loaded. For instance, on a web form with a PlaceHolder control named ImageHolder, I dynamically add an image...
4
by: Bas Groeneveld | last post by:
I am developing an ASP.NET application part of which consists of a data entry wizard defined by entries in a data table - ie the controls on each page of the wizard are determined by definitions in...
2
by: Quentin Huo | last post by:
Hi: I have a user control and I want to dynamically create a Label control and TextBox control when the button "add more author name" is clicked. I did like this: 1. there is a...
2
by: buran | last post by:
Dear ASP.NET Programmers, I have a HTML table (running as server control with the control ID: tblInsertSP). The table has 16 rows with textboxes. Depending on the value of the ddlSPType, which...
2
by: Chad | last post by:
I have a problem that I am desperate to understand. It involves dynamically adding controls to a Table control that is built as a result of performing a database query. I am not looking to...
1
by: semomaniz | last post by:
I have a form where i have created the form dynamically. First i manually added a panel control to the web page. Then i added another panel dynamically and inside this panel i created tables. I have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.