Myujth

I have some combo boxes with code for adding new items to the source table with a form when it doesn't exist.

The code will replace Chr(47) / and Chr(92) with Chr(45) - if present. This is done because a file name is created using concatenation later.

The problem is if a character is replaced, I get an Access error that the item is not in the list. This does not happen if a character is not replaced. In both instances the correct items are added to the corresponding tables.

I have tried replacing the character before passing it to OpenArgs, AfterUpdate, on the form after it opens, etc. The error does not break so the program is working, I just want to eliminate a unnecessary pop-up message.

Any help is greatly appreciated.

Private Sub cboManual_NotInList(NewData As String, Response As Integer)
 Dim MyMessage As String
 Dim myButtons As Integer
 Dim myTitle As String
 Dim strSQL As String

On Error GoTo ErrHandler

MyMessage = "This Manual does not exist. Create it?"
myButtons = vbYesNo + vbDefaultButton1 + vbQuestion + vbApplicationModal
myTitle = "Add Manual?"
MyChoice = MsgBox(MyMessage, myButtons, myTitle)

If MyChoice = 6 Then
 If Not DBAuthority = "Admin" And Not DBAuthority = "Data Entry" Then
 Response = acDataErrContinue
 MsgBox "Sorry, authorized access only", _
 vbOKOnly, "Important Information"
 Exit Sub
 Else
 Response = acDataErrAdded
 CallerField = "Manual"
 CallerForm = "NewDocument"
 NewData = Replace(NewData, Chr(47), Chr(45))
 NewData = Replace(NewData, Chr(92), Chr(45))
 DoCmd.OpenForm "AddManual", windowmode:=acDialog, OpenArgs:=NewData
 Me.cboManual.RowSource = Me.cboManual.RowSource
 Me.cboManual.value = strAddManual
 strManual = Me.cboManual.value
 strAddManual = vbNullString
 Me.cboSection.value = strAddSection
 strSection = Me.cboSection.value
 strAddSection = vbNullString
 Me.cboEngine.value = strAddEngine
 strEngine = Me.cboEngine.value
 strAddEngine = vbNullString
 End If
ElseIf MyChoice = 7 Then
 Response = acDataErrContinue
 MsgBox "Select Manual from list.", vbOKOnly, "Select Manual"
 Me.cboManual.Undo
 Me.cboManual.SetFocus
 Exit Sub
End If
Exit Sub

ErrHandler:
 If Err = 20 Then
 Response = acDataErrContinue
 ElseIf Err = 94 Then
 Response = acDataErrContinue
 Resume Next
 ElseIf Err = 2237 Then
 Response = acDataErrContinue
 Resume Next
 ElseIf Err = 0 Then
 Response = acDataErrContinue
 Else
 MsgBox "cboManual.NotInList Err = " & Err.Number & " :" & Err.Description
 Exit Sub
 End If
 Exit Sub
End Sub

Option one
Replace while typing

Select Case KeyCode
 Case vbKeyDown
 Me![cboNewPart].Dropdown
 Case 220, 191 ' / and 
 KeyCode = 189 ' with -
 Case Else
 End Select

Option two
after adding the new value to the table. do

me.combo.undo, me.combo.requery. me.combo.value = newValue

followed by acDataErrContinue
this way you won't get error message but the list will flicker a and it's purely a hack.

Try using a different variable name (other than NewData) to store the modified version of the value passed to the NewData argument, i.e.:

Dim NewString as String
NewString = NewData
NewString = Replace(NewString, Chr(47), Chr(45))
NewString = Replace(NewString, Chr(92), Chr(45))
DoCmd.OpenForm "AddManual", windowmode:=acDialog, OpenArgs:=NewString

Since VBA arguments are passed ByRef unless otherwise stated, any modification to the argument value will be modifying the original value passed to your cboManual_NotInList event handler.

Given the above, you could alternatively try changing the NewData argument to be passed by value (ByVal):

Private Sub cboManual_NotInList(ByVal NewData As String, Response As Integer)