Assistance needed with a small piece of code

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

re: Assistance needed with a small piece of code

"B Love" <blove2651@charter.net> wrote in
news:vu1p36jlc925ce@corp.supernews.com:

[color=blue]
> If Me!DefaultHairColor IsNull Then[/color]

probably IsDull(Me!DefaultHairColor)

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

re: Assistance needed with a small piece of code

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns9454BD5BEB59DFFDBA@130.133.1.4...[color=blue]
> "B Love" <blove2651@charter.net> wrote in
> news:vu1p36jlc925ce@corp.supernews.com:
>
>[color=green]
> > If Me!DefaultHairColor IsNull Then[/color]
>
> probably IsDull(Me!DefaultHairColor)
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

re: Assistance needed with a small piece of code

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns9454BD5BEB59DFFDBA@130.133.1.4...[color=blue]
> "B Love" <blove2651@charter.net> wrote in
> news:vu1p36jlc925ce@corp.supernews.com:
>
>[color=green]
> > If Me!DefaultHairColor IsNull Then[/color]
>
> probably IsDull(Me!DefaultHairColor)[/color]


You Britishers... such cheeky chaps, what!

re: Assistance needed with a small piece of code

"DFS" <nospam@nospam.com> wrote in news:vu29sc9mjbt8a9@corp.supernews.com:
[color=blue]
> "Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
> news:Xns9454BD5BEB59DFFDBA@130.133.1.4...[color=green]
>> "B Love" <blove2651@charter.net> wrote in
>> news:vu1p36jlc925ce@corp.supernews.com:
>>[color=darkred]
>> > If Me!DefaultHairColor IsNull Then[/color]
>>
>> probably IsDull(Me!DefaultHairColor)[/color]
>
> You Britishers... such cheeky chaps, what![/color]

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)

re: Assistance needed with a small piece of code

"DFS" wrote
[color=blue]
> You Britishers... such cheeky chaps, what![/color]

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".

re: Assistance needed with a small piece of code

"Larry Linson" <bouncer@localhost.not> wrote in message
news:RHbEb.30567$xO.5636@nwrddc02.gnilink.net...[color=blue]
> "DFS" wrote
>[color=green]
> > You Britishers... such cheeky chaps, what![/color]
>
> 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".[/color]

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

re: Assistance needed with a small piece of code

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns9455B2E0FC65FFDBA@130.133.1.4...[color=blue]
> "DFS" <nospam@nospam.com> wrote in news:vu29sc9mjbt8a9@corp.supernews.com:
>[color=green]
> > "Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
> > news:Xns9454BD5BEB59DFFDBA@130.133.1.4...[color=darkred]
> >> "B Love" <blove2651@charter.net> wrote in
> >> news:vu1p36jlc925ce@corp.supernews.com:
> >>
> >> > If Me!DefaultHairColor IsNull Then
> >>
> >> probably IsDull(Me!DefaultHairColor)[/color]
> >
> > You Britishers... such cheeky chaps, what![/color]
>
> 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.[/color]


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!

re: Assistance needed with a small piece of code

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" <MissingAddress@Invalid.Com> wrote in message
news:Xns9455B2E0FC65FFDBA@130.133.1.4...[color=blue]
> "DFS" <nospam@nospam.com> wrote in news:vu29sc9mjbt8a9@corp.supernews.com:
>[color=green]
> > "Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
> > news:Xns9454BD5BEB59DFFDBA@130.133.1.4...[color=darkred]
> >> "B Love" <blove2651@charter.net> wrote in
> >> news:vu1p36jlc925ce@corp.supernews.com:
> >>
> >> > If Me!DefaultHairColor IsNull Then
> >>
> >> probably IsDull(Me!DefaultHairColor)[/color]
> >
> > You Britishers... such cheeky chaps, what![/color]
>
> 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)[/color]

re: Assistance needed with a small piece of code

"Fred Boer" <fredboer1@STOPyahooSPAM.com> wrote in
news:3fe1bf00$1@news.sentex.net:
[color=blue]
> Ah ha! I can put another push-pin in my map of Canadian
> "Access" people...
>
> Greetings from Guelph (home) and Brantford (work)...
>
> Fred Boer
>[/color]
Pop one in to the westernmost tip of Montreal island. Label it with
my name.

Bob Quintal

re: Assistance needed with a small piece of code

Bonjour! Ok.. you get a blue push-pin!

Fred

"Bob Quintal" <bquintal@generation.net> wrote in message
news:44d562e28bf8d86dc53842908d2e579c@news.teranew s.com...[color=blue]
> "Fred Boer" <fredboer1@STOPyahooSPAM.com> wrote in
> news:3fe1bf00$1@news.sentex.net:
>[color=green]
> > Ah ha! I can put another push-pin in my map of Canadian
> > "Access" people...
> >
> > Greetings from Guelph (home) and Brantford (work)...
> >
> > Fred Boer
> >[/color]
> Pop one in to the westernmost tip of Montreal island. Label it with
> my name.
>
> Bob Quintal[/color]

re: Assistance needed with a small piece of code

"DFS" <nospam@nospam.com> wrote in message news:<vu3d5o5tuukmc2@corp.supernews.com>...[color=blue]
> "Larry Linson" <bouncer@localhost.not> wrote in message
> news:RHbEb.30567$xO.5636@nwrddc02.gnilink.net...[color=green]
> > "DFS" wrote
> >[color=darkred]
> > > You Britishers... such cheeky chaps, what![/color]
> >
> > 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".[/color]
>
> Whoops. Can't remember why I thought he was from Merry Old...[/color]

It's that whole vowel shift thing...

(Former Toronto Resident)

re: Assistance needed with a small piece of code

"Fred Boer" <fredboer1@STOPyahooSPAM.com> wrote:
[color=blue]
>Bonjour! Ok.. you get a blue push-pin![/color]

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

re: Assistance needed with a small piece of code

> >Bonjour! Ok.. you get a blue push-pin![color=blue]
>
> Can you put a small fleurs-de-lis on the head of the push pin? <smile>
>
> Tony[/color]

It's a fleur de lys, moron.

re: Assistance needed with a small piece of code

thesavage444@yahoo.com (Alex) wrote:
[color=blue][color=green][color=darkred]
>> >Bonjour! Ok.. you get a blue push-pin![/color]
>>
>> Can you put a small fleurs-de-lis on the head of the push pin? <smile>[/color][/color]
[color=blue]
>It's a fleur de lys, moron.[/color]

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

re: Assistance needed with a small piece of code

> What is more definitive that the website of the Premier of Quebec? Or am I missing[color=blue]
> something?[/color]

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.



[color=blue][color=green][color=darkred]
> >> Can you put a small fleurs-de-lis on the head of the push pin? <smile>[/color][/color]
>[color=green]
> >It's a fleur de lys, moron.[/color]
>
> 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[/color]

re: Assistance needed with a small piece of code

thesavage444@yahoo.com (Alex) wrote:
[color=blue][color=green]
>> What is more definitive that the website of the Premier of Quebec? Or am I missing
>> something?[/color]
>
>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.[/color]

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

re: Assistance needed with a small piece of code

thesavage444@yahoo.com (Alex) wrote:
[color=blue]
>It's a fleur de lys, moron.[/color]

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

re: Assistance needed with a small piece of code

Tony Toews <ttoews@telusplanet.net> wrote:
[color=blue]
>thesavage444@yahoo.com (Alex) wrote:
>[color=green]
>>It's a fleur de lys, moron.[/color]
>
>BTW you'd be a much better ambassador for the French language and culture if you were
>a lot politer.[/color]

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