Adding code for Propercase

[Paul Cooke]

Hi guys,

I have added some code to a combo box to add items to the list if they are not already there and would like to know if anyone could tell me what I would need to do to change the item added to Propercase so it is added to the table as "Home Choice" not "home choice"

The code I have already in the NotInlist event is

Private Sub RXProductionName_NotInList(NewData As String, Response As Integer)

On Error GoTo RXProductionName_NotInList_Err
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("The Production or Event name " & Chr(34) & NewData & _
Chr(34) & " is not currently listed." & vbCrLf & vbCrLf & _
"Is spelling correct? If so" & vbCrLf & _
"Would you like to add it to the list now?" _
, vbQuestion + vbYesNo, "Production / Event Name")
If intAnswer = vbYes Then
strSQL = "INSERT INTO ProductionsEventNames([ProductionName]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "The new Production / Event name has been added to the list." _
, vbInformation, "Production / Event Name"
Response = acDataErrAdded
Else
MsgBox "Please choose a Production / Event Name from the list." _
, vbInformation, "Production / Event Name"
Response = acDataErrContinue
End If
RXProductionName_NotInList_Exit:
Exit Sub
RXProductionName_NotInList_Err:
MsgBox Err.Description, vbCritical, "Error"
Resume RXProductionName_NotInList_Exit

End Sub

Any help would be gratefully appriciated!

Thanks guys

Paul

 

[pbaldy]

In the SQL, wrap NewData in a StrConv function (more info in VBA Help).

 

[Paul Cooke]

Thanks for the prompt resonse Paul (good name!!)

Although I'm not sure what you mean could you explain a bit for me?

Just so you know the orignal code was shown on the forum yonks ago I have just adapted it to me - I didn't actually write it myself

Thanks and regards

Paul

 

[pbaldy]

Isn't it the middle of the night over there? You must be a night owl.

Did you look in help at that function? Once you understand that, use it in this line, replacing NewData with the function (which will include NewData within it):

"VALUES ('" & NewData & "');"

 

[Paul Cooke]

Thanks again Paul I will research the help a bit more

(and yes it was the middle of the night !!)

Regards

Paul

 

[Dennisk]

use one of the readily available proper functions. this can then be used in any situation.

 

[geekay]

You can write a Public Fuction to convert your string to title case. You can call it anywhere in your project to convert a string to title case. If you can not write it yourself, please feel free to ask. I can give it for you.

I think it is better helping to fish than giving fish

 

[Paul Cooke]

Hi Guys thanks again for the responses

Geeykay I would love to take you up on your offer of the public function please please please !! After spending many many hours searching this forum as well as a million other websites I can't seem to find what I need (or more truthfully don't actually understand what I'm reading or how to do it!!)

your offer of help is greatly apprciated although may i ask that you could explain it as well so i can try to understand what goes on and learn for the future.

Thanks

Paul

 

[Brianwarnock]

There are a number of public functions for proper case in the forum.One is here
http://www.access-programmers.co.uk/forums/showthread.php?t=53376

but search under propercase and scan the threads will reveal others.

Brian

 

[Paul Cooke]

Thanks Brian,

I have actually read that post before and most of the others across the world !! My problem is I don't understand it - sorry but I'm just learning.

it is difficult to find some replies that are written in "plain" English although that is not a critisism on anyone who takes the time to reply more an acknowledgement of my lack of understanding if you know what I mean.

I always search first before posting and usually try to work it out myself but sometimes the only way to get an answer specific to you is to ask

Thanks again.

 

[pbaldy]

"VALUES ('" & StrConv(NewData , vbProperCase) & "');"

 

[Paul Cooke]

Brillient thanks Paul that works a treat could i be a pain and ask about how it is formatted in that way? I did try something similar in my efforts to work it out myself but again really did not understand what i was doing?

Thanks again, hopefully I can have an early night tonight now !!

 

[pbaldy]

Not sure what you're asking. It's formatted as required by the function, as described in Help.

 

[Paul Cooke]

I don't thikn I know what i'm asking either

Thanks very much for all your help though !!