Force capitalization in NotInList event code?

  • Thread starter Thread starter rocketD
  • Start date Start date
R

rocketD

Hi All,

I have a form with a set of combo boxes that pull values from lookup
tables and enter them in a main table for the given record. They run
on NotInList events so that users can add values on the fly. I'I
tried to convert new entries to capitalized values before they are
entered in the lookup tables by the code, but so far have been
unsuccessful; I'm not VBA savvy. Anyone who is, please help! Thanks!

Here is my code for a combobox:

'If UWW typed in is not in the list for the selected area, add it.
Private Sub uww_NotInList(NewData As String, Response As Integer)

Dim ctl As Control
Dim strSQL As String
Set ctl = Me!uww

If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then

Response = acDataErrAdded

strSQL = "INSERT INTO lkpChargeCode(uwwNum, areaID) VALUES('"
strSQL = strSQL & NewData & "', " & Me.area & ");"
CurrentDb.Execute strSQL
Debug.Print strSQL

Else
Response = acDataErrContinue
ctl.Undo
End If

End Sub

'Refresh form after a value is selected for UWW
Private Sub uww_AfterUpdate()
Me.uww = UCase(Me.uww)
Me.Refresh
End Sub
 
This should do it:

strSQL = strSQL & StrConv(NewData,3) & "', " & Me.area & ");"

Ken Sheridan
Stafford, England
 
This should do it:

strSQL = strSQL & StrConv(NewData,3) & "', " & Me.area & ");"

Ken Sheridan
Stafford, England















- Show quoted text -
Click to expand...

Joy, it worked perfectly! I searched Help for StrConv() and found
that the property I needed was actually 1, to make everything
uppercase. Thanks so much, I would never have gotten it without your
help.
 
Another thing you could do to force all input to upper case is put the
following in the control's KeyPress event procedure:

Dim strCharacter As String

' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))

Ken Sheridan
Stafford, England
 
Back
Top