NOTINLIST

[Scott]

After three days I'm starting to beat my head against the desk....Here Goes!

I have three tables 2 of which are linked to a third in a many-many
relationship. I have a complaint table which holds the fields for the
complaint form. I have a subject table which holds the fields for the subject
details(name, dob, address etc...) And I have a Involvedsubjects table which
holds the combined fields of both tables. That's the meat.

I have a subform imbedded in the complaint form that allows me to input each
subject that is involved in each complaint . This subform is based on the
involvedsubjects table. I have a combobox that allows me to lookup the names
in the Subject table to make entry quick and easy. The combobox is getting
its data from the subject table through a query.

My problem is that the notinlist event is not working. The event opens my
Subject input form to allow me to enter the details of a new subject but when
I exit the form it says "Subject Not Added" (that's what appears in the
msgbox I have in the code) However when I open the Subject table the new data
is there. The combox does not contain the new data.

Confusing...probably. some questions might clarify it.

Thanks

Scott

 

[Beetle]

Can you post the code you are using in your Not In List procedure?

 

[Scott]

Here is the code I am using. It is based on a code that I have gotten from
Ken Sheridan earlier:


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

Dim ctrl As Control
Dim strMessage As String

Set ctrl = Me.ActiveControl
strMessage = "ADD " & NewData & " TO THE DATABASE?"

If MsgBox(strMessage, vbYesNo + vbQuestion) = vbYes Then
DoCmd.OpenForm "NewSubjectInputF", _
DataMode:=acFormAdd, _
WindowMode:=acDialog, _
OpenArgs:=NewData
' ensure frmCities closed
DoCmd.Close acForm, "NewSubjectInputF"
' ensure city has been added
If Not IsNull(DLookup("SubjId", "Subject", "SubjectInputCombo = """
& _
NewData & """")) Then
Response = acDataErrAdded
Else
strMessage = NewData & " WAS NOT ADDED TO THE DATABASE."
MsgBox strMessage, vbInformation, "Warning"
Response = acDataErrContinue
ctrl.Undo
End If
Else
Response = acDataErrContinue
ctrl.Undo
End If

My tables are as follows:

Table: "Complaint"
Table: "Subject"
Table: "SubjectInvolved"---This is the table that links the other two
together in the many to many relationship.

Thanks

Scott

 

[Arvin Meyer MVP]

Here is some generic NotInList code:

http://www.datastrat.com/Code/NotInListCode.txt

and a sample of how to use it:

http://www.accessmvp.com/Arvin/NotInListDemo.zip

 

[Beetle]

This line;

If Not IsNull(DLookup("SubjId", "Subject", "SubjectInputCombo = """ & _
NewData & """")) Then
Response = acDataErrAdded

looks wrong to me. The criteria for your DLookup is attempting to compare
the data in the combo box to NewData, which is wrong for a couple of reasons.

First, the combo box won't contain the NewData value until after it is
requeried, which doesn't happen until the next line in the code
(acDataErrAdded).

Second, presumably when a user types a new value in the combo box they are
typing a subject name, not an ID number, so NewData will be the SubjName
not the SubjID. So, the criteria in your DLookup should look for a SubjName in
the table that matches NewData. The criteria you're using now will result in
the
DLookup always returning null, so your else condition will always run and you
will always get the "Data Not Added" message box.

Change that line to;

If Not IsNull(DLookup("SubjId", "Subject", "SubjName = """ & _
NewData & """")) Then
Response = acDataErrAdded

using your actual field name in place of SubjName. See if that solves your
issue.

 

[Scott]

Perfect guys. Thanks for your help!

I was beginning to wear a hole in the desk not to mention my head hurts.

Scott