By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Assistance needed with a small piece of code

P: n/a
I just need a small mod to my code (I think) to get the result I want.
Earlier this week I had received help from FredG on concatenating a field in
a form. I went on to create a field in the form to collect the temporary
concatenation and store it to the underlying table. I am certain that this
breaks a few rules of good database practices, but believe me, it was
necessary.

What I am trying to accomplish now is test one of the two fields I wish to
concatenate to see if it is Null or not, and based upon the result either
store just one field (NewLongDescription in the instance where the other
field (DefaultHairColor) is Null) or store the concatenated field (when
DefaultHairColor is not Null). Below is what I tried to use to accomplish
this. My coding abilities are rather weak, so you might see a fundemantal
flaw in what I have written. If not, read further for additional background.

Private Sub NewLongDescription_LostFocus()

If Me!DefaultHairColor IsNull Then

Me!NewLongDescription.Requery

Me![LongDescription] = Me![NewLongDescription]

Else

Me!NewLongDescription.Requery

Me![LongDescription] = Me![ConcatDescript]

End If

End Sub

Under the direction of FredG in this Newsgroup, I created an unbound
calculating form field with the expression:

=[NewLongDescription] & " " & "Stock Hair Color (if any): " &
[DefaultHairColor]

I was able to make a calculated (concatenated) field. In my application I
really needed to make this calculated field a field that would be recorded
to the underlying table. With some minor coding, I was able to accomplish
this. I realize that in most circumstances this is probably not the proper
way to build a database, but honestly, it was appropriate in this case.

If you are interested, here is what I did:

When either NewLongDescription or DefaultHairColor lost focus, the following
now occurs:

Private Sub DefaultHairColor_LostFocus()
Me!DefaultHairColor.Requery
Me![LongDescription] = Me![ConcatDescript]
End Sub

Private Sub NewLongDescription_LostFocus()
Me!NewLongDescription.Requery
Me![LongDescription] = Me![ConcatDescript]
End Sub

The result is that the calculated (unbound) field (ConcatDescript) gets
loaded into a bound field called LongDescription when either of the two user
entry fields lose focus after an update (NewLongDescription
orDefaultHairColor). Bottom line is that it works thus far (is loaded into
the underlying table). Now I want the concatenation to take place only if
DefaultHairColor is not Null.

Thanks again for any input from this group!
~Bruce

Nov 12 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
"B Love" <bl*******@charter.net> wrote in
news:vu************@corp.supernews.com:

If Me!DefaultHairColor IsNull Then


probably IsDull(Me!DefaultHairColor)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #2

P: n/a
Lyle,

Thanks! That did the Trick (except for the IsDull comment!). Specifically,
I used:

If IsNull(Me!DefaultHairColor) Then ..............

Again, thanks so much! It works great now!
~Bruce
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"B Love" <bl*******@charter.net> wrote in
news:vu************@corp.supernews.com:

If Me!DefaultHairColor IsNull Then


probably IsDull(Me!DefaultHairColor)

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Nov 12 '05 #3

P: n/a
DFS
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"B Love" <bl*******@charter.net> wrote in
news:vu************@corp.supernews.com:

If Me!DefaultHairColor IsNull Then


probably IsDull(Me!DefaultHairColor)

You Britishers... such cheeky chaps, what!


Nov 12 '05 #4

P: n/a
"DFS" <no****@nospam.com> wrote in news:vu************@corp.supernews.com:
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"B Love" <bl*******@charter.net> wrote in
news:vu************@corp.supernews.com:
> If Me!DefaultHairColor IsNull Then


probably IsDull(Me!DefaultHairColor)


You Britishers... such cheeky chaps, what!


O Canada! Terre de nos aïeux,
Ton front est ceint de fleurons glorieux!

Car ton bras sait porter l'épée,
Il sait porter la croix!

Ton histoire est une épopée
Des plus brillants exploits.

Et ta valeur, de foi trempée,
Protégera nos foyers et nos droits.

Protégera nos foyers et nos droits.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5

P: n/a
"DFS" wrote
You Britishers... such cheeky chaps, what!


It'll be interesting to see Lyle's reply to this -- he's Canadian. I'm not
at all certain how Canadians view being called "Britishers".
Nov 12 '05 #6

P: n/a
DFS

"Larry Linson" <bo*****@localhost.not> wrote in message
news:RH*****************@nwrddc02.gnilink.net...
"DFS" wrote
> You Britishers... such cheeky chaps, what!


It'll be interesting to see Lyle's reply to this -- he's Canadian. I'm not
at all certain how Canadians view being called "Britishers".


Whoops. Can't remember why I thought he was from Merry Old...


Nov 12 '05 #7

P: n/a
DFS

"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn******************@130.133.1.4...
"DFS" <no****@nospam.com> wrote in news:vu************@corp.supernews.com:
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"B Love" <bl*******@charter.net> wrote in
news:vu************@corp.supernews.com:

> If Me!DefaultHairColor IsNull Then

probably IsDull(Me!DefaultHairColor)


You Britishers... such cheeky chaps, what!


O Canada! Terre de nos aïeux,
Ton front est ceint de fleurons glorieux!

Car ton bras sait porter l'épée,
Il sait porter la croix!

Ton histoire est une épopée
Des plus brillants exploits.

Et ta valeur, de foi trempée,
Protégera nos foyers et nos droits.

Protégera nos foyers et nos droits.

Ah! A Canuck. My mistake. Here's a right pretty song you might have
heard...

O, I wish I was in the land of cotton
Old times there are not forgotten
Look away! Look away!
Look away! Dixie Land.

In Dixie Land where I was born in
Early on one frosty mornin'
Look away! Look away!
Look away! Dixie Land.

Chorus:
O, I wish I was in Dixie!
Hooray! Hooray!
In Dixie Land I'll take my stand
To live and die in Dixie
Away, away,
Away down south in Dixie!

Nov 12 '05 #8

P: n/a
Ah ha! I can put another push-pin in my map of Canadian "Access" people...

Greetings from Guelph (home) and Brantford (work)...

Fred Boer
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn******************@130.133.1.4...
"DFS" <no****@nospam.com> wrote in news:vu************@corp.supernews.com:
"Lyle Fairfield" <Mi************@Invalid.Com> wrote in message
news:Xn*******************@130.133.1.4...
"B Love" <bl*******@charter.net> wrote in
news:vu************@corp.supernews.com:

> If Me!DefaultHairColor IsNull Then

probably IsDull(Me!DefaultHairColor)


You Britishers... such cheeky chaps, what!


O Canada! Terre de nos aïeux,
Ton front est ceint de fleurons glorieux!

Car ton bras sait porter l'épée,
Il sait porter la croix!

Ton histoire est une épopée
Des plus brillants exploits.

Et ta valeur, de foi trempée,
Protégera nos foyers et nos droits.

Protégera nos foyers et nos droits.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

Nov 12 '05 #9

P: n/a
"Fred Boer" <fr*******@STOPyahooSPAM.com> wrote in
news:3f********@news.sentex.net:
Ah ha! I can put another push-pin in my map of Canadian
"Access" people...

Greetings from Guelph (home) and Brantford (work)...

Fred Boer

Pop one in to the westernmost tip of Montreal island. Label it with
my name.

Bob Quintal
Nov 12 '05 #10

P: n/a
Bonjour! Ok.. you get a blue push-pin!

Fred

"Bob Quintal" <bq******@generation.net> wrote in message
news:44******************************@news.teranew s.com...
"Fred Boer" <fr*******@STOPyahooSPAM.com> wrote in
news:3f********@news.sentex.net:
Ah ha! I can put another push-pin in my map of Canadian
"Access" people...

Greetings from Guelph (home) and Brantford (work)...

Fred Boer

Pop one in to the westernmost tip of Montreal island. Label it with
my name.

Bob Quintal

Nov 12 '05 #11

P: n/a
"DFS" <no****@nospam.com> wrote in message news:<vu************@corp.supernews.com>...
"Larry Linson" <bo*****@localhost.not> wrote in message
news:RH*****************@nwrddc02.gnilink.net...
"DFS" wrote
> You Britishers... such cheeky chaps, what!


It'll be interesting to see Lyle's reply to this -- he's Canadian. I'm not
at all certain how Canadians view being called "Britishers".


Whoops. Can't remember why I thought he was from Merry Old...


It's that whole vowel shift thing...

(Former Toronto Resident)
Nov 12 '05 #12

P: n/a
"Fred Boer" <fr*******@STOPyahooSPAM.com> wrote:
Bonjour! Ok.. you get a blue push-pin!


Can you put a small fleurs-de-lis on the head of the push pin? <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #13

P: n/a
> >Bonjour! Ok.. you get a blue push-pin!

Can you put a small fleurs-de-lis on the head of the push pin? <smile>

Tony


It's a fleur de lys, moron.
Nov 12 '05 #14

P: n/a
th**********@yahoo.com (Alex) wrote:
>Bonjour! Ok.. you get a blue push-pin!
Can you put a small fleurs-de-lis on the head of the push pin? <smile>

It's a fleur de lys, moron.


http://www.premier.gouv.qc.ca/secteu...mblemes_en.htm
Under the section laballed flag it states " Its fleurs-de-lis pointed toward the
centre, recalling a flag said to have accompanied Montcalm's army during its victory
in 1758 over the British troops at Fort Carillon (now Ticonderoga, New York). "

What is more definitive that the website of the Premier of Quebec? Or am I missing
something?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #15

P: n/a
> What is more definitive that the website of the Premier of Quebec? Or am I missing
something?


The dictionary:

http://www.francophonie.hachette-liv...SCIP.LL0181400

"Fleur" is singular the way you phrased it. Moreover, your
hyphenation breaks all rules of the "trait d'union," namely, that the
French hyphen is only used within composed (compound) words, of which
Fleur de lys clearly ARE not.

The rule is exactly the same in English. Therefore, the site is
wrong. There is no Quebécois dictionary as of yet. Thinking the
premier is an authority in French is as moronic as thinking Bush to be
an authority in English.

Can you put a small fleurs-de-lis on the head of the push pin? <smile>

It's a fleur de lys, moron.


http://www.premier.gouv.qc.ca/secteu...mblemes_en.htm
Under the section laballed flag it states " Its fleurs-de-lis pointed toward the
centre, recalling a flag said to have accompanied Montcalm's army during its victory
in 1758 over the British troops at Fort Carillon (now Ticonderoga, New York). "

What is more definitive that the website of the Premier of Quebec? Or am I missing
something?

Tony

Nov 12 '05 #16

P: n/a
th**********@yahoo.com (Alex) wrote:
What is more definitive that the website of the Premier of Quebec? Or am I missing
something?


The dictionary:

http://www.francophonie.hachette-liv...SCIP.LL0181400

"Fleur" is singular the way you phrased it. Moreover, your
hyphenation breaks all rules of the "trait d'union," namely, that the
French hyphen is only used within composed (compound) words, of which
Fleur de lys clearly ARE not.

The rule is exactly the same in English. Therefore, the site is
wrong. There is no Quebécois dictionary as of yet. Thinking the
premier is an authority in French is as moronic as thinking Bush to be
an authority in English.


Ok, well that was the best authority I could think of. <shrug> I suggest you take
it up with the Premiers office then.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #17

P: n/a
th**********@yahoo.com (Alex) wrote:
It's a fleur de lys, moron.


BTW you'd be a much better ambassador for the French language and culture if you were
a lot politer.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #18

P: n/a
Tony Toews <tt****@telusplanet.net> wrote:
th**********@yahoo.com (Alex) wrote:
It's a fleur de lys, moron.


BTW you'd be a much better ambassador for the French language and culture if you were
a lot politer.


I should've said French language and Quebec culture.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.