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 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
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.
Oops. Sorry.
I get it. The apostrophe's already there. Misread the OP.
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
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
"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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |