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

Problems with Code

P: n/a
Hi All,

Have added a Strconv function to the AfterUpdate event of a field to give me
first letter capitalization (vbProperCase). I have added a msgbox that will
allow me to over-ride where necessary however it does not work.

The code below will allow me to manually type in capitals into the full
string when I hit the "Yes" button, but when I elect "No" the vbProperCase
is not activated and no change is made. Can anyone please help me with my
code, I am teaching myself VBA very slowly and finding it a bit frustrating

Code as follows

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards

Zellan
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
"Zellan" <Ze****@hotmail.com> wrote in message
news:42***********************@news.optusnet.com.a u...

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If


Untested, but along the lines of:

If MsgBox "Override Capitalization", vbYesNo = vbNo Then
Do something
Else
Do something else
End If

HTH - Keith.
www.keithwilby.com
Nov 13 '05 #2

P: n/a
On Fri, 27 May 2005 19:39:00 +1000, Zellan wrote:
Hi All,

Have added a Strconv function to the AfterUpdate event of a field to give me
first letter capitalization (vbProperCase). I have added a msgbox that will
allow me to over-ride where necessary however it does not work.

The code below will allow me to manually type in capitals into the full
string when I hit the "Yes" button, but when I elect "No" the vbProperCase
is not activated and no change is made. Can anyone please help me with my
code, I am teaching myself VBA very slowly and finding it a bit frustrating

Code as follows

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards

Zellan


You need to use the MsgBox() function.
Also vbYesNo is a Number constant, so no quotes.

If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
etc.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #3

P: n/a
Thanks for the reply however this does not allow me to have a Yes/No option
just the "OK" button only and the functionality will give me the
vbProperCase only and not allow me to override the text string.

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:6t****************************@40tude.net...
On Fri, 27 May 2005 19:39:00 +1000, Zellan wrote:
Hi All,

Have added a Strconv function to the AfterUpdate event of a field to give
me
first letter capitalization (vbProperCase). I have added a msgbox that
will
allow me to over-ride where necessary however it does not work.

The code below will allow me to manually type in capitals into the full
string when I hit the "Yes" button, but when I elect "No" the
vbProperCase
is not activated and no change is made. Can anyone please help me with my
code, I am teaching myself VBA very slowly and finding it a bit
frustrating

Code as follows

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards

Zellan


You need to use the MsgBox() function.
Also vbYesNo is a Number constant, so no quotes.

If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
etc.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 13 '05 #4

P: n/a
Thanks Keith,

Tried it but it doesnt give me the full functionality, provides an "OK"
message only and executes vbProperCase - wont allow me to override the text
Zellan

"Keith" <ke*********@baeAWAYWITHITsystems.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
"Zellan" <Ze****@hotmail.com> wrote in message
news:42***********************@news.optusnet.com.a u...

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If


Untested, but along the lines of:

If MsgBox "Override Capitalization", vbYesNo = vbNo Then
Do something
Else
Do something else
End If

HTH - Keith.
www.keithwilby.com

Nov 13 '05 #5

P: n/a
On Sat, 28 May 2005 09:04:11 +1000, Zellan wrote:
Thanks for the reply however this does not allow me to have a Yes/No option
just the "OK" button only and the functionality will give me the
vbProperCase only and not allow me to override the text string.

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:6t****************************@40tude.net...
On Fri, 27 May 2005 19:39:00 +1000, Zellan wrote:
Hi All,

Have added a Strconv function to the AfterUpdate event of a field to give
me
first letter capitalization (vbProperCase). I have added a msgbox that
will
allow me to over-ride where necessary however it does not work.

The code below will allow me to manually type in capitals into the full
string when I hit the "Yes" button, but when I elect "No" the
vbProperCase
is not activated and no change is made. Can anyone please help me with my
code, I am teaching myself VBA very slowly and finding it a bit
frustrating

Code as follows

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards

Zellan


You need to use the MsgBox() function.
Also vbYesNo is a Number constant, so no quotes.

If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
etc.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


I don't think you understood my reply.
Please post the entire event procedure.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #6

P: n/a
This is the code I have used and I receive the message with an "OK" button
only and am not able to override the code.

Private Sub Company_AfterUpdate()
If MsgBox("Override Capitalization", vbYesNo = vbNo) Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards and thanks for your help Fred

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:1w*******************************@40tude.net. ..
On Sat, 28 May 2005 09:04:11 +1000, Zellan wrote:
Thanks for the reply however this does not allow me to have a Yes/No
option
just the "OK" button only and the functionality will give me the
vbProperCase only and not allow me to override the text string.

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:6t****************************@40tude.net...
On Fri, 27 May 2005 19:39:00 +1000, Zellan wrote:

Hi All,

Have added a Strconv function to the AfterUpdate event of a field to
give
me
first letter capitalization (vbProperCase). I have added a msgbox that
will
allow me to over-ride where necessary however it does not work.

The code below will allow me to manually type in capitals into the full
string when I hit the "Yes" button, but when I elect "No" the
vbProperCase
is not activated and no change is made. Can anyone please help me with
my
code, I am teaching myself VBA very slowly and finding it a bit
frustrating

Code as follows

Private Sub Company_AfterUpdate()
MsgBox "Override Capitalization", vbYesNo
If "vbYesNo" = "No" Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards

Zellan

You need to use the MsgBox() function.
Also vbYesNo is a Number constant, so no quotes.

If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
etc.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


I don't think you understood my reply.
Please post the entire event procedure.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 13 '05 #7

P: n/a
On Sat, 28 May 2005 11:08:46 +1000, Zellan wrote:
This is the code I have used and I receive the message with an "OK" button
only and am not able to override the code.

Private Sub Company_AfterUpdate()
If MsgBox("Override Capitalization", vbYesNo = vbNo) Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards and thanks for your help Fred

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:1w*******************************@40tude.net. ..
On Sat, 28 May 2005 09:04:11 +1000, Zellan wrote:
Thanks for the reply however this does not allow me to have a Yes/No
option
just the "OK" button only and the functionality will give me the
vbProperCase only and not allow me to override the text string.

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:6t****************************@40tude.net...
On Fri, 27 May 2005 19:39:00 +1000, Zellan wrote:

> Hi All,
>
> Have added a Strconv function to the AfterUpdate event of a field to
> give
> me
> first letter capitalization (vbProperCase). I have added a msgbox that
> will
> allow me to over-ride where necessary however it does not work.
>
> The code below will allow me to manually type in capitals into the full
> string when I hit the "Yes" button, but when I elect "No" the
> vbProperCase
> is not activated and no change is made. Can anyone please help me with
> my
> code, I am teaching myself VBA very slowly and finding it a bit
> frustrating
>
> Code as follows
>
> Private Sub Company_AfterUpdate()
> MsgBox "Override Capitalization", vbYesNo
> If "vbYesNo" = "No" Then
> Company = StrConv(Company, vbProperCase)
>
> Else
>
> End If
>
> End Sub
>
> Regards
>
> Zellan

You need to use the MsgBox() function.
Also vbYesNo is a Number constant, so no quotes.

If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
etc.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.


I don't think you understood my reply.
Please post the entire event procedure.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

1) You have not correctly copied the code I gave you.
You moved the closing parenthesis.
You wrote:

If MsgBox("Override Capitalization", vbYesNo = vbNo) Then
etc.

vbYesNo has a value of 4.
vbNo has a value of 7
so your code equates to:
If MsgBox("Override Capitalization",4 = 7) Then ....
which Access equates to:
If MsgBox(""Override Capitalization",False) Then
...... because 4 does not = 7.
False has a value of 0.
vbOKOnly has a value of 0.
That's why you only get just OK on the message box instead of Yes and
No.

Please change it to what I wrote.
I wrote:
If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Now you will get both buttons.

2) Even after you get both buttons, you haven't told Access what to do
if Yes is clicked.
How is Access supposed to know unless you tell it?

Below is how your code should look.
All you need do is add code to the Else portion to tell access what to
do if Yes is selected.

Private Sub Company_AfterUpdate()
If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
Else
' Tell it what to do if Yes is clicked here
End If
End Sub
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
Nov 13 '05 #8

P: n/a
Thank you very much for the code... I really appreciate your reply... I am
only a relative starter to the world of VBA and this will help me
enourmously.

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:m0*****************************@40tude.net...
On Sat, 28 May 2005 11:08:46 +1000, Zellan wrote:
This is the code I have used and I receive the message with an "OK"
button
only and am not able to override the code.

Private Sub Company_AfterUpdate()
If MsgBox("Override Capitalization", vbYesNo = vbNo) Then
Company = StrConv(Company, vbProperCase)

Else

End If

End Sub

Regards and thanks for your help Fred

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:1w*******************************@40tude.net. ..
On Sat, 28 May 2005 09:04:11 +1000, Zellan wrote:

Thanks for the reply however this does not allow me to have a Yes/No
option
just the "OK" button only and the functionality will give me the
vbProperCase only and not allow me to override the text string.

Zellan

"fredg" <fg******@example.invalid> wrote in message
news:6t****************************@40tude.net...
> On Fri, 27 May 2005 19:39:00 +1000, Zellan wrote:
>
>> Hi All,
>>
>> Have added a Strconv function to the AfterUpdate event of a field to
>> give
>> me
>> first letter capitalization (vbProperCase). I have added a msgbox
>> that
>> will
>> allow me to over-ride where necessary however it does not work.
>>
>> The code below will allow me to manually type in capitals into the
>> full
>> string when I hit the "Yes" button, but when I elect "No" the
>> vbProperCase
>> is not activated and no change is made. Can anyone please help me
>> with
>> my
>> code, I am teaching myself VBA very slowly and finding it a bit
>> frustrating
>>
>> Code as follows
>>
>> Private Sub Company_AfterUpdate()
>> MsgBox "Override Capitalization", vbYesNo
>> If "vbYesNo" = "No" Then
>> Company = StrConv(Company, vbProperCase)
>>
>> Else
>>
>> End If
>>
>> End Sub
>>
>> Regards
>>
>> Zellan
>
> You need to use the MsgBox() function.
> Also vbYesNo is a Number constant, so no quotes.
>
> If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
> Company = StrConv(Company, vbProperCase)
> etc.
>
> --
> Fred
> Please only reply to this newsgroup.
> I do not reply to personal email.

I don't think you understood my reply.
Please post the entire event procedure.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

1) You have not correctly copied the code I gave you.
You moved the closing parenthesis.
You wrote:

If MsgBox("Override Capitalization", vbYesNo = vbNo) Then
etc.

vbYesNo has a value of 4.
vbNo has a value of 7
so your code equates to:
If MsgBox("Override Capitalization",4 = 7) Then ....
which Access equates to:
If MsgBox(""Override Capitalization",False) Then
..... because 4 does not = 7.
False has a value of 0.
vbOKOnly has a value of 0.
That's why you only get just OK on the message box instead of Yes and
No.

Please change it to what I wrote.
I wrote:
If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Now you will get both buttons.

2) Even after you get both buttons, you haven't told Access what to do
if Yes is clicked.
How is Access supposed to know unless you tell it?

Below is how your code should look.
All you need do is add code to the Else portion to tell access what to
do if Yes is selected.

Private Sub Company_AfterUpdate()
If MsgBox("Override Capitalization", vbYesNo) = vbNo Then
Company = StrConv(Company, vbProperCase)
Else
' Tell it what to do if Yes is clicked here
End If
End Sub
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.