notinlist routine adds data to combo box but I still get an error

[vircalendar via AccessMonster.com]

I've been reading posts about this for hours, and trying all kinds of things
that people have suggested, but I can't seem to fix my problem.

I have a Form called frmHistory that includes a combo box cboAttending. The
combo box pulls from a table called tblAttending. The table has two columns:
a text field called txtattending (column one) and an autonumber key field.
There is no defined relationship other than "limit to list" being set to yes.
When someone tries to enter an unrecognized name into the combo box, the
notinlist event triggers the following code:

Private Sub cboAttending(NewData As String, Response As Integer)
Dim intResponse As Integer
Dim strMsg As String

strMsg = NewData & " is not a known Referring Attending. Add him or her?"
intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Unknown Referrer")
Select Case intResponse

Case vbYes
DoCmd.OpenForm "frmNewDoc", acNormal, , , acFormAdd, acDialog
If IsLoaded("frmNewDoc") Then
Me![cboAttending] = Forms!frm_NewDoc![txtAttending]
DoCmd.Close acForm, "frmnewDoc"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


The code successfully adds the new name to both tblAttending and cboAttending,
but I still get the standard error message "The text you entered isn't an
item in the list." Any ideas about what I may be leaving out?

 

[vircalendar via AccessMonster.com]

One additional piece of info: the form frmNewDoc has on it two unbound text
boxes for first and last names. When the names have been added, there's a
routine that concatenates them and stores the result in the bound field
txtAttending. The table tblAttending is then requeried. Here's that code:

Private Sub Accept_Click()
Dim StrNewDoc As String
If IsNull(lastname) Or IsNull(firstname) Then
MsgBox "Please supply first and last names", vbOKOnly, "Error"
Exit Sub
Else
StrNewDoc = lastname & ", " & firstname
[txtattending] = StrNewDoc
Me.Visible = False
End If
End Sub

I've been reading posts about this for hours, and trying all kinds of things
that people have suggested, but I can't seem to fix my problem.

I have a Form called frmHistory that includes a combo box cboAttending. The
combo box pulls from a table called tblAttending. The table has two columns:
a text field called txtattending (column one) and an autonumber key field.
There is no defined relationship other than "limit to list" being set to yes.
When someone tries to enter an unrecognized name into the combo box, the
notinlist event triggers the following code:

Private Sub cboAttending(NewData As String, Response As Integer)
Dim intResponse As Integer
Dim strMsg As String

strMsg = NewData & " is not a known Referring Attending. Add him or her?"
intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Unknown Referrer")
Select Case intResponse

Case vbYes
DoCmd.OpenForm "frmNewDoc", acNormal, , , acFormAdd, acDialog
If IsLoaded("frmNewDoc") Then
Me![cboAttending] = Forms!frmNewDoc![txtAttending]
DoCmd.Close acForm, "frmnewDoc"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub

The code successfully adds the new name to both tblAttending and cboAttending,
but I still get the standard error message "The text you entered isn't an
item in the list." Any ideas about what I may be leaving out?

 

[Guest]

I can't see any obvious reason for the error consistently occurring but
because you are not passing any value to the frmNewDoc form you are relying
on the name entered in the form matching that entered in the combo box. A
better option would be to pass the value to the form as its OpenArgs
property. However, as the tblAttending table has only the one non-key column
you don't need to open the frmNewDoc form at all, simply add the row to the
table in code.

Its generally considered better to store the first and last names in
separate columns rather than in a single column, on the basis that
concatenation of values is simpler than parsing out values from a string
expression. However, you probably won't want to change this so, taking
account of the above comments, the code for the combo box control's NotInList
event procedure would go like this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = NewData & " is not a known Referring Attending. Add him or
her?"

strSQL = "INSERT INTO tblAttending (txtAttending) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

In circumstances where it is necessary to open a form to add other data than
that entered into the combo box I'd do it like this, passing the value to the
form:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = NewData & " is not a known Referring Attending. Add him or
her?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmNewDoc", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmNewDoc closed
DoCmd.Close acForm, "frmNewDoc"
' ensure row has been added
If Not IsNull(DLookup("YourIDColumn", "tblAttending", "txtAttending
= """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to the list."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

Then in frmNewDoc's Open event procedure put:

Dim strFullName as String
Dim intCommaPos as Integer

If Not IsNull(Me.OpenArgs) Then
strFullName = Me.OpenArgs
intCommaPos = Instr(strFullName,",")
Me.txtAttending.DefaultValue = """" & strFullName & """"
Me.LastName = Left$(strFullName,intCommaPos -1)
Me.FirstName = Trim(Mid$(strFullName,intCommaPos +1))
End If

Note that this sets the DefaultValue of a txtAttending control (which can be
hidden), not its Value property. This means that a new record is not
initiated until the user enters more data into the form, so if they decide to
abandon the new record before doing so they can simply close the form.

Note also that when setting the DefaultValue property of a control it should
always be delimited with quotes characters regardless of the data type. The
pair of quotes within quotes is interpreted by Access as a single literal
quotes character. Mostly this isn't strictly necessary but it can be
crucial in some cases, particularly with dates. Don't be tempted to use the
usual # date delimiter character in such cases; it will more often than not
give false results on systems using non-US date formats.

Finally, I'd be inclined to include some validation code in the NotInList
event procedure to ensure that the NewData value is in the correct format.
This is difficult with names as they can vary so much, e.g. 'Gheorghiu,
Angela and 'de los Angeles, Victoria' are both valid names, but of rather
different format (and the latter was a far better singer!). At the least you
could test for a comma, though:

If Instr(NewData,",") = 0 Then
' the value contains no comma s should be rejected
End If

Ken Sheridan
Stafford, England

I've been reading posts about this for hours, and trying all kinds of things
that people have suggested, but I can't seem to fix my problem.

I have a Form called frmHistory that includes a combo box cboAttending. The
combo box pulls from a table called tblAttending. The table has two columns:
a text field called txtattending (column one) and an autonumber key field.
There is no defined relationship other than "limit to list" being set to yes.
When someone tries to enter an unrecognized name into the combo box, the
notinlist event triggers the following code:

Private Sub cboAttending(NewData As String, Response As Integer)
Dim intResponse As Integer
Dim strMsg As String

strMsg = NewData & " is not a known Referring Attending. Add him or her?"
intResponse = MsgBox(strMsg, vbYesNo + vbQuestion, "Unknown Referrer")
Select Case intResponse

Case vbYes
DoCmd.OpenForm "frmNewDoc", acNormal, , , acFormAdd, acDialog
If IsLoaded("frmNewDoc") Then
Me![cboAttending] = Forms!frm_NewDoc![txtAttending]
DoCmd.Close acForm, "frmnewDoc"
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Case vbNo
Response = acDataErrContinue
End Select
End Sub


The code successfully adds the new name to both tblAttending and cboAttending,
but I still get the standard error message "The text you entered isn't an
item in the list." Any ideas about what I may be leaving out?

 

[vircalendar via AccessMonster.com]

Wow. Thanks a lot for your extremely detailed answer.

It's interesting that you say that you "can't see any obvious reson for the
error CONSISTENTLY occurring." because in fact it does not happen
consistently. It seems to work okay the first couple of times I test it
(after loading the whole database) but then starts popping up every time
thereafter. I thought that might help me pin down the problem, but so far,
it hasn't.

I'm intrigued by the first option you suggested, because it contains some
code I haven't seen before (though I'm admittedly a novice). Can you explain
to me what's happening in this line:

Set ctrl = Me.ActiveControl

and in this part:

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Thanks!

I can't see any obvious reason for the error consistently occurring but
because you are not passing any value to the frmNewDoc form you are relying
on the name entered in the form matching that entered in the combo box. A
better option would be to pass the value to the form as its OpenArgs
property. However, as the tblAttending table has only the one non-key column
you don't need to open the frmNewDoc form at all, simply add the row to the
table in code.

Its generally considered better to store the first and last names in
separate columns rather than in a single column, on the basis that
concatenation of values is simpler than parsing out values from a string
expression. However, you probably won't want to change this so, taking
account of the above comments, the code for the combo box control's NotInList
event procedure would go like this:

Dim cmd As ADODB.Command
Dim ctrl As Control
Dim strSQL As String, strMessage As String

Set ctrl = Me.ActiveControl
strMessage = NewData & " is not a known Referring Attending. Add him or
her?"

strSQL = "INSERT INTO tblAttending (txtAttending) VALUES(""" & _
NewData & """)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
cmd.CommandText = strSQL
cmd.Execute
Response = acDataErrAdded
Else
Response = acDataErrContinue
ctrl.Undo
End If

Set cmd = Nothing

In circumstances where it is necessary to open a form to add other data than
that entered into the combo box I'd do it like this, passing the value to the
form:

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = NewData & " is not a known Referring Attending. Add him or
her?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "frmNewDoc", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmNewDoc closed
DoCmd.Close acForm, "frmNewDoc"
' ensure row has been added
If Not IsNull(DLookup("YourIDColumn", "tblAttending", "txtAttending
= """ & _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " was not added to the list."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

Then in frmNewDoc's Open event procedure put:

Dim strFullName as String
Dim intCommaPos as Integer

If Not IsNull(Me.OpenArgs) Then
strFullName = Me.OpenArgs
intCommaPos = Instr(strFullName,",")
Me.txtAttending.DefaultValue = """" & strFullName & """"
Me.LastName = Left$(strFullName,intCommaPos -1)
Me.FirstName = Trim(Mid$(strFullName,intCommaPos +1))
End If

Note that this sets the DefaultValue of a txtAttending control (which can be
hidden), not its Value property. This means that a new record is not
initiated until the user enters more data into the form, so if they decide to
abandon the new record before doing so they can simply close the form.

Note also that when setting the DefaultValue property of a control it should
always be delimited with quotes characters regardless of the data type. The
pair of quotes within quotes is interpreted by Access as a single literal
quotes character. Mostly this isn't strictly necessary but it can be
crucial in some cases, particularly with dates. Don't be tempted to use the
usual # date delimiter character in such cases; it will more often than not
give false results on systems using non-US date formats.

Finally, I'd be inclined to include some validation code in the NotInList
event procedure to ensure that the NewData value is in the correct format.
This is difficult with names as they can vary so much, e.g. 'Gheorghiu,
Angela and 'de los Angeles, Victoria' are both valid names, but of rather
different format (and the latter was a far better singer!). At the least you
could test for a comma, though:

If Instr(NewData,",") = 0 Then
' the value contains no comma s should be rejected
End If

Ken Sheridan
Stafford, England

I've been reading posts about this for hours, and trying all kinds of things
that people have suggested, but I can't seem to fix my problem.

[quoted text clipped - 31 lines]

but I still get the standard error message "The text you entered isn't an
item in the list." Any ideas about what I may be leaving out?